peggasus88
Technical User
My database tables:
* TestCardInfo * <- Table Name
- BarcodeNumber (int) <- Primary Key
ProjectGroupID (int)
ProductName (nvarchar 10)
BoardRevision (nvarchar 5)
ReworkLevel (nvarchar 5)
Status (nvarchar 20)
Comments (nvarchar 255)
* ForecastDemand *
- ProjectGroupID (int)
- Quarter (nvarchar 20)
- ProductName (nvarchar 10)
Quantity (smallint)
A user goes to one of my web pages and predicts the quantity of various products (test cards) needed for particular quarters of the year; this information is stored in the 'ForecastDemand' table. These predictions help us determine the quantity of each type of test card to make each quarter, after taking into account the current inventory. The 'TestCardInfo' table stores information on individual test cards in our inventory; a test card with its Status field = 'Serviceable' means it is a useable card. The equation used to determine the quantity of each type of test card to make each quarter is:
# To Make = (# Needed - # Useable) * 1.1
I can write separate SQL statements for the '# Needed' and '# Useable':
(1)
SELECT ProductName, SUM(Quantity) AS Need
FROM ForecastDemand
WHERE Qtr = ?
GROUP BY ProductName
which gives something like:
*ProductName* *Need*
TestCard_1 100
TestCard_3 50
TestCard_5 120
(2)
SELECT ProductName, COUNT(*) AS Useable
FROM TestCardInfo
WHERE Status = 'Serviceable'
GROUP BY ProductName
which gives something like:
*ProductName* *Useable*
TestCard_1 50
TestCard_2 10
TestCard_3 60
TestCard_4 30
But I need a SELECT statement to give something like:
(3)
*ProductName* *Need* *Useable* *ToMake*
TestCard_1 100 50 55
TestCard_3 50 60 0
TestCard_5 120 0 132
I am having so much trouble trying to combine (1) and (2) to get (3)...
SELECT ForecastDemand.ProductName,
SUM(ForecastDemand.Quantity) AS Need,
COUNT(*) AS Useable
FROM ForecastDemand, TestCardInfo
WHERE ForecastDemand.Qtr = ? AND
TestCardInfo.Status = 'Serviceable'
GROUP BY ForecastDemand.ProductName
Doesn't work!
I haven't even tried to include a clause to find 'ToMake', but I think it would be something like: (MAX(Need-Useable, 0)*1.1) AS ToMake (after substituting 'Need' and 'Useable' with something that works!).
Can someone please help? It can be stored procedure if you think that would be better. I am incredibly stuck as a newbie, but I am really hoping that it's not very tricky for you SQL gurus.
-- Peggy --
* TestCardInfo * <- Table Name
- BarcodeNumber (int) <- Primary Key
ProjectGroupID (int)
ProductName (nvarchar 10)
BoardRevision (nvarchar 5)
ReworkLevel (nvarchar 5)
Status (nvarchar 20)
Comments (nvarchar 255)
* ForecastDemand *
- ProjectGroupID (int)
- Quarter (nvarchar 20)
- ProductName (nvarchar 10)
Quantity (smallint)
A user goes to one of my web pages and predicts the quantity of various products (test cards) needed for particular quarters of the year; this information is stored in the 'ForecastDemand' table. These predictions help us determine the quantity of each type of test card to make each quarter, after taking into account the current inventory. The 'TestCardInfo' table stores information on individual test cards in our inventory; a test card with its Status field = 'Serviceable' means it is a useable card. The equation used to determine the quantity of each type of test card to make each quarter is:
# To Make = (# Needed - # Useable) * 1.1
I can write separate SQL statements for the '# Needed' and '# Useable':
(1)
SELECT ProductName, SUM(Quantity) AS Need
FROM ForecastDemand
WHERE Qtr = ?
GROUP BY ProductName
which gives something like:
*ProductName* *Need*
TestCard_1 100
TestCard_3 50
TestCard_5 120
(2)
SELECT ProductName, COUNT(*) AS Useable
FROM TestCardInfo
WHERE Status = 'Serviceable'
GROUP BY ProductName
which gives something like:
*ProductName* *Useable*
TestCard_1 50
TestCard_2 10
TestCard_3 60
TestCard_4 30
But I need a SELECT statement to give something like:
(3)
*ProductName* *Need* *Useable* *ToMake*
TestCard_1 100 50 55
TestCard_3 50 60 0
TestCard_5 120 0 132
I am having so much trouble trying to combine (1) and (2) to get (3)...
SELECT ForecastDemand.ProductName,
SUM(ForecastDemand.Quantity) AS Need,
COUNT(*) AS Useable
FROM ForecastDemand, TestCardInfo
WHERE ForecastDemand.Qtr = ? AND
TestCardInfo.Status = 'Serviceable'
GROUP BY ForecastDemand.ProductName
Doesn't work!
I haven't even tried to include a clause to find 'ToMake', but I think it would be something like: (MAX(Need-Useable, 0)*1.1) AS ToMake (after substituting 'Need' and 'Useable' with something that works!).
Can someone please help? It can be stored procedure if you think that would be better. I am incredibly stuck as a newbie, but I am really hoping that it's not very tricky for you SQL gurus.
-- Peggy --