April 30, 2011 at 3:35 am
I have a result set as below.
ItemExpectedQtyReceivedQtyShort
Item0130455
Item0120455
Item0240382
item03509010
item03309010
item03209010
query is:
select a.Item, a.ExpectedQty,b.ReceivedQty, b.Short
from a join b on a.Item = b.Item
I need to get result as in second chart. Basically I have a total of received quantity in each line and I need to show received quantity against Expected Quantity, if there is any shortage I need to show in last line.
Expected:
**ItemExpectedQtyReceivedQtyShort**
item0130300
item0120155
item0240382
item0350500
item0330300
item03201010
Thanks in advance.
April 30, 2011 at 7:29 am
Please read the first article referenced in my signature and post table def and sample data in a ready to use format together with your expected result set.
As an alternative you could search this site for Jeff Modens "Running Total" article.
April 30, 2011 at 3:30 pm
Sorry for the inconvenient, here I have rework on it and appreciate if you can go through it.
-- Just a brief of business scenario is table has been created for a good receipt.
-- So here we have good expected line with PurchaseOrder(PO) in first few line.
-- And then we receive each expected line physically and that time these quantity may be different
-- due to business case like quantity may damage and short quantity like that.
-- So we maintain a status for that eg: OK, Damage, also we have to calculate short quantity
-- based on total of expected quantity of each item and total of received line.
if object_id('[DB]..Temp','U') is not null
drop table Temp
CREATE TABLE Temp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Item VARCHAR(32),
PO VARCHAR(32) NULL,
ExpectedQty INT NULL,
ReceivedQty INT NULL,
[STATUS] VARCHAR(32) NULL,
BoxName VARCHAR(32) NULL
)
-- Please see first few line with PO data will be the expected lines,
-- and then rest line will be received line
INSERT INTO TEMP (Item,PO,ExpectedQty,ReceivedQty,[STATUS],BoxName)
SELECT 'ITEM01','PO-01','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM01','PO-02','20',NULL,NULL,NULL UNION ALL
SELECT 'ITEM02','PO-01','40',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-01','50',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-02','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-03','20',NULL,NULL,NULL UNION ALL
SELECT 'ITEM04','PO-01','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM01',NULL,NULL,'20','OK','box01' UNION ALL
SELECT 'ITEM01',NULL,NULL,'25','OK','box02' UNION ALL
SELECT 'ITEM01',NULL,NULL,'5','DAMAGE','box03' UNION ALL
SELECT 'ITEM02',NULL,NULL,'38','OK','box04' UNION ALL
SELECT 'ITEM02',NULL,NULL,'2','DAMAGE','box05' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box06' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box07' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box08' UNION ALL
SELECT 'ITEM03',NULL,NULL,'10','DAMAGE','box09' UNION ALL
SELECT 'ITEM04',NULL,NULL,'25','OK','box10'
-- Below Table is my expected result based on above data.
-- I need to show those data following way.
-- So I appreciate if you can give me an appropriate query for it.
-- Note: first row is blank and it is actually my table header. 🙂
SELECT ''as'ITEM', ''as'PO#', ''as'ExpectedQty',''as'ReceivedQty',''as'DamageQty' ,''as'ShortQty' UNION ALL
SELECT 'ITEM01','PO-01','30','30','0' ,'0' UNION ALL
SELECT 'ITEM01','PO-02','20','15','5' ,'0' UNION ALL
SELECT 'ITEM02','PO-01','40','38','2' ,'0' UNION ALL
SELECT 'ITEM03','PO-01','50','50','0' ,'0' UNION ALL
SELECT 'ITEM03','PO-02','30','30','0' ,'0' UNION ALL
SELECT 'ITEM03','PO-03','20','10','10','0' UNION ALL
SELECT 'ITEM04','PO-01','30','25','0' ,'5'
April 30, 2011 at 5:18 pm
first of all: THANK YOU for providing ready to use sample data. EXCELLENT job!!
Regarding the data itself:
How do you assign a box to a PO? E.g. item01, box01 could be assigned to PO-02 making this order fulfilled without damage.
Do you always have one box with one status? (e.g. you cannot have box02 15 ok and 5 damaged)?
Is it possible to receive more than expected? (e.g. Item02: 40 expected, 42 received, 5 damaged)?
Edit: please confirm you're using SQL2000/SQL7. It will make a huge difference regarding the solution compared to SQL2005 and up....
April 30, 2011 at 10:55 pm
Thank you very much for your reply. You, genius are exactly in correct track of business.
How do you assign a box to a PO? E.g. item01, box01 could be assigned to PO-02 making this order fulfilled without damage.
Actually we partially segregate the items to boxes and boxes will have any number of quantities with same item. Yes, actually we can assign PO-02 to box01 also. But we don’t maintain such relationship. We just need to split total received quantity against total expected quantity. If there is any discrepancy, we will show at the last line of same item.
Do you always have one box with one status? (e.g. you cannot have box02 15 ok and 5 damaged)?
Yes, always one box with one status, either OK or Damage quantities.
Is it possible to receive more than expected? (e.g. Item02: 40 expected, 42 received, 5 damaged)?
No, there is no way to receive more than expected.
Edit: please confirm you're using SQL2000/SQL7. It will make a huge difference regarding the solution compared to SQL2005 and up....
I need solution in SQL 2000 right now.
May 1, 2011 at 9:10 am
It looks like a FiFo stock problem as discussed in one of Phil Factor SQL Speed Phreak Competitions.
You might want to have a look at Daven Ballantynes answer. The main difference to the issue you're describing is the missing date information. And, you'd need to change the CTE's into subqueries or maybe you should use an intermediate table.
Unfortunately, I won't have the time today to further work on it.
May 1, 2011 at 12:37 pm
Here's a solution right now. Never use code from a website without checking it first and fully understanding how it works - this article [/url]will explain the method used in the solution. There's plenty of scope for improvement, take the time to experiment and optimise.
------------------------------------------------------------------
-- create the data sample
------------------------------------------------------------------
drop table #Temp
CREATE TABLE #Temp
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Item VARCHAR(32),
PO VARCHAR(32) NULL,
ExpectedQty INT NULL,
ReceivedQty INT NULL,
[STATUS] VARCHAR(32) NULL,
BoxName VARCHAR(32) NULL
)
INSERT INTO #TEMP (Item,PO,ExpectedQty,ReceivedQty,[STATUS],BoxName)
SELECT 'ITEM01','PO-01','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM01','PO-02','20',NULL,NULL,NULL UNION ALL
SELECT 'ITEM02','PO-01','40',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-01','50',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-02','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM03','PO-03','20',NULL,NULL,NULL UNION ALL
SELECT 'ITEM04','PO-01','30',NULL,NULL,NULL UNION ALL
SELECT 'ITEM01',NULL,NULL,'20','OK','box01' UNION ALL
SELECT 'ITEM01',NULL,NULL,'25','OK','box02' UNION ALL
SELECT 'ITEM01',NULL,NULL,'5','DAMAGE','box03' UNION ALL
SELECT 'ITEM02',NULL,NULL,'38','OK','box04' UNION ALL
SELECT 'ITEM02',NULL,NULL,'2','DAMAGE','box05' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box06' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box07' UNION ALL
SELECT 'ITEM03',NULL,NULL,'30','OK','box08' UNION ALL
SELECT 'ITEM03',NULL,NULL,'10','DAMAGE','box09' UNION ALL
SELECT 'ITEM04',NULL,NULL,'25','OK','box10'
------------------------------------------------------------------
-- reformat the data to make it easier to perform the update
------------------------------------------------------------------
DROP TABLE #expected
SELECT rn = IDENTITY(int,1,1),
e.Item, e.PO, e.ExpectedQty, e.ReceivedQty,
[DamageQty] = CAST(NULL AS INT),
[ShortQty] = CAST(NULL AS INT),
r.TotalReceivedOK, r.TotalReceivedDamaged,
c.ItemRows
INTO #expected
FROM #Temp e
LEFT JOIN (
SELECT Item,
TotalReceivedOK = SUM(CASE WHEN [STATUS] = 'OK' THEN ReceivedQty ELSE 0 END),
TotalReceivedDamaged = SUM(CASE WHEN [STATUS] = 'DAMAGE' THEN ReceivedQty ELSE 0 END)
FROM #Temp
WHERE PO IS NULL AND ReceivedQty IS NOT NULL
GROUP BY Item
) r ON r.Item = e.Item
INNER JOIN (
SELECT Item, ItemRows = COUNT(*)
FROM #Temp
WHERE PO IS NOT NULL AND ReceivedQty IS NULL
GROUP BY Item
) c ON c.Item = e.Item
WHERE e.PO IS NOT NULL AND e.ReceivedQty IS NULL
ORDER BY e.Item, e.PO
CREATE CLUSTERED INDEX IXC_Item_PO ON #expected (Item, PO)
------------------------------------------------------------------
-- perform the update; first, set up some variables
------------------------------------------------------------------
DECLARE @ItemRow INT, @ReceivedQty INT, @ReceivedQtyBalance INT, @Item VARCHAR(32)
SELECT @ReceivedQtyBalance = TotalReceivedOK, @Item = Item FROM #expected WHERE rn = 1
UPDATE #expected SET
@ItemRow = CASE WHEN @Item = Item THEN ISNULL(@ItemRow,0) + 1 ELSE 1 END,
DamageQty = CASE WHEN @ItemRow = ItemRows THEN TotalReceivedDamaged ELSE 0 END,
@ReceivedQtyBalance = CASE WHEN @ItemRow = 1 THEN TotalReceivedOK ELSE @ReceivedQtyBalance - @ReceivedQty END,
@ReceivedQty = ReceivedQty = CASE WHEN @ReceivedQtyBalance > ExpectedQty THEN ExpectedQty ELSE @ReceivedQtyBalance END,
ShortQty = CASE WHEN ExpectedQty > (@ReceivedQty+TotalReceivedDamaged) THEN ExpectedQty - (@ReceivedQty+TotalReceivedDamaged) ELSE 0 END,
@Item = Item
FROM #expected WITH (TABLOCKX) OPTION (MAXDOP 1)
------------------------------------------------------------------
-- check the results
------------------------------------------------------------------
SELECT Item, PO, ExpectedQty, ReceivedQty, DamageQty, ShortQty
FROM #expected
ORDER BY Item, PO
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 1, 2011 at 3:16 pm
great, thank you veru much. Also I got lot of knowledge.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply