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.
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 TotalFROM 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.PARTWHERE (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 ..CheersMIK |
|
|
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 OccFROM DATABASE.dbo.INVMAS AS inventory LEFT OUTER JOINDATABASE.dbo.PRICE AS price ON inventory.SUP = price.SUP AND inventory.PART = price.PART LEFT OUTER JOINDATABASE.dbo.INVIDX AS location ON inventory.SUP = location.SUP AND inventory.PART = location.PART)tWHERE Occ=0AND QTYONH > 0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|