December 4, 2007 at 7:22 am
BIG question 🙂
how to
show dynamic full month table horizontal 1-30 \ 1-31 \ 1-29 \ 1-28
of employee
like i select the "month"
and i get the employee horizontal
--------------------
the employee table is
----------------------------
ID DATE shift_id
1234501/01/2007 1
8887701/01/2007 2
9999401/03/2007 2
1234502/01/2007 1
1234503/01/2007 1
-----------------------------
i need to do this
-----------------------------------------
d1 d2 d3 d4 d5 d6 ......................................................28/29/30/31
Sunday Monday Tuesday
123451 3 2 8
888772 4 5 5
999943 7 7 4
-----------------------------
BIG TNX for all
December 4, 2007 at 7:35 am
Use PIVOT with either dynamic values or fixed values....
--Ramesh
December 4, 2007 at 7:38 am
You can do this using PIVOT operator.
Please refer to Books Online to see how to use PIVOT.
Imran
December 4, 2007 at 3:48 pm
perhaps someone
show me how
TNX for all
December 5, 2007 at 3:02 am
DECLARE@Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
INSERT@Employee
SELECT12345, '01/01/2007', 1 UNION ALL
SELECT88877, '01/01/2007', 2 UNION ALL
SELECT99994, '01/03/2007', 2 UNION ALL
SELECT12345, '02/01/2007', 1 UNION ALL
SELECT12345, '03/01/2007', 1
DECLARE@WantedDate SMALLDATETIME, -- Use as parameter to SP!
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT@WantedDate = CURRENT_TIMESTAMP, -- User supplied parameter to SP!
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN
SELECTDATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate))
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 29
BEGIN
SELECTDATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate))
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29])
) AS p
END
ELSE IF @Numdays = 30
BEGIN
SELECTDATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 29, @BaseDate))
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
END
ELSE IF @Numdays = 31
BEGIN
SELECTDATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 29, @BaseDate)),
DATENAME(WEEKDAY, DATEADD(DAY, 30, @BaseDate))
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END
N 56°04'39.16"
E 12°55'05.25"
December 5, 2007 at 3:08 am
You can do something like this....
SELECTID, MAX( [1] ) AS [1],......., MAX( [31] ) AS [31]
FROM(
SELECTID, shift_id, DAY( [DATE] ) AS [Day]
FROMdbo.Employee
WHERE[DATE] BETWEEN '01-Apr-2007' AND '30-Apr-2007'
) S
PIVOT
(
MAX( shift_id)
FOR [Day] IN( [1], ...., [31] )
) P
GROUP BY ID
EDIT>>
Jinx...
--Ramesh
December 5, 2007 at 4:12 pm
hi and tnx for all for the great help i get here
-----------------------------------------
1)i dont see the shift value (1 , 2 ,3, 4, 5) in the square day
2)how to generate report dynamic, to one month like i need 12/2007 , 01/2008 ..........
like this
DECLARE @month / @year
DECLARE@Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
INSERT@Employee
SELECT12345, '01/01/2007', 1 UNION ALL
SELECT88877, '01/01/2007', 2 UNION ALL
SELECT99994, '01/03/2007', 2 UNION ALL
SELECT12345, '02/01/2007', 1 UNION ALL
SELECT12345, '03/01/2007', 1
DECLARE@WantedDate SMALLDATETIME,-- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT@WantedDate = CURRENT_TIMESTAMP,-- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [8],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 29
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28],
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)) AS [29]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29])
) AS p
END
ELSE IF @Numdays = 30
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [8],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28],
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)) AS [29],
DATENAME(WEEKDAY, DATEADD(DAY, 29, @BaseDate)) AS [30]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
END
ELSE IF @Numdays = 31
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [8],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28],
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)) AS [29],
DATENAME(WEEKDAY, DATEADD(DAY, 29, @BaseDate)) AS [30],
DATENAME(WEEKDAY, DATEADD(DAY, 30, @BaseDate)) AS [31]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END
TNX
December 5, 2007 at 4:14 pm
hi and tnx for all for the great help i get here
-----------------------------------------
1)i dont see the shift value (1 , 2 ,3, 4, 5) in the square day
2)how to generate report dynamic, to one month like i need 12/2007 , 01/2008 ..........
like this
DECLARE @month / @year
DECLARE@Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
INSERT@Employee
SELECT12345, '01/01/2007', 1 UNION ALL
SELECT88877, '01/01/2007', 2 UNION ALL
SELECT99994, '01/03/2007', 2 UNION ALL
SELECT12345, '02/01/2007', 1 UNION ALL
SELECT12345, '03/01/2007', 1
DECLARE@WantedDate SMALLDATETIME,-- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT@WantedDate = CURRENT_TIMESTAMP,-- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))
IF @NumDays = 28
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [8],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28])
) AS p
END
ELSE IF @Numdays = 29
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28],
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)) AS [29]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29])
) AS p
END
ELSE IF @Numdays = 30
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [8],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28],
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)) AS [29],
DATENAME(WEEKDAY, DATEADD(DAY, 29, @BaseDate)) AS [30]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30])
) AS p
END
ELSE IF @Numdays = 31
BEGIN
SELECT@NumDays AS NumDays,
DATENAME(WEEKDAY, DATEADD(DAY, 0, @BaseDate)) AS [1],
DATENAME(WEEKDAY, DATEADD(DAY, 1, @BaseDate)) AS [2],
DATENAME(WEEKDAY, DATEADD(DAY, 2, @BaseDate)) AS [3],
DATENAME(WEEKDAY, DATEADD(DAY, 3, @BaseDate)) AS [4],
DATENAME(WEEKDAY, DATEADD(DAY, 4, @BaseDate)) AS [5],
DATENAME(WEEKDAY, DATEADD(DAY, 5, @BaseDate)) AS [6],
DATENAME(WEEKDAY, DATEADD(DAY, 6, @BaseDate)) AS [7],
DATENAME(WEEKDAY, DATEADD(DAY, 7, @BaseDate)) AS [8],
DATENAME(WEEKDAY, DATEADD(DAY, 8, @BaseDate)) AS [9],
DATENAME(WEEKDAY, DATEADD(DAY, 9, @BaseDate)) AS [10],
DATENAME(WEEKDAY, DATEADD(DAY, 10, @BaseDate)) AS [11],
DATENAME(WEEKDAY, DATEADD(DAY, 11, @BaseDate)) AS [12],
DATENAME(WEEKDAY, DATEADD(DAY, 12, @BaseDate)) AS [13],
DATENAME(WEEKDAY, DATEADD(DAY, 13, @BaseDate)) AS [14],
DATENAME(WEEKDAY, DATEADD(DAY, 14, @BaseDate)) AS [15],
DATENAME(WEEKDAY, DATEADD(DAY, 15, @BaseDate)) AS [16],
DATENAME(WEEKDAY, DATEADD(DAY, 16, @BaseDate)) AS [17],
DATENAME(WEEKDAY, DATEADD(DAY, 17, @BaseDate)) AS [18],
DATENAME(WEEKDAY, DATEADD(DAY, 18, @BaseDate)) AS [19],
DATENAME(WEEKDAY, DATEADD(DAY, 19, @BaseDate)) AS [20],
DATENAME(WEEKDAY, DATEADD(DAY, 20, @BaseDate)) AS [21],
DATENAME(WEEKDAY, DATEADD(DAY, 21, @BaseDate)) AS [22],
DATENAME(WEEKDAY, DATEADD(DAY, 22, @BaseDate)) AS [23],
DATENAME(WEEKDAY, DATEADD(DAY, 23, @BaseDate)) AS [24],
DATENAME(WEEKDAY, DATEADD(DAY, 24, @BaseDate)) AS [25],
DATENAME(WEEKDAY, DATEADD(DAY, 25, @BaseDate)) AS [26],
DATENAME(WEEKDAY, DATEADD(DAY, 26, @BaseDate)) AS [27],
DATENAME(WEEKDAY, DATEADD(DAY, 27, @BaseDate)) AS [28],
DATENAME(WEEKDAY, DATEADD(DAY, 28, @BaseDate)) AS [29],
DATENAME(WEEKDAY, DATEADD(DAY, 29, @BaseDate)) AS [30],
DATENAME(WEEKDAY, DATEADD(DAY, 30, @BaseDate)) AS [31]
SELECTp.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22],p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM(
SELECTID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM@Employee
WHEREDate >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT(
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END
TNX
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply