Group by to get the remain qty

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!

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2014-11-30 : 23:45:11

Solved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources