Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

subtract from two tables Expand / Collapse
Author
Message
Posted Monday, September 23, 2013 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 4, 2014 4:18 AM
Points: 15, Visits: 44
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
Post #1497367
Posted Monday, September 23, 2013 7:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:57 AM
Points: 2,434, Visits: 7,514
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



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1497372
Posted Monday, September 23, 2013 10:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 4, 2014 4:18 AM
Points: 15, Visits: 44
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
Post #1497461
Posted Monday, September 23, 2013 6:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1497607
Posted Tuesday, September 24, 2013 4:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:57 AM
Points: 2,434, Visits: 7,514
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.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1497754
Posted Tuesday, September 24, 2013 4:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 4:10 AM
Points: 3,634, Visits: 5,283
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1497759
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse