SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Displayed Yearly calendar
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 01/14/2014 :  23:14:09  Show Profile  Reply with Quote
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

Malaysia
228 Posts

Posted - 01/15/2014 :  01:18:00  Show Profile  Reply with Quote
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

Edited by - micnie_2020 on 01/15/2014 01:29:20
Go to Top of Page

nagino
Yak Posting Veteran

Japan
68 Posts

Posted - 01/15/2014 :  01:43:01  Show Profile  Reply with Quote
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.
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 01/15/2014 :  04:37:23  Show Profile  Reply with Quote
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

Edited by - micnie_2020 on 01/15/2014 04:42:42
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/15/2014 :  05:48:38  Show Profile  Reply with Quote
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
Go to Top of Page

micnie_2020
Posting Yak Master

Malaysia
228 Posts

Posted - 01/15/2014 :  09:11:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/16/2014 :  05:17:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000