September 2, 2009 at 10:04 am
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
(CLIENTCHAR(3),
MACTNMDECIMAL,
DOSDECIMAL,
CLMNUMCHAR(7),
PTBALDECIMAL(18,2),
DOSDATEDATETIME,
TODAYDATETIME,
DAYSLATEINT
)
/*
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
(
CLIENTCHAR(3),
MACTNMDECIMAL,
"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
September 2, 2009 at 11:30 am
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
September 2, 2009 at 11:41 am
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
September 2, 2009 at 11:55 am
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.
September 2, 2009 at 12:05 pm
No problem, glad it worked. Have fun with the remaining logic in your process. Sounds like it should be fun.
September 2, 2009 at 12:10 pm
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
(CLIENTCHAR(3),
MACTNMDECIMAL,
DOSDECIMAL,
CLMNUMCHAR(7),
PTBALDECIMAL(18,2),
DOSDATEDATETIME,
TODAYDATETIME,
DAYSLATEINT
)
/*
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
(
CLIENTCHAR(3),
MACTNMDECIMAL,
"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.
September 2, 2009 at 12:18 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply