SQL Query - View to be made - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

SQL Query - View to be made

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources