CREATE PROCEDURE dbo.Receivable_Aging_InDays_2
@CompanyNumber CHAR(2) = NULL,
@Account CHAR(4) = NULL,
@SubCode CHAR(5) = NULL,
@AgingDate DATETIME = NULL
AS
--****************************************************************************
IF @AgingDate IS NULL SET @AgingDate = CONVERT(DATETIME, CONVERT(CHAR(10), GETDATE(), 101))
DECLARE TABLE @tempAIOI (strCo CHAR(2),
strAcct CHAR(4),
strAcctTitle CHAR(30),
strSubCode CHAR(5),
strSubCodeTitle CHAR(30),
strRef CHAR(6),
strTran CHAR(3),
strVouch CHAR(6),
strVouchDate DATETIME,
curAmount DEC(11,2))
INSERT INTO @tempAIOI
SELECT *
FROM tblAIOI ta
WHERE
(ta.strCo = @CompanyNumber OR @CompanyNumber IS NULL)
AND (ta.strAcct = @Account OR @Account IS NULL)
AND (ta.strSubCode = @SubCode OR @SubCode IS NULL)
AND (ta.strVouchDate <= @AgingDate OR @AgingDate IS NULL)
SELECT
ta.strCo,
ta.strAcct,
ta.strAcctTitle,
ta.strSubCode,
ta.strSubCodeTitle,
ta.strRef,
ta.strTran,
ta.strVouch,
ta.strVouchDate,
@AgingDate AS [Aging_UpTo],
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 0 AND 30
THEN ta.curAmount
ELSE 0
END) AS 'D_0_30',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 31 AND 60
THEN ta.curAmount
ELSE 0
END) AS 'D_31_60',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 61 AND 90
THEN ta.curAmount
ELSE 0
END) AS 'D_61_90',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 91 AND 120
THEN ta.curAmount
ELSE 0
END) AS 'D_91_120',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 121 AND 150
THEN ta.curAmount
ELSE 0
END) AS 'D_121_150',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 151 AND 180
THEN ta.curAmount
ELSE 0
END) AS 'D_151_180',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 181 AND 270
THEN ta.curAmount
ELSE 0
END) AS 'D_181_270',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 271 AND 360
THEN ta.curAmount
ELSE 0
END) AS 'D_271_360',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 361 AND 540
THEN ta.curAmount
ELSE 0
END) AS 'D_361_540',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) BETWEEN 541 AND 720
THEN ta.curAmount
ELSE 0
END) AS 'D_541_720',
SUM(CASE
WHEN CONVERT(smallint,(CONVERT(DATETIME, CONVERT(CHAR(10), @AgingDate, 101)) - ta.strVouchDate)) > 720
THEN ta.curAmount
ELSE 0
END) AS 'D_G_720'
FROM @tempAIOI ta
JOIN (
SELECT
ta2.strCo,
ta2.strAcct,
ta2.strSubCode,
ta2.strRef,
Sum(ta.curAmount) AS [OSvalue]
FROM #tempAIOI ta2
GROUP BY
ta2.strCo,
ta2.strAcct,
ta2.strSubCode,
ta2.strRef
HAVING
NOT Sum(ta2.curAmount) = 0
) AS tblOSItems
ON
ta.strCo = tblOSItems.strCO
AND ta.strAcct = tblOSItems.strAcct
AND ta.strSubCode = tblOSItems.strSubCode
AND ta.strRef = tblOSItems.strRef
GROUP BY
ta.strCo,
ta.strAcct,
ta.strAcctTitle,
ta.strSubCode,
ta.strSubCodeTitle,
ta.strRef,
ta.strTran,
ta.strVouch,
ta.strVouchDate
ORDER BY
ta.strCo,
ta.strAcct,
ta.strSubCode
