Quick Query Question - 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

General SQL Server Forums

New to SQL Server Programming

Quick Query Question

Author

Topic

jcawood
Starting Member

2 Posts

Posted - 2013-04-11 : 11:51:01

I am trying to modify my query so that it checks if an inventory item is dead at all locations (1-7 and 50). I want the query to check each location for a given item and return the result only if the item meets that criteria at all locations and has a QTY > 0.

My current query...

SELECT inventory.SUP,
inventory.PART,
location.LOC,
inventory.DESCR1 AS Description,
location.LSALDT AS [Last Sale],
location.LRECDT AS [Last Rec],
location.LTRNDT AS [Last Trans],
location.LADJDT AS [Last Adj],
location.QTYONH AS QTY,
price.COST,
location.QTYONH * price.COST AS Total

FROM DATABASE.dbo.INVMAS AS inventory LEFT OUTER JOIN
DATABASE.dbo.PRICE AS price ON inventory.SUP = price.SUP AND inventory.PART = price.PART LEFT OUTER JOIN
DATABASE.dbo.INVIDX AS location ON inventory.SUP = location.SUP AND inventory.PART = location.PART

WHERE (location.LSALDT < 20120401) AND
(location.LRECDT < 20120401) AND
(location.LTRNDT < 20120401) AND
(location.QTYONH > 0)

The image below is a table I pulled by removing my "QTY>0" from the Where division.
[url]http://imgur.com/FmKMhlg[/url]

Thanks in advance!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-11 : 12:00:39

What is the logic behind "an inventory item is dead"? how do you decide deadness ..

Cheers
MIK

jcawood
Starting Member

2 Posts

Posted - 2013-04-11 : 12:40:55

If it hasn't been received (LRECDT), transfered (LTRNDT), and sold (LSALDT) in over a year.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-12 : 05:24:20

[code]
SELECT *
FROM
(
SELECT inventory.SUP,
inventory.PART,
location.LOC,
inventory.DESCR1 AS Description,
location.LSALDT AS [Last Sale],
location.LRECDT AS [Last Rec],
location.LTRNDT AS [Last Trans],
location.LADJDT AS [Last Adj],
location.QTYONH AS QTY,
price.COST,
location.QTYONH * price.COST AS Total,
SUM(CASE WHEN QTY > 0 AND (location.LSALDT > DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0) OR location.LRECDT > DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0) OR location.LTRNDT > DATEADD(yy,DATEDIFF(yy,0,GETDATE())-1,0)) THEN 1 ELSE 0 END) OVER (PARTITION BY inventory.PART) AS Occ
FROM DATABASE.dbo.INVMAS AS inventory LEFT OUTER JOIN
DATABASE.dbo.PRICE AS price ON inventory.SUP = price.SUP AND inventory.PART = price.PART LEFT OUTER JOIN
DATABASE.dbo.INVIDX AS location ON inventory.SUP = location.SUP AND inventory.PART = location.PART
)t
WHERE Occ=0
AND QTYONH > 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources