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)
 emphasize the day "Saturday i pivot table

Author  Topic 

ilan
Starting Member

21 Posts
Posted - 2007-12-05 : 18:43:42
1) how 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

send by Peso(tnx to Peso)

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

		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

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-12-05 : 18:45:38
Duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93684, locking thread.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site