I have the following code in which I need to check something in the ELSE. The problem is how to form it correctly. I could use a cursor I guess to transverse through the records.
In the Else, I want to:
1) Check the count of the results of my statement. IF > 1 then check to see if m.original is between lowlimit and highlimit for any of those records found
2) If m.original is between the lowlimit and highlimit, then select Fee1 from FeeScheduleDetails
m.original and m.FeeSchedule is out here somewhere, just know this...it's part of the query that you don't see
CASE WHEN Len(c.FeeSchedule) < 3 then
CONVERT(int, c.feeSchedule)
ELSE
Select Count(*) FROM FeeScheduleDetails fd
INNER JOIN Master m ON m.FeeSchedule = fd.code
IF Count(*) > 3
Check to see if m.original is BETWEEN fd.lowlimit AND fd.highlimit
If yes, then bring me back fee1, if no then just bring me back m.FeeSchedule
END AS FeeSchedule
Master
FeeSchedule
FeeScheduleDetails
-
Code
LowLimit
HighLimit
Fee1
thanks for the heads up. Here's the entire query...and a new attept at fee1
INSERT INTO ReportingServer.dbo.DCR
SELECT
m.customer,
c.name,
c.customer,
c.state,
CASE WHEN Len(c.FeeSchedule) < 3 THEN
CONVERT(int, c.feeSchedule)
WHEN Len(c.FeeSchedule) > 3 THEN
SELECT fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit
ELSE
CONVERT(int, c.feeSchedule)
END AS FeeSchedule,
m.Branch,
CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then
(ph.totalpaid - ph.ForwardeeFee)
ELSE
0.00
END AS [Posted Amount],
ph.systemmonth,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
0 AS [New Old CC],
0 AS [New Old PDC],
'In-House' AS Type,
1 AS Active,
ph.UID,
m.number,
dc.amount CC,
p.amount AS PDC,
m.original,
CONVERT(money, ph.OverPaidAmt),
0,
0,
''
FROM dbo.Master m (NOLOCK) LEFT JOIN dbo.payhistory ph ON m.number = ph.number
LEFTJOIN dbo.DebtorCreditCards dc ON dc.number = m.number
LEFTJOIN dbo.pdc p ON p.number = m.number
LEFTJOIN dbo.Customer c ON c.Customer = m.Customer
LEFTJOIN Apex_ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer
GROUP BYm.customer,
c.name,
c.customer,
c.state,
c.FeeSchedule,
m.Branch,
ph.OverPaidAmt,
ph.systemmonth,
ph.datepaid,
ph.totalpaid,
ph.batchtype,
m.desk,
ph.UID,
m.number,
dc.amount,
p.amount,
m.original ,
ph.systemmonth,
ph.systemyear,
ph.ForwardeeFee
HAVINGph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND
ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate())
AND (c.Name is not null AND c.Name <> '')
ORDER BY m.customer
|||? Hi dba123, Thanks - but you didn't post CREATE TABLE and INSERT statements, so I still can't test any code, nor see what exactly you try to do. Anyway - I'll respond to your other post; it looks to be a simplified version of this problem. If you do need more help in this thread, then add some CREATE TABLE and INSERT statements and expected results, and I'll have a look at it. -- Hugo Kornelis, SQL Server MVP <dba123@.discussions.microsoft.com> schreef in bericht news:662f6401-ea73-4c37-a98e-dd9267c0ed2c@.discussions.microsoft.com... thanks for the heads up. Here's the entire query...and a new attept at fee1 INSERT INTO ReportingServer.dbo.DCR SELECT m.customer, c.name, c.customer, c.state, CASE WHEN Len(c.FeeSchedule) < 3 THEN CONVERT(int, c.feeSchedule) WHEN Len(c.FeeSchedule) > 3 THEN SELECT fd.Fee1 FROM FeeScheduleDetails fd where c.feeSchedule = fd.code AND m.original BETWEEN fd.LowLimit AND fd.HighLimit ELSE CONVERT(int, c.feeSchedule) END AS FeeSchedule, m.Branch, CASE WHEN ph.batchtype = 'PUR' OR ph.batchtype = 'PAR' OR ph.batchtype = 'PCR' Then (ph.totalpaid - ph.ForwardeeFee) ELSE 0.00 END AS [Posted Amount], ph.systemmonth, ph.datepaid, ph.totalpaid, ph.batchtype, m.desk, 0 AS [New Old CC], 0 AS [New Old PDC], 'In-House' AS Type, 1 AS Active, ph.UID, m.number, dc.amount CC, p.amount AS PDC, m.original, CONVERT(money, ph.OverPaidAmt), 0, 0, '' FROM dbo.Master m (NOLOCK) LEFT JOIN dbo.payhistory ph ON m.number = ph.number LEFT JOIN dbo.DebtorCreditCards dc ON dc.number = m.number LEFT JOIN dbo.pdc p ON p.number = m.number LEFT JOIN dbo.Customer c ON c.Customer = m.Customer LEFT JOIN Apex_ReportingServer.dbo.FeeGoal fg ON fg.CustomerID = c.Customer GROUP BY m.customer, c.name, c.customer, c.state, c.FeeSchedule, m.Branch, ph.OverPaidAmt, ph.systemmonth, ph.datepaid, ph.totalpaid, ph.batchtype, m.desk, ph.UID, m.number, dc.amount, p.amount, m.original , ph.systemmonth, ph.systemyear, ph.ForwardeeFee HAVING ph.systemmonth = datepart(mm, getdate()) AND ph.batchtype <> 'DA' AND ph.batchtype <> 'DAR' AND ph.systemyear = datepart(yy, getdate()) AND (c.Name is not null AND c.Name <> '') ORDER BY m.customer|||So are you asking for my table structure then? The table has already been created...so are you asking for the erd ?|||I am not sure what you are asking. I have also never seen a CASE statement used in this way. CASE statements are always used within a SELECT clause to select a result value to display in the column based on the value in a particular row. SQL has an IF condition of form:
IF.....
BEGIN
END
ELSE
BEGIN
END
Firstly I suggest you use IF that instead. Is your IF statement returning multiple values, or just one value? If it is just returning one value (and with a count(*) you normally only return one valuer, unless using a GROUP BY) then use and IF, otherwise use a case statement. IF example below)
declare @.result int
declare @.lowlimit int
declare @.highlimit int
IF Len(c.FeeSchedule) < 3
BEGIN
set @.result = CONVERT(int, c.feeSchedule)
END
ELSE
work out @.lowlimit and @.highlimit here.....
delcare @.cnt int
Select @.cnt = Count(*) FROM FeeScheduleDetails fd
INNER JOIN Master m ON m.FeeSchedule = fd.code
IF @.cntBETWEEN @.lowlimit AND @.highlimit
BEGIN
@.result = (get Fee1......)
END
ELSE
BEGIN
@.result = (get m.FeeSchedule.....)
END
END
Otherwise, if you want the second conditional to be a CASE statement, you will need to rewrite it accordingly
Clarity Consulting
|||I don't think you can do a select statment inside of a CASE statement. Store the result in a variable, and then select that in the CASE statement instead if possible. Otherwise, get the value from a JOIN instead, then you just need to select the column in the CASE...WHEN .. THEN statement.|||? Hi dba123, Sorry for the delayed reply... >>So are you asking for my table structure then? The table has already been created...so are you asking for the erd ? What I'm askking you for is a bunch of statements that I can copy and paste into Query Analyzer and run to recreate the problem. To give you a very simplified example: instead of asking "how to get the lowest wage for each department", you'd have to post some SQL: CREATE TABLE Personnel (EmpID int NOT NULL PRIMARY KEY, DeptID int NOT NULL, Wage decimal(7,2) NOT NULL) go INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (1, 1, 20000) INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (2, 1, 30000) INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (3, 2, 15000) INSERT INTO Personnel (EmpID, DeptID, Wage) VALUES (4, 3, 20000) go And then, you'd add the required results: DeptID LowestWage 1 20000 2 15000 3 20000 Anyone can now copy the SQL statements, execute them in a test database, try some queries and finally come up with this reply: SELECT DeptID, MIN(Wage) AS LowestWage FROM Personnel GROUP BY DeptID Of course, the example above is pretty basic. Your questions (at least the ones I've seen here) are a lot more complex. Trying to answer them without knowing exactly how the tables look (i.e. columns, datatypes, constraints, indexes, defaults, ... everything you'd see in a CREATE TABLE statement), what kind of data is in them (i.e. the INSERT statements) and what results you expect to get makes it more an exercise at guessing, or even mind reading, than an exercise in writing SQL. I'm pretty good at writing SQL. I think I help a lot of people with my skills in this and other forums and groups. But my clairvoyance skills are lousy. In other words: if you don't explain the problem clear enough, you're likely to get no answer or a wrong answer from me - and probably from others as well. From experience, I know that most probles are best explained by posting CREATE TABLE statements, INSERT statements, expected results and a short explanation. Check out www.aspfaq.com/5006 for more information about this and for some techniques that can help you assemble the information for your posts. I hope this helps. -- Hugo Kornelis, SQL Server MVP|||thanks, I was using the case statement to determine which to return back as FeeSchedule. It's dependent on the lenght of a certain field. I the field was>3 I needed to do a lookup else, juse use that field.|||thanksNNTP User
. Sometimes I don't have all those statements created because the statement I'm working on is it and is my only attempt/approach at the time! Yes, I could have posted some data examples though. Thanks for your explanation.
No comments:
Post a Comment