Tricky SQL SELECT Statement...Please Help.

[peggasus88]

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

 

[peggasus88]

Sorry, 'Quarter' in the *ForecastDemand* table should be 'Qtr'.

 

[sunila7]

Try this SQL .... i have not tested it.
SELECT ForecastDemand.ProductName,
SUM(ForecastDemand.Quantity) AS Need,
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProjectGroupId = FD.ProjectGroupID) as Usable,
(SUM(ForecastDemand.Quantity) -
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProjectGroupId = FD.ProjectGroupID))*1.1 as ToMake
FROM ForecastDemand FD
WHERE FD.Qtr = 4
GROUP BY ForecastDemand.ProductName

Thanks
Sunil

 

[sunila7]

I had missed out that serviceable condition in the previous query.
SELECT ForecastDemand.ProductName,
SUM(ForecastDemand.Quantity) AS Need,
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProjectGroupId = FD.ProjectGroupID and Status = 'Serviceable') as Usable,
(SUM(ForecastDemand.Quantity) -
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProjectGroupId = FD.ProjectGroupID and Status = 'Serviceable'))*1.1 as ToMake
FROM ForecastDemand FD
WHERE FD.Qtr = 4
GROUP BY ForecastDemand.ProductName

Thanks
Sunil

 

[sunila7]

Some more mistakes........ i forgot to change reference to table ForecastDemad to FD.... i think this should work.
SELECT FD.ProductName,
SUM(FD.Quantity) AS Need,
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProjectGroupId = FD.ProjectGroupID and Status = 'Serviceable') as Usable,
(SUM(FD.Quantity) -
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProjectGroupId = FD.ProjectGroupID and Status = 'Serviceable'))*1.1 as ToMake
FROM ForecastDemand FD
WHERE FD.Qtr = 4
GROUP BY FD.ProductName


sunil

 

[peggasus88]

Hi.

Yeah! I'm getting the correct numbers for the 'Need'. The numbers for 'Useable' still aren't correct though.

I forgot to mention that the two tables aren't related via their ProjectGroupIDs. I guess they're not really related at all. A test card belongs to a project group, but when I'm calculating the number of 'Useable' cards, I look at ALL the records in the *TestCardInfo* table (cards can be transferred between projects groups). Similarly, with the *ForecastDemand* table, I sum every project groups's prediction for that quarter to get the total number needed for each type of card. Sorry I wasn't more clear. I was already scared that I would get yelled at for submitting such a long question.

Thanks for your input. I really do appreciate it. Any more suggestions on the 'Useable' calculation? Thanks.

 

[sunila7]

Hi
if u want to get the sum all that is there in the TestCardInfo table this querry should work

SELECT
FD.ProductName,
SUM(FD.Quantity) AS Need,
SELECT COUNT(*) FROm TestCardInfo TCI Where
Status = 'Serviceable') as Usable,
SUM(FD.Quantity) -
SELECT COUNT(*) FROm TestCardInfo TCI Where
Status = 'Serviceable'))*1.1 as ToMake
FROM ForecastDemand FD
WHERE FD.Qtr = 4
GROUP BY FD.ProductName

Sunil

 

[sunila7]

Hi
if u want to get the sum all that is there in the TestCardInfo table this querry should work

SELECT
FD.ProductName,
SUM(FD.Quantity) AS Need,
SELECT COUNT(*) FROm TestCardInfo TCI Where
Status = 'Serviceable') as Usable,
SUM(FD.Quantity) -
SELECT COUNT(*) FROm TestCardInfo TCI Where
Status = 'Serviceable'))*1.1 as ToMake
FROM ForecastDemand FD
WHERE FD.Qtr = 4
GROUP BY FD.ProductName

i hope this is what u r trying to get. b'cos i am not sure wether i understood it well enough

Sunil

 

[tlbroadbent]

You can join two queries. Take a look at the following. Does it return the needed result?

SELECT
q1.ProductName,
q1.Need,
q2.Useable,
ToMake=q1.Need-q2.Useable
FROM
(SELECT
ProductName,
Need=SUM(Quantity)
FROM ForecastDemand
WHERE Qtr = ?
GROUP BY ProductName) q1

INNER JOIN

(SELECT
ProductName,
Useable=COUNT(*)
FROM TestCardInfo
WHERE Status = 'Serviceable'
GROUP BY ProductName) q2

ON q1.ProductName=q2.ProductName Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[peggasus88]

Hi again.

When I try to run Terry's query, I get the following error:
[Microsoft][ODBC SQL Servcer Driver][SQL Server]Incorrect syntax near the keyword 'SET'.

I feel kinda dumb because I can't find the keyword 'SET' anywhere! Also, I'm going to have to do some studying up on INNER JOINs, but since the tables are joined by ProductName, what happens if someone needs a new product that is not currently in the inventory, like in the case of TestCard_5 above.

When I ran the last query Sunil suggested, the 'Need' values were correct and the 'Useable' values were incorrect; I figured out where the incorrect 'Useable' values were coming from...They are the TOTAL number of 'Serviceable' cards in the *TestCardInfo* table! Ignoring the 'ToMake' for now, I am getting something like:

*ProductName* *Need* *Useable*
TestCard_1 100 350
TestCard_3 50 350
TestCard_5 120 350

where 350 is the total number of 'Serviceable' cards in the *TestCardInfo* table. It looks like the query is counting up ALL the Serviceable cards, rather than counting 'Serviceable' cards of a SPECIFIC ProductName (TestCard_1, TestCard_2, TestCard_3). So close!

Please don't give up on this question just yet. I don't have anyone else to ask.

 

[tlbroadbent]

ODBC doesn't like the query string you are submitting from the web page. Can you post the code that builds the query and executes it?

You can use a LEFT JOIN rather than an INNER JOIN to get all the needs, even those without matching inventory. You may want to create a view and then select from the view. That will simplify the web page code.

Create View vToMakeQty As

SELECT
q1.ProductName,
q1.Need,
--Handle condition where no inventory exists
Useable=IsNull(q2.Useable,0),
ToMake=q1.Need-Isnull(q2.Useable,0)
FROM
(SELECT
ProductName,
Need=SUM(Quantity)
FROM ForecastDemand
GROUP BY ProductName) q1

LEFT JOIN

(SELECT
ProductName,
Useable=COUNT(*)
FROM TestCardInfo
WHERE Status = 'Serviceable'
GROUP BY ProductName) q2

ON q1.ProductName=q2.ProductName

Then in the web page you could select from the view. ODBC will likely be more tolerant of this code.

SELECT * FROM vToMakeQty
WHERE Qtr = ? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

pegassus:
I notice you mentioned that you can use stored procedures. Just to take a different slant on it, you could decide to create an SP rather than a view, as in tb's last suggestion. i.e. If you are creating a new database object, you perhaps might as well create an SP (where you might get some caching benefits) rather than a view (no caching benefit.)

Your front-end code might find executing the SP more acceptable than the raw SQL. So you could put tb's earlier suggestion (&quot;...you can join two queries...&quot;) into your new SP. (but as he suggested change the Inner Join to a Left Join)

Just as another possible option, you could also try this code, too, which seems to work quite well with the test data on my system. It might run well directly for you, but personally I would also put it into a stored procedure.
[tt]
Select
ProductName, Need, Usable,
CASE When Usable > Need
Then 0
Else
((Need - Usable) * 1.1)
END as ToMake
from
(
Select
f.ProductName,
SUM(f.Quantity) as Need,
(Select ISNULL(Count(*),0) as Usable
from TestCardInfo t
where t.ProductName = f.ProductName
and t.Status = 'Serviceable') as Usable
From ForecastDemand f
where f.Qtr = ???
Group By f.ProductName
) dt
[/tt]
-----------------

 

[sunila7]

Hi Peggy,
Try this query.....
SELECT
FD.ProductName,
SUM(FD.Quantity) AS Need,

(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProductName = FD.ProductName AND
Status = 'Serviceable') as Usable,

(SUM(FD.Quantity) -
(SELECT COUNT(*) FROm TestCardInfo TCI Where
TCI.ProductName = FD.ProductName AND
Status = 'Serviceable'))*1.1 as ToMake

FROM ForecastDemand FD
WHERE FD.Qtr = 4
GROUP BY FD.ProductName

Sunil

 

[peggasus88]

I'm so happy it's working! I tried all of your suggestions because I want to learn as much as possible. I'm not at the stage where I know which implementation is more effecient...I guess that comes with experience.

I do have a question about using the view...SELECTing * FROM vToMakeQty gives the correct results, but when I append the clause, WHERE Qtr = ?, I get the following:
[Microsoft][ODBC SQL Servcer Driver][SQL Server]Invalid column name 'Qtr'.
Does the view have to include a 'Qtr' column?

If you don't have time to reply, then I want to thank you. You guys are really great.

 

[sunila7]

Hi,

I think u should include that field into ur view or u can link that view to forecastdemand table and then write ur where clause.


sunil

 

[tlbroadbent]

The view needs the column Qtr. I apologize for missing that.

Create View vToMakeQty As

SELECT
q1.Qtr,
q1.ProductName,
q1.Need,
--Handle condition where no inventory exists
Useable=IsNull(q2.Useable,0),
ToMake=q1.Need-Isnull(q2.Useable,0)
FROM
(SELECT
Qtr,
ProductName,
Need=SUM(Quantity)
FROM ForecastDemand
GROUP BY ProductName) q1

LEFT JOIN

(SELECT
ProductName,
Useable=COUNT(*)
FROM TestCardInfo
WHERE Status = 'Serviceable'
GROUP BY ProductName) q2

ON q1.ProductName=q2.ProductName
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[bperry]

[tt]
...
FROM
(SELECT
Qtr,
ProductName,
Need=SUM(Quantity)
FROM ForecastDemand
GROUP BY ProductName) q1
LEFT JOIN
....
[/tt]

I am wondering if this will still work, as Qtr is not included in the Group By. not sure about that.

 

[peggasus88]

Yeah, I'm getting:
[Microsoft][ODBC SQL Servcer Driver][SQL Server]Column 'ForecastDemand.Qtr' is invalid in the select list because it is not contained in either an aggreagate function or the GROUP BY clause.

 

[tlbroadbent]

Add Qtr to the Group By statement. Sorry I missed that one also. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.