holiday date - 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)

holiday date

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-01 : 01:44:45

i have table - SpecialDate (hold dates)

OpenDate      CloseDate      AmountHours
2009-04-08    2009-04-08         19
2009-04-09    2009-04-09         24
2009-04-10    2009-04-10         19
2009-04-11    2009-04-11         19

i want to update the table OrderTable

if my orderTable.OpenDate <= SpecialDate.OpenDate And orderTable.CloseDate >= SpecialDate.CloseDate
Than
orderTable.TotalHours=orderTable.TotalHours-SpecialDate.AmountHours
how can do it in query

thanks

Sachin.Nand

2937 Posts

Posted - 2010-02-01 : 01:56:03

[code]
Update ORDTBL
SET ORDTBL .TotalHours=ORDTBL .TotalHours-SPDT.AmountHours
FROM OrderTable inner join SpecialDate SPDT ON
ORDTBL.OpenDate <= SPDT .OpenDate And ORDTBL.CloseDate >= SPDT .CloseDate
[/code]
P.S please take a backup before using the query just in case :)

PBUH

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-02-04 : 08:58:05

i try that
the problem is ,the didnt calculate righ:
suppose i have
OpenDate : 2009-02-08
CloseDate: 2009-04-12

so he need ORDTBL .TotalHours-19-24-19-19
and actually he take the first one (the first -19)

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:10:31

[code]Update ORDTBL
SET ORDTBL .TotalHours=ORDTBL .TotalHours-SPDT.AmountHours
FROM OrderTable ORDTBL
cross apply(select sum(AmountHours) AS AmountHours
from SpecialDate
where ORDTBL.OpenDate <= OpenDate
And ORDTBL.CloseDate >= CloseDate)SPDT
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources