question

leolej avatar image
leolej asked

T-SQL Split date ranges into months

I'm working with a datawarehouse solution and I'm a little stuck with a problem regarding date periods in my etl. I'd like to split up all the date ranges in this staging table into month periods like this, see below the source and then the desired output. I only use T-sql and i've seen some solutions i C# but i can't really use those :-( Any help for this in T-SQL is much appreciated: source table id valid_from valid_to 1 2013-08-01 2013-08-11 1 2013-08-12 2013-09-11 2 2013-04-02 2013-05-02 Desired output id valid_from valid_to 1 2013-08-01 2013-08-11 1 2013-08-12 2013-08-31 1 2013-09-01 2013-09-11 2 2013-04-02 2013-04-30 2 2013-05-01 2013-05-02
splitdate-range
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So, any date range that crosses a month boundary needs to be split on that month boundary? Are these date ranges coming from the OLTP system? Do you have a date dimension?
0 Likes 0 ·
Yes, these date ranges are coming from the source oltp but i've loaded them into a staging table in my DW test environment. And yes i have a date dimension. Actually, the users want to be able to see the total sum of days at any given month for each persons case so my first assumption was that i needed to split them up in chunks according to which month these periods belongs to (month boundries). For example if there are cases in january and february for a specific person and the users selects february then they want to see that persons sum of days in jan+feb. And the way i got the source data now is cases in the range of 1 day to 700 days.
0 Likes 0 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.