September 22, 2016 at 3:01 am
WeekNo FromDate ThruDate Period
36 2016-08-29 2016-09-04 201609
37 2016-09-05 2016-09-11 201609
38 2016-09-12 2016-09-18 201609
39 2016-09-19 2016-09-25 201609
i want to make a series of record like this
WeekNo Date Period
36 2016-08-29 201609
36 2016-08-30 201609
36 2016-08-31 201609
36 2016-09-01 201609
36 2016-09-02 201609
36 2016-09-03 201609
36 2016-09-04 201609
37 2016-09-05 201609
37 2016-09-06 201609
37 2016-09-07 201609
37 2016-09-08 201609
37 2016-09-09 201609
37 2016-09-10 201609
37 2016-09-11 201609
38 2016-09-12 201609
.... etc
is it possible create a query using recursive cte ?
i need a help from expert..
thank u..
September 22, 2016 at 3:27 am
ok..
i give u temporary table for u try..
CREATE TABLE #tamp
( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )
INSERT INTO #tamp VALUES (36,'2016-08-29','2016-09-04','201609')
INSERT INTO #tamp VALUES (37,'2016-09-05','2016-09-11','201609')
INSERT INTO #tamp VALUES (38,'2016-09-12','2016-09-18','201609')
INSERT INTO #tamp VALUES (39,'2016-09-19','2016-09-25','201609')
September 22, 2016 at 3:31 am
Recursive CTE can work, but it'll be slow and it's not a good solution.
Better is to use a calendar table/numbers table. If you don't have one in the DB, create one (as a permanent table), they're very useful for a whole pile of things.
Code in a few minutes...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 3:39 am
actually i can loop datetime variable using while, and increment with 1 days through loop.
but i wanna hear any idea which simplify the code..
if u said using cte will getting worst performance, then i will wait for your code..
September 22, 2016 at 3:40 am
gentong.bocor (9/22/2016)
ok..i give u temporary table for u try..
CREATE TABLE #tamp
( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )
INSERT INTO #tamp VALUES (36,'2016-08-29','2016-09-04','201609')
INSERT INTO #tamp VALUES (37,'2016-09-05','2016-09-11','201609')
INSERT INTO #tamp VALUES (38,'2016-09-12','2016-09-18','201609')
INSERT INTO #tamp VALUES (39,'2016-09-19','2016-09-25','201609')
are you only going to be having one "period" or are you going to have a much lager data set that spans multiple periods/years?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 22, 2016 at 3:50 am
J Livingston SQL (9/22/2016)
gentong.bocor (9/22/2016)
ok..i give u temporary table for u try..
CREATE TABLE #tamp
( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )
INSERT INTO #tamp VALUES (36,'2016-08-29','2016-09-04','201609')
INSERT INTO #tamp VALUES (37,'2016-09-05','2016-09-11','201609')
INSERT INTO #tamp VALUES (38,'2016-09-12','2016-09-18','201609')
INSERT INTO #tamp VALUES (39,'2016-09-19','2016-09-25','201609')
are you only going to be having one "period" or are you going to have a much lager data set that spans multiple periods/years?
basically, i want to make a function which supply only 1 parameter such as @year, then the query result number of week and date
for example.. in september 2016, the first week is starting from 2016-08-29 up to 2016-09-04, the 2nd week is from 2016-09-05 up to 2016-09-11
but i want the date become rows (not like my example FromDate & ThruDate) but just a series of date..
i just call the function i.e dbo.fn_dayofweekcalendar(2016)
then the result is like what i describe.
thanks
September 22, 2016 at 3:54 am
gentong.bocor (9/22/2016)
J Livingston SQL (9/22/2016)
gentong.bocor (9/22/2016)
ok..i give u temporary table for u try..
CREATE TABLE #tamp
( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )
INSERT INTO #tamp VALUES (36,'2016-08-29','2016-09-04','201609')
INSERT INTO #tamp VALUES (37,'2016-09-05','2016-09-11','201609')
INSERT INTO #tamp VALUES (38,'2016-09-12','2016-09-18','201609')
INSERT INTO #tamp VALUES (39,'2016-09-19','2016-09-25','201609')
are you only going to be having one "period" or are you going to have a much lager data set that spans multiple periods/years?
and what about periods...how are these calculated?
12 per year / every 4 weeks or something else?
basically, i want to make a function which supply only 1 parameter such as @year, then the query result number of week and date
for example.. in september 2016, the first week is starting from 2016-08-29 up to 2016-09-04, the 2nd week is from 2016-09-05 up to 2016-09-11
but i want the date become rows (not like my example FromDate & ThruDate) but just a series of date..
i just call the function i.e dbo.fn_dayofweekcalendar(2016)
then the result is like what i describe.
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 22, 2016 at 4:00 am
The stuff you posted in the #tamp table, do you have that data at the start, or is it part of what you want to generate?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 22, 2016 at 4:09 am
GilaMonster (9/22/2016)
The stuff you posted in the #tamp table, do you have that data at the start, or is it part of what you want to generate?
the #tamp table is the part of the result from my function..
my function can produce the result like on #tamp table..
but my code is not good enough.. i need your idea to make a function which result week number and series of date by supplying only the year
select * from dbo.myfunction(2016) where weekno=1
WeekNo Date Period
1 2015-12-28 201601
1 2015-12-29 201601
1 2015-12-30 201601
1 2015-12-31 201601
1 2016-01-01 201601
1 2016-01-02 201601
1 2016-01-03 201601
select * from dbo.myfunction(2016) where weekno=4
WeekNo Date Period
4 2016-01-18 201601
4 2016-01-19 201601
4 2016-01-20 201601
4 2016-01-21 201601
4 2016-01-22 201601
4 2016-01-23 201601
4 2016-01-24 201601
hope u understand what i mean..
September 22, 2016 at 4:10 am
Quick inline calendar table example using the sample data posted previously on this thread.
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#tamp') IS NOT NULL DROP TABLE #tamp;
CREATE TABLE #tamp
( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )
INSERT INTO #tamp (WeekNo,FromDate,ThruDate,Period)
VALUES (36,'2016-08-29','2016-09-04','201609')
,(37,'2016-09-05','2016-09-11','201609')
,(38,'2016-09-12','2016-09-18','201609')
,(39,'2016-09-19','2016-09-25','201609')
;
CREATE NONCLUSTERED INDEX NCLIDX_#TAMP_FROMDATE_THRUDATE_INC_WEEKNO_PERIOD ON #tamp (FromDate ASC,ThruDate ASC) INCLUDE (WeekNo,Period);
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
MIN(T.FromDate) AS FIRST_DATE
,DATEDIFF(DAY,MIN(T.FromDate),MAX(ThruDate)) AS NUM_DAYS
FROM #tamp T
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC) + 1) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,DATEADD(DAY,NM.N,BC.FIRST_DATE) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
SELECT
T.WeekNo
,INC.DATE_VAL
,T.Period
FROM #tamp T
CROSS APPLY INLINE_CALENDAR INC
WHERE INC.DATE_VAL BETWEEN T.FromDate AND T.ThruDate;
Output
WeekNo DATE_VAL Period
----------- ----------------------- ------
36 2016-08-29 00:00:00 201609
36 2016-08-30 00:00:00 201609
36 2016-08-31 00:00:00 201609
36 2016-09-01 00:00:00 201609
36 2016-09-02 00:00:00 201609
36 2016-09-03 00:00:00 201609
36 2016-09-04 00:00:00 201609
37 2016-09-05 00:00:00 201609
37 2016-09-06 00:00:00 201609
37 2016-09-07 00:00:00 201609
37 2016-09-08 00:00:00 201609
37 2016-09-09 00:00:00 201609
37 2016-09-10 00:00:00 201609
37 2016-09-11 00:00:00 201609
38 2016-09-12 00:00:00 201609
38 2016-09-13 00:00:00 201609
38 2016-09-14 00:00:00 201609
38 2016-09-15 00:00:00 201609
38 2016-09-16 00:00:00 201609
38 2016-09-17 00:00:00 201609
38 2016-09-18 00:00:00 201609
39 2016-09-19 00:00:00 201609
39 2016-09-20 00:00:00 201609
39 2016-09-21 00:00:00 201609
39 2016-09-22 00:00:00 201609
39 2016-09-23 00:00:00 201609
39 2016-09-24 00:00:00 201609
39 2016-09-25 00:00:00 201609
September 22, 2016 at 4:14 am
Eirikur Eiriksson (9/22/2016)
Quick inline calendar table example using the sample data posted previously on this thread.😎
USE TEEST;GO
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#tamp') IS NOT NULL DROP TABLE #tamp;
CREATE TABLE #tamp
( WeekNo INT, FromDate SMALLDATETIME, ThruDate SMALLDATETIME, Period VARCHAR(6) )
INSERT INTO #tamp (WeekNo,FromDate,ThruDate,Period)
VALUES (36,'2016-08-29','2016-09-04','201609')
,(37,'2016-09-05','2016-09-11','201609')
,(38,'2016-09-12','2016-09-18','201609')
,(39,'2016-09-19','2016-09-25','201609')
;
CREATE NONCLUSTERED INDEX NCLIDX_#TAMP_FROMDATE_THRUDATE_INC_WEEKNO_PERIOD ON #tamp (FromDate ASC,ThruDate ASC) INCLUDE (WeekNo,Period);
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
MIN(T.FromDate) AS FIRST_DATE
,DATEDIFF(DAY,MIN(T.FromDate),MAX(ThruDate)) AS NUM_DAYS
FROM #tamp T
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC) + 1) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,DATEADD(DAY,NM.N,BC.FIRST_DATE) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
SELECT
T.WeekNo
,INC.DATE_VAL
,T.Period
FROM #tamp T
CROSS APPLY INLINE_CALENDAR INC
WHERE INC.DATE_VAL BETWEEN T.FromDate AND T.ThruDate;
[/code]
Output
WeekNo DATE_VAL Period----------- ----------------------- ------
36 2016-08-29 00:00:00 201609
36 2016-08-30 00:00:00 201609
36 2016-08-31 00:00:00 201609
36 2016-09-01 00:00:00 201609
36 2016-09-02 00:00:00 201609
36 2016-09-03 00:00:00 201609
36 2016-09-04 00:00:00 201609
37 2016-09-05 00:00:00 201609
37 2016-09-06 00:00:00 201609
37 2016-09-07 00:00:00 201609
37 2016-09-08 00:00:00 201609
37 2016-09-09 00:00:00 201609
37 2016-09-10 00:00:00 201609
37 2016-09-11 00:00:00 201609
38 2016-09-12 00:00:00 201609
38 2016-09-13 00:00:00 201609
38 2016-09-14 00:00:00 201609
38 2016-09-15 00:00:00 201609
38 2016-09-16 00:00:00 201609
38 2016-09-17 00:00:00 201609
38 2016-09-18 00:00:00 201609
39 2016-09-19 00:00:00 201609
39 2016-09-20 00:00:00 201609
39 2016-09-21 00:00:00 201609
39 2016-09-22 00:00:00 201609
39 2016-09-23 00:00:00 201609
39 2016-09-24 00:00:00 201609
39 2016-09-25 00:00:00 201609
thanks Eirikur Eiriksson for your code,
according to http://www.sqlservercentral.com/Forums/FindPost1819885.aspx
can u make a function which result field weeknumber & date by @year as parameter ?
September 22, 2016 at 5:10 am
Everything needed was already in the code sample;-), here is a function that returns one year
😎
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.ITVFN_CALENDAR_YEAR
(
@YEAR INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
--------------------------------------------------------------------------------------
-- INLINE CALENDAR RETURNING ONE YEAR
-- 2016-09-22 Eirikur Eiriksson
--------------------------------------------------------------------------------------
RETURN
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
DATEADD(YEAR,(@YEAR - 1900),0) AS FIRST_DATE
,DATEDIFF(DAY,DATEADD(YEAR,@YEAR - 1900,0),DATEADD(YEAR,(@YEAR - 1900) + 1,0)) AS NUM_DAYS
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC)) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,CONVERT(DATE,DATEADD(DAY,NM.N,BC.FIRST_DATE),0) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
SELECT
CONVERT(INT,DATENAME(WEEK,INC.DATE_VAL),0) AS WEEK_NO
,INC.DATE_VAL AS DATE_VALUE
,(YEAR(INC.DATE_VAL) * 100)
+ CONVERT(INT,DATENAME(QUARTER,INC.DATE_VAL),0) AS PERIOD_NO
FROM INLINE_CALENDAR INC;
GO
Usage
DECLARE @YEAR INT = 2015;
SELECT
INC.WEEK_NO
,INC.DATE_VALUE
,INC.PERIOD_NO
FROM dbo.ITVFN_CALENDAR_YEAR(@YEAR) AS INC
WHERE INC.WEEK_NO = 32;
Output
WEEK_NO DATE_VALUE PERIOD_NO
-------- ---------- -----------
32 2015-08-02 201503
32 2015-08-03 201503
32 2015-08-04 201503
32 2015-08-05 201503
32 2015-08-06 201503
32 2015-08-07 201503
32 2015-08-08 201503
Edit: removed implicit conversion
September 22, 2016 at 8:01 pm
Eirikur Eiriksson (9/22/2016)
Everything needed was already in the code sample;-), here is a function that returns one year😎
USE TEEST;GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.ITVFN_CALENDAR_YEAR
(
@YEAR INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
--------------------------------------------------------------------------------------
-- INLINE CALENDAR RETURNING ONE YEAR
-- 2016-09-22 Eirikur Eiriksson
--------------------------------------------------------------------------------------
RETURN
WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
DATEADD(YEAR,(@YEAR - 1900),0) AS FIRST_DATE
,DATEDIFF(DAY,DATEADD(YEAR,@YEAR - 1900,0),DATEADD(YEAR,(@YEAR - 1900) + 1,0)) AS NUM_DAYS
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC)) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,CONVERT(DATE,DATEADD(DAY,NM.N,BC.FIRST_DATE),0) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
SELECT
CONVERT(INT,DATENAME(WEEK,INC.DATE_VAL),0) AS WEEK_NO
,INC.DATE_VAL AS DATE_VALUE
,(YEAR(INC.DATE_VAL) * 100)
+ CONVERT(INT,DATENAME(QUARTER,INC.DATE_VAL),0) AS PERIOD_NO
FROM INLINE_CALENDAR INC;
GO
Usage
DECLARE @YEAR INT = 2015;SELECT
INC.WEEK_NO
,INC.DATE_VALUE
,INC.PERIOD_NO
FROM dbo.ITVFN_CALENDAR_YEAR(@YEAR) AS INC
WHERE INC.WEEK_NO = 32;
Output
WEEK_NO DATE_VALUE PERIOD_NO-------- ---------- -----------
32 2015-08-02 201503
32 2015-08-03 201503
32 2015-08-04 201503
32 2015-08-05 201503
32 2015-08-06 201503
32 2015-08-07 201503
32 2015-08-08 201503
Edit: removed implicit conversion
your function returning start date not the same as mine..
let's say
SELECT *
FROM dbo.ITVFN_CALENDAR_YEAR(2016) AS INC
WHERE INC.WEEK_NO = 32
your function return start date with 2016-07-31, but mine is 2016-08-01
2016-07-31 is part of week no. 31
week no 31 is from 2016-07-25 up to 2016-07-31
but yours is from 2016-07-24 up to 2016-07-30
only different 1 days..
which part should i modify ?
thanks
September 22, 2016 at 8:22 pm
@Eirikur Eiriksson
i see, your code may get wrong result if called with week no. 1
calling SELECT * FROM dbo.ITVFN_CALENDAR_YEAR(2016) AS INC
WHERE INC.WEEK_NO = 1 may get 2 days, 2016-01-01 & 2016-01-02
it should be from 2015-12-28 up to 2016-01-03
here i give u my function :
CREATE FUNCTION [dbo].[SplitPeriodByWeek] (@Year AS VARCHAR(4))
RETURNS @RetStructure TABLE (BaseDate DATETIME, WeekNo INT, FromDate DATETIME, ThruDate DATETIME, Period VARCHAR(6))
AS
BEGIN
DECLARE @BaseDatetmp DATETIME, @WKTmp INT, @WKSTmp INT, @WKETmp INT, @FromDate DATETIME, @ThruDate DATETIME
SET @WKSTmp = 1
SET @WKETmp = 52
SET @WKTmp = @WKSTmp
SET @BaseDatetmp = @Year+ '-01-01'
IF UPPER(DATENAME(weekday, @BaseDatetmp)) = 'TUESDAY'
SET @BaseDatetmp = DATEADD(DAY, -1, @BaseDatetmp)
ELSE IF UPPER(DATENAME(weekday, @BaseDatetmp)) = 'WEDNESDAY'
SET @BaseDatetmp = DATEADD(DAY, -2, @BaseDatetmp)
ELSE IF UPPER(DATENAME(weekday, @BaseDatetmp)) = 'THURSDAY'
SET @BaseDatetmp = DATEADD(DAY, -3, @BaseDatetmp)
ELSE IF UPPER(DATENAME(weekday, @BaseDatetmp)) = 'FRIDAY'
SET @BaseDatetmp = DATEADD(DAY, -4, @BaseDatetmp)
ELSE IF UPPER(DATENAME(weekday, @BaseDatetmp)) = 'SATURDAY'
SET @BaseDatetmp = DATEADD(DAY, -5, @BaseDatetmp)
ELSE IF UPPER(DATENAME(weekday, @BaseDatetmp)) = 'SUNDAY'
SET @BaseDatetmp = DATEADD(DAY, -6, @BaseDatetmp)
WHILE @WKTmp <= @WKETmp
BEGIN
INSERT INTO @RetStructure
SELECT @BaseDatetmp, @WKTmp, DATEADD(DAY, 7 * (@WKTmp - 1), @BaseDatetmp), DATEADD(DAY, -1, DATEADD(DAY, 7 * @WKTmp, @BaseDatetmp)), ''
SET @WKTmp = @WKTmp + 1
END
UPDATE @RetStructure SET period=CONVERT(VARCHAR(6),thrudate,112)
RETURN
END
September 23, 2016 at 1:24 am
anyone ???
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply