CODEWITHSUNDEEP
sqlsql-server
mohan111
mohan111

Reputation: 8865

How can i split dates into 15 days intervals in a month

I have sample data set like this : 

CREATE TABLE Data
(
   Start DATETIME,
   EndDt DATETIME,
   Amount INT
)
GO

--Insert Data

INSERT INTO Data(Start,EndDt,Amount)
VALUES
('14-Apr-14','13-May-14',200),
('15-May-14','16-Jun-14',320)

Sample Input :

Start        End       Amount
14-Apr-14   13-May-14   200
15-May-14   16-Jun-14   320

Sample Output : 

Start        End       Amount
14-Apr-14   30-Apr-14   100
01-May-14   13-May-14   100
15-May-14   31-May-14   160
01-Jun-14   16-Jun-14   160

can anyone suggest on this?

Upvotes: 1

Views: 689

Answers (3)

Thom A
Thom A

Reputation: 95827

This solution works, but assumes you have an ID column on your table. I create a Tally table to be able to generate the date ranges (which in in sets of 16 days, not 15), and then finally get a count in the group and divide Amount by it:

CREATE TABLE dbo.[Data] (ID int IDENTITY,
                         [Start] date,
                         EndDt date,
                         Amount int);
GO

INSERT INTO dbo.[Data] ([Start],
                        EndDt,
                        Amount)
VALUES ('14-Apr-14', '13-May-14', 200),
       ('15-May-14', '16-Jun-14', 320);
GO
--Create a Tally 
WITH N AS
    (SELECT N
     FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N (N) ),
Tally AS
    (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
     FROM N N1,
          N N2,
          N N3), --1000 rows, more than enough
--Create the dates
Dates AS(
    SELECT D.ID,
           DATEADD(DAY, (T.I - 1) * 16, D.[Start]) AS [Start],
           DATEADD(DAY, T.I * 16, D.[Start]) AS Enddt,
           D.EndDt AS FinalDate,
           D.Amount
    FROM dbo.[Data] D
         CROSS JOIN Tally T
    WHERE DATEADD(DAY, (T.I - 1) * 16, D.[Start]) < D.EndDt)
--Final result set
SELECT D.ID,
       LAG(DATEADD(DAY,1,D.Enddt),1,D.[Start]) OVER (PARTITION BY D.ID ORDER BY D.[Start]) AS [Start],
       CASE WHEN D.Enddt > D.FinalDate THEN D.FinalDate ELSE D.Enddt END AS EndDt,
       D.Amount / COUNT(ID) OVER (PARTITION BY ID) AS Amount
FROM Dates D
ORDER BY ID, [Start];

DB<>fiddle

Upvotes: 2

Ritika
Ritika

Reputation: 104

Try this. I have tried this code on your table and it is giving desired output

select start as dd,'start' as type,amount/2 as amount from data
union all
select enddt as dd,'end' as type,amount/2 as amount from data

select case when type = 'start' then dd--DATEADD(mm, DATEDIFF(mm, 0, dd), 0)
else DATEADD(mm, DATEDIFF(mm, 0, dd), 0) end
as start,case when type = 'start' then  DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, dd) + 1, 0))
else dd end as [end],amount
from (select start as dd,'start' as type,amount/2 as amount from data
union all
select enddt as dd,'end' as type,amount/2 as amount from data) a

Upvotes: 0

Isuri Subasinghe
Isuri Subasinghe

Reputation: 147

You can use this query:

select Start a, last_day(Start) b, amount/2 c from Data
union
select  trunc(EndDt , 'MM') a, EndDt  b, amount/2 c from Data;

Output

Upvotes: -1

Related Questions