SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


subtract from two tables


subtract from two tables

Author
Message
ashley.shookhye
ashley.shookhye
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 55
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
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9238 Visits: 8492
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



Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
ashley.shookhye
ashley.shookhye
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 55
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
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18205 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9238 Visits: 8492
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? :-D

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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18205 Visits: 6431
Cadavre (9/24/2013)

The question now of course becomes, which is faster over a million rows? :-D



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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search