Help w/my Update Logic

  • SparTodd

    SSChasing Mays

    Points: 600

    Hopefully one of you experts can help me with my Update logic here, because I've achieved the point where I am drawing a blank.

    In a nutshell, the table #AGED2A is only being updated with the first matching record from table #AGED1B, which means I probably need some kind of "for each", but I'm scratching my head how to get it done and I would greatly appreciate your help.

    /*

    Create Temp table #AGED1B to hold the initial selection results from database

    */

    CREATE TABLE #AGED1B

    ( CLIENT CHAR(3),

    MACTNM DECIMAL,

    DOS DECIMAL,

    CLMNUM CHAR(7),

    PTBAL DECIMAL(18,2),

    DOSDATE DATETIME,

    TODAY DATETIME,

    DAYSLATE INT

    )

    /*

    Populate Temp table #AGED1B with test data; in reality the data is inserted using a SQL

    query, drawing data from two SQL tables

    */

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90815, '8037186', 7.80, 2009-08-15, 2009-09-02, 18)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90725, '8037189', 41.31, 2009-07-25, 2009-09-02, 39)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90630, '8037210', 13.77, 2009-06-30, 2009-09-02, 64)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90521, '8101003', 14.81, 2009-05-21, 2009-09-02, 124)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90614, '8101003', 11.23, 2009-06-14, 2009-09-02, 80)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90713, '8101003', 4.26, 2009-07-13, 2009-09-02, 51)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 62809, '8101003', 28.35, 6/28/2009, 9/2/2009, 66)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 62809, '8101003', 14.35, 6/28/2009, 9/2/2009, 66)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 71509, '8101004', 11.26, 7/15/2009, 9/2/2009, 49)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 72809, '8101005', 9.78, 7/28/2009, 9/2/2009, 36)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 72509, '9178253', 2.42, 7/25/2009, 9/2/2009, 39)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 72809, '9142001', 52.48, 7/28/2009, 9/2/2009, 36)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 81209, '9142002', 12.45, 8/12/2009, 9/2/2009, 21)

    /*

    Create second Temp table #AGED2A to hold the distinct values from Temp table #AGED1B

    */

    CREATE TABLE #AGED2A

    (

    CLIENT CHAR(3),

    MACTNM DECIMAL,

    "0_30" DECIMAL(18,2) DEFAULT(0),

    "31_60" DECIMAL(18,2) DEFAULT(0),

    "61_90" DECIMAL(18,2) DEFAULT(0),

    "91PLUS" DECIMAL(18,2) DEFAULT(0)

    )

    /*

    Insert distinct values into Temp table #AGED2A from Temp table #AGED1B

    */

    INSERT INTO #AGED2A(CLIENT, MACTNM)

    SELECT DISTINCT CLIENT, MACTNM

    FROM #AGED1B

    /*

    Logic to update Temp table #AGED2A aging bucket columns 0-31, 61-90, etc. with data from Temp table #AGED1B

    */

    UPDATE #AGED2A

    SET "0_30" = "0_30" + #AGED1B.PTBAL

    FROM #AGED1B

    WHERE #AGED2A.CLIENT = #AGED1B.CLIENT AND #AGED2A.MACTNM = #AGED1B.MACTNM AND #AGED1B.DAYSLATE <31

    UPDATE #AGED2A

    SET "31_60" = "31_60" + #AGED1B.PTBAL

    FROM #AGED1B

    WHERE #AGED2A.CLIENT = #AGED1B.CLIENT AND #AGED2A.MACTNM = #AGED1B.MACTNM AND #AGED1B.DAYSLATE

    BETWEEN 31 AND 60

    UPDATE #AGED2A

    SET "61_90" = "61_90" + #AGED1B.PTBAL

    FROM #AGED1B

    WHERE #AGED2A.CLIENT = #AGED1B.CLIENT AND #AGED2A.MACTNM = #AGED1B.MACTNM AND #AGED1B.DAYSLATE

    BETWEEN 61 AND 91

    UPDATE #AGED2A

    SET "91PLUS" = "91PLUS" + #AGED1B.PTBAL

    FROM #AGED1B

    WHERE #AGED2A.CLIENT = #AGED1B.CLIENT AND #AGED2A.MACTNM = #AGED1B.MACTNM AND #AGED1B.DAYSLATE >90

    /*

    Display results of Temp table #AGED2A

    */

    SELECT *

    FROM #AGED2A

    /*

    Drop Temp tables

    */

    DROP TABLE #AGED1B

    DROP TABLE #AGED2A

  • matt6288

    SSCertifiable

    Points: 5058

    instead of using a second table you could use the pivot function. this is what i came up with. i think it fits your requirements.

    /*

    Create Temp table #AGED1B to hold the initial selection results from database

    */

    CREATE TABLE #AGED1B

    ( CLIENT CHAR(3),

    MACTNM DECIMAL,

    DOS DECIMAL,

    CLMNUM CHAR(7),

    PTBAL DECIMAL(18,2),

    DOSDATE DATETIME,

    TODAY DATETIME,

    DAYSLATE INT

    )

    /*

    Populate Temp table #AGED1B with test data; in reality the data is inserted using a SQL

    query, drawing data from two SQL tables

    */

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90815, '8037186', 7.80, 2009-08-15, 2009-09-02, 18)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90725, '8037189', 41.31, 2009-07-25, 2009-09-02, 39)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90630, '8037210', 13.77, 2009-06-30, 2009-09-02, 64)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90521, '8101003', 14.81, 2009-05-21, 2009-09-02, 124)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90614, '8101003', 11.23, 2009-06-14, 2009-09-02, 80)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90713, '8101003', 4.26, 2009-07-13, 2009-09-02, 51)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 62809, '8101003', 28.35, 6/28/2009, 9/2/2009, 66)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 62809, '8101003', 14.35, 6/28/2009, 9/2/2009, 66)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 71509, '8101004', 11.26, 7/15/2009, 9/2/2009, 49)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 72809, '8101005', 9.78, 7/28/2009, 9/2/2009, 36)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 72509, '9178253', 2.42, 7/25/2009, 9/2/2009, 39)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 72809, '9142001', 52.48, 7/28/2009, 9/2/2009, 36)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 81209, '9142002', 12.45, 8/12/2009, 9/2/2009, 21)

    SELECT client, mactnm, ISNULL([0_30],0.00) AS [0_30],

    ISNULL([31_60],0.00) AS [31_60],

    ISNULL([61_90],0.00) AS [61_90],

    ISNULL([90PLUS],0.00) AS [90PLUS]

    FROM

    (

    SELECT client, mactnm, ptbal AS ptbal, CASE WHEN DAYSLATE 30 AND DAYSLATE 60 AND DAYSLATE <= 90 THEN '61_90'

    ELSE '90PLUS'

    END AS dayslate

    FROM #aged1b

    ) AS a

    PIVOT

    (

    SUM(ptbal) FOR daysLate IN ([0_30], [31_60], [61_90], [90PLUS])

    ) AS pivottable

    DROP TABLE #AGED1B

  • drew.allen

    SSC Guru

    Points: 76580

    Here is what I believe is happening. Your FROM statement is doing an implicit join on the two tables. It is then calculating the original value for each of your date ranges plus the balance for the current record. Note that's the original value, and since that is zero, this is equivalent to the balance for each record. Then it is processing your query for each record and updating the total balance due with the balance for just the current record. What you need to do is calculate the sum of the records and then update. You can also do all of the updates in one statement, which will be much more efficient than running four updates.

    I created the following using a pivot table in a CTE.

    WITH PastDueSumm AS(

    SELECT Client, MactNm, [0], [30], [60], [90]

    FROM (

    SELECT

    Client

    , MactNm

    , CASE

    WHEN DaysLate < 30 THEN 0

    WHEN DaysLate < 60 THEN 30

    WHEN DaysLate < 90 THEN 60

    ELSE 90

    END AS DaysLate

    , PtBal

    FROM #AGED1B

    ) AS p

    PIVOT (

    Sum(PtBal)

    FOR DaysLate IN ( [0], [30], [60], [90] )

    ) AS Pvt

    )

    UPDATE #AGED2A

    SET [0_30] = [0_30] + [0]

    , [31_60] = [31_60] + [30]

    , [61_90] = [61_90] + [60]

    , [91Plus] = [91Plus] + [90]

    FROM PastDueSumm

    WHERE #AGED2A.CLIENT = PastDueSumm.CLIENT AND #AGED2A.MACTNM = PastDueSumm.MACTNM

    SELECT *

    FROM #AGED2A

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • SparTodd

    SSChasing Mays

    Points: 600

    Matt, thanks. That worked very well. My background is more on Access where the Crosstab Wizard simply does it for you, and I'm still learning the SQL Pivot function, so I appreciate the lesson. The fact that I got as far as I did on my own with this is a minor miracle.

    On thing I did though, is that I kept my code to create #AGED2A and modified your Pivot to insert the results into #AGED2A. The reason is that my next step is to start adjusting the amounts in 0_30, 31_60, etc. columns based on recent payment activity. So, for example, if someone has recently paid $25.00, for example, then I need to adjust the balance of the 91PLUS column by the payment amount, take the remainder if 25 > 91PLUS and apply it to 61_90, as well as set 91PLUS = 0 where 91PLUS < 0 after subtracting the amount of the payment. Fun stuff.

    I do thank you immensely for helping me through my next to last hurdle here. Next time I should be able to handle such a requirement on my own.

  • matt6288

    SSCertifiable

    Points: 5058

    No problem, glad it worked. Have fun with the remaining logic in your process. Sounds like it should be fun.

  • SparTodd

    SSChasing Mays

    Points: 600

    drew.allen (9/2/2009)


    Here is what I believe is happening. Your FROM statement is doing an implicit join on the two tables. It is then calculating the original value for each of your date ranges plus the balance for the current record. Note that's the original value, and since that is zero, this is equivalent to the balance for each record. Then it is processing your query for each record and updating the total balance due with the balance for just the current record. What you need to do is calculate the sum of the records and then update. You can also do all of the updates in one statement, which will be much more efficient than running four updates.

    I created the following using a pivot table in a CTE.

    WITH PastDueSumm AS(

    SELECT Client, MactNm, [0], [30], [60], [90]

    FROM (

    SELECT

    Client

    , MactNm

    , CASE

    WHEN DaysLate < 30 THEN 0

    WHEN DaysLate < 60 THEN 30

    WHEN DaysLate < 90 THEN 60

    ELSE 90

    END AS DaysLate

    , PtBal

    FROM #AGED1B

    ) AS p

    PIVOT (

    Sum(PtBal)

    FOR DaysLate IN ( [0], [30], [60], [90] )

    ) AS Pvt

    )

    UPDATE #AGED2A

    SET [0_30] = [0_30] + [0]

    , [31_60] = [31_60] + [30]

    , [61_90] = [61_90] + [60]

    , [91Plus] = [91Plus] + [90]

    FROM PastDueSumm

    WHERE #AGED2A.CLIENT = PastDueSumm.CLIENT AND #AGED2A.MACTNM = PastDueSumm.MACTNM

    SELECT *

    FROM #AGED2A

    Drew

    Thanks for taking the time to help me with this. CTEs are definitely an area that I need to get more familiar with. I've tried to splice your code in with mine as follows, and I'm not getting any results.

    /*

    Create Temp table #AGED1B to hold the initial selection results from database

    */

    CREATE TABLE #AGED1B

    ( CLIENT CHAR(3),

    MACTNM DECIMAL,

    DOS DECIMAL,

    CLMNUM CHAR(7),

    PTBAL DECIMAL(18,2),

    DOSDATE DATETIME,

    TODAY DATETIME,

    DAYSLATE INT

    )

    /*

    Populate Temp table #AGED1B with test data; in reality the data is inserted using a SQL

    query, drawing data from two SQL tables

    */

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90815, '8037186', 7.80, 2009-08-15, 2009-09-02, 18)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90725, '8037189', 41.31, 2009-07-25, 2009-09-02, 39)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90630, '8037210', 13.77, 2009-06-30, 2009-09-02, 64)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90521, '8101003', 14.81, 2009-05-21, 2009-09-02, 124)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90614, '8101003', 11.23, 2009-06-14, 2009-09-02, 80)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('BME', 25, 90713, '8101003', 4.26, 2009-07-13, 2009-09-02, 51)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 62809, '8101003', 28.35, 6/28/2009, 9/2/2009, 66)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 62809, '8101003', 14.35, 6/28/2009, 9/2/2009, 66)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 71509, '8101004', 11.26, 7/15/2009, 9/2/2009, 49)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 425, 72809, '8101005', 9.78, 7/28/2009, 9/2/2009, 36)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 72509, '9178253', 2.42, 7/25/2009, 9/2/2009, 39)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 72809, '9142001', 52.48, 7/28/2009, 9/2/2009, 36)

    INSERT INTO #AGED1B(CLIENT, MACTNM, DOS, CLMNUM, PTBAL, DOSDATE, TODAY, DAYSLATE)

    VALUES ('CDX', 32, 81209, '9142002', 12.45, 8/12/2009, 9/2/2009, 21)

    /*

    Create second Temp table #AGED2A to hold the distinct values from Temp table #AGED1B

    */

    CREATE TABLE #AGED2A

    (

    CLIENT CHAR(3),

    MACTNM DECIMAL,

    "0_30" DECIMAL(18,2) DEFAULT(0),

    "31_60" DECIMAL(18,2) DEFAULT(0),

    "61_90" DECIMAL(18,2) DEFAULT(0),

    "91PLUS" DECIMAL(18,2) DEFAULT(0)

    );

    WITH PastDueSumm AS(

    SELECT Client, MactNm, [0], [30], [60], [90]

    FROM (

    SELECT

    Client

    , MactNm

    , CASE

    WHEN DaysLate < 30 THEN 0

    WHEN DaysLate < 60 THEN 30

    WHEN DaysLate < 90 THEN 60

    ELSE 90

    END AS DaysLate

    , PtBal

    FROM #AGED1B

    ) AS p

    PIVOT (

    Sum(PtBal)

    FOR DaysLate IN ( [0], [30], [60], [90] )

    ) AS Pvt

    )

    UPDATE #AGED2A

    SET [0_30] = [0_30] + [0]

    , [31_60] = [31_60] + [30]

    , [61_90] = [61_90] + [60]

    , [91Plus] = [91Plus] + [90]

    FROM PastDueSumm

    WHERE #AGED2A.CLIENT = PastDueSumm.CLIENT AND #AGED2A.MACTNM = PastDueSumm.MACTNM

    SELECT *

    FROM #AGED2A

    DROP TABLE #AGED1B

    DROP TABLE #AGED2A

    The first time I ran it, it errored out with the suggestion that I add a semicolon to the statement prior to WITH. I added that and it ran, but with an empty results set.

  • SparTodd

    SSChasing Mays

    Points: 600

    Matt Wilhoite (9/2/2009)


    No problem, glad it worked. Have fun with the remaining logic in your process. Sounds like it should be fun.

    Yeah, I don't think it will be too bad.

    Once I get my logic down, then believe it or not I have to rewrite this in SQL400 as the data actually exists in DB2 on an AS400, then I have to figure out to translate all that into a procedure to bind the results to an ASP.net Gridview. :w00t:

    One thing at a time.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply