Max() query problems

I have a bit of code that checks through all orders and looks for all
orderlines where the status is either closed, completed or cancelled, but
where at least one is cancelled, and it sets the status of the order to
closed.
I also have an equivalent script which sets the order status to complete
where all order lines are completed or cancelled but none are closed.
I want to modify both scripts to update the Orders table by setting the
LastModified field to the most recent LastModified value from the
Orderdetail table (eg Max(d.LastModified)). This is where I have my
problem - I havent found the right code to make this work.
Here is my code snippet:
update orders
set status = 'Closed'
where orderid in
(select
o.orderid
from
orders as o
inner join
orderdetail as d
on o.orderid = d.orderid
group by
o.orderid, o.Status
having
sum(case when d.status in ('Complete', 'Cancelled', 'Failed Delivery',
'Closed')
then 1 else 0 end) = count(*)
and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
and (o.status <> 'Complete' and o.status <> 'Closed'))
Any ideas?
Thanks in advance...
CJM
--
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]CJM (cjmnews04@.newsgroup.nospam) writes:
> I want to modify both scripts to update the Orders table by setting the
> LastModified field to the most recent LastModified value from the
> Orderdetail table (eg Max(d.LastModified)). This is where I have my
> problem - I havent found the right code to make this work.
> Here is my code snippet:
> update orders
> set status = 'Closed'
> where orderid in
> (select
> o.orderid
> from
> orders as o
> inner join
> orderdetail as d
> on o.orderid = d.orderid
> group by
> o.orderid, o.Status
> having
> sum(case when d.status in ('Complete', 'Cancelled', 'Failed Delivery',
> 'Closed')
> then 1 else 0 end) = count(*)
> and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
> and (o.status <> 'Complete' and o.status <> 'Closed'))
Without table definitions and that, it will have to be a bit of
guesswork:
update orders
set status = 'Closed',
closedate = od.MaxLastModified
from orders o
JOIN (select od.orderid, MaxLastModified = MAX(od.LastModified)
from orders as o
inner join orderdetail as d on o.orderid = d.orderid
group by o.orderid, o.Status
having
sum(case when d.status in ('Complete', 'Cancelled',
'Failed Delivery', 'Closed')
then 1 else 0 end) = count(*)
and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
and (o.status <> 'Complete' and o.status <> 'Closed')) AS od
ON o.orderid = od.orderid
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715F4136B20Yazorman@.127.0.0.1...
> CJM (cjmnews04@.newsgroup.nospam) writes:
> Without table definitions and that, it will have to be a bit of
> guesswork:
> update orders
> set status = 'Closed',
> closedate = od.MaxLastModified
> from orders o
> JOIN (select od.orderid, MaxLastModified = MAX(od.LastModified)
> from orders as o
> inner join orderdetail as d on o.orderid = d.orderid
> group by o.orderid, o.Status
> having
> sum(case when d.status in ('Complete', 'Cancelled',
> 'Failed Delivery', 'Closed')
> then 1 else 0 end) = count(*)
> and sum(case when d.status = 'Closed' then 1 else 0 end) > 0
> and (o.status <> 'Complete' and o.status <> 'Closed')) AS od
> ON o.orderid = od.orderid
>
Erland,
Sorry, I omitted the DDl for speed - I figured it was simple enough to
figure out. You got it right anyway.
Yes that Join did the trick for me.
Thanks for your help
Chris

MAX Values (Query)

Hi,

I have a problem to get the max values. I have table like this...

STATUS CUSTOMER NUMBER OTHER
0 000001 1 DATA1
0 000001 2 DATA2
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 1 DATA1
0 000003 2 DATA2

I need to get this

STATUS CUSTOMER NUMBER OTHER
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 2 DATA2

Please advice

Thanks

Try:

;with cte

as

(

select *, row_number() over(partition by customer order by number DESC) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

Here it is,

Code Snippet

Create Table #data (

[STATUS] int ,

[CUSTOMER] Varchar(100) ,

[NUMBER] int ,

[OTHER] Varchar(100)

);

Insert Into #data Values('0','000001','1','DATA1');

Insert Into #data Values('0','000001','2','DATA2');

Insert Into #data Values('0','000001','3','DATA3');

Insert Into #data Values('0','000002','1','DATA1');

Insert Into #data Values('0','000003','1','DATA1');

Insert Into #data Values('0','000003','2','DATA2');

go

select status,customer,max(number),max(Other) from #data

group by status,customer

|||
Ismael try this,

SELECT MainTable.*

FROM MainTable

JOIN (

SELECT CUSTOMER

, MAX(NUMBER) MaxNUMBER

FROM MainTable

GROUP BY CUSTOMER

) SQ

ON MainTable.CUSTOMER = SQ.CUSTOMER

AND MainTable.NUMBER = SQ.MaxNUMBER

MAX Values (Query)

Hi,

I have a problem to get the max values. I have table like this...

STATUS CUSTOMER NUMBER OTHER
0 000001 1 DATA1
0 000001 2 DATA2
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 1 DATA1
0 000003 2 DATA2

I need to get this

STATUS CUSTOMER NUMBER OTHER
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 2 DATA2

Please advice

Thanks

Try:

;with cte

as

(

select *, row_number() over(partition by customer order by number DESC) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

Here it is,

Code Snippet

Create Table #data (

[STATUS] int ,

[CUSTOMER] Varchar(100) ,

[NUMBER] int ,

[OTHER] Varchar(100)

);

Insert Into #data Values('0','000001','1','DATA1');

Insert Into #data Values('0','000001','2','DATA2');

Insert Into #data Values('0','000001','3','DATA3');

Insert Into #data Values('0','000002','1','DATA1');

Insert Into #data Values('0','000003','1','DATA1');

Insert Into #data Values('0','000003','2','DATA2');

go

select status,customer,max(number),max(Other) from #data

group by status,customer

|||
Ismael try this,

SELECT MainTable.*

FROM MainTable

JOIN (

SELECT CUSTOMER

, MAX(NUMBER) MaxNUMBER

FROM MainTable

GROUP BY CUSTOMER

) SQ

ON MainTable.CUSTOMER = SQ.CUSTOMER

AND MainTable.NUMBER = SQ.MaxNUMBER