emphasize the day "Saturday i pivot table

Question

Post reply

emphasize the day "Saturday i pivot table

midan1

SSCertifiable

Points: 5100
More actions
December 5, 2007 at 4:26 pm

#177232

1) i need to emphasize the value in "Saturday" like this in the square | 1 | , | 2 | , | 3 | only "Saturday"
2) 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"
can i do this
TNX for the help
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 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jason- SSCrazy Points: 2795 More actions · Answer 1

To emphasize you can use case statements. For example

Select CASE datename(dw,getdate()) WHEN 'Saturday' THEN '|Saturday|' ELSE datename(dw,getdate()) END

To change to your language, change the language of the user account connecting.

Assume you run this statement Tuesday December 11, 2007

If the user account language is english then the statement

Select datename(dw,getdate())

Returns "Tuesday"

If the user account language is spanish then the statement

Select datename(dw,getdate())

Returns "Martes"

Hope this helps

-

midan1 SSCertifiable Points: 5100 More actions · Answer 2

Jason TNX

To change to your language, change the language of the user account connecting.
Assume you run this statement Tuesday December 11, 2007
If the user account language is english then the statement
Select datename(dw,getdate())
Returns "Tuesday"
If the user account language is spanish then the statement
Select datename(dw,getdate())
Returns "Martes"

i canot do this this is a web application i must show my value !

like insted "Tuesday" i need to show "??"

insted "Monday" i need to show "??"

insted "January" i need to show "??" .....

and olso i need to show all month in one page

i must to save space to show all

any idea how

tnx