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
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.
15 People are following this question.
