Case Statements

• kgbs12

  Grasshopper

  Points: 20

  More actions

  January 7, 2010 at 4:58 pm

  #214334

  I’m using SQL Server 2005, I use a third party reporting tool. The bulk of the report logic is done in SQL. The Third party report product is used to add parameters(which forms part of the WHERE clause), and pretty the report up etc.

  I’m using case statements to give the results I need, however, when I try to add a parameter to the report, the same results appear everytime, not just for the parameter specified.

  The SQL Statement that I’ve been using is:

  SELECT USERS.DEPARTMENT, TASKS.ASSIGNEENAME, COUNT(TASKS.TASKID) AS TOTALDOCUMENTS,

  COUNT(CASE WHEN (TASKS.FINISHDATE <= TASKS.DUEDATE) AND (TASKS.DUEDATE < CONVERT(char(11), Getdate(), 106)) THEN TASKS.taskid END) AS CompletedDocs,

  COUNT(CASE WHEN (TASKS.finishdate > TASKS.duedate) AND (TASKS.DUEDATE < CONVERT(char(11), Getdate(), 106)) THEN TASKS.taskid END) AS CompletedLate,

  COUNT(CASE WHEN (TASKS.DUEDATE < CONVERT(char(11), Getdate(), 106)) AND (TASKS.status = 10) THEN TASKS.taskid END) AS OverdueDocs,

  COUNT(CASE WHEN (TASKS.STARTDATE IS NULL) AND (TASKS.STATUS = 0) THEN TASKS.taskid END) AS NotStarted,

  PLAYER.STATUS

  FROM PLAYER INNER JOIN

  TASKS ON PLAYER.PLAYERID = TASKS.ASSIGNEEID LEFT OUTER JOIN

  USERS ON PLAYER.PLAYERID = USERS.OBJECTID

  WHERE (PLAYER.STATUS !=0)

  GROUP BY USERS.DEPARTMENT, TASKS.ASSIGNEENAME, PLAYER.STATUS

  ORDER BY USERS.DEPARTMENT, TASKS.ASSIGNEENAME

  With the results as per below (reduced table results for display purposes, so yes totals don't calculate.)

  DEPARTMENT ASSIGNEENAME TOTALDOCUMENTS CompletedDocs CompletedLate ...

  NULL Donald Duck 5 0 3

  NULL Daffy Duck 4 0 1

  Engineering Mickey Mouse 6 0 6

  Engineering Minnie Mouse 110 43 57

  Maintenance Scooby Doo 56 34 19

  Ideally, I would the report to show, if Engineering has been selected as the department

  DEPARTMENT ASSIGNEENAME TOTALDOCUMENTS CompletedDocs CompletedLate ...

  Enginerring Mickey Mouse 6 0 6

  Engineering Minnie Mouse 110 43 57

  If I include the department name within the WHERE clause, I get the correct results, however, I want the report parameter to take care of this for me.

  I found on another website the following information “CASE is just a searched (or lookup) expression - you cannot RETURN from inside it - it’s kind of like IF() in Excel”

  That being said what other expressions could I look at?

  (I looked at UNION ALL, but that gives me that same effect as the case statements.)

  The attached word document attached contains a complete example and all above mentioned detail.

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  January 7, 2010 at 5:20 pm

  #1100618

  There is no reason in sql why you cannot put a parameter in the WHERE clause...

  ..... WHERE Department=@Dept ... for example as long as your reporting tool can handle that...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]