check date in table - 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 2005 Forums

Transact-SQL (2005)

check date in table

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-21 : 07:49:35

hi,

i have table

StartDate, EndDate, TotalDays.
1.1.10     21.1.10   20
2.1.10     5.1.10    3
1.4.10     10.4.10   10

i want to check if some dates are between StartDate and EndDate,if tit is so i decrease -1.
i want to hold table that hold this Dates.

for example:
i have DateTables:
4.1.10
5.4.10

4.1.10 >=1.1.10 AND 4.1.10<21.1.10 so totaldays now is 19 (20-1)
4.1.10>=2.1.10 AND 4.1.10<5.1.10 so totaldays now is 2 (3-1)
5.4.10 >=1.4.10 AND 1.4.10<10.4.10 so totaldays now is 9 (10-1)

(how can i do it in query?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 07:53:12

4.1.10 is also between 2.1.10 and 5.1.10 so what do you want to to?

No, you're never too old to Yak'n'Roll if you're too young to die.

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-04-21 : 08:00:20

see now,
so ,do you have any idea?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-21 : 08:09:48

Try this:


-- make testdata
declare @StartEndTotal table(StartDate datetime, EndDate datetime, TotalDays int)
declare @DateTable table(MyDate datetime)

insert @StartEndTotal
select '20100101', '20100121', 20 union all
select '20100102', '20100105', 3 union all
select '20100401', '20100410', 10

insert @DateTable
select '20100104' union all
select '20100405'

-- show testdata
select * from @StartEndTotal
select * from @DateTable

-- do the update
update s
set TotalDays=TotalDays-1
from @StartEndTotal s
join @DateTable d
on d.MyDate between s.StartDate and s.EndDate

-- show result
select * from @StartEndTotal

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 23:55:28

or is it this?

UPDATE s
SET s.TotalDays=s.TotalDays-d.Cnt
FROM @StartEndTotal s
CROSS APPLY (SELECT COUNT(1) AS Cnt
             FROM @DateTable
             WHERE MyDate between s.StartDate and s.EndDate) d

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources