September 9, 2014 at 3:44 pm
I have this example that is relative to something I am working on and I don't know how to over come this. I nee the custID and orderID for the order with the highest amount. I have tried several things but failed. So here i have the basis of getting the orders with total anount. I just don't know how to get what I want from there.
below is where I am at this time with the sample:
declare @cust table(custID int, name varchar(15))
declare @order table(orderid int, custid int, orderNum varchar(15),orderdate datetime)
declare @orderdetail table(orderDetid int, orderid int, itemnum varchar(10),Itemcnt int,amt int)
insert into @cust(custID,name)
values (1,'CustOne'),(2,'CustTwo')
insert into @order (orderid,custid,orderNum,orderdate)
values (1,1,'100','2014-01-01'),(2,1,'101','2014-02-02'),(3,1,'103','2014-03-03'),
(4,2,'201','2014-03-03')
insert into @orderdetail(orderDetid,orderid,itemnum,Itemcnt,amt)
values (1,1,'itm#3',1,2),(2,1,'itme#4',2,3),(3,2,'item5',2,6),
(4,4,'401',3,5),(5,4,'402',2,10)
-- get custID, order, tot with highest amount for each cust
select o.custid,od.orderid as IorderID, sum(amt * itemcnt) as tot from @orderDetail od
join @order o
on o.orderid = od.orderid
group by o.custid,od.orderid
September 9, 2014 at 4:27 pm
I think this is what you are after.
with cte as (
select
o.custid, od.orderid as IorderID, sum(amt * itemcnt) as tot,
ROW_NUMBER() over (PARTITION by o.custid order by sum(amt * itemcnt) desc) as rownum
from @orderDetail od
join @order o on o.orderid = od.orderid
group by o.custid,od.orderid
)
select *
from cte
where rownum = 1
September 9, 2014 at 11:14 pm
declare @cust table(custID int, name varchar(15))
declare @order table(orderid int, custid int, orderNum varchar(15),orderdate datetime)
declare @orderdetail table(orderDetid int, orderid int, itemnum varchar(10),Itemcnt int,amt int)
insert into @cust(custID,name)
values (1,'CustOne'),(2,'CustTwo')
insert into @order (orderid,custid,orderNum,orderdate)
values (1,1,'100','2014-01-01'),(2,1,'101','2014-02-02'),(3,1,'103','2014-03-03'),
(4,2,'201','2014-03-03')
insert into @orderdetail(orderDetid,orderid,itemnum,Itemcnt,amt)
values (1,1,'itm#3',1,2),(2,1,'itme#4',2,3),(3,2,'item5',2,6),
(4,4,'401',3,5),(5,4,'402',2,10)
-- get custID, order, tot with highest amount for each cust
select p.custid,p.orderid,p.tot from
(
select *,ROW_NUMBER()over(partition by custid order by tot desc) as rowno from
(
select o.custid,od.orderid, sum(amt * itemcnt) as tot from @orderDetail od
join @order o
on o.orderid = od.orderid
group by o.custid,od.orderid
) t
)p
where p.rowno=1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply