SQLTeam.com | Weblogs | Forums

Need to split dates into 90 days


#1

hi All,

i have min date as 11/30/2016 and max date as 10/10/2018

i need to split this into 90 days

like 11/30/2016 as min and 2/28/2017 as max
03/01/2017 as min and 04/30/2017 as max ... till 10/10/2018

please help me with this


#2

how many columns and what would the row values look like?


#3

Hi

I have something like this ..

Please provide WHAT YOU are looking for

Drop Create Function
drop  FUNCTION [dbo].[Split]
go 

CREATE FUNCTION [dbo].[Split](@String date)     
returns @temptable TABLE (items varchar(8000))     
as     
begin     

	declare @idx int   =1
    declare @slice date = null
    declare @len int = 90
    
   
   while @idx <= @len     
    begin     
        
      set @slice =  dateadd(day,@idx,@string)
      insert into @temptable(Items) values(@slice) 
      
      set @idx = @idx  +1   
        
    end 
    return 
end
Drop Create Sample Data Script
use tempdb
go

drop table #data
go

create table #data
(
date123 date 
)
go

insert into #data select '11/30/2016' 
insert into #data select '10/10/2018'
go

select * from #data 
go
SQL Script
SELECT a.data, 
       abc.items AS character 
FROM   (SELECT Min(date123) AS data 
        FROM   #data) AS A 
       CROSS apply dbo.[Split] (A.data) AS abc

Upto 90 Rows

Result


#4

Hi

This has been taken from @harishgg1 solution and altered a bit, not sure if I understood your requirement properly but try this:-

Create Function
create  FUNCTION [dbo].[Split](@min date,@max date)     
returns @temptable TABLE (items varchar(8000))     
as     
begin     

	declare @idx int   =1
    declare @slice date = null
    declare @len int = datediff(day,@min,@max)
   
   while @idx <= @len     
    begin     
        
      set @slice =  dateadd(day,@idx,@min)
      insert into @temptable(Items) values(@slice) 
      
      set @idx = @idx  +1   
        
    end 
    return 
end
Drop Create Sample Data Script
use tempdb
go

if object_id('tempdb..#data') is not null drop table #data
go

create table #data (Min_date date, MAx_date date)
go

insert into #data select '11/30/2016','10/10/2018'

go

select * from #data 
go
SQL Script
if object_id('tempdb..#temp2') is not null drop table #temp2

SELECT a.Min_date,MAx_date, 
       abc.items AS character 
into #temp2
FROM   (SELECT * FROM #data) AS A 
       CROSS apply dbo.[Split] (A.Min_date,a.MAx_date) AS abc
go


;with cte as (
				select 
				 Min_date	
				,MAx_date	
				,datediff(day,Min_date,MAx_date)/90 as [Diff]
				,character
				,row_number() over ( partition by Min_date,MAx_date  order by character) as RN
from #temp2 a
)
	select 
	 Min_date	
	,MAx_date	
	--,[Diff]
	--,character
	--,RN
	,Min_date as Min_date_1
	,max(case when RN = 90 then character end) as MAx_date_1
	,max(case when RN = 91 then character end) as Min_date_2
	,max(case when RN = 180 then character end) as MAx_date_2
	,max(case when RN = 181 then character end) as Min_date_3
	,max(case when RN = 270 then character end) as MAx_date_3
	,max(case when RN = 271 then character end) as Min_date_4
	,max(case when RN = 360 then character end) as MAx_date_4
	,max(case when RN = 361 then character end) as Min_date_5
	,max(case when RN = 450 then character end) as MAx_date_5
	,max(case when RN = 451 then character end) as Min_date_6
	,max(case when RN = 540 then character end) as MAx_date_6
	,max(case when RN = 541 then character end) as Min_date_7
	,max(case when RN = 630 then character end) as MAx_date_7
	,max(case when RN = 631 then character end) as Min_date_8
	,max(case when RN = 720 then character end) as MAx_date_8
from cte
group by 
 Min_date	
,MAx_date	

#5

create proc [dbo].[SplitItems](@min date)
--returns @temptable TABLE (items varchar(8000),mindate date)
as
begin

create table #temp
(
Items varchar(8000),
mindate date
)

declare @idx int   =1
declare @slice date = null
declare @len int = 90

while @idx <= @len
begin

  set @slice =  dateadd(day,@idx,@min)
  insert into #temp(Items,mindate) values(@slice,@min) 
  set @idx = @idx  +1   
  
    
end 
select max(Items) AS MaxDate,mindate as MinDate from #temp
group by mindate
return 

end

exec [dbo].[SplitItems] @min = '11/1/2016'


#6

here is another way of doing it

using tally table

please check it and let me know

Create Tally Table
use tempdb 
go 

drop table #tally
go 

select  TOP 90 IDENTITY(INT,1,1) AS N into #tally   from sys.all_columns 
go
Create Data
use tempdb
go

drop table #data
go

create table #data
(
date123 date 
)
go

insert into #data select '11/30/2016' 
insert into #data select '10/10/2018'
go

select * from #data 
go
SQL
SELECT Dateadd(day, b.n, a.min123) 
FROM   (SELECT Min(date123) AS min123 
        FROM   #data) a, 
       #tally b
Result


#7

Alternative:

with cte_tally
  as (select dateadd(day,91*(row_number() over(order by (select null))-1),@startdt) as dt
        from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t1(n) /*  10 periods= 900 days=aprox 2½ years */
/*****
 * uncomment this if needed
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as t2(n) /* 100 periods=9000 days=aprox 25 years */
 */
     )
select dt as dt_min
      ,case
          when dateadd(day,90,dt)>@enddt
          then @enddt
          else dateadd(day,90,dt)
       end
       as dt_max
  from cte_tally
 where dt<@enddt
;

#8

i have another solution using RECURSIVE CTE

hard coded max(date )

Recursive CTE
use tempdb 
go 

;with rec_cte as 
( 
	SELECT min(date123)  as n from #data  -- Anchor
 
	UNION ALL
 
	SELECT	dateadd(DAY,90,n)
	FROM	rec_cte	
	WHERE dateadd(DAY,90,n) <= '2018-10-10'
)
SELECT 
* from rec_cte

instead of hardcoding another way

SQL
declare @mindt date = null
declare @maxdt date = null

select @mindt = MIN(date123) from #data 
select @maxdt = MAX(date123) from #data 


;with rec_cte as 
( 
	SELECT @mindt as n
 
	UNION ALL
 
	SELECT	dateadd(DAY,90,n)
	FROM	rec_cte
	WHERE dateadd(DAY,90,n) <= @maxdt
)
SELECT 
* from rec_cte
go