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)
 Need help to get remain qty dynamically

Author  Topic 

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-22 : 22:03:04
Hi,

I have a requirements to get the remaining qty per transaction. I have already the query retrieving all the data. its tricky to me on how to get the remaining qty. Below is the DDL and sample desired result. Thank you in Advance.


Create table #TempTable
(IDNUM nvarchar(35), Itemid nvarchar(35), shipdate datetime, transid nvarchar(35),
Qtytransfer int, qtyship int, qtyshipremain int, poolid nvarchar(3))

Create table #Table1
(IDNUM nvarchar(35), SHIPDATE Datetime, POOLID nvarchar(12))
Insert into #Table1(IDNUM,SHIPDATE,POOLID) values('TC000051','2015-01-25','TCM')

Create table #Table2
(IDNUM nvarchar(35), ITEMID nvarchar(35), SHIPDATE datetime, TRANSID nvarchar(35), QTYTRANSFER INT, QTYSHIP int, QTYSHIPREMAIN int)
Insert #Table2
Select 'TC000051','TTC2574-IR','2015-01-25','TT507793281',570,570,0 union all
Select 'TC000051','TTC2574-IR','2015-01-25','TT507793930',570,0,0 union all
Select 'TC000051','TTC2292-IR','2015-01-25','TT507790041',29,9,20

Create table #Table3
(IDNUM nvarchar(35), ITEMID nvarchar(35), TRANSID nvarchar(35), TRANDATE datetime, QTYSHIP int)
Insert #Table3
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-27',14 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-27',6 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-28',4 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-29',12 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-23',105 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-23',171 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-28',140 union all
Select 'TC000051','TTC2574-IR','TT507793281','2014-12-29',118 union all
Select 'TC000051','TTC2292-IR','TT507790041','2014-12-21',5 union all
Select 'TC000051','TTC2292-IR','TT507790041','2014-12-22',3 union all
Select 'TC000051','TTC2292-IR','TT507790041','2014-12-22',1

Select * from #Table1
Select * from #Table2
Select * from #Table3


--My Query

Insert into #TempTable
(IDNUM,Itemid,shipdate,transid,Qtytransfer,qtyship,qtyshipremain,poolid)
Select
t2.IDNUM, t2.ITEMID, t2.SHIPDATE, t2.TRANSID, t2.QTYTRANSFER, t2.QTYSHIP AS QTYSHIP, t2.QTYSHIPREMAIN, t1.POOLID
From #Table2 t2
Inner Join #Table1 t1 On t1.IDNUM = t2.IDNUM
Where t2.SHIPDATE between ('2015-01-01') and ('2015-01-25')


SELECT DISTINCT
item.transid
, item.ITEMID as ItemNumber
, item.shipdate as TransactionDate
, item.ItemQtyTransfer as TransferQuantity
, ship.TRANDATE AS Shipdate
, COALESCE(Ship.ShippedPerDate,0) as ShipMentQty
, item.ItemRemainShip as ShipRemainQty
FROM
(
SELECT TOTemp1.ITEMID
,SUM(TOTemp1.QTYTRANSFER) as ItemQtyTransfer
,SUM(TOTemp1.qtyshipremain) as ItemRemainShip
,TOTemp1.transid
,TOTemp1.shipdate
,TOTemp1.IDNUM
FROM
#TempTable TOTemp1
GROUP BY TOTemp1.transid , TOTemp1.ITEMID, TOTemp1.shipdate, TOTemp1.IDNUM
) item
LEFT JOIN
(
SELECT ITJL1.ITEMID
,ITJL1.TRANDATE
,SUM(ITJL1.QTYSHIP) as ShippedPerDate
,ITJL1.IDNUM
,ITJL1.TRANSID
FROM #Table3 ITJL1
LEFT JOIN
#TempTable TOTemp3
ON ITJL1.TRANSID = TOTemp3.transid
GROUP BY ITJL1.ITEMID, ITJL1.TRANDATE, ITJL1.IDNUM , ITJL1.TRANSID
)Ship ON Ship.ITEMID = item.ITEMID AND ship.IDNUM = item.IDNUM and ship.TRANSID =item.transid


--DESIRED RESULT
transid--- ItemNumber--TransactionDate--TransferQuantity-- Shipdate--ShipMentQty--ShipRemainQty
==================================================================================================
TT507790041--TTC2292-IR--1/25/2015-----------29----------- 12/21/2014----5---------24
TT507790041--TTC2292-IR--1/25/2015-----------29----------- 12/22/2014----4---------20
TT507793281--TTC2574-IR--1/25/2015-----------570---------- 12/23/2014----276-------294
TT507793281--TTC2574-IR--1/25/2015-----------570---------- 12/27/2014----20--------274
TT507793281--TTC2574-IR--1/25/2015-----------570---------- 12/28/2014----144-------130
TT507793281--TTC2574-IR--1/25/2015-----------570---------- 12/29/2014----130-------0
TT507793930--TTC2574-IR--1/25/2015-----------570---------- NULL -------0--------0

nagino
Yak Posting Veteran

75 Posts

Posted - 2015-01-22 : 22:28:12
Like following?

SELECT
#Table2.TRANSID AS transid,
#Table2.ITEMID AS ItemNumber,
#Table2.SHIPDATE AS TransactionDate,
#table2.QTYTRANSFER AS TransferQuantity,
#Table3.TRANDATE AS Shipdate,
ISNULL(SUM(#Table3.QTYSHIP), 0) AS ShipMentQty,
#table2.QTYSHIPREMAIN AS ShipRemainQty
FROM #Table2
LEFT JOIN #Table3
ON #Table2.TRANSID = #Table3.TRANSID
GROUP BY #Table2.TRANSID, #Table2.ITEMID, #Table2.SHIPDATE, #table2.QTYTRANSFER, #Table3.TRANDATE, #table2.QTYSHIPREMAIN



However, I cannot understand why Shipdate and TransactionDate are exchanged...

-------------------------------------
From Japan
Sorry, my English ability is limited.
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2015-01-22 : 23:44:36
Transaction date is were the Transid created while the shipdate where the item transact.
Go to Top of Page
   

- Advertisement -