SQL Query For Custo Report

[shadowlesshand]

Hi All,

I need to generate records for reports.
Currently I have:

SOME_ID EmpName ShiftType
101 John Normal
101 John OT
102 Ann Normal
102 Ann Absent
102 Ann OT
101 John Absent

Now I want to create a view which will look like..

SOME_ID EmpName ShiftType ShiftType ShiftType
101 John Normal OT Absent
102 Ann Normal Absent OT

Eagerly waiting, Please Suggest,
Thanks,

 

[shadowlesshand]

Sorry, Can I have This:

Hi All,

I need to generate records for reports.
Currently I have:

ID EmpName ShiftType WorkDate
101 John Normal 01/12/2004
101 John OT 01/12/2004
102 Ann Normal 21/12/2004
102 Ann Absent 22/12/2004
102 Ann OT 21/12/2004

Now I want to create a view which will look like..

ID EmpName Friday Monday TUESDAY
(1/12/2004) (21/12/2004) (22/12/2004)

101 John Normal N/A N/A
OT

102 Ann N/A OT ABSENT


Eagerly waiting, Please Suggest,
Thanks,

 

[zhavic]

hmmm, have a question

In your example of data, there is two rows with the same date for 'Ann' - 21/12/2004.
Is this OK ? If yes, which of that two rows you want in result ?
Also, will there be rows for more weeks for the same ID ?
E.g.:

ID WorkDate
101 06/12/2004
101 20/12/2004

Because these two dates are in Friday. What you want in result in that situation ?

One way how to do somethig like you want is this, but without solving above problems:

SELECT your_table.[ID], 
	   MAX( your_table.EmpName ) AS EmpName,
	   MAX( Friday.ShiftType ) AS Friday,
	   MAX( Monday.ShiftType ) AS Monday,
	   MAX( Tuesday.ShiftType ) AS Tuesday
FROM your_table
	LEFT JOIN your_table AS Friday ON Friday.[ID] = your_table.[ID] AND DATEPART( weekday, Friday.WorkDate ) = 6
	LEFT JOIN your_table AS Monday ON Monday.[ID] = your_table.[ID] AND DATEPART( weekday, Monday.WorkDate ) = 2
	LEFT JOIN your_table AS Tuesday ON Tuesday.[ID] = your_table.[ID] AND DATEPART( weekday, Tuesday.WorkDate ) = 3
GROUP BY your_table.[ID]

PS: Where you want to put dates for each day in the result ?
You want put them into first record of the result ?

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.