Convert/Pivot Table with dates to calendar

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

General SQL Server Forums

New to SQL Server Programming

Convert/Pivot Table with dates to calendar

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-06-25 : 06:17:41

Dear Gurus,
I have a table storing Leave Application. Employees apply for leave, The table is simplified as below:

Tbl_LAs
1. LA_ID
2. LA_Date
3. LA_EmpID

LA_ID | LA_Date | LA_EmpID
1 | 2012/06/25 | 001
2 | 2012/06/25 | 002
3 | 2012/06/26 | 002
4 | 2012/06/29 | 003
etc...

It simply stores the Date and Employee ID.

Now, I want to display the data it on a calendar form, just a long horizontal:

The data examples above will produce the output as below.

EmpID | 25/06 | 26/06 | 27/06 | 28/06 | 29/06
001 | 1 | 0 | 0 | 0 | 0
002 | 1 | 1 | 0 | 0 | 0
003 | 0 | 0 | 0 | 0 | 1

Would it be possible to do that with a stored procedure? Any examples or guide lines anyone done that?

I need to display this table in ASP format, but with my limited knowledge on SQL, that I will loop at least two SQLs by:
1. Use 1st sql to get all EmpID who is applying leave on the selected date range
2. Loop 2nd sql to run each employee's leave within the selected date range, then display as YES if the record matches the date,
3. Move 2nd sql to the next record until the end of the date range
4. Loop 1st sql to get send employee and loop 2nd sql again until all employees are displayed.

Is there any better and easier way?

sakthiananth
Starting Member

2 Posts

Posted - 2012-06-25 : 06:32:46

Sorry no idea ):

Let's keep learning to explore towards future.):

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-06-25 : 08:18:02

Hey,

Look at this example, it is easy to understand.

SELECT YEAR(ord.OrderDate) YEAR,
SUM(CASE prod.CategoryID WHEN 1 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Beverages,
SUM(CASE prod.CategoryID WHEN 2 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Condiments,
SUM(CASE prod.CategoryID WHEN 3 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Confections,
SUM(CASE prod.CategoryID WHEN 4 THEN
det.UnitPrice * det.Quantity ELSE 0 END) [Dairy Products],
SUM(CASE prod.CategoryID WHEN 5 THEN
det.UnitPrice * det.Quantity ELSE 0 END) [Grains/Cereals],
SUM(CASE prod.CategoryID WHEN 6 THEN
det.UnitPrice * det.Quantity ELSE 0 END) [Meat/Poultry],
SUM(CASE prod.CategoryID WHEN 7 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Produce,
SUM(CASE prod.CategoryID WHEN 8 THEN
det.UnitPrice * det.Quantity ELSE 0 END) Seafood

FROM Orders ord

INNER JOIN [Order Details] det
ON det.OrderID = ord.OrderID

INNER JOIN Products prod
ON prod.ProductID = det.ProductID

GROUP BY YEAR(ord.OrderDate)

ORDER BY YEAR(ord.OrderDate)

Good luck,

kml

calvinfoo
Posting Yak Master

129 Posts

Posted - 2012-06-25 : 20:29:48