query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query problem

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-03 : 17:24:08

Hi All,

I have a View:

SELECT     TOP (100) PERCENT dbo.tbl_Case.CaseWorkerID, dbo.tbl_Worker.WorDescription, DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate)   AS da
FROM         dbo.tbl_Case LEFT OUTER JOIN
                      dbo.tbl_Worker ON dbo.tbl_Case.CaseWorkerID = dbo.tbl_Worker.WorkerID
ORDER BY dbo.tbl_Case.CaseWorkerID

The result is:
7 Anne 11
7 Anne 28
7 Anne 3
50 Andrea 6
50 Andrea 30
57 Alison 4
57 Alison 24
57 Alison 19

I need to compare numbers according to intervals like 1 to 14 days, 15 to 29, 30 to 44…
I need to have a query like this:

ID Name 1to14 15to29 30to44
7 Anne 2 1 0
50 Andrea 1 0 1
57 Alison 1 2 0

Please help me.

Sep

Sep410
Posting Yak Master

117 Posts

Posted - 2008-09-03 : 17:56:12

I did it up to here:

SELECT     TOP (100) PERCENT dbo.tbl_Case.CaseWorkerID, dbo.tbl_Worker.WorDescription, CASE WHEN DATEDIFF(dd, dbo.tbl_Case.WaitListDate, 
                      dbo.tbl_Case.OpenDate) < 15 THEN '1' END AS d14, CASE WHEN DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate) < 30 AND 
                      DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate) > 14 THEN '1' END AS d29, CASE WHEN DATEDIFF(dd, dbo.tbl_Case.WaitListDate, 
                      dbo.tbl_Case.OpenDate) < 45 AND DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate) > 29 THEN '1' END AS d44
FROM         dbo.tbl_Case LEFT OUTER JOIN
                      dbo.tbl_Worker ON dbo.tbl_Case.CaseWorkerID = dbo.tbl_Worker.WorkerID
ORDER BY dbo.tbl_Case.CaseWorkerID

It returns:
7 Anne NULL NULL NULL NULL NULL NULL 1
7 Anne NULL 1 NULL NULL NULL NULL NULL
7 Anne NULL NULL NULL NULL NULL NULL 1
50 Andrea NULL 1 NULL NULL NULL NULL NULL
50 Andrea NULL 1 NULL NULL NULL NULL NULL
57 Alison NULL NULL NULL 1 NULL NULL NULL
57 Alison NULL NULL NULL 1 NULL NULL NULL

How can I change the code to get this:
7 Anne NULL NULL NULL NULL NULL NULL 2
7 Anne NULL 1 NULL NULL NULL NULL NULL

50 Andrea NULL 2 NULL NULL NULL NULL NULL

57 Alison NULL NULL NULL 2 NULL NULL NULL

Sep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-04 : 01:22:55

try like this:-


SELECT     TOP (100) PERCENT dbo.tbl_Case.CaseWorkerID, dbo.tbl_Worker.WorDescription, 
SUM(CASE WHEN DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate)>=1 
AND  DATEDIFF(dd, dbo.tbl_Case.WaitListDate,
dbo.tbl_Case.OpenDate)<=14  THEN 1 ELSE 0 END) AS [1 to 14 days],
SUM(CASE WHEN DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate)>=15 
AND  DATEDIFF(dd, dbo.tbl_Case.WaitListDate,
dbo.tbl_Case.OpenDate)<=29  THEN 1 ELSE 0 END) AS [15 to 29 days],
SUM(CASE WHEN DATEDIFF(dd, dbo.tbl_Case.WaitListDate, dbo.tbl_Case.OpenDate)>=30 
AND  DATEDIFF(dd, dbo.tbl_Case.WaitListDate,
dbo.tbl_Case.OpenDate)<=44  THEN 1 ELSE 0 END) AS [30 to 44 days]
...
FROM         dbo.tbl_Case LEFT OUTER JOIN
                      dbo.tbl_Worker ON dbo.tbl_Case.CaseWorkerID = dbo.tbl_Worker.WorkerID
GROUP BY dbo.tbl_Case.CaseWorkerID, dbo.tbl_Worker.WorDescription
ORDER BY dbo.tbl_Case.CaseWorkerID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources