Pattern : 0 1 1 0 2 3
Repeat Cycle : 6 (after every 6th value 7th Value should start from 0 ..and it continues)
Enddate : 1 month ( 30 / 6 = 5) ..... extension to another month if we cannot complete the repeat
I have Calender table (it has days , months, years, weeks, )
I wanted to repeat this pattern for each day in the Calender starting today till end date as mentioned above.
29/7/2019 - 0
30/7/2019 - 1
31/7/2019 - 1
01/08/2019 - 0
02/08/2019 - 2
03/08/2019 - 3
Solution Planned:
Get all the month days (30/6 = 5) in temp table
Get the pattern in Temp table
Will this solution work or can we do it any other better way?
declare @tmp as table (id int)
insert into @tmp
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
;WITH gen_num(id,n) AS (
SELECT
id,
(row_number() OVER (ORDER BY id) - 1) % 6 + 1
FROM @tmp)
select * from gen_num
hi
i tried to do this ... hope it helps 
does this look like something you want !!
drop create data ...
drop table #tmp
go
create table #tmp ( id int )
insert into #tmp
select 1
union
select 2
union
select 3
union
select 4
union
select 5
union
select 6
union
select 7
union
select 8
union
select 9
union
select 10
union
select 11
SQL
; WITH cte_tmp
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT NULL)) AS rn,
*
FROM #tmp),
cte_tmpmod6
AS (SELECT ( rn - 1 )%6 AS rn6,
id
FROM cte_tmp),
cte_table
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT NULL) ) - 1 AS rn,
a.*
FROM (SELECT 0 AS ok
UNION ALL
SELECT 1
UNION ALL
SELECT 1
UNION ALL
SELECT 0
UNION ALL
SELECT 2
UNION ALL
SELECT 3) a)
SELECT a.ok,b.id,a.rn
FROM cte_table a
JOIN cte_tmpmod6 b
ON a.rn = b.rn6
go
1 Like
declare @tmpPattern as table (tmpID int, tmpPattern int)
declare @tmpupdatePattern as table (dateint int, pattern int)
insert into @tmpPattern
select 1 , 0
union
select 2 , 1
union
select 3 , 1
union
select 4 , 0
union
select 5 , 2
union
select 6 , 2
union
select 7 , 3
declare @DDstartdateint int,
@DDEnddateint int,
@Startdate DATE= '07/01/2019',
@enddate DATE,
@Patterndays INT=7;
select @enddate = EOMONTH(@Startdate)
--- get the startdate and the enddate (will be as per the Pattern completion)
;WITH cte
AS (SELECT CONVERT(DATE, @Startdate) [dates],
1 AS id
UNION ALL
SELECT Dateadd(dd, @Patterndays, [dates]),
ID + 1
FROM cte
WHERE dates < Dateadd(dd, -@Patterndays, CONVERT(DATE, @enddate)))
select @DDstartdateint = CONVERT(VARCHAR,min(StartshiftDate),112) , @DDEnddateint = CONVERT(VARCHAR,max(EndShiftDate),112) from
(
SELECT id,
dates AS StartshiftDate,
Dateadd(DD, @Patterndays, dates) EndShiftDate
FROM cte
) tbl
Option (maxrecursion 0)
--select * from @tmpdates
-- this will be my calender days where date will be stored in INT and the key with Rownumber
; with datedim as
(
select OID
,(row_number() OVER (ORDER BY OID) - 1) % @Patterndays + 1 as rn
from DATEDIMENSION where oid >= @DDstartdateint and OID < @DDEnddateint
)
--select * from datedim
--Numbering the Pattern
, patterncte as
(
select
tmpPattern,
(row_number() OVER (ORDER BY tmpID)) as rn
from @tmpPattern
)--select * from patterncte
insert into @tmpupdatePattern
select
d.OID
,p.tmpPattern
from
datedim d
inner join
patterncte p on d.rn = p.rn
select * from @tmpupdatePattern order by dateint
Output
dateint pattern
20190701 0
20190702 1
20190703 1
20190704 0
20190705 2
20190706 2
20190707 3
20190708 0
20190709 1
20190710 1
20190711 0
20190712 2
20190713 2
20190714 3
20190715 0
20190716 1
20190717 1
20190718 0
20190719 2
20190720 2
20190721 3
20190722 0
20190723 1
20190724 1
20190725 0
20190726 2
20190727 2
20190728 3
20190729 0
20190730 1
20190731 1
20190801 0
20190802 2
20190803 2
20190804 3