segregating weekly status report

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 2000 Forums

SQL Server Development (2000)

segregating weekly status report

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-06-12 : 08:03:16

erik writes "Database: SQL Server 2000
Windows 2000, Service Pack 2

I can't explain this fully in english so I'm going to show this via data...


Data result from an initial query: 
      Date          Status  
     (yr-mm-dd)
1.    2002-06-07    ok
2.    2002-06-08    temp
3.    2002-06-09    perm
4.    2002-06-10    ok
5.    2002-06-11    ok    
6.    2002-06-12    temp
7.    2002-06-13    perm
8.    2002-06-14    temp
9.    2002-06-15    perm
10.   2002-06-16    temp
11.   2002-06-17    ok
12.   2002-06-18    perm

the first 3 records belongs to the first week of the month of
June, and records from 4 to 10 belongs to the second week, and
the last 2 belongs to the third week of the month... What would
be the necessary steps so that I can make a dynamic report just
like below out of these records?

The Report should be:(if the first day of the week is monday)


                                 ok   temp  perm
1.  2002-06-07 to 2002-06-09     1    1     1
2.  2002-06-10 to 2002-06-16     2    3     2
3.  2002-06-17 to 2002-06-18     1    0     1

Please take note that the start date(2002-06-07) and the
end date (2002-06-18) may vary, it depends on the initial query.

Thanks and more power!
Erik"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-12 : 08:23:31

You're cross-tabulating (cross-tabbing for short), and the CASE statement is probably the easiest way to accomplish it:

SELECT Min([Date]) AS StartDate, Max([Date]) AS EndDate,
Sum(CASE Status WHEN 'ok' THEN 1 ELSE 0 END) AS OK,
Sum(CASE Status WHEN 'temp' THEN 1 ELSE 0 END) AS Temp,
Sum(CASE Status WHEN 'perm' THEN 1 ELSE 0 END) AS Perm
FROM myTable
GROUP BY DatePart(wk, [Date])

One suggestion I have is that you change the column Date to some other name; Date is a reserved word in SQL and you'll encounter lots of little problems if you name objects/columns after reserved words. Something like StatusDate would be fine.

Take a look at the DatePart function in Books Online, and the CASE expression, for more examples. I put the start and end dates in separate columns for simplicity's sake, if you need to the output to exactly match what you described:

SELECT Convert(char(10), Min([Date]), 102) +
' to ' + Convert(char(10), Max([Date]), 102) AS WeekOf,
Sum(CASE Status WHEN 'ok' THEN 1 ELSE 0 END) AS OK,
Sum(CASE Status WHEN 'temp' THEN 1 ELSE 0 END) AS Temp,
Sum(CASE Status WHEN 'perm' THEN 1 ELSE 0 END) AS Perm
FROM myTable
GROUP BY DatePart(wk, [Date])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources