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.
| Author |
Topic |
|
jaisonlucas
Starting Member
3 Posts |
 Posted - 2010-06-29 : 03:08:58
|
I need your help.
I have a table in which i have records FromDate and ToDate, and other colns.
I want to generate a view in which i will get the following:
FromDate=01/01/2010 ; ToDate=04/01/2010
ChkDate FromDate ToDate
01/01/2010 01/01/2010 04/01/2010
02/01/2010 01/01/2010 04/01/2010
03/01/2010 01/01/2010 04/01/2010
chkdate is not part of the table record.
For each date range, it should show me <ToDate - FromDate> records.
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2010-06-30 : 10:13:22
|
DECLARE @TABLE TABLE (FromDate datetime,ToDate datetime)
INSERT INTO @table
SELECT '01/01/2010','04/01/2010' UNION
SELECT '01/01/2010','04/01/2010' UNION
SELECT '01/01/2010','04/01/2010' U
select DATEADD(month,spt.Number,FromDate) ,t.FromDate,t.ToDate
from @table t
CROSS JOIN
(select number from master..spt_values where type = 'P'
) spt
WHERE spt.number < datediff(month,FromDate,ToDate)
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
|
|
|
SQL Query - View to be made