Update with an aggregate value

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)

Update with an aggregate value

Author

Topic

rcoop
Starting Member

4 Posts

Posted - 2007-08-24 : 16:59:45

Hi All,

Very new to SQL so I apologize in advance. I have an issue where I need to take the results from this example, and update them to a field in another table:

DECLARE @d1 SMALLDATETIME, @d2 SMALLDATETIME;
SELECT @d1 = '20030601', @d2 = '20030630';

SELECT COUNT(*)
FROM dbo.WorkCalendar
WHERE dt >= @d1
AND dt <= @d2
AND isWorkDay = 1;

With @d1 and @d2 being smalldatetime fields from the table I want to update the results to. If anyone can help me with the syntax, I would much appreciate it!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 17:03:44

Where is the other table? What is the structure of each of the tables? Which table do you want to update? where is the data on which the records are to be updated coming from?

Without all this information, it is hard to suggest anything.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rcoop
Starting Member

4 Posts

Posted - 2007-08-24 : 17:20:39

Thank you for your reply.
The table I would like to update is:
OrderNum
OrderDate
ShipDate
DaystoShip

My requirement is to populate DaystoShip with the results of:

SELECT COUNT(*)-1
FROM dbo.WorkCalendar
WHERE dt >= OrderDate
AND dt <= ShipDate
AND isWorkDay = 1;

Where WorkCalendar is a Calendar that I created to exclude weekends and holidays.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 17:50:01

can you post some sample data from each table and how the DaysToShip column has to be updated for those values?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rcoop
Starting Member

4 Posts

Posted - 2007-08-24 : 18:02:35

Table WorkCalendar

dt isWeekday isWorkday
12/3/2006 0 0
12/4/2006 1 1
12/5/2006 1 1
12/6/2006 1 1
12/7/2006 1 1
12/8/2006 1 1
12/9/2006 0 0

Table [Orders]
OrderNum OrderDate ShipDate DaystoShip
2001707 12/1/2006 12/8/2006 NULL
2001708 12/2/2006 12/9/2006 NULL

DaystoShip should be the count of isWorkDay between the OrderDate and ShipDate.
Thanks so much for your help!

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-24 : 18:10:51

[code]
Declare @WorkCalendar Table (dt datetime, isWeekday tinyint, isWorkday tinyint)
insert into @WorkCalendar
select '12/3/2006' ,0 ,0 union all
select '12/4/2006', 1 ,1 union all
select '12/5/2006', 1 ,1 union all
select '12/6/2006', 1 ,1 union all
select '12/7/2006', 1 ,1 union all
select '12/8/2006', 1 ,1 union all
select '12/9/2006', 0 ,0

Declare @Orders Table (OrderNum int, OrderDate datetime, ShipDate datetime, DaystoShip int)
Insert into @Orders
Select 2001707, '12/1/2006', '12/8/2006', NULL union all
Select 2001708, '12/2/2006', '12/9/2006', NULL

update o
Set o.DaystoShip = (Select count(isWorkday) From @WorkCalendar W Where W.dt >= O.OrderDate And W.dt <= O.ShipDate)
From @Orders O

select * from @Orders

[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rcoop
Starting Member

4 Posts

Posted - 2007-08-24 : 18:38:08

I can now enjoy my weekend thanks to your kindness!!!
It worked like a charm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources