Help with Case Statement

Help with Case Statement

• Adam Miles

  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.
  
  

• Amadeus

  Hi dba123,

   

  In your qeury pseudo-code, where is the alias c (used in c.FeeSchedule) defined

   

  It's easier to help you if you post CREATE TABLE statements for your table structures, INSERT statements with some rows of sample data and the expected results of the query. Not only will that help others understand what you ask, it also enables them to easily test what they post.

  
  --
  Hugo Kornelis, SQL Server MVP

   

  <dba123@discussions.microsoft.com> schreef in bericht news:7fb6e0f7-ae20-451c-abee-677d4d3ee6f8@discussions.microsoft.com...

  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

  
  

• adshah

  thanks

  NNTP 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.

  
  
  

• Oliver-LundK.de

  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

  
  
  

• Markus W&amp;#246;&amp;#223;

  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
  

  
  

• Danielsan Ichiban

  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

  
  

• Rob Goodridge

  So are you asking for my table structure then The table has already been created...so are you asking for the erd
  
  

• DeCion05

  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

  
  

• MGraves

  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.
  
• Help with Case Statement