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 2008 Forums
 Transact-SQL (2008)
 Group by to get the remain qty

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-11-24 : 01:50:22
Hi,

When i used the below query I get return the value in the remain qty. What if i will get the remain qty using row number which the RN =1 value will has the remain qty. Any Idea. thank you.

Please see below sample data.

Declare @sample Table (SALESID nvarchar(10), SHIPDATE datetime, ITEMID nvarchar(35),ITEMNAME   nvarchar(35),
ORDERQTY int, DELIVEREDQTY int, REMAIN int, WHSE nvarchar(10))
Insert @sample (SALESID, SHIPDATE, ITEMID,ITEMNAME,ORDERQTY,DELIVEREDQTY,REMAIN, WHSE )
Values('SO0030341','11/21/2014','ITEM-A','A',7000,1500,500,'H1'),
('SO0030341','11/19/2014','ITEM-A','A',7000,1000,2000,'H1'),
('SO0030341','11/18/2014','ITEM-A','A',7000,1000,3000,'H1'),
('SO0030341','11/17/2014','ITEM-A','A',7000,500,4000,'H2'),
('SO0030341','11/15/2014','ITEM-A','A',7000,500,4500,'H2'),
('SO0030341','11/14/2014','ITEM-A','A',7000,1000,5000,'H2'),
('SO0030341','11/12/2014','ITEM-A','A',7000,1000,6000,'H2'),
('SO0030342','11/11/2014','ITEM-B','B',2000,500,0,'H1'),
('SO0030342','11/10/2014','ITEM-B','B',2000,500,500,'H1'),
('SO0030342','11/8/2014','ITEM-B','B',2000,200,1000,'H1'),
('SO0030342','11/7/2014','ITEM-B','B',2000,200,1200,'H1'),
('SO0030342','11/5/2014','ITEM-B','B',2000,200,1400,'H2'),
('SO0030342','11/4/2014','ITEM-B','B',2000,400,1600,'H2')


;With CTE as
(
Select SALESID,SHIPDATE, ITEMID, ITEMNAME ,
ORDERQTY,
DELIVEREDQTY,
REMAIN,
WHSE,
RN=ROW_NUMBER() OVER (partition by salesid, ITEMID order by SHIPDATE desc)
from @sample
)--, CTE2 AS
--(
SELECT
ITEMID, ITEMNAME, ORDERQTY,
SUM(DELIVEREDQTY) AS QTY,
ORDERQTY-SUM(DELIVEREDQTY) AS REMAIN,
--SUM(REMAIN) AS REMAIN,
SUM(CASE WHEN WHSE='H1' THEN DELIVEREDQTY ELSE 0 END) AS H1,
SUM(CASE WHEN WHSE='H2' THEN DELIVEREDQTY ELSE 0 END) AS H2
From CTE
GROUP BY ITEMID, ITEMNAME, ORDERQTY



Sample desired result
---------------------------------------------------------------------
ITEMID--- ITEMNAME--- ORDERQTY--- QTY--- REMAIN--- H1---- H2
ITEM-A-- A------- 7000---- 6500-- 500------ 3500-- 3000
ITEM-B-- B------- 2000---- 2000-- 0-------- 1400-- 600

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-24 : 09:08:59
When I run your query, I get the desired result you're looking for.

Looks like you solved your own problem!
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-11-24 : 20:10:16
Hi,

Yes, Finally i got the result using different approach. instead of summing up the delivery qty and deducted from Order qty, i get the rank and order by shipdate to get the remain qty. below is the modified query.

;With CTE as
(
Select SALESID,SHIPDATE, ITEMID, ITEMNAME ,
ORDERQTY,
DELIVEREDQTY,
REMAIN,
WHSE,
RN=ROW_NUMBER() OVER (partition by salesid, ITEMID order by SHIPDATE desc)
from @sample
)
SELECT
ITEMID, ITEMNAME, ORDERQTY,
SUM(DELIVEREDQTY) AS QTY,
SUM(CASE WHEN RN=1 THEN REMAIN ELSE 0 END) AS REMAIN,
SUM(CASE WHEN WHSE='H1' THEN DELIVEREDQTY ELSE 0 END) AS H1,
SUM(CASE WHEN WHSE='H2' THEN DELIVEREDQTY ELSE 0 END) AS H2
From CTE
GROUP BY ITEMID, ITEMNAME, ORDERQTY





Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-11-30 : 23:45:11
Solved
Go to Top of Page
   

- Advertisement -