Displayed Yearly calendar - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Displayed Yearly calendar

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-01-14 : 23:14:09

Hi All,

I tried to populate yearly calendar as below but failed:-


BEGIN
SET NOCOUNT ON
DECLARE @mmonth NVARCHAR(5),
@myear NVARCHAR(10),
@lastday INT,
@firstday INT,
@tsql NVARCHAR(100),
@dateval NVARCHAR(15),
@dayval NVARCHAR(15),
@row INT = 1,
@daycount INT = 0

--drop table tab
CREATE TABLE tab (
Row INT,
Months Nvarchar(20),
Years Nvarchar(20),
Sunday NVARCHAR(6),
Monday NVARCHAR(6),
Tuesday NVARCHAR(6),
Wednesday NVARCHAR(6),
Thursday NVARCHAR(6),
Friday NVARCHAR(6),
Saturday NVARCHAR(6)
)


declare @i int
declare @j int
set @i=0
WHILE(@i<52)
Begin
	INSERT INTO tab (Row)
	SELECT '1'+@j
	INSERT INTO tab (Row)
	SELECT '2'+@j
	INSERT INTO tab (Row)
	SELECT '3'+@j
	INSERT INTO tab (Row)
	SELECT '4'+@j
	INSERT INTO tab (Row)
	SELECT '5'+@j
	SET @i=@i+1
	SET @j=@j+5
End


SET @firstday = 1
SET @mmonth = 1
SET @myear = YEAR(getdate())
WHILE(@mmonth<=12)
BEGIN
		SELECT @lastday = DAY(DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, (@myear + '-' + @mmonth + '-01')) + 1, 0)))
		SET @daycount = 1

		/******************************************************************************
		-- While loop starts - Populate the calendar
		*******************************************************************************/ 

		WHILE (@firstday <= @lastday)
		BEGIN
			SET @dateval = @myear + '-' + @mmonth + '-' + CONVERT(NVARCHAR(5), @firstday)
			SET @dayval = DATENAME(WEEKDAY, @dateval);
			IF (
			SELECT LEN(SATURDAY)
			FROM tab
			WHERE ROW = @row
			) IS NULL
			BEGIN
			   -- Set the dynamic SQL
				SET @tsql = 'UPDATE tab SET Months='''+@mmonth+''', years='''+@myear+''',' + @dayval + ' = (DAY(''' + @dateval + ''')) WHERE ROW= ' + '''' + convert(NVARCHAR(6), @row) + ''''
				SET @firstday = (@firstday + 1)
				EXEC SP_EXECUTESQL @tsql
			END

			IF (@daycount = 7)
				BEGIN
					SET @daycount = 1
					SET @row = @row + 1
				END
			ELSE
				SET @daycount = @daycount + 1
			END
SET @mmonth=@mmonth+1
END	
END
/******************************************************************************
-- Select the data from the calendar
*******************************************************************************/ 

SELECT Months,
Years,
Sunday,
Monday,
Tuesday,
Wednesday,
Thursday,
Friday,
Saturday
FROM tab

drop table tab

If only a month, it's work fine:-


BEGIN
SET NOCOUNT ON
DECLARE @mmonth NVARCHAR(5),
@myear NVARCHAR(10),
@lastday INT,
@firstday INT,
@tsql NVARCHAR(100),
@dateval NVARCHAR(15),
@dayval NVARCHAR(15),
@row INT = 1,
@daycount INT = 0

--drop table tab
CREATE TABLE tab (
Row INT,
Months Nvarchar(20),
Years Nvarchar(20),
Sunday NVARCHAR(6),
Monday NVARCHAR(6),
Tuesday NVARCHAR(6),
Wednesday NVARCHAR(6),
Thursday NVARCHAR(6),
Friday NVARCHAR(6),
Saturday NVARCHAR(6)
)


--declare @i int
--declare @j int
--set @i=0
--WHILE(@i<52)
--Begin
	INSERT INTO tab (Row)
	SELECT '1'--+@j
	INSERT INTO tab (Row)
	SELECT '2'--+@j
	INSERT INTO tab (Row)
	SELECT '3'--+@j
	INSERT INTO tab (Row)
	SELECT '4'--+@j
	INSERT INTO tab (Row)
	SELECT '5'--+@j
--	SET @i=@i+1
--	SET @j=@j+5
--End


SET @firstday = 1
SET @mmonth = 1
SET @myear = YEAR(getdate())
--WHILE(@mmonth<=12)
--BEGIN
		SELECT @lastday = DAY(DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, (@myear + '-' + @mmonth + '-01')) + 1, 0)))
		SET @daycount = 1

		/******************************************************************************
		-- While loop starts - Populate the calendar
		*******************************************************************************/ 

		WHILE (@firstday <= @lastday)
		BEGIN
			SET @dateval = @myear + '-' + @mmonth + '-' + CONVERT(NVARCHAR(5), @firstday)
			SET @dayval = DATENAME(WEEKDAY, @dateval);
			IF (
			SELECT LEN(SATURDAY)
			FROM tab
			WHERE ROW = @row
			) IS NULL
			BEGIN
			   -- Set the dynamic SQL
				SET @tsql = 'UPDATE tab SET Months='''+@mmonth+''', years='''+@myear+''',' + @dayval + ' = (DAY(''' + @dateval + ''')) WHERE ROW= ' + '''' + convert(NVARCHAR(6), @row) + ''''
				SET @firstday = (@firstday + 1)
				EXEC SP_EXECUTESQL @tsql
			END

			IF (@daycount = 7)
				BEGIN
					SET @daycount = 1
					SET @row = @row + 1
				END
			ELSE
				SET @daycount = @daycount + 1
			END
--SET @mmonth=@mmonth+1
--END	
END
/******************************************************************************
-- Select the data from the calendar
*******************************************************************************/ 

SELECT Months,
Years,
Sunday,
Monday,
Tuesday,
Wednesday,
Thursday,
Friday,
Saturday
FROM tab

drop table tab

Please advise.

Thank you.

Regards,
Micheale

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-01-15 : 01:18:00

Hi,

I manage to get the SP as below, but now I got 1 more question. If Month(getDate())<4 then show calendar from Apr 2013 till Mar 2014. If the Month(getDate()>3 then show calendar from Apr 2014 till Mar 2015.

How can I modified below calendar:-


SET DATEFIRST 7
Declare @years INT
Declare @yearsnext INT

IF(Month(getdate())<4)
BEGIN
SET @years=Year(getdate())-1
SET @yearsnext=Year(getdate())
END
ELSE
BEGIN
SET @years=Year(getdate())
SET @yearsnext=Year(getdate())+1
END


While(@years<@yearsnext)
Begin


DECLARE @Date DATETIME = @years+ '01'+ '01'

DECLARE @Dates TABLE 
(   Date DATE, MonthNum INT, 
	MonthName VARCHAR(15), 
    DayNum INT, 
    MonthStart DATE, 
    Day VARCHAR(2)
	
)

;WITH Dates AS
(   SELECT  DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY Object_ID) - 8, @Date) [Date]
    FROM    sys.All_Objects
)
INSERT @Dates
SELECT  Date,
		DATEPART(MONTH, Date) [MonthNum],
        LEFT(DATENAME(MONTH, Date), 3) [MonthName], 
        DATEPART(WEEKDAY, Date) [DayNum],
        DATEADD(MONTH, DATEDIFF(MONTH, 0, d1.Date), 0) [MonthStart],
        CONVERT(VARCHAR(2), DATEPART(DAY, Date)) [Day]
FROM    Dates d1
WHERE   Date < DATEADD(YEAR, 1, @Date)

;WITH Calendar AS
(   SELECT  MonthName, 
			MonthNum, 
            CONVERT(VARCHAR(1),  DayNum) + CONVERT(VARCHAR(1),  Occurance) [Identifier], 
            [Day]
    FROM    @Dates d1
            CROSS APPLY
            (   SELECT  COUNT(*) [Occurance]
                FROM    @Dates d2
                WHERE   d2.Date BETWEEN DATEADD(DAY, 1 - DATEPART(WEEKDAY, d1.MonthStart), d1.MonthStart) AND d1.Date
                AND     d1.DayNum = d2.DayNum
            ) o
    WHERE   Date >= @Date
UNIONS ALL   /*Remove 's' from unions*/
 SELECT  LEFT(DATENAME(MONTH, @years + RIGHT('0' + CONVERT(VARCHAR, MonthNumber), 2) + '01'), 3) [MonthName], 
			MonthNumber, 
            DayNumber + Occurance [ID], '' [Day]
    FROM    (VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7')) AS d (DayNumber),
            (VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6')) AS o (Occurance),
            (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS m (MonthNumber)
)
SELECT  [MonthName],
        [11] [Sun], [21] [Mon], [31] [Tues], [41] [Wed], [51] [Thu], [61] [Friday], [71] [Sat],
        [12] [Sun], [22] [Mon], [32] [Tues], [42] [Wed], [52] [Thu], [62] [Friday], [72] [Sat],
        [13] [Sun], [23] [Mon], [33] [Tues], [43] [Wed], [53] [Thu], [63] [Friday], [73] [Sat],
        [14] [Sun], [24] [Mon], [34] [Tues], [44] [Wed], [54] [Thu], [64] [Friday], [74] [Sat],
        [15] [Sun], [25] [Mon], [35] [Tues], [45] [Wed], [55] [Thu], [65] [Friday], [75] [Sat],
        [16] [Sun], [26] [Mon], [36] [Tues], [46] [Wed], [56] [Thu], [66] [Friday], [76] [Sat]
FROM    Calendar
        PIVOT
        (   MAX([Day])
            FOR [Identifier] IN
            (   [11], [21], [31], [41], [51], [61], [71],
                [12], [22], [32], [42], [52], [62], [72],
                [13], [23], [33], [43], [53], [63], [73],
                [14], [24], [34], [44], [54], [64], [74],
                [15], [25], [35], [45], [55], [65], [75],
                [16], [26], [36], [46], [56], [66], [76]
            )
     ) pvt
ORDER BY MonthNum  

SET @years=@years+1
END

Please advise.

Thank you.

Regards,
Micheale

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-01-15 : 01:43:01

Is following substitute you need?

-----------------------------------------------------------

DECLARE @startdate date = '2014-01-01';
DECLARE @enddate date = '2014-12-31';

WITH daylist([day]) AS (
	SELECT CONVERT(date, @startdate)
	UNION ALL
	SELECT DATEADD(d, 1, [day]) FROM daylist
	WHERE DATEADD(d, 1, [day]) <= @enddate
)
SELECT
	MAX(CASE DATEPART(weekday, [day]) WHEN 1 THEN [day] END) Sunday,
	MAX(CASE DATEPART(weekday, [day]) WHEN 2 THEN [day] END) Monday,
	MAX(CASE DATEPART(weekday, [day]) WHEN 3 THEN [day] END) Tuesday,
	MAX(CASE DATEPART(weekday, [day]) WHEN 4 THEN [day] END) Wednesday,
	MAX(CASE DATEPART(weekday, [day]) WHEN 5 THEN [day] END) Thursday,
	MAX(CASE DATEPART(weekday, [day]) WHEN 6 THEN [day] END) Friday,
	MAX(CASE DATEPART(weekday, [day]) WHEN 7 THEN [day] END) Saturday
FROM daylist
GROUP BY DATEPART(WEEK, [day])
ORDER BY DATEPART(WEEK, [day])
OPTION (MAXRECURSION 364) --Set number you need

-------------------------------------
From Japan
Sorry, my English ability is limited.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-01-15 : 04:37:23

Hi,

I need the output like this:

http://oi39.tinypic.com/2gxr4pc.jpg

Can someone help me?

My sql code before:
Alter procedure CALENDARFY AS
--exec CALENDARFY
SET DATEFIRST 7
Declare @years nvarchar(4)
Declare @yearsnext INT
Declare @yearscurrent int

IF(Month(getdate())<4)
BEGIN
SET @years=Year(getdate())-1
SET @yearscurrent=Year(getdate())-1
SET @yearsnext=Year(getdate())
END
ELSE
BEGIN
SET @years=Year(getdate())
SET @yearscurrent=Year(getdate())
SET @yearsnext=@years+1
END

Create table tempDataStore
(
[MonthName] nvarchar(3),
[Year] int,
Dat Date,
Sun nvarchar(2),
Mon nvarchar(2),
Tue nvarchar(2),
Wed nvarchar(2),
Thu nvarchar(2),
Fri nvarchar(2),
Sat nvarchar(2)
--Sun1 nvarchar(2),
--Mon1 nvarchar(2),
--Tue1 nvarchar(2),
--Wed1 nvarchar(2),
--Thu1 nvarchar(2),
--Fri1 nvarchar(2),
--Sat1 nvarchar(2),
--Sun2 nvarchar(2),
--Mon2 nvarchar(2),
--Tue2 nvarchar(2),
--Wed2 nvarchar(2),
--Thu2 nvarchar(2),
--Fri2 nvarchar(2),
--Sat2 nvarchar(2),
--Sun3 nvarchar(2),
--Mon3 nvarchar(2),
--Tue3 nvarchar(2),
--Wed3 nvarchar(2),
--Thu3 nvarchar(2),
--Fri3 nvarchar(2),
--Sat3 nvarchar(2),
--Sun4 nvarchar(2),
--Mon4 nvarchar(2),
--Tue4 nvarchar(2),
--Wed4 nvarchar(2),
--Thu4 nvarchar(2),
--Fri4 nvarchar(2),
--Sat4 nvarchar(2),
--Sun5 nvarchar(2),
--Mon5 nvarchar(2),
--Tue5 nvarchar(2),
--Wed5 nvarchar(2),
--Thu5 nvarchar(2),
--Fri5 nvarchar(2),
--Sat5 nvarchar(2)
)

While(@years<=@yearsnext)
Begin
DECLARE @Date DATETIME = @years+ '01'+ '01'

DECLARE @Dates TABLE
( Date DATE, MonthNum INT,
MonthName VARCHAR(15),
DayNum INT,
MonthStart DATE,
Day VARCHAR(2)
)
;WITH Dates AS
( SELECT DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY Object_ID) - 8, @Date) [Date]
FROM sys.All_Objects
)
INSERT @Dates
SELECT Date,
DATEPART(MONTH, Date) [MonthNum],
LEFT(DATENAME(MONTH, Date), 3) [MonthName],
DATEPART(WEEKDAY, Date) [DayNum],
DATEADD(MONTH, DATEDIFF(MONTH, 0, d1.Date), 0) [MonthStart],
CONVERT(VARCHAR(2), DATEPART(DAY, Date)) [Day]
FROM Dates d1
WHERE Date < DATEADD(YEAR, 1, @Date)

;WITH Calendar AS
( SELECT MonthName,
MonthNum,
CONVERT(VARCHAR(1), DayNum) + CONVERT(VARCHAR(1), Occurance) [Identifier],
[Day]
FROM @Dates d1
CROSS APPLY
( SELECT COUNT(*) [Occurance]
FROM @Dates d2
WHERE d2.Date BETWEEN DATEADD(DAY, 1 - DATEPART(WEEKDAY, d1.MonthStart), d1.MonthStart) AND d1.Date
AND d1.DayNum = d2.DayNum
) o
WHERE Date >= @Date
UNIONS ALL
SELECT LEFT(DATENAME(MONTH, @years + RIGHT('0' + CONVERT(VARCHAR, MonthNumber), 2) + '01'), 3) [MonthName],
MonthNumber,
DayNumber + Occurance [ID], '' [Day]
FROM (VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7')) AS d (DayNumber),
(VALUES ('1'), ('2'), ('3'), ('4'), ('5'), ('6')) AS o (Occurance),
(VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS m (MonthNumber)
)
--INSERT INTO tempDataStore
--([MonthName],[Year],Dat,Sun,Mon,Tue,Wed,Thu,Fri,Sat,Sun1,Mon1,Tue1,Wed1,Thu1,Fri1,Sat1,Sun2,Mon2,Tue2,Wed2,Thu2,Fri2,Sat2,Sun3,Mon3,Tue3,Wed3,Thu3,Fri3,Sat3,Sun4,Mon4,Tue4,Wed4,Thu4,Fri4,Sat4,Sun5,Mon5,Tue5,Wed5,Thu5,Fri5,Sat5)
SELECT [MonthName],[Year]=@years,'01/'+Cast([MonthName] as varchar)+'/'+Cast(@years as varchar) as Dt,
[11] [Sun], [21] [Mon], [31] [Tue], [41] [Wed], [51] [Thu], [61] [Fri], [71] [Sat],
[12] [Sun], [22] [Mon], [32] [Tue], [42] [Wed], [52] [Thu], [62] [Fri], [72] [Sat],
[13] [Sun], [23] [Mon], [33] [Tue], [43] [Wed], [53] [Thu], [63] [Fri], [73] [Sat],
[14] [Sun], [24] [Mon], [34] [Tue], [44] [Wed], [54] [Thu], [64] [Fri], [74] [Sat],
[15] [Sun], [25] [Mon], [35] [Tue], [45] [Wed], [55] [Thu], [65] [Fri], [75] [Sat],
[16] [Sun], [26] [Mon], [36] [Tue], [46] [Wed], [56] [Thu], [66] [Fri], [76] [Sat]
FROM Calendar
PIVOT
( MAX([Day])
FOR [Identifier] IN
( [11], [21], [31], [41], [51], [61], [71],
[12], [22], [32], [42], [52], [62], [72],
[13], [23], [33], [43], [53], [63], [73],
[14], [24], [34], [44], [54], [64], [74],
[15], [25], [35], [45], [55], [65], [75],
[16], [26], [36], [46], [56], [66], [76]
)
) pvt
ORDER BY MonthNum
SET @years=@years+1
End

--select *
-- from tempDataStore
--where Dat between '01/Apr/'+Cast(@yearscurrent as varchar) and '31/Mar/'+Cast(@yearsnext as varchar)
--drop table tempDataStore

/*

Create table #events
(
UserName varchar(200),
[Start_Date] Datetime null,
End_Date Datetime null,
Description nvarchar(255),
Room nvarchar(50)
)
insert into #events ('Ali','23/Dec/2013','24/Dec/2013','Demo','Room2')
insert into #events ('Ali','12/Nov/2013','12/Nov/2013','Kits','Room3')
insert into #events ('John','22/May/2013','23/Jan/2013','Jom','Room1')
insert into #events ('Ali','15/Jan/2014','17/Jan/2014','Testing','Room1')
*/

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-15 : 05:48:38

try this


;With Calendar
AS
(
SELECT CAST('20130101' AS Datetime) AS dt
UNION ALL
SELECT Dt + 1
FROM Calendar
WHERE Dt + 1 < '20140101'
)
SELECT dt,
DAY(dt) AS Dy,
DATENAME(dw,dt) AS DayName,
LEFT(DATENAME(dw,dt),3) + CAST(ROW_NUMBER() OVER (PARTITION BY YEAR(dt),DATENAME(dw,dt) ORDER BY [dt]) AS varchar(2)) AS DayNameHeader
INTO #Temp
FROM Calendar
ORDER BY Dt
OPTION (MAXRECURSION 0)



DECLARE @DayList varchar(max)
SELECT @DayList = COALESCE(@DayList,'') + ',[' + DayNameHeader + ']'
FROM #Temp
ORDER BY dt

DECLARE @SQL varchar(max)='SELECT *
FROM (SELECT Dy,DayNameHeader FROM #Temp)t
PIVOT(MAX(Dy) FOR DayNameHeader IN (' + STUFF(@DayList,1,1,'') + '))p'


EXEC (@SQL)

DROP TABLE #Temp


output
--------
Tue1	Wed1	Thu1	Fri1	Sat1	Sun1	Mon1	Tue2	Wed2	Thu2	Fri2	Sat2	Sun2	Mon2	Tue3	Wed3	Thu3	Fri3	Sat3	Sun3	Mon3	Tue4	Wed4	Thu4	Fri4	Sat4	Sun4	Mon4	Tue5	Wed5	Thu5	Fri5	Sat5	Sun5	Mon5	Tue6	Wed6	Thu6	Fri6	Sat6	Sun6	Mon6	Tue7	Wed7	Thu7	Fri7	Sat7	Sun7	Mon7	Tue8	Wed8	Thu8	Fri8	Sat8	Sun8	Mon8	Tue9	Wed9	Thu9	Fri9	Sat9	Sun9	Mon9	Tue10	Wed10	Thu10	Fri10	Sat10	Sun10	Mon10	Tue11	Wed11	Thu11	Fri11	Sat11	Sun11	Mon11	Tue12	Wed12	Thu12	Fri12	Sat12	Sun12	Mon12	Tue13	Wed13	Thu13	Fri13	Sat13	Sun13	Mon13	Tue14	Wed14	Thu14	Fri14	Sat14	Sun14	Mon14	Tue15	Wed15	Thu15	Fri15	Sat15	Sun15	Mon15	Tue16	Wed16	Thu16	Fri16	Sat16	Sun16	Mon16	Tue17	Wed17	Thu17	Fri17	Sat17	Sun17	Mon17	Tue18	Wed18	Thu18	Fri18	Sat18	Sun18	Mon18	Tue19	Wed19	Thu19	Fri19	Sat19	Sun19	Mon19	Tue20	Wed20	Thu20	Fri20	Sat20	Sun20	Mon20	Tue21	Wed21	Thu21	Fri21	Sat21	Sun21	Mon21	Tue22	Wed22	Thu22	Fri22	Sat22	Sun22	Mon22	Tue23	Wed23	Thu23	Fri23	Sat23	Sun23	Mon23	Tue24	Wed24	Thu24	Fri24	Sat24	Sun24	Mon24	Tue25	Wed25	Thu25	Fri25	Sat25	Sun25	Mon25	Tue26	Wed26	Thu26	Fri26	Sat26	Sun26	Mon26	Tue27	Wed27	Thu27	Fri27	Sat27	Sun27	Mon27	Tue28	Wed28	Thu28	Fri28	Sat28	Sun28	Mon28	Tue29	Wed29	Thu29	Fri29	Sat29	Sun29	Mon29	Tue30	Wed30	Thu30	Fri30	Sat30	Sun30	Mon30	Tue31	Wed31	Thu31	Fri31	Sat31	Sun31	Mon31	Tue32	Wed32	Thu32	Fri32	Sat32	Sun32	Mon32	Tue33	Wed33	Thu33	Fri33	Sat33	Sun33	Mon33	Tue34	Wed34	Thu34	Fri34	Sat34	Sun34	Mon34	Tue35	Wed35	Thu35	Fri35	Sat35	Sun35	Mon35	Tue36	Wed36	Thu36	Fri36	Sat36	Sun36	Mon36	Tue37	Wed37	Thu37	Fri37	Sat37	Sun37	Mon37	Tue38	Wed38	Thu38	Fri38	Sat38	Sun38	Mon38	Tue39	Wed39	Thu39	Fri39	Sat39	Sun39	Mon39	Tue40	Wed40	Thu40	Fri40	Sat40	Sun40	Mon40	Tue41	Wed41	Thu41	Fri41	Sat41	Sun41	Mon41	Tue42	Wed42	Thu42	Fri42	Sat42	Sun42	Mon42	Tue43	Wed43	Thu43	Fri43	Sat43	Sun43	Mon43	Tue44	Wed44	Thu44	Fri44	Sat44	Sun44	Mon44	Tue45	Wed45	Thu45	Fri45	Sat45	Sun45	Mon45	Tue46	Wed46	Thu46	Fri46	Sat46	Sun46	Mon46	Tue47	Wed47	Thu47	Fri47	Sat47	Sun47	Mon47	Tue48	Wed48	Thu48	Fri48	Sat48	Sun48	Mon48	Tue49	Wed49	Thu49	Fri49	Sat49	Sun49	Mon49	Tue50	Wed50	Thu50	Fri50	Sat50	Sun50	Mon50	Tue51	Wed51	Thu51	Fri51	Sat51	Sun51	Mon51	Tue52	Wed52	Thu52	Fri52	Sat52	Sun52	Mon52	Tue53
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	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	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	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	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	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	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	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	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	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	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	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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

micnie_2020
Posting Yak Master

232 Posts

Posted - 2014-01-15 : 09:11:42

Thank you Nagino & Visakh.

I manage to get it after refer to both code & modified to suit my solution. Here is the correct answer I looking for:-

;With Calendar
AS
(
SELECT CAST('20130101' AS Datetime) AS dt
UNION ALL
SELECT Dt + 1
FROM Calendar
WHERE Dt + 1 < '20140101'
)
SELECT dt,
DAY(dt) AS Dy,
DATENAME(dw,dt) AS DayName,
LEFT(DATENAME(dw,dt),3) + CAST(ROW_NUMBER() OVER (PARTITION BY YEAR(dt),DATENAME(dw,dt) ORDER BY [dt]) AS varchar(2)) AS DayNameHeader
INTO #Temp
FROM Calendar
ORDER BY Dt
OPTION (MAXRECURSION 0)

DECLARE @DayList varchar(max)
SELECT @DayList = COALESCE(@DayList,'') + ',[' + DayNameHeader + ']'
FROM #Temp
ORDER BY dt

DECLARE @SQL varchar(max)='SELECT *
FROM (SELECT Dy,DayNameHeader FROM #Temp)t
PIVOT(MAX(Dy) FOR DayNameHeader IN (' + STUFF(@DayList,1,1,'') + '))p'

EXEC (@SQL)

DROP TABLE #Temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-16 : 05:17:09

quote:
Originally posted by micnie_2020

Thank you Nagino & Visakh.

I manage to get it after refer to both code & modified to suit my solution. Here is the correct answer I looking for:-

;With Calendar
AS
(
SELECT CAST('20130101' AS Datetime) AS dt
UNION ALL
SELECT Dt + 1
FROM Calendar
WHERE Dt + 1 < '20140101'
)
SELECT dt,
DAY(dt) AS Dy,
DATENAME(dw,dt) AS DayName,
LEFT(DATENAME(dw,dt),3) + CAST(ROW_NUMBER() OVER (PARTITION BY YEAR(dt),DATENAME(dw,dt) ORDER BY [dt]) AS varchar(2)) AS DayNameHeader
INTO #Temp
FROM Calendar
ORDER BY Dt
OPTION (MAXRECURSION 0)

DECLARE @DayList varchar(max)
SELECT @DayList = COALESCE(@DayList,'') + ',[' + DayNameHeader + ']'
FROM #Temp
ORDER BY dt

DECLARE @SQL varchar(max)='SELECT *
FROM (SELECT Dy,DayNameHeader FROM #Temp)t
PIVOT(MAX(Dy) FOR DayNameHeader IN (' + STUFF(@DayList,1,1,'') + '))p'

EXEC (@SQL)

DROP TABLE #Temp

what did you modify?
I cant spot any changes from my earlier suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources