how to write this query - 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)

how to write this query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-10-07 : 08:48:04

i have 2 tables:

Orders:

OrderDate     OrderEndDate  TotalDays
01-01-2010     05-01-2010       4
02-01-2010     10-01-2010       8 
05-01-2010     07-01-2010       2
10-01-2010     13-01-2010       3

holidays

HolidayDate    Days
03-01-2010      1
09-01-2010      1

so i want to update Orders Table,i mean that i want to get this table:

OrderDate     OrderEndDate  TotalDays
01-01-2010     05-01-2010          (4-1=)3
02-01-2010     10-01-2010          (8-1-1=)6 
05-01-2010     07-01-2010           2
10-01-2010     13-01-2010           3 (no holidays between dates)

how i can get it by query?

Sachin.Nand

2937 Posts

Posted - 2010-10-07 : 09:10:49

Why are you subtracting for this?

05-01-2010     07-01-2010          (2-1=)1

PBUH

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-10-07 : 09:11:58

sorry my mistake

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-10-07 : 09:14:14

i try something like that ,
but it is not work:
(each order i need to check if was holiday between OrderDate ans OrderEndDate).

DECLARE @HolidayDate DateTime,@Days int
DECLARE Holiday_Cursor CURSOR FOR
SELECT * FROM holidays

OPEN Holiday_Cursor
WHILE @@fetch_status=0
BEGIN
UPDATE o
SET o.TotalDays = o.TotalDays - 1
FROM Orders o JOIN holidays h ON h.HolidayDate Between o.OrderDate AND o.OrderEndDate

fetch next from Holiday_Cursor into @HolidayDate ,@Days
END

close Holiday_Cursor
deallocate Holiday_Cursor

Sachin.Nand

2937 Posts

Posted - 2010-10-07 : 09:23:29

[code]
set dateformat dmy
go
declare @o table(OrderDate date, OrderEndDate date,TotalDays int)
insert into @o
select '01-01-2010' , '05-01-2010', 4 union
select '02-01-2010' , '10-01-2010' , 8 union
select '05-01-2010' , '07-01-2010' , 2 union
select '10-01-2010' , '13-01-2010' , 3

declare @h table(HolidayDate date,Days int)
insert into @h
select '03-01-2010' , 1 union
select '09-01-2010' , 1

select OrderDate,
OrderEndDate,
isnull(TotalDays-SUM(days),TotalDays)TotalDays from @o
outer apply(
select holidaydate,days from @h where HolidayDate
between OrderDate and OrderEndDate
)h
group by OrderDate,OrderEndDate,TotalDays
[/code]

PBUH

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-10-07 : 09:52:01

why not use cursor?
and i use sql 2000 and i can not use outer apply

Sachin.Nand

2937 Posts

Posted - 2010-10-07 : 13:02:02

quote:
Originally posted by inbs

why not use cursor?
and i use sql 2000 and i can not use outer apply

Now you are telling me....

Why did you post it in SQL 2005 forum in the first place???

PBUH

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-07 : 13:24:13

quote:
why not use cursor?

Because cursors suck and they're not needed:

SELECT O.OrderDate, O.OrderEndDate, 
O.TotalDays-IsNull((select sum(days) from holidays where holidaydate between O.OrderDate AND O.OrderEndDate),0) TotalDays
FROM Orders O

Is there any reason you're storing TotalDays instead of calculating it from the 2 order dates?

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-10-10 : 01:48:36

Sachin.Nand

sorry,i wrote here because this forum is more active than SQL 2K.

robvolk

thanks ,very good, ididnt thinks of that.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-10-10 : 08:41:43

quote:
sorry,i wrote here because this forum is more active than SQL 2K.

That doesn't help us answer your questions promptly or accurately, and wastes our time. Post in the forum most appropriate to your environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources