Share via

Returning multiple rows based on a date range in a single row

  • Article
  • 12/16/2008

Question

Tuesday, December 16, 2008 3:08 PM

I am working with a database that stores information using a date range and a value as shown below.

ORIGINAL RECORDS FROM FORECAST Table

FORECASTID ASSINGMENTID         STARTDATE   ENDDATE       FORECASTVALUE
1121                      578              10/1/08         12/31/08       0.75
2327                      648              11/1/08         12/31/09       0.60

In this example, the FORECAST value is an estimate of the FTE (full time equivalent) headcount that will be needed during that date range. For reporting purposes, it would be much easier if I could expand that single record into a separate record for each month. This would allow me to aggregate the data much more easily than I can now with the way the database is laid out.

The desired state would look like the table shown below..

DESIRED RECORDSET

FORECASTID ASSIGNMENTID FORECASTMONTH FORECASTVALUE
1121             578                        10/1/08         0.75
1121             578                        11/1/08         0.75
1121             578                        12/1/08         0.75
2327             648                        11/1/08         0.60
2327             648                        12/1/08         0.60
2327             648                        01/1/09         0.60
2327             648                        02/1/09         0.60
2327             648                        03/1/09         0.60

Anybody have any ideas on how to accomplish this?

All replies (5)

Tuesday, December 16, 2008 4:22 PM ✅Answered | 3 votes

I agree with Arnie here.  A calendar table makes this type of processing very simplistic, performant, and manageable.  You can use the code below to get a general idea of how to attack the problem.

DECLARE @t TABLE(  
FORECASTID INT,  
ASSIGNMENTID INT,  
STARTDATE datetime,  
ENDDATE datetime,  
FORECASTVALUE DECIMAL(9,2)  
)  
 
INSERT INTO @t VALUES (1121,578,'10/1/2008','12/31/2008',0.75);  
INSERT INTO @t VALUES (2327,648,'11/1/2008','3/30/2009',0.60);  
 
SELECT 
    t.Forecastid,  
    t.AssignmentID,  
    DateAdd(MONTH,DateDiff(MONTH,0,c.dte),0) AS ForecastMonth,  
    t.ForecastValue  
FROM [Calendar] c  
LEFT JOIN @t t  
    ON c.dte >= t.StartDate AND   
       c.dte <= EndDate  
WHERE   
    c.[Dte] >= '10/1/2008' AND 
    c.[Dte] <= '12/31/2009' AND 
    t.ForecastvALUE > 0  
GROUP BY   
    t.Forecastid,  
    t.AssignmentID,  
    DateAdd(MONTH,DateDiff(MONTH,0,c.dte),0),  
    t.ForecastValue  
 
/*  
FORECASTID ASSIGNMENTID        FORECASTMONTH FORECASTVALUE  
1121             578              10/1/08         0.75  
1121             578              11/1/08         0.75  
1121             578              12/1/08         0.75  
2327             648              11/1/08         0.60  
2327             648              12/1/08         0.60  
2327             648              01/1/09         0.60  
2327             648              02/1/09         0.60  
2327             648              03/1/09         0.60  
*/ 

Tuesday, December 16, 2008 3:20 PM

The only way i see this possible is to do it with a loop in a transact SQL procedure. Here are the steps i don't know by heart the way to do it so i'll use keywords instead

  • Declare and open a cursor on the data to work with
  • Loop the cursor and for each record:
  • Loop the month difference between the start and enddate using a for loop
  • Output the data to the buffer

You can find all the info you need with the keywords CURSOR. I started with that and was able to produce some quite crazy things with this.

Peace man
Groupe-CDGI Developper

Tuesday, December 16, 2008 3:24 PM | 1 vote

This is one of those situations where have a 'Calendar' table in the database makes life simple.

Please review these resources about using a Calendar table.

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/20/calendar-table-example-and-how-to-use-it.aspx
http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

You may be only one person in the world, but you may also be the world to one person.

Note: Using a CURSOR is NOT the best solution for this problem.

Tuesday, December 16, 2008 3:49 PM

Wow arnie, this is great, i think i can actually solve my problem in the thread i just posted this morning. If not, i can probably go one step further... I'll stop here, my point is not to hijack this thread. thanks
Groupe-CDGI Developper

Monday, January 5, 2009 6:39 PM

marcjmorris,

Has your issue been resolved?  If so, please mark the post(s) that helped you solve your issue, as the answer.

Thanks,
Adam