Count - 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)

Count

Author

Topic

cool.mugil
Starting Member

32 Posts

Posted - 2008-07-31 : 01:10:53

hai,
i have my table structure as follows,
S.No. FromDate ToDate
1 01/26/2008 01/26/2008
2 07/01/2008 07/05/2008
3 08/09/2008 08/17/2008

I will pass two parameters StartDate and EndDate,If the Startdate or Enddate exists in this table,then it should display the count of days.
For example,
If Startdate=07/02/2008 and Enddate=07/03/2008 then count=2
If StartDate=08/01/2008 and Enddate=08/14/2008 then count=6
Please guide me.
Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-31 : 01:21:07

[code]DECLARE @TABLE TABLE
(
SNo int,
FromDate datetime,
ToDate datetime
)

INSERT INTO @TABLE
SELECT 1, '20080126', '20080126' UNION ALL
SELECT 2, '20080701', '20080705' UNION ALL
SELECT 3, '20080809', '20080817'

DECLARE @StartDate datetime,
@EndDate datetime

SELECT @StartDate = '20080702',
@EndDate = '20080703'

-- SELECT @StartDate = '20080801',
-- @EndDate = '20080814'

SELECT DATEDIFF(DAY,
CASE WHEN FromDate < @StartDate THEN @StartDate ELSE FromDate END,
CASE WHEN ToDate < @EndDate THEN ToDate ELSE @EndDate END) + 1
FROM @TABLE
WHERE (
FromDate <= @StartDate
AND ToDate >= @StartDate
)
OR (
FromDate <= @EndDate
AND ToDate >= @EndDate
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources