September 23, 2013 at 6:59 am
hi everyone,
Hope you are good. Need some little help. I am developing some inventory app and now need some help on the stock status. please find below.
table1 consists of all items In.(may consist of duplicate entries)
table2 consists of items that will be out.
table1-
PurchaseOrderNo SerialNo Qty
001 I000 20
001 I001 10
002 I000 50
003 I002 20
table2-
IssueNo SerialNo Qty
S001 I000 10
S002 I001 5
I will need as follows if possible
SerialNo BalanceQty
I000 60
I001 5
I002 20
Can you guys help me with the query? I came up with something but the item should be in both tables. For instance For item I002, I don't get the balance quantity.
please see query below. Thanks to help
SELECT s.Item, SUM(m.Qty) as total, SUM(s.Qty) as used, SUM(m.Qty) - SUM(s.Qty) as bal
FROM Store_Out_Details s
JOIN Store_PO_Details m ON m.Item=s.Item
GROUP BY s.Item,s.Qty
September 23, 2013 at 7:14 am
This looks a little bit like homework.
First, let's set up your sample data so that people can use it: -
DECLARE @Store_Out_Details AS TABLE (PurchaseOrderNo CHAR(3), SerialNo CHAR(4), Qty INT);
INSERT INTO @Store_Out_Details
VALUES ('001','I000',20),('001','I001',10),('002','I000',50),('003','I002',20);
DECLARE @Store_PO_Details AS TABLE (IssueNo CHAR(4), SerialNo CHAR(4), Qty INT);
INSERT INTO @Store_PO_Details
VALUES('S001','I000',10),('S002','I001',5);
OK, now what you want to do is group each table together before you do the arithmetic. Let's break it down a little: -
SELECT SerialNo, SUM(Qty)
FROM @Store_Out_Details
GROUP BY SerialNo;
So we now have: -
SerialNo
-------- -----------
I000 70
I001 10
I002 20
Which are the totals from the OutDetails table.
Next, let's group up the PODetails table: -
SELECT SerialNo, SUM(Qty)
FROM @Store_PO_Details
GROUP BY SerialNo;
Now we have: -
SerialNo
-------- -----------
I000 70
I001 10
I002 20
(3 row(s) affected)
SerialNo
-------- -----------
I000 10
I001 5
(2 row(s) affected)
So all we want to do is join the two result-sets together. We'll do a LEFT OUTER join to preserve the rows that exist in the Out table but not in the PO table.
SELECT OutDetails.SerialNo,
OutDetails.Qty - PODetails.Qty AS BalanceQty
FROM (SELECT SerialNo, SUM(Qty)
FROM @Store_Out_Details
GROUP BY SerialNo
) OutDetails(SerialNo, Qty)
LEFT OUTER JOIN (SELECT SerialNo, SUM(Qty)
FROM @Store_PO_Details
GROUP BY SerialNo
) PODetails(SerialNo, Qty) ON OutDetails.SerialNo = PODetails.SerialNo;
And now we have: -
SerialNo BalanceQty
-------- -----------
I000 60
I001 5
I002 NULL
Wait, what's that NULL doing there? Can you see the deliberate mistake? The clue is in the outer join. I'm going to assume that you spotted it 😉
SELECT OutDetails.SerialNo,
OutDetails.Qty - ISNULL(PODetails.Qty,0) AS BalanceQty
FROM (SELECT SerialNo, SUM(Qty)
FROM @Store_Out_Details
GROUP BY SerialNo
) OutDetails(SerialNo, Qty)
LEFT OUTER JOIN (SELECT SerialNo, SUM(Qty)
FROM @Store_PO_Details
GROUP BY SerialNo
) PODetails(SerialNo, Qty) ON OutDetails.SerialNo = PODetails.SerialNo;
SerialNo BalanceQty
-------- -----------
I000 60
I001 5
I002 20
September 23, 2013 at 10:21 am
hello,
I could not ask for a better explanation than that.
Awesome explanation.
Thank you for your time and patience in writing all this.
Works like a charm.
Cheers,
Ashley
September 23, 2013 at 6:46 pm
Perhaps this works also?
DECLARE @Store_Out_Details AS TABLE (PurchaseOrderNo CHAR(3), SerialNo CHAR(4), Qty INT);
INSERT INTO @Store_Out_Details
VALUES ('001','I000',20),('001','I001',10),('002','I000',50),('003','I002',20);
DECLARE @Store_PO_Details AS TABLE (IssueNo CHAR(4), SerialNo CHAR(4), Qty INT);
INSERT INTO @Store_PO_Details
VALUES('S001','I000',10),('S002','I001',5);
SELECT SerialNo, Qty=SUM(Qty)
FROM
(
SELECT SerialNo, Qty
FROM @Store_Out_Details
UNION ALL
SELECT SerialNo, -Qty
FROM @Store_PO_Details
) a
GROUP BY SerialNo;
With thanks to Cadavre for the set up data!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 24, 2013 at 4:38 am
dwain.c (9/23/2013)
Perhaps this works also?
Definitely, but I suspect that the point of the homework assignment is to learn about outer joins. The question now of course becomes, which is faster over a million rows? 😀
SET NOCOUNT ON;
IF object_id('tempdb..#Store_Out_Details') IS NOT NULL
BEGIN;
DROP TABLE #Store_Out_Details;
END;
SELECT TOP 1000000
RIGHT('000'+CAST((ABS(CHECKSUM(NEWID())) % 999) + 1 AS CHAR(4)),3) AS PurchaseOrderNo,
'I'+RIGHT('000'+CAST((ABS(CHECKSUM(NEWID())) % 999) + 1 AS CHAR(4)),3) AS SerialNo,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS Qty
INTO #Store_Out_Details
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
IF object_id('tempdb..#Store_PO_Details') IS NOT NULL
BEGIN;
DROP TABLE #Store_PO_Details;
END;
SELECT TOP 1000000
'S'+RIGHT('000'+CAST((ABS(CHECKSUM(NEWID())) % 999) + 1 AS CHAR(4)),3) AS IssueNo,
'I'+RIGHT('000'+CAST((ABS(CHECKSUM(NEWID())) % 999) + 1 AS CHAR(4)),3) AS SerialNo,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS Qty
INTO #Store_PO_Details
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;
DECLARE @Loop CHAR(1) = '0', @HOLDER_QTY INT, @HOLDER_SERIALNO CHAR(4), @Duration CHAR(12), @StartTime DATETIME;
SELECT @StartTime = GETDATE();
SELECT @HOLDER_QTY = COUNT(*)
FROM (SELECT *
FROM #Store_Out_Details
UNION ALL
SELECT *
FROM #Store_PO_Details
)a;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('BaseLine Duration: %s',0,1,@Duration) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
WHILE @Loop <= 5
BEGIN;
RAISERROR('Loop: %s',0,1,@Loop) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT
@HOLDER_SERIALNO = SerialNo,
@HOLDER_QTY = SUM(Qty)
FROM
(
SELECT SerialNo, Qty
FROM #Store_Out_Details
UNION ALL
SELECT SerialNo, -Qty
FROM #Store_PO_Details
) a
GROUP BY SerialNo;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Union All Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SELECT @StartTime = GETDATE();
SELECT
@HOLDER_SERIALNO = OutDetails.SerialNo,
@HOLDER_QTY = OutDetails.Qty - ISNULL(PODetails.Qty,0)
FROM (SELECT SerialNo, SUM(Qty)
FROM #Store_Out_Details
GROUP BY SerialNo
) OutDetails(SerialNo, Qty)
LEFT OUTER JOIN (SELECT SerialNo, SUM(Qty)
FROM #Store_PO_Details
GROUP BY SerialNo
) PODetails(SerialNo, Qty) ON OutDetails.SerialNo = PODetails.SerialNo;
SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Outer Join Duration: %s',0,1,@Duration) WITH NOWAIT;
RAISERROR('============',0,1) WITH NOWAIT;
SET @Loop = @Loop + 1;
END;
BaseLine Duration: 00:00:00:063
============
============
Loop: 0
============
============
Union All Duration: 00:00:06:453
============
Outer Join Duration: 00:00:01:077
============
Loop: 1
============
============
Union All Duration: 00:00:00:997
============
Outer Join Duration: 00:00:01:013
============
Loop: 2
============
============
Union All Duration: 00:00:01:053
============
Outer Join Duration: 00:00:00:940
============
Loop: 3
============
============
Union All Duration: 00:00:01:057
============
Outer Join Duration: 00:00:00:903
============
Loop: 4
============
============
Union All Duration: 00:00:00:983
============
Outer Join Duration: 00:00:01:033
============
Loop: 5
============
============
Union All Duration: 00:00:01:047
============
Outer Join Duration: 00:00:01:123
============
If we don't include the first run, you'd have to call that roughly equivalent.
ashley.shookhye (9/23/2013)
hello,I could not ask for a better explanation than that.
Awesome explanation.
Thank you for your time and patience in writing all this.
Works like a charm.
Cheers,
Ashley
No problem. Make sure you understand what we did so that you can apply the same knowledge elsewhere.
September 24, 2013 at 4:50 am
Cadavre (9/24/2013)
The question now of course becomes, which is faster over a million rows? 😀
Somehow I just knew you were going to say that. 🙂 +1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply