Friday, May 29, 2015

Dynamics GP - SQL View - Inventory Aging - Aged Inventory Trial Balance

-------------------------------------------------------------------------------
6-Period Version
-------------------------------------------------------------------------------
SELECT TOP (100) PERCENT LEFT(iv00101.itemnmbr, 2) 
                         AS Section, 
                         Rtrim(iv00101.itemnmbr) 
                         AS ITEMNUMBER, 
                         Rtrim(iv00101.itemdesc) 
                         AS ITEMNAME, 
                         iv10200.daterecd 
                         AS DATERECEIVED, 
                         iv10200.qtyrecvd 
                         AS QTYRECEIVED, 
                         iv10200.qtysold, 
                         iv00101.itmclscd 
                         AS ITEMCLASS, 
                         iv10200.unitcost, 
                         iv10200.pchsrcty 
                         AS RECEIPTTYPE, 
                         iv10200.rcptnmbr 
                         AS RECEIPTNO, 
                         Rtrim(Ltrim(iv10200.trxloctn)) 
                         AS LOCATION, 
                         iv10200.qtyrecvd - iv10200.qtysold 
                         AS QTYAVAILABLE, 
                         ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                         iv10200.unitcost AS CURRENTVALUE, 
                         Datediff(day, iv10200.daterecd, Getdate()) 
                         AS AGEDDAYS, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN '0-30' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN '31-60' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN '61-90' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN '91-120' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN '121-180' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN '181-360' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN '361-720' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 720 
                         THEN 'Over720' 
                         END 
                         AS BUCKET, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN 1 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN 2 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN 3 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN 4 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN 5 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN 6 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN 7 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 720 
                         THEN 8 
                         END 
                         AS BUCKETORDER, 
                         iv00101.itmgedsc, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [0-30], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [31-60], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [61-90], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [91-120], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [121-180], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [181-360], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [361-720], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 
                                720 THEN ( 
                           iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS Over720, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol0-30], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol31-60], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol61-90], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol91-120], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol121-180], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol181-360], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol361-720], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 
                                720 THEN ( 
                           iv10200.qtyrecvd - iv10200.qtysold ) * 
                                         iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS DolOver720 
FROM   dbo.iv00101 AS IV00101 
       INNER JOIN dbo.iv10200 AS IV10200 
               ON iv00101.itemnmbr = iv10200.itemnmbr 
WHERE  ( iv10200.pchsrcty <> 0 ) 
       AND ( iv10200.qtyrecvd - iv10200.qtysold <> 0 ) 
ORDER  BY receiptno 

----------------------------------------------------------------------------------
12-Period version
----------------------------------------------------------------------------------
SELECT TOP (100) PERCENT LEFT(iv00101.itemnmbr, 2) 
                         AS Section, 
                         Rtrim(iv00101.itemnmbr) 
                         AS ITEMNUMBER, 
                         Rtrim(iv00101.itemdesc) 
                         AS ITEMNAME, 
                         iv10200.daterecd 
                         AS DATERECEIVED, 
                         iv10200.qtyrecvd 
                         AS QTYRECEIVED, 
                         iv10200.qtysold, 
                         iv00101.itmclscd 
                         AS ITEMCLASS, 
                         iv10200.unitcost, 
                         iv10200.pchsrcty 
                         AS RECEIPTTYPE, 
                         iv10200.rcptnmbr 
                         AS RECEIPTNO, 
                         Rtrim(Ltrim(iv10200.trxloctn)) 
                         AS LOCATION, 
                         iv10200.qtyrecvd - iv10200.qtysold 
                         AS QTYAVAILABLE, 
                         ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                         iv10200.unitcost AS CURRENTVALUE, 
                         Datediff(day, iv10200.daterecd, Getdate()) 
                         AS AGEDDAYS, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN '0-30' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN '31-60' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN '61-90' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN '91-120' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 150 THEN '121-150' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 150 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN '151-180' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 210 THEN '181-210' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 210 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 240 THEN '211-240' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 240 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 270 THEN '241-270' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 270 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 300 THEN '271-300' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 300 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 330 THEN '301-330' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 330 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN '331-360' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN '361-720' 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 720 
                         THEN 'Over720' 
                         END 
                         AS BUCKET, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN 1 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN 2 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN 3 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN 4 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 150 THEN 5 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 150 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN 6 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 210 THEN 7 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 210 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 240 THEN 8 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 240 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 270 THEN 9 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 270 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 300 THEN 10 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 300 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 330 THEN 11 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 330 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN 12 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN 13 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 720 
                         THEN 14 
                         END 
                         AS BUCKETORDER, 
                         iv00101.itmgedsc, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [0-30], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [31-60], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [61-90], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [91-120], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 150 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [121-150], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 150 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [151-180], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 210 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [181-210], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 210 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 240 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [211-240], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 240 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 270 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [241-270], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 270 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 300 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [271-300], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 300 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 330 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [301-330], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 330 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [330-360], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS [361-720], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 
                                720 THEN ( 
                           iv10200.qtyrecvd - iv10200.qtysold ) 
                           ELSE 0 
                         END 
                         AS Over720, 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 0 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 30 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol0-30], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 30 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 60 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol31-60], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 60 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 90 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol61-90], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 90 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 120 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol91-120], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 120 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 150 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol121-150], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 150 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 180 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol151-180], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 180 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 210 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol181-210], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 210 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 240 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol211-240], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 240 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 270 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol241-270], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 270 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 300 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol271-300], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 300 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 330 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol301-330], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 330 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 360 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol331-360], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) > 360 
                                AND Datediff(day, iv10200.daterecd, Getdate()) 
                                    <= 720 THEN 
                           ( iv10200.qtyrecvd - iv10200.qtysold ) * 
                           iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS [Dol361-720], 
                         CASE 
                           WHEN Datediff(day, iv10200.daterecd, Getdate()) >= 
                                720 THEN ( 
                           iv10200.qtyrecvd - iv10200.qtysold ) * 
                                         iv10200.unitcost 
                           ELSE 0 
                         END 
                         AS DolOver720 
FROM   dbo.iv00101 AS IV00101 
       INNER JOIN dbo.iv10200 AS IV10200 
               ON iv00101.itemnmbr = iv10200.itemnmbr 
WHERE  ( iv10200.pchsrcty <> 0 ) 
       AND ( iv10200.qtyrecvd - iv10200.qtysold <> 0 ) 
ORDER  BY receiptno 

No comments:

Post a Comment