April 20, 2011 at 8:45 am
Thanks ChrisM@home for your advice...lets see if this will help out and get a better response...Sorry for the other posts...
I need to get the results to only list on ins company and there total and not 5 BCBSNC i just need one and the total for all BCBSNC...maybe this will be better then clicking on attachments...sorry guys for original post...
SELECT
INS.CARRIER_ID AS FINANCIAL_CLASS,
ZERO.BAL0 AS '0-30',
ONE.BAL1 AS '31-60',
TWO.BAL2 AS '61-90',
THREE.BAL3 AS '91-120',
FOUR.BAL4 AS '120+'
--SUM((ZERO.BAL0) + (ONE.BAL1) + (TWO.BAL2) + (THREE.BAL3) + (FOUR.BAL4)) AS TOTAL
--COUNT(ONE.BAL) AS THIRTYONE_SIXTY
FROM ARCHRG99 CHARG
JOIN PPCARR99 INS
ON INS.CARRIER_ID = CHARG.ACCT_PLAN1
left JOIN (SELECT
CHRG0.PTID PATID0,
INS0.CARRIER_ID,
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 CHRG0.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG0.D_SERVICEFROM, GETDATE()) <=30) --AND CHRG0.CHRG_STATEMENT IN ('U','H','B')
GROUP BY
CHRG0.PTID,
INS0.CARRIER_ID,
CHRG0.CHRG_BAL) ZERO
ON ZERO.PATID0 = CHARG.PTID
LEFT JOIN (SELECT
CHRG1.PTID PATID1,
INS1.CARRIER_ID,
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 CHRG1.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG1.D_SERVICEFROM, GETDATE()) BETWEEN 31 AND 60) --AND CHRG1.CHRG_STATEMENT IN ('U','H','B')
GROUP BY
CHRG1.PTID,
INS1.CARRIER_ID,
CHRG1.CHRG_BAL) ONE
ON ONE.PATID1 = CHARG.PTID
LEFT JOIN (SELECT
CHRG2.PTID PATID2,
INS2.CARRIER_ID,
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 CHRG2.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG2.D_SERVICEFROM, GETDATE()) BETWEEN 61 AND 90) --AND CHRG2.CHRG_STATEMENT IN ('U','H','B')
GROUP BY
CHRG2.PTID,
INS2.CARRIER_ID,
CHRG2.CHRG_BAL) TWO
ON TWO.PATID2 = CHARG.PTID
LEFT JOIN (SELECT
CHRG3.PTID PATID3,
INS3.CARRIER_ID,
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 CHRG3.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG3.D_SERVICEFROM, GETDATE()) BETWEEN 91 AND 120) --AND CHRG3.CHRG_STATEMENT IN ('U','H','B')
GROUP BY
CHRG3.PTID,
INS3.CARRIER_ID,
CHRG3.CHRG_BAL) THREE
ON THREE.PATID3 = CHARG.PTID
LEFT JOIN (SELECT
CHRG4.PTID PATID4,
INS4.CARRIER_ID INS4,
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 CHRG4.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG4.D_SERVICEFROM, GETDATE()) >=120)
GROUP BY
INS4.CARRIER_ID,
CHRG4.PTID,
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
ORDER BY INS.CARRIER_ID
RESULTS BELOW
FINANCIAL_CLASS 0-30 31-60 61-90 91-120 120+
AETNA 112.00 NULL NULL NULL NULL
AETNA 1 111.00 NULL NULL NULL NULL
AETNA 6 142.00 NULL NULL NULL NULL
AETNA 6 434.00 NULL NULL NULL NULL
AETNA MCAR 75.00 NULL NULL NULL NULL
AMERCIAS 75.00 NULL NULL NULL NULL
AMERCIAS 1353.00 NULL NULL NULL NULL
ANTHEM 142.00 NULL NULL NULL NULL
BC BS NC 8.00 NULL NULL NULL NULL
BC BS NC 10.00 NULL NULL NULL NULL
BC BS NC 23.00 NULL NULL NULL NULL
BC BS NC 111.00 NULL NULL NULL NULL
BC BS NC 117.00 NULL NULL NULL NULL
BC BS NC 136.00 NULL NULL NULL NULL
BC BS NC 142.00 NULL NULL NULL NULL
BC BS NC 168.00 NULL NULL NULL NULL
BC BS NC 176.00 NULL NULL NULL NULL
BC BS NC 200.00 NULL NULL NULL NULL
BC BS NC 222.00 NULL NULL NULL NULL
BC BS NC 256.00 NULL NULL NULL NULL
April 20, 2011 at 8:50 am
I want it to look something like this
FIN Class 0-30 31-60 61-90 91-120 120+
ATNA $300.00 $0 NULL NULL NULL
BCBSNC $400.00 $102.00 NULL NULL NULL
April 20, 2011 at 8:53 am
in all of your subqueries you are summing by CHRG1.CHRG_BAL and you include CHRG1.CHRG_BAL in your group by cluase. The column you are summing should not be in the group by
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 20, 2011 at 9:03 am
I have changed it in my code and still getting the same results...
April 20, 2011 at 9:04 am
I GOT IT Thanks...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply