-------------------------------------------------------------------------------
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