aggregate query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

aggregate query

Author

Topic

nyuser
Starting Member

4 Posts

Posted - 2008-02-29 : 13:45:20

I have two tables ItemHistory and ItemStock.
I would like to write a query which checks last years history and let us know if we have enough items in stock this year for a given span date.

First, It should get all items from @ItemHistory where WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007'
and then checks if corresponding items are found in @ItemStock,
and then returns all the ItemID where sum(@ItemStock.Quantity) < sum(@ItemHistory.Quantity)

Thank You.

Here is the DDL and DML


DECLARE @ItemHistory TABLE (
	ItemID INT,
	Quantity INT,
	DateSold DATETIME )
INSERT INTO @ItemHistory 
SELECT 12, 18, '2007-01-10' UNION ALL
SELECT 12, 18, '2007-01-10' UNION ALL
SELECT 26, 12, '2007-01-10' UNION ALL
SELECT 28, 06, '2007-01-10' UNION ALL
SELECT 29, 06, '2007-01-10' UNION ALL
SELECT 30, 06, '2007-01-10' UNION ALL
SELECT 31, 06, '2007-01-10' UNION ALL
SELECT 31, 06, '2007-01-10' UNION ALL
SELECT 32, 12, '2007-01-10' UNION ALL
SELECT 33, 01, '2007-01-10' UNION ALL
SELECT 33, 06, '2007-01-10' UNION ALL
SELECT 36, 01, '2007-01-10' UNION ALL
SELECT 52, 12, '2007-01-10' UNION ALL
SELECT 83, 01, '2007-01-10' UNION ALL
SELECT 36, 12, '2007-01-10' UNION ALL
SELECT 37, 01, '2007-01-10' UNION ALL
SELECT 38, 12, '2007-01-10' UNION ALL
SELECT 17, 01, '2007-01-10' UNION ALL
SELECT 17, 08, '2007-01-10' UNION ALL
SELECT 12, 20, '2007-02-20' UNION ALL
SELECT 26, 10, '2007-02-20' UNION ALL
SELECT 30, 08, '2007-02-20' UNION ALL
SELECT 31, 12, '2007-02-20' 

DECLARE @ItemStock TABLE (
	ItemID INT,
	Quantity INT )
INSERT INTO @ItemStock 
SELECT 12, 20 UNION ALL
SELECT 12, 10 UNION ALL
SELECT 14, 48 UNION ALL
SELECT 17, 24 UNION ALL
SELECT 19, 36 UNION ALL
SELECT 19, 72 UNION ALL
SELECT 20, 72 UNION ALL
SELECT 26, 24 UNION ALL
SELECT 28, 12 UNION ALL
SELECT 29, 12 UNION ALL
SELECT 30, 12 UNION ALL
SELECT 31, 18 UNION ALL
SELECT 32, 20 UNION ALL
SELECT 32, 68 UNION ALL
SELECT 33, 10 UNION ALL
SELECT 35, 18 UNION ALL
SELECT 36, 46 UNION ALL
SELECT 36, 40 UNION ALL
SELECT 37, 30 UNION ALL
SELECT 38, 10 UNION ALL
SELECT 38, 33

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-29 : 13:57:47

Something similar to

SELECT ItemID, SUM(QtySold), SUM(QtyStock)
FROM (
SELECT ItemID, Quantity AS QtySold, 0 AS QtyStock FROM @ItemHistory WHERE DateSold >= '20070101' AND DateSold < '20070401' -- Q1 2007
UNION ALL
SELECT ItemID, 0 AS QtySold, Quantity AS QtyStock FROM @ItemHistory
) AS y
GROUP BY ItemID

E 12°55'05.25"
N 56°04'39.16"

nyuser
Starting Member

4 Posts

Posted - 2008-02-29 : 15:32:00

Thank you very much for quick reply.
What I wanted in final results is only rows whose QtySold > QtyStock.

So i modified the query as follows:

SELECT ItemID, SUM(QtySold) AS QtySold, SUM(QtyStock) AS QtyStock
FROM (
SELECT ItemID, Quantity AS QtySold, 0 AS QtyStock FROM @ItemHistory WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007' -- Q1 2007
UNION ALL
SELECT ItemID, 0 AS QtySold, Quantity AS QtyStock FROM @ItemStock
) AS y
WHERE y.QtySold > y.QtyStock
GROUP BY ItemID

But now all values in QtyStock is zero. Any clues!!
[CODE]
ItemID QtySold QtyStock
12 36 0
17 9 0
26 12 0
28 6 0
29 6 0
30 6 0
31 12 0
32 12 0
33 7 0
36 13 0
37 1 0
38 12 0
52 12 0
83 1 0
[/CODE]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-01 : 02:47:07

SELECT ItemID, SUM(QtySold) AS QtySold, SUM(QtyStock) AS QtyStock
FROM (
SELECT ItemID, Quantity AS QtySold, 0 AS QtyStock FROM @ItemHistory WHERE DateSold >= '1/10/2007' AND DateSold < '1/11/2007' -- Q1 2007
UNION ALL
SELECT ItemID, 0 AS QtySold, Quantity AS QtyStock FROM @ItemStock
) AS y
GROUP BY ItemID
HAVING SUM(y.QtySold) > SUM(y.QtyStock)

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources