counting the range between the leave

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

General SQL Server Forums

New to SQL Server Programming

counting the range between the leave

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2009-02-08 : 22:49:44

Declare @begin datetime
declare @end datetime

leave ---table
leaveid -pk
begindate datetime
enddate datetime

---------------------------
leave id begindate enddate
---------------------------
1 1/25/2009 2/5/2009

how about if i get the range on the middle of the begindate and enddate??i want a count that will specify the date between the begindate and enddate...............

i want an output goes like this!...

1/29/2009
1/30/2009
1/31/2009
2/1/2009
2/2/2009
2/3/2009
4/4/2009

pls. help me...
thank you in advance!

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-08 : 23:13:21

What About 1/26/2009,1/27/2009
1/28/2009
these dates dont u need them in ur o/p

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-09 : 03:03:47

[code]DECLARE @Sample TABLE
(
LeaveID INT IDENTITY(1, 1) PRIMARY KEY,
BeginDate DATETIME,
EndDate DATETIME
)

INSERT @Sample
SELECT '1/25/2009', '2/5/2009'

SELECT s.*,
DATEADD(DAY, v.Number, s.BeginDate) AS Peso
FROM @Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.Number <= DATEDIFF(DAY, BeginDate, EndDate)[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-09 : 03:21:48

Also


;With Date_CTE ([leave id],DateVal,enddate)
AS
(
SELECT [leave id],begindate,enddate
FROM table
UNION ALL
SELECT [leave id],DATEADD(dd,1,begindate),enddate
FROM Date_CTE 
WHERE DATEADD(dd,1,begindate) <=enddate
)
SELECT [leave id],DateVal FROM Date_CTE 
OPTION(MAXRECURSION 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources