Returning multiple records from a single record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Returning multiple records from a single record

Author

Topic

marcjmorris
Starting Member

1 Post

Posted - 2008-12-16 : 08:23:41

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 1/1/09 0.60
2327 648 2/1/09 0.60
2327 648 3/1/09 0.60

Any help would be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:24:11

[code]
SELECT t.FORECASTID,
t.ASSINGMENTID,
DATEADD(mm,v.number,t.STARTDATE) AS FORECASTMONTH,
t.FORECASTVALUE
FROM Table t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(mm,v.number,t.STARTDATE)<=t.ENDDATE
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources