show dynamic full month table horizontal 1-30 \ 1-

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)

show dynamic full month table horizontal 1-30 \ 1-

Author

Topic

ilan
Starting Member

21 Posts

Posted - 2007-12-04 : 18:24:18

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

12345 01/01/2007 1
88877 01/01/2007 2
99994 01/03/2007 2
12345 02/01/2007 1
12345 03/01/2007 1
-----------------------------
i need to do this (get the employee horizontal evry month)
1 row date from 1 to 28/29/30/31
below
(Sunday Monday Tuesday ..............)days names
-----------------------------------------
d1 d2 d3 d4 d5 d6 ......................................................28 / 29 / 30 /31
Sunday Monday Tuesday
12345 1 3 2 8
88877 2 4 5 5
99994 3 7 7 4

-----------------------------
BIG TNX for all

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 05:11:55

[code]DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)

INSERT @Employee
SELECT 12345, '01/01/2007', 1 UNION ALL
SELECT 88877, '01/01/2007', 2 UNION ALL
SELECT 99994, '01/03/2007', 2 UNION ALL
SELECT 12345, '02/01/2007', 1 UNION ALL
SELECT 12345, '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

,
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]

SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.

, 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 (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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

,
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]

SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.

, 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 (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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

,
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]

SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.

, 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 (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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

,
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]

SELECT p.ID,
p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.

, 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 (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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[/code]

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-05 : 08:26:53

wow tnx

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 @day / @month
3)i need to emphasize the "Saturday" like this in the square | 1 | , | 2 | , | 3 | only "Saturday"

4) how to change the name of the MONTH + DAY in the week to my language text
like this
Sunday="aaaa"
Monday= "bbbb" ....................
----------------
January="jjjjjjj"

February="fffffffffff"

5)and in the end how to insert all to real table for archive and save it to new table like "tb_12_07 " and after "tb_01_08 "

TNX x (1000000000000000000000000000)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 08:53:38

You ask for a lot, don't you?
And the new things are not mentioned in first original posting.

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-05 : 17:22:41

Peso don't be angry on me !
i am jast stuck in a middle project
can you guide me how to solve
my problems
and where can i write my question ??
only if you can !

i don't see the shift value (1 , 2 ,3, 4, 5) in the square day

TNX

ilan
Starting Member

21 Posts

Posted - 2007-12-06 : 04:23:14

need help

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 @day / @month
3)i need to emphasize the "Saturday" like this in the square | 1 | , | 2 | , | 3 | only "Saturday"

TNX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 04:29:04

2) Aleady done! You pass a date and the algorithm calculates which month it is for you.

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-06 : 09:43:55

Peso hi
i have problem can you see
---------------
PIVOT (
COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], , [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
----------------------------------------------------------------------------------------------
in "ShiftID"
i need to get the value of the ShiftID not how many times the employee appear (not count)
----------------------------------------------------------------------------
now i get this
-----------------
ID | 1 | 2| 3 |..............................DAY
123| 1 | 1| 1 |
I get evry day the value =1
because the employee appear 1 evry day

i need to see this
-----------------
ID | 1 | 2| 3 |..............................DAY
123| 3 | 5| 4|
------------
need the ShiftID value not count (in the square)
TNX

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 10:25:28

Replase COUNT with MIN or MAX or AVG or whatever aggregate function you need!

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-06 : 10:43:44

Peso you are magician

but now i have NULL
how can i see zero "0" instead NULL

TNX
for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 10:46:28

SELECT ... ,
... ,
COALESCE(p.[14], 0) AS [14],
... ,

Change ALL select statements as the one above

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 10:55:35

,
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]

SELECT p.ID,
COALESCE(p.[1], 0) AS [1], COALESCE(p.[2], 0) AS [2], COALESCE(p.[3], 0) AS [3], COALESCE(p.[4], 0) AS [4],
COALESCE(p.[5], 0) AS [5], COALESCE(p.[6], 0) AS [6], COALESCE(p.[7], 0) AS [7], COALESCE(p.

, 0) AS

,
COALESCE(p.[9], 0) AS [9], COALESCE(p.[10], 0) AS [10], COALESCE(p.[11], 0) AS [11], COALESCE(p.[12], 0) AS [12],
COALESCE(p.[13], 0) AS [13], COALESCE(p.[14], 0) AS [14], COALESCE(p.[15], 0) AS [15], COALESCE(p.[16], 0) AS [16],
COALESCE(p.[17], 0) AS [17], COALESCE(p.[18], 0) AS [18], COALESCE(p.[19], 0) AS [19], COALESCE(p.[20], 0) AS [20],
COALESCE(p.[21], 0) AS [21], COALESCE(p.[22], 0) AS [22], COALESCE(p.[23], 0) AS [23], COALESCE(p.[24], 0) AS [24],
COALESCE(p.[25], 0) AS [25], COALESCE(p.[26], 0) AS [26], COALESCE(p.[27], 0) AS [27], COALESCE(p.[28], 0) AS [28]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
MAX(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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

,
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]

SELECT p.ID,
COALESCE(p.[1], 0) AS [1], COALESCE(p.[2], 0) AS [2], COALESCE(p.[3], 0) AS [3], COALESCE(p.[4], 0) AS [4],
COALESCE(p.[5], 0) AS [5], COALESCE(p.[6], 0) AS [6], COALESCE(p.[7], 0) AS [7], COALESCE(p.

, 0) AS

,
COALESCE(p.[9], 0) AS [9], COALESCE(p.[10], 0) AS [10], COALESCE(p.[11], 0) AS [11], COALESCE(p.[12], 0) AS [12],
COALESCE(p.[13], 0) AS [13], COALESCE(p.[14], 0) AS [14], COALESCE(p.[15], 0) AS [15], COALESCE(p.[16], 0) AS [16],
COALESCE(p.[17], 0) AS [17], COALESCE(p.[18], 0) AS [18], COALESCE(p.[19], 0) AS [19], COALESCE(p.[20], 0) AS [20],
COALESCE(p.[21], 0) AS [21], COALESCE(p.[22], 0) AS [22], COALESCE(p.[23], 0) AS [23], COALESCE(p.[24], 0) AS [24],
COALESCE(p.[25], 0) AS [25], COALESCE(p.[26], 0) AS [26], COALESCE(p.[27], 0) AS [27], COALESCE(p.[28], 0) AS [28],
COALESCE(p.[29], 0) AS [29]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
MAX(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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

,
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]

SELECT p.ID,
COALESCE(p.[1], 0) AS [1], COALESCE(p.[2], 0) AS [2], COALESCE(p.[3], 0) AS [3], COALESCE(p.[4], 0) AS [4],
COALESCE(p.[5], 0) AS [5], COALESCE(p.[6], 0) AS [6], COALESCE(p.[7], 0) AS [7], COALESCE(p.

, 0) AS

,
COALESCE(p.[9], 0) AS [9], COALESCE(p.[10], 0) AS [10], COALESCE(p.[11], 0) AS [11], COALESCE(p.[12], 0) AS [12],
COALESCE(p.[13], 0) AS [13], COALESCE(p.[14], 0) AS [14], COALESCE(p.[15], 0) AS [15], COALESCE(p.[16], 0) AS [16],
COALESCE(p.[17], 0) AS [17], COALESCE(p.[18], 0) AS [18], COALESCE(p.[19], 0) AS [19], COALESCE(p.[20], 0) AS [20],
COALESCE(p.[21], 0) AS [21], COALESCE(p.[22], 0) AS [22], COALESCE(p.[23], 0) AS [23], COALESCE(p.[24], 0) AS [24],
COALESCE(p.[25], 0) AS [25], COALESCE(p.[26], 0) AS [26], COALESCE(p.[27], 0) AS [27], COALESCE(p.[28], 0) AS [28],
COALESCE(p.[29], 0) AS [29], COALESCE(p.[30], 0) AS [30]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
MAX(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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

,
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]

SELECT p.ID,
COALESCE(p.[1], 0) AS [1], COALESCE(p.[2], 0) AS [2], COALESCE(p.[3], 0) AS [3], COALESCE(p.[4], 0) AS [4],
COALESCE(p.[5], 0) AS [5], COALESCE(p.[6], 0) AS [6], COALESCE(p.[7], 0) AS [7], COALESCE(p.

, 0) AS

,
COALESCE(p.[9], 0) AS [9], COALESCE(p.[10], 0) AS [10], COALESCE(p.[11], 0) AS [11], COALESCE(p.[12], 0) AS [12],
COALESCE(p.[13], 0) AS [13], COALESCE(p.[14], 0) AS [14], COALESCE(p.[15], 0) AS [15], COALESCE(p.[16], 0) AS [16],
COALESCE(p.[17], 0) AS [17], COALESCE(p.[18], 0) AS [18], COALESCE(p.[19], 0) AS [19], COALESCE(p.[20], 0) AS [20],
COALESCE(p.[21], 0) AS [21], COALESCE(p.[22], 0) AS [22], COALESCE(p.[23], 0) AS [23], COALESCE(p.[24], 0) AS [24],
COALESCE(p.[25], 0) AS [25], COALESCE(p.[26], 0) AS [26], COALESCE(p.[27], 0) AS [27], COALESCE(p.[28], 0) AS [28],
COALESCE(p.[29], 0) AS [29], COALESCE(p.[30], 0) AS [30], COALESCE(p.[31], 0) AS [31]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM @Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
MAX(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],

, [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[/code]

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-06 : 11:10:20

Peso can i ask you about "Saturday"
don't be angry on me

jast i don't find how to do it
can you help me ??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 11:24:37

I hav absolutely no idea what you want.

E 12°55'05.25"
N 56°04'39.16"

ilan
Starting Member

21 Posts

Posted - 2007-12-06 : 11:46:32

i need to emphasize the "Saturday" like this in the square [ 1 ] , [ 2 ] , [ 3 ] only "Saturday"

----date--- 1-------2--------3
ID -----Friday | Saturday | Sunday ......................Saturday
1234 | 4 | [ 5 ] | 3

add only to Saturday the "[ ]" brackets
number shift in the brackets (only in Saturday)

like find where i get Saturday in this month and put brackets to the shift value
so the number is like this "[ 4 ]
and not like this " 4 "

TNX Peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 15:16:38

Are you REALLY REALLY sure this is not a front-end application formatting?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-06 : 15:36:31

It's very sad that dozens or hundreds of lines of SQL are being written when probably only 10-15 lines of front end code will do the trick much easier .....

ilan -- what you want to do is all presentation, nothing to do with T-SQL. Where are you displaying this information? What kind of client? (report, webpage, .net windows app, Excel, Access, etc) .... Your client app should handle ALL formatting. T-SQL is not a presentation language, it is a data manipulation language.

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-06 : 15:40:25

Slow day on my part.

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-06 : 16:28:00