September 11, 2008 at 3:12 am
I have the following data, which are invoices and there payment details
The TR_AGE is the number of months ago the transaction was created ie 19 Months ago, the PAY_AGE is the number of months ago the invoice was paid (or part paid). AMT is the original invoice value,
i.e. invoice No. 123 for Account # 57 was created in month 19 and part paid in month 15 and fully paid in month 14.
i.e. Invoice No. 345 for account 97 was created in month 17 and has had 4 payments made on it (note: 2 payments made in month 14) and currently has $180 outstanding
INVNO/ACCNO/TR_AGE/PAY_AGE/AMT/ PAY_AMT/ RUN_BAL/ BAL
123571915480240240240
1235719144802404800
345971716600100100500
345971714600100200400
345971714600100300300
345971713600120420180
7895418172502502500
5686218182302302300
I want to turn it into the table below, so that I can then recreate the aging at a particular month. For example pick month 17 and I can analyze all transactions outstanding at that month and how many months they were unpaid. The Value = 0 rows are not required are just included for clarity. DIFF is TR_AGE – AGE
AGE /INVNO / ACCNO / TR_AGE / VALUE / DIFF
1912357194800
1812357194801
1712357194802
1612357194803
1512357192404
14123571905
1734597176000
1634597175001
1534597175002
1434597173003
1334597171804
1234597171805
1134597171806
1034597171807
934597171808
834597171809
7345971718010
6345971718011
5345971718012
4345971718013
3345971718014
2345971718015
1345971718016
0345971718017
1878954182500
19789541801
18568621800
I have reached the limit of my sql skills so any Help would be greatly appreciated, Thanks John
September 11, 2008 at 9:26 am
This should get you started:
-- *** Test Data ***
CREATE TABLE #t
(
    InvNO int NOT NULL
    ,AccNo int NOT NULL
    ,TR_Age int NOT NULL
    ,Pay_Age int NOT NULL
    ,Amt int NOT NULL
    ,Pay_Amt int NOT NULL
    ,Run_Bal int NOT NULL
    ,Bal int NOT NULL
)
INSERT INTO #t
SELECT 123, 57, 19, 15, 480, 240, 240, 240 UNION ALL
SELECT 123, 57, 19, 14, 480, 240, 480, 0 UNION ALL
SELECT 345, 97, 17, 16, 600, 100, 100, 500 UNION ALL
SELECT 345, 97, 17, 14, 600, 100, 200, 400 UNION ALL
SELECT 345, 97, 17, 14, 600, 100, 300, 300 UNION ALL
SELECT 345, 97, 17, 13, 600, 120, 420, 180 UNION ALL
SELECT 789, 54, 18, 17, 250, 250, 250, 0 UNION ALL
SELECT 568, 62, 18, 18, 230, 230, 230, 0
-- *** End Test Data ***
-- Here goes....
;WITH cte (InvNo, AccNo, TR_Age, Pay_Age, Bal)
AS
(
    SELECT InvNo, AccNo, TR_Age, Pay_Age, Bal
    FROM
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY InvNo, Pay_Age ORDER BY Bal) AS Low
            ,InvNo, AccNo, TR_Age, Pay_Age, Bal
        FROM #t
    ) D
    WHERE Low = 1
    UNION ALL
    SELECT DISTINCT InvNo, AccNo, TR_Age, TR_Age, Amt
    FROM #t
    WHERE TR_Age <> Pay_Age
)
, N (N) -- use number/tally table in practice!
AS
(
        SELECT 0 UNION ALL
        SELECT 1 UNION ALL
        SELECT 2 UNION ALL
        SELECT 3 UNION ALL
        SELECT 4 UNION ALL
        SELECT 5 UNION ALL
        SELECT 6 UNION ALL
        SELECT 7 UNION ALL
        SELECT 8 UNION ALL
        SELECT 9 UNION ALL
        SELECT 10 UNION ALL
        SELECT 11 UNION ALL
        SELECT 12 UNION ALL
        SELECT 13 UNION ALL
        SELECT 14 UNION ALL
        SELECT 15 UNION ALL
        SELECT 16 UNION ALL
        SELECT 17 UNION ALL
        SELECT 18 UNION ALL
        SELECT 19 UNION ALL
        SELECT 20
)
SELECT TR_Age - Diff AS Age
    ,InvNo
    ,AccNo
    ,TR_Age
    ,Bal AS [Value]
    ,Diff
FROM
(
    SELECT T.*, V.MinPayAge
        ,ROW_NUMBER() OVER(PARTITION BY T.InvNo ORDER BY V.MinPayAge DESC) - 1 AS Diff
    FROM cte T
        JOIN
        (
            SELECT V1.InvNo, V1.Pay_Age, COALESCE(V1.MinPayAge, V2.MinPayAge, 0) AS MinPayAge
            FROM
            (
                SELECT A1.InvNo, A1.Pay_Age, MAX(A2.Pay_Age) + 1 AS MinPayAge
                FROM cte A1
                    LEFT JOIN cte A2
                        ON A1.InvNo = A2.InvNo
                            AND A2.Pay_Age < A1.Pay_Age
                GROUP BY A1.InvNo, A1.Pay_Age
            ) V1
                LEFT JOIN
                (
                    SELECT B1.InvNo, B1.Pay_Age AS MinPayAge
                    FROM #t B1
                    WHERE B1.Bal = 0
                ) V2
                    ON V1.InvNo = V2.InvNo
        ) V
            ON T.InvNo = V.InvNo
                AND T.Pay_Age = V.Pay_Age
        JOIN N
            ON N.N BETWEEN V.MinPayAge AND V.Pay_Age
) D
ORDER BY InvNo, Age DESC
September 11, 2008 at 8:01 pm
Thanks Ken
That worked a treat ! I really appreciate your efforts. I would never have got there on my own.
Cheers
John
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply