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
 General SQL Server Forums
 New to SQL Server Programming
 Query Help.

Author  Topic 

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-04-11 : 15:12:47
I have created two separate queries.

1. Queries our inventory on hand (in stock) grouped by part number
2. Queries all of the open sales orders and sums the quantity required to fill those orders grouped by part number.

How can I compare query 1 to query 2? If query 1 is greater, how can I subtract the amount on hand from the amount ordered and diplay it?

TIA,

Dave

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-11 : 15:22:25
with limited information you provided this is what i think will get you close to your reqmnt

SELECT CASE WHEN t1.Instock > t2.Ordered THEN t1.Instock - t2.Ordered END AS Residue
FROM
(SELECT partnumber,SUM(InventoryOnHand) AS InStock
FROM Stock
GROUP BY partnumber) t1
INNER JOIN
(
SELECT partnumber,SUM(QuantityOrdered) AS Ordered
FROM Order
GROUP BY partnumber)t2
ON t2.partnumber=t1.partnumber
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-04-11 : 15:44:45
I'm sorry visa, I didn't want to inundate you with extraneous info. Here is teh first query (inventory)

(select (inmast.fpartno + inmast.frev) as partandrevINV, SUM(inmast.fonhand) as QuantInv from inmast
where frev = '000'and flocate1 = 'FR' AND fonhand >0
GROUP BY (inmast.fpartno + inmast.frev))

Sales:
(SELECT (SOITEM.fpartno + soitem.fpartrev) as partnorevDEM, sum((sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)) as QuantLeft
FROM somast INNER JOIN soitem ON somast.fsono=soitem.fsono
INNER JOIN sorels ON (soitem.finumber=sorels.finumber) AND (soitem.fsono=sorels.fsono)
WHERE somast.fstatus='Open' and (sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)>0 and soitem.fpartrev<>'REP'
GROUP BY (SOITEM.fpartno + soitem.fpartrev))

When I attempt following your suggestion I get:

SELECT CASE WHEN I.QuantInv > S.QuantLeft THEN I.QuantInv > S.QuantLeft END AS Residue
FROM
(select (inmast.fpartno + inmast.frev) as partandrevINV, SUM(inmast.fonhand) as QuantInv from inmast
where frev = '000'and flocate1 = 'FR' AND fonhand >0
GROUP BY (inmast.fpartno + inmast.frev) I
INNER JOIN
(SELECT (SOITEM.fpartno + soitem.fpartrev) as partnorevDEM, sum((sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)) as QuantLeft
FROM somast INNER JOIN soitem ON somast.fsono=soitem.fsono
INNER JOIN sorels ON (soitem.finumber=sorels.finumber) AND (soitem.fsono=sorels.fsono)
WHERE somast.fstatus='Open' and (sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)>0 and soitem.fpartrev<>'REP'
GROUP BY (SOITEM.fpartno + soitem.fpartrev)) S
ON I.partandrevINV =S.partnorevDEM


I get several errors:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '>'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'I'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'S'.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-04-14 : 09:00:57
Again, can anyone please help me?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-14 : 09:08:34
quote:
Originally posted by DavidChel

I'm sorry visa, I didn't want to inundate you with extraneous info. Here is teh first query (inventory)

(select (inmast.fpartno + inmast.frev) as partandrevINV, SUM(inmast.fonhand) as QuantInv from inmast
where frev = '000'and flocate1 = 'FR' AND fonhand >0
GROUP BY (inmast.fpartno + inmast.frev))

Sales:
(SELECT (SOITEM.fpartno + soitem.fpartrev) as partnorevDEM, sum((sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)) as QuantLeft
FROM somast INNER JOIN soitem ON somast.fsono=soitem.fsono
INNER JOIN sorels ON (soitem.finumber=sorels.finumber) AND (soitem.fsono=sorels.fsono)
WHERE somast.fstatus='Open' and (sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)>0 and soitem.fpartrev<>'REP'
GROUP BY (SOITEM.fpartno + soitem.fpartrev))

When I attempt following your suggestion I get:

SELECT CASE WHEN I.QuantInv > S.QuantLeft THEN I.QuantInv > S.QuantLeft END AS Residue
FROM
(select (inmast.fpartno + inmast.frev) as partandrevINV, SUM(inmast.fonhand) as QuantInv from inmast
where frev = '000'and flocate1 = 'FR' AND fonhand >0
GROUP BY (inmast.fpartno + inmast.frev)) I
INNER JOIN
(SELECT (SOITEM.fpartno + soitem.fpartrev) as partnorevDEM, sum((sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)) as QuantLeft
FROM somast INNER JOIN soitem ON somast.fsono=soitem.fsono
INNER JOIN sorels ON (soitem.finumber=sorels.finumber) AND (soitem.fsono=sorels.fsono)
WHERE somast.fstatus='Open' and (sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)>0 and soitem.fpartrev<>'REP'
GROUP BY (SOITEM.fpartno + soitem.fpartrev)) S
ON I.partandrevINV =S.partnorevDEM


I get several errors:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '>'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'I'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'S'.

you cant have > inside THEN i think you meant -. ALso you missed a braces before alias I. Can you try after making these changes and let us know how you got on?
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-04-14 : 11:07:06
Alright, I made some changes:

SELECT CASE WHEN I.QuantInv > S.QuantLeft THEN I.QuantInv - S.QuantLeft END AS Residue
FROM
(select (inmast.fpartno + inmast.frev) as partandrevINV, SUM(inmast.fonhand) as QuantInv from inmast
where frev = '000'and flocate1 = 'FR' AND fonhand >0
GROUP BY (inmast.fpartno + inmast.frev)) as I
INNER JOIN
(SELECT (SOITEM.fpartno + soitem.fpartrev) as partnorevDEM, sum((sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)) as QuantLeft
FROM somast INNER JOIN soitem ON somast.fsono=soitem.fsono
INNER JOIN sorels ON (soitem.finumber=sorels.finumber) AND (soitem.fsono=sorels.fsono)
WHERE somast.fstatus='Open' and (sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)>0 and soitem.fpartrev<>'REP'
GROUP BY (SOITEM.fpartno + soitem.fpartrev)) S
ON I.partandrevinv = S.partnorevdem

This returns data with no errors. However, there are two issues.

1. Do I need brackets around an alias? I'm not sure that I do and this returns without errors.
2. This returns a column "Residue" with some numbers and a bunch of NULLs. What I want to return is the part number and the quantity in inventory minus the quantity left on order.

Thanks for your help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-14 : 11:15:22
1.The brackets was not around alias but to enclose the derived table queries.
2.The NULLs are because you've not specified what you want in cases where I.QuantInv <= S.QuantLeft.Include the returning value that you want in those cases within ELSE condition in CASE WHEN..Also include partno within your derived table and also in your select list.
Go to Top of Page

DavidChel
Constraint Violating Yak Guru

474 Posts
Posted - 2008-04-14 : 14:21:16
Thanks for your help visakh16, I think I've got it so far. One last question:

SELECT i.partandrevinv, CASE
WHEN (I.QuantInv > S.QuantLeft) THEN I.QuantInv - S.QuantLeft
WHEN (I.QuantInv <= S.QuantLeft) THEN 0
END AS Quantity
FROM
(select (inmast.fpartno + inmast.frev) as partandrevINV, SUM(inmast.fonhand) as QuantInv from inmast
where frev = '000'and flocate1 = 'FR' AND fonhand >0
GROUP BY (inmast.fpartno + inmast.frev)) as I
INNER JOIN
(SELECT (SOITEM.fpartno + soitem.fpartrev) as partnorevDEM, sum((sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)) as QuantLeft
FROM somast INNER JOIN soitem ON somast.fsono=soitem.fsono
INNER JOIN sorels ON (soitem.finumber=sorels.finumber) AND (soitem.fsono=sorels.fsono)
WHERE somast.fstatus='Open' and (sorels.forderqty- sorels.fshipmake- sorels.fshipbuy- sorels.fshipbook)>0 and soitem.fpartrev<>'REP'
GROUP BY (SOITEM.fpartno + soitem.fpartrev)) S
ON I.partandrevinv = S.partnorevdem


How can I simply take out the Nulls? When I try something like:

Where..... and Quantity>0
or
Where.... I.QuantInv > S.QuantLeft

I get errors.


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-04-14 : 14:27:03
What error you are getting? can you post how you added the condition?
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site