Friday, May 29, 2015

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

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