0

Experts, Have a question regarding splitting a date range into equal parts by months including the time part

example - fromdate - 06/29/2020 09:00:00 and todate - 06/29/2021 09:00:00

Want to split this date range into twelve equal parts like below

06/29/2020 09:00:00 - 06/30/2020 12:59:59
07/01/2020 00:00:00 - 07/31/2020 12:59:59
.........
.......
06/01/2021 00:00:00 - 06/29/2021 09:00:00

I cant write recursive CTE as this is a sql synapse module I am running against..

With below query I am able to split the date part, but time part is not coming properly as above.. Please help me as this is a blocking my development

declare @FromTs DATETIME
declare @ToTs DATETIME
SET @FromTs = GetDate()
SET @ToTs = DATEADD(month, 12, @FromTs)

;WITH n(n) AS 
(
    SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(qi,qrt,qtt,n,f,t,md,bp,ep,rn) AS  
(
    SELECT 
    ,n.n, @FromTs, @ToTs, 
    DATEDIFF(MONTH, @FromTs, @ToTs),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY( @FromTs),  @FromTs)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
    DATEADD(DAY, 1-DAY( @FromTs),  @FromTs))))
    FROM n INNER JOIN <datetbl> AS d 
    ON @ToTs >= DATEADD(MONTH, n.n-1, @FromTs)
)
SELECT qi,qrt,qtt,
new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
new_to_date   = CASE n WHEN md THEN t ELSE ep END,rn
FROM d WHERE md >= n

1 Answer 1

0
WITH 
-- Range borders
src_data AS (SELECT CAST('2020-06-29 09:00:00' AS DATETIME) AS fromdate, 
                    CAST('2021-06-29 09:00:00' AS DATETIME) AS todate),
-- numbers table
numbers AS (SELECT 0 num UNION 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)                         
-- The query
SELECT num + 1 id, 
       DATEADD(SECOND, num * DATEDIFF(SECOND, fromdate, todate) / 12, fromdate) fromdate,
       DATEADD(SECOND, (num + 1) * DATEDIFF(SECOND, fromdate, todate) / 12, fromdate) todate
FROM src_data, numbers
ORDER BY id;

fiddle

The range is divided to equal (in seconds) parts. You may alter the calculations granularity (for example, use HOURS instead of SECOND), parts lengths will be adjusted and rounded automatically.

You may replace numbers CTE with any other which gets/generates the integers list from 0 to parts_amount-1, and parametrize the divident used in a query (12, may be replaced with, for example, SELECT COUNT(num) FROM numbers).

5
  • Thank you so much for the response.. Only one issue now is that the time part is overlapping like below2020-06-29 20:31:33.873 2020-07-30 06:31:33.873 2020-07-30 06:31:33.873 2020-08-29 16:31:33.873 2020-08-29 16:31:33.873 2020-09-29 02:31:33.873
    – Tech_Enthu
    Jun 30, 2020 at 13:27
  • @Tech_Enthu the time part is overlapping I think this is impossible. Adjacent, that is true - but this is how it should be. Edit source data in the fiddle, post the link and point on the rows pair with overlapping.
    – Akina
    Jun 30, 2020 at 13:31
  • dbfiddle.uk/…
    – Tech_Enthu
    Jun 30, 2020 at 13:37
  • if you see above fiddle link, the new_to_date and new_from_date is same from second row onwards
    – Tech_Enthu
    Jun 30, 2020 at 13:38
  • I see that new_to_date in each row is equal to new_from_date in the next row. This is normal. Maybe you need to round datetime values to days? do it additionally...
    – Akina
    Jun 30, 2020 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.