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
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
how many columns and what would the row values look like?
Hi
I have something like this ..
Please provide WHAT YOU are looking for
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
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
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
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 [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
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
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
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'
here is another way of doing it
using tally table
please check it and let me know
use tempdb
go
drop table #tally
go
select TOP 90 IDENTITY(INT,1,1) AS N into #tally from sys.all_columns
go
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
SELECT Dateadd(day, b.n, a.min123)
FROM (SELECT Min(date123) AS min123
FROM #data) a,
#tally b
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
;
i have another solution using RECURSIVE CTE
hard coded max(date )
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
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
hi
i know this is from a long long time ago ....
I saw that no one understood ( what moh_sul was asking !!! )
including me ...
Now after 1 year here it goes
( stars have aligned including ... sun moon venus .. saturn )
there is small doubt here
does he want mindate till 10/10/2018 or maxdate till 10/10/2018
i assumed maxdate ...
;WITH tally (n)
AS (SELECT TOP 1000 Row_number()
OVER (
ORDER BY (SELECT NULL)) - 1
FROM sys.all_columns a
CROSS JOIN sys.all_columns b),
cte
AS (SELECT min_date = Cast('2016-11-30' AS DATE),
max_date = Cast('2018-10-10' AS DATE))
SELECT 'SQL Output',
Dateadd(dd, ( n * 90 ) + 1, cte.min_date) AS mindate,
Dateadd(dd, ( ( n + 1 ) * 90 ), cte.min_date) AS maxdate
FROM tally,
cte
WHERE Dateadd(dd, ( ( n + 1 ) * 90 ), cte.min_date) <= max_date
go
Thank you @ [harishgg1] yes it max date.