CODEWITHSUNDEEP
sqlsql-server
user2270911
user2270911

Reputation: 305

Adding Rows Based on the Number of Months Between two Dates

I have a data set which contains the the monthly budget for types of services a client can receive. The SERVICES_ID is the type of service, the Begin_DT and End_DT give the range of time the service is good for, the COST_PER_MONTH gives the amount budgeted monthly, and NUMBER_OF_MONTHS gives the number of months that the budget for that service is good for.

Here is a sample data set, you will notice there are two clients each have two services budgeted. One client has both services for 12 months, the other has one service 12 months and another for 6. 

CREATE TABLE Services (
    ID int,
    SERVICES_ID int,
    Begin_DT datetime, 
    End_DT datetime, 
    COST_PER_MONTH  int, 
    NUMBER_OF_MONTHS int)

('10178',   9,  '7/21/2011',    '7/21/2012' 203.2,  12)
('10178',   10, '7/21/2011',    '7/21/2012' 78.2,   12)
('13862',   12, '12/8/2011',    '6/3/2012', 469.2,  6)
('13862',   10,  '6/3/2011',    '6/3/2012',  78.2,  12)

I need to have query which adds in a Month and a Year variable and adds rows for each month the service is budgeted in the month range between Begin_DT and End_DT. This is what I am looking for: 

CREATE TABLE Services_monthly (
    ID int,
    SERVICES_ID int,
    Begin_DT datetime, 
    End_DT datetime, 
    Month int,
    Year int,
    COST_PER_MONTH  int, 
    NUMBER_OF_MONTHS int)

('10178',   9,  '7/21/2011',    '7/21/2012',    7,  2011,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    8,  2011,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    9,  2011,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    10, 2011,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    11, 2011,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    12, 2011,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    1,  2012,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    2,  2012,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    3,  2012,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    4,  2012,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    5,  2012,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    6,  2012,   203.2,  12),
('10178',   9,  '7/21/2011',    '7/21/2012',    7,  2012,   203.2,  12),
('10178',   10, '7/21/2011',    '7/21/2012',    7,  2011,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    8,  2011,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    9,  2011,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    10, 2011,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    11, 2011,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    12, 2011,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    1,  2012,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    2,  2012,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    3,  2012,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    4,  2012,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    5,  2012,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    6,  2012,   78.2,   12),
('10178',   10, '7/21/2011',    '7/21/2012',    7,  2012,   78.2,   12),
('13862',   12, '12/8/2011',    '6/3/2012',     12, 2011,   469.2,  6),
('13862',   12, '12/8/2011',    '6/3/2012',     1,  2012,   469.2,  6),
('13862',   12, '12/8/2011',    '6/3/2012',     2,  2012,   469.2,  6),
('13862',   12, '12/8/2011',    '6/3/2012',     3,  2012,   469.2,  6),
('13862',   12, '12/8/2011',    '6/3/2012',     4,  2012,   469.2,  6),
('13862',   12, '12/8/2011',    '6/3/2012',     5,  2012,   469.2,  6),
('13862',   12, '12/8/2011',    '6/3/2012',     6,  2012,   469.2,  6),
('13862',   10, '6/3/2011',     '6/3/2012',     6,  2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',     7,  2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',     8,  2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',     9,  2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',     10, 2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',     11, 2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',     12, 2011,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',      1, 2012,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',      2, 2012,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',      3, 2012,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',      4, 2012,   78.2,   12),
('13862',   10, '6/3/2011',     '6/3/2012',      5, 2012,   78.2,   12)

Upvotes: 0

Views: 591

Answers (2)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT  ID, 
        SERVICES_ID,
        Begin_DT, 
        End_DT,
        MONTH(DATEADD(MONTH,n.number, Begin_DT)) AS Month,
        YEAR(DATEADD(MONTH,n.number, Begin_DT)) AS Year,
        COST_PER_MONTH , 
        NUMBER_OF_MONTHS
FROM   Services  s,
(SELECT DISTINCT number 
 FROM master..spt_values 
 WHERE number >= 0 
 AND number < 100)  n
WHERE  n.number <= s.NUMBER_OF_MONTHS

Your result wrong, you need to add last row to your data :

| 13862 |          10 |     June, 03 2011 00:00:00+0000 | June, 03 2012 00:00:00+0000 |     6 | 2012 |           78.2 |               12 |

I think my fiddle works like you want.

Upvotes: 1

Sonam
Sonam

Reputation: 3466

Please try this:

with number_cte (number) as
(select distinct number from master..spt_values where number between 1 and 1000) 

Select  ID, SERVICES_ID,Begin_DT, End_DT,COST_PER_MONTH , NUMBER_OF_MONTHS
from   Services_monthly  s cross join number_cte n
where  n.number <= s.NUMBER_OF_MONTHS

Also, the best solution would be to create a table variable and populate it with numbers.

Upvotes: 0

Related Questions