April 1, 2011 at 9:55 am
in the code below I am trying to do an aging report. trying to get the total balances for every thirty days. When I run the code I get nothing back however I know that I should get something back for the 120plus category. if i highlight the code within the sub-selects I get what I expect back but not if I run it all together. anyone have any suggestions as to why this is not working?
SELECT
--INS.CARRIER_ID
CHARG.ACCT_PLAN1,
ZERO.BAL0 AS ZERO_30,
ONE.BAL1 AS THIRTYONE_SIXTY,
TWO.BAL2 AS SIXTONE_NINTY,
THREE.BAL3 AS NINTYONE_ONETWENTY,
FOUR.BAL4 AS ONETWENTY_PLUS
--COUNT(ONE.BAL) AS THIRTYONE_SIXTY
FROM ARCHRG99 CHARG
--JOIN PPCARR99 INS
--ON INS.CARRIER_ID = CHARG.ACCT_PLAN1
JOIN (SELECT
CHRG0.PTID PATID0,
CHRG0.ACCT_PLAN1 PLAN0,
--CHRG1.ACCT_ID,
--INS0.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG0.CHRG_BAL AS MONEY)) AS BAL0
FROM ARCHRG99 CHRG0
--JOIN PPCARR99 INS0
--ON INS0.CARRIER_ID = CHRG0.ACCT_PLAN1
WHERE CHRG0.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHRG0.D_SERVICEFROM, GETDATE()) <=30)
GROUP BY CHRG0.ACCT_PLAN1,
CHRG0.PTID,
--INS0.CARRIER_ID,
CHRG0.CHRG_BAL) ZERO
ON ZERO.PATID0 = CHARG.PTID
JOIN (SELECT
CHRG1.PTID PATID1,
CHRG1.ACCT_PLAN1 PLAN1,
--INS1.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG1.CHRG_BAL AS MONEY)) AS BAL1
FROM ARCHRG99 CHRG1
--JOIN PPCARR99 INS1
--ON INS1.CARRIER_ID = CHRG1.ACCT_PLAN1
WHERE CHRG1.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHRG1.D_SERVICEFROM, GETDATE()) BETWEEN 31 AND 60)
GROUP BY CHRG1.ACCT_PLAN1,
CHRG1.PTID,
--INS1.CARRIER_ID,
CHRG1.CHRG_BAL) ONE
ON ONE.PATID1 = CHARG.PTID
JOIN (SELECT
CHRG2.PTID PATID2,
CHRG2.ACCT_PLAN1 PLAN2,
--INS2.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG2.CHRG_BAL AS MONEY)) AS BAL2
FROM ARCHRG99 CHRG2
--JOIN PPCARR99 INS2
--ON INS2.CARRIER_ID = CHRG2.ACCT_PLAN1
WHERE CHRG2.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHRG2.D_SERVICEFROM, GETDATE()) BETWEEN 61 AND 90)
GROUP BY CHRG2.ACCT_PLAN1,
CHRG2.PTID,
--INS2.CARRIER_ID,
CHRG2.CHRG_BAL) TWO
ON TWO.PATID2 = CHARG.PTID
JOIN (SELECT
CHRG3.PTID PATID3,
CHRG3.ACCT_PLAN1 PLAN3,
--INS3.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG3.CHRG_BAL AS MONEY)) AS BAL3
FROM ARCHRG99 CHRG3
--JOIN PPCARR99 INS3
--ON INS3.CARRIER_ID = CHRG3.ACCT_PLAN1
WHERE CHRG3.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHRG3.D_SERVICEFROM, GETDATE()) BETWEEN 91 AND 120)
GROUP BY CHRG3.ACCT_PLAN1,
CHRG3.PTID,
--INS3.CARRIER_ID,
CHRG3.CHRG_BAL) THREE
ON THREE.PATID3 = CHARG.PTID
JOIN (SELECT
CHRG4.PTID PATID4,
CHRG4.ACCT_PLAN1 PLAN4,
--INS4.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG4.CHRG_BAL AS MONEY)) AS BAL4
FROM ARCHRG99 CHRG4
--JOIN PPCARR99 INS4
--ON INS4.CARRIER_ID = CHRG4.ACCT_PLAN1
WHERE CHRG4.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHRG4.D_SERVICEFROM, GETDATE()) >=120)
GROUP BY CHRG4.ACCT_PLAN1,
CHRG4.PTID,
--INS4.CARRIER_ID,
CHRG4.CHRG_BAL) FOUR
ON FOUR.PATID4 = CHARG.PTID
/*
--WHERE CHARG.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHARG.D_SERVICEFROM, GETDATE()) >=0)
--GROUP BY INS.CARRIER_ID,
--ZERO.BAL0,
--ONE.BAL1,
--TWO.BAL2,
--THREE.BAL3,
--FOUR.BAL4
--CHARG.CHRG_BAL,
--ONE.BAL
*/
ORDER BY CHARG.ACCT_PLAN1
April 1, 2011 at 10:09 am
This is just to make it easier for us to read.
SELECT
--INS.CARRIER_ID
CHARG.ACCT_PLAN1
, ZERO.BAL0 AS ZERO_30
, ONE.BAL1 AS THIRTYONE_SIXTY
, TWO.BAL2 AS SIXTONE_NINTY
, THREE.BAL3 AS NINTYONE_ONETWENTY
, FOUR.BAL4 AS ONETWENTY_PLUS
--COUNT(ONE.BAL) AS THIRTYONE_SIXTY
FROM
ARCHRG99 CHARG --JOIN PPCARR99 INS
--ON INS.CARRIER_ID = CHARG.ACCT_PLAN1
JOIN (
SELECT
CHRG0.PTID PATID0
, CHRG0.ACCT_PLAN1 PLAN0
,
--CHRG1.ACCT_ID,
--INS0.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG0.CHRG_BAL AS MONEY)) AS BAL0
FROM
ARCHRG99 CHRG0
--JOIN PPCARR99 INS0
--ON INS0.CARRIER_ID = CHRG0.ACCT_PLAN1
WHERE
CHRG0.CHRG_BAL <> '0.00'
AND ( DATEDIFF(DD , CHRG0.D_SERVICEFROM , GETDATE()) <= 30 )
GROUP BY
CHRG0.ACCT_PLAN1
, CHRG0.PTID
,
--INS0.CARRIER_ID,
CHRG0.CHRG_BAL
) ZERO
ON ZERO.PATID0 = CHARG.PTID
JOIN (
SELECT
CHRG1.PTID PATID1
, CHRG1.ACCT_PLAN1 PLAN1
,
--INS1.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG1.CHRG_BAL AS MONEY)) AS BAL1
FROM
ARCHRG99 CHRG1
--JOIN PPCARR99 INS1
--ON INS1.CARRIER_ID = CHRG1.ACCT_PLAN1
WHERE
CHRG1.CHRG_BAL <> '0.00'
AND ( DATEDIFF(DD , CHRG1.D_SERVICEFROM , GETDATE()) BETWEEN 31
AND 60 )
GROUP BY
CHRG1.ACCT_PLAN1
, CHRG1.PTID
,
--INS1.CARRIER_ID,
CHRG1.CHRG_BAL
) ONE
ON ONE.PATID1 = CHARG.PTID
JOIN (
SELECT
CHRG2.PTID PATID2
, CHRG2.ACCT_PLAN1 PLAN2
,
--INS2.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG2.CHRG_BAL AS MONEY)) AS BAL2
FROM
ARCHRG99 CHRG2
--JOIN PPCARR99 INS2
--ON INS2.CARRIER_ID = CHRG2.ACCT_PLAN1
WHERE
CHRG2.CHRG_BAL <> '0.00'
AND ( DATEDIFF(DD , CHRG2.D_SERVICEFROM , GETDATE()) BETWEEN 61
AND 90 )
GROUP BY
CHRG2.ACCT_PLAN1
, CHRG2.PTID
,
--INS2.CARRIER_ID,
CHRG2.CHRG_BAL
) TWO
ON TWO.PATID2 = CHARG.PTID
JOIN (
SELECT
CHRG3.PTID PATID3
, CHRG3.ACCT_PLAN1 PLAN3
,
--INS3.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG3.CHRG_BAL AS MONEY)) AS BAL3
FROM
ARCHRG99 CHRG3
--JOIN PPCARR99 INS3
--ON INS3.CARRIER_ID = CHRG3.ACCT_PLAN1
WHERE
CHRG3.CHRG_BAL <> '0.00'
AND ( DATEDIFF(DD , CHRG3.D_SERVICEFROM , GETDATE()) BETWEEN 91
AND 120 )
GROUP BY
CHRG3.ACCT_PLAN1
, CHRG3.PTID
,
--INS3.CARRIER_ID,
CHRG3.CHRG_BAL
) THREE
ON THREE.PATID3 = CHARG.PTID
JOIN (
SELECT
CHRG4.PTID PATID4
, CHRG4.ACCT_PLAN1 PLAN4
,
--INS4.CARRIER_ID,
--COUNT(
SUM(CAST(CHRG4.CHRG_BAL AS MONEY)) AS BAL4
FROM
ARCHRG99 CHRG4
--JOIN PPCARR99 INS4
--ON INS4.CARRIER_ID = CHRG4.ACCT_PLAN1
WHERE
CHRG4.CHRG_BAL <> '0.00'
AND ( DATEDIFF(DD , CHRG4.D_SERVICEFROM , GETDATE()) >= 120 )
GROUP BY
CHRG4.ACCT_PLAN1
, CHRG4.PTID
,
--INS4.CARRIER_ID,
CHRG4.CHRG_BAL
) FOUR
ON FOUR.PATID4 = CHARG.PTID
/*
--WHERE CHARG.CHRG_BAL <> '0.00' AND (DATEDIFF(DD,CHARG.D_SERVICEFROM, GETDATE()) >=0)
--GROUP BY INS.CARRIER_ID,
--ZERO.BAL0,
--ONE.BAL1,
--TWO.BAL2,
--THREE.BAL3,
--FOUR.BAL4
--CHARG.CHRG_BAL,
--ONE.BAL
*/
ORDER BY
CHARG.ACCT_PLAN1
April 1, 2011 at 10:10 am
awesome. thanks
April 1, 2011 at 10:10 am
The way this is coded, as soon as 1 period does not return something, the whole row is filtered out in the join.
option 1 would be to use left joins instead of inners.
However you can do all that work in only 1 pass by simply by moving the derived table to the main select and do it all there.
April 1, 2011 at 12:31 pm
I knew it was something simple. The left join gave me data back. Thanks...
April 1, 2011 at 4:09 pm
That's great, but if you removed the inner joins altogether, this would run probably 5 times faster that it is at the moment...
April 1, 2011 at 4:10 pm
Ninja's_RGR'us (4/1/2011)
That's great, but if you removed the inner joins altogether, this would run probably 5 times faster that it is at the moment...
:blink: So would removing 2/3s of the data in the table, but that might get you slightly different results, too.... 😀
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 1, 2011 at 4:18 pm
I'm not kidding. He's using the same base table for all 4-5 selects. I don't see why I he can't do a single scan (or big range seek) of the base table once with the correct SUM(CASE WHEN Whatever and date check then col? ELSE 0 END) AS Period.
Am I missing something really obvious??
April 4, 2011 at 1:57 pm
Ninja's_RGR'us (4/1/2011)
I'm not kidding. He's using the same base table for all 4-5 selects. I don't see why I he can't do a single scan (or big range seek) of the base table once with the correct SUM(CASE WHEN Whatever and date check then col? ELSE 0 END) AS Period.Am I missing something really obvious??
I'd have to go through all the logic, but the where clausing groups the sums differently. My guess is a single pass with a CASE to control SUM grouping might work well enough with a pivot on the end of the pass, but those are subs of subselects and I didn't completely disassemble the existing code.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 2:20 pm
I'm 99.999999999999% sure it can be done. The missing decimal is that I don't have the data in front of me. It looks like denormalized table, so using coalesce should work on the grouping columns. If not then he's screwing using this code or refactoring.
Since those are my last options, try my query first! :w00t:
April 4, 2011 at 2:44 pm
Ninja's_RGR'us (4/4/2011)
I'm 99.999999999999% sure it can be done. The missing decimal is that I don't have the data in front of me. It looks like denormalized table, so using coalesce should work on the grouping columns. If not then he's screwing using this code or refactoring.Since those are my last options, try my query first! :w00t:
You're absolutely right. Had a few minutes and my curiousity was piqued, so I disassembled it a bit. It's a poor man's pivot.
This should work:
;WITH cte AS
(SELECT
ch.ACCT_PLAN1,
ch.PTID,
-- , ch.CHRG_BAL --See GROUP BY notes
CASE WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) <= 30
THEN 'col_0To30'
WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) BETWEEN 31 AND 60
THEN 'col_31To60'
WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) BETWEEN 61 AND 90
THEN 'col_61To90'
WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) BETWEEN 91 AND 120
THEN 'col_91To120'
ELSE 'Over120'
END AS RangeGroup,
SUM( ch.CHRG_BAL) AS PivotedBalSum
FROM
ARCHRG99 AS ch
GROUP BY
ACCT_PLAN1,
PTID,
CASE WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) <= 30
THEN 'col_0To30'
WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) BETWEEN 31 AND 60
THEN 'col_31To60'
WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) BETWEEN 61 AND 90
THEN 'col_61To90'
WHEN DATEDIFF( dd, ch.D_SERVICEFROM, GETDATE()) BETWEEN 91 AND 120
THEN 'col_91To120'
ELSE 'Over120'
END
-- There was a groupby on CHRG_BAL here. That doesn't make sense to me
-- off hand but if you need it uncomment the next line:
-- , CHRG_BAL
)
-- To see what that does, uncomment the below and highlight from it to the top:
-- SELECT * FROM cte
-- Now the data needs a pivot:
SELECT
ACCT_PLAN1,
[col_0To30], [col_31To60], [col_61To90], [col_91To120], [Over120]
FROM
cte AS c
PIVOT
(PivotedBalSum FOR RangeGroup IN ( [col_0To30], [col_31To60], [col_61To90], [col_91To120], [Over120]
) AS pvt
ORDER BY
ACCT_PLAN1
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2011 at 8:49 pm
Cool, I just hope he understands the code before using it in prod.
April 5, 2011 at 6:49 am
Thanks so much for all your guys help. I don't completely understand the pivot idea but I do have a coworker that does and we are going to look at it today... and we are only working with Test environment...I will let you guys know how it all turn out.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply