April 20, 2011 at 7:58 am
I need the following listed where ins company carrier ID is only listed once and the total for each is only listed once. I hope that makes sense...
SEE ATTACHMENTS
April 20, 2011 at 8:05 am
jrbass81 (4/20/2011)
I need the following listed where ins company carrier ID is only listed once and the total for each is only listed once. I hope that makes sense...SEE ATTACHMENTS
Read the links in Wayne's signature here. They will tell you how to post a question to maximise your chance of a good response.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 8:12 am
Lets try this way...below is my code and a few results....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
--CHARG.PTID,
INS.CARRIER_ID AS FINANCIAL_CLASS,
--CHARG.ACCT_PLAN1,
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,
--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 CHRG0.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG0.D_SERVICEFROM, GETDATE()) <=30) --AND CHRG0.CHRG_STATEMENT IN ('U','H','B')
GROUP BY --CHRG0.ACCT_PLAN1,
CHRG0.PTID,
INS0.CARRIER_ID,
CHRG0.CHRG_BAL) ZERO
ON ZERO.PATID0 = CHARG.PTID
LEFT 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 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.ACCT_PLAN1,
CHRG1.PTID,
INS1.CARRIER_ID,
CHRG1.CHRG_BAL) ONE
ON ONE.PATID1 = CHARG.PTID
LEFT 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 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.ACCT_PLAN1,
CHRG2.PTID,
INS2.CARRIER_ID,
CHRG2.CHRG_BAL) TWO
ON TWO.PATID2 = CHARG.PTID
LEFT 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 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.ACCT_PLAN1,
CHRG3.PTID,
INS3.CARRIER_ID,
CHRG3.CHRG_BAL) THREE
ON THREE.PATID3 = CHARG.PTID
LEFT JOIN (SELECT
CHRG4.PTID PATID4,
--CHRG4.ACCT_PLAN1 PLAN4,
INS4.CARRIER_ID INS4,
--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 CHRG4.CHRG_BAL NOT LIKE '-%'AND (DATEDIFF(DD,CHRG4.D_SERVICEFROM, GETDATE()) >=120)
--AND CHRG4.CHRG_STATEMENT IN ('U','H','B')
GROUP BY
--CHRG4.ACCT_PLAN1,
INS4.CARRIER_ID,
CHRG4.PTID,
CHRG4.CHRG_BAL) FOUR
ON FOUR.PATID4 = CHARG.PTID
--WHERE --ZERO.BAL0 IS NOT NULL
--OR ONE.BAL1 IS NOT NULL
--OR TWO.BAL2 IS NOT NULL
--OR THREE.BAL3 IS NOT NULL
---OR
--FOUR.BAL4 IS NOT NULL
--WHERE CHARG.CHRG_STATEMENT IN ('U','H','B')
WHERE CHARG.CHRG_BAL <> '0.00' --AND (DATEDIFF(DD,CHARG.D_SERVICEFROM, GETDATE()) >=0)
GROUP BY
--CHARG.ACCT_PLAN1,
INS.CARRIER_ID,
--CHARG.PTID,
ZERO.BAL0,
ONE.BAL1,
TWO.BAL2,
THREE.BAL3,
FOUR.BAL4
--CHARG.CHRG_BAL,
--ONE.BAL
ORDER BY INS.CARRIER_ID
FINANCIAL_CLASS0-3031-6061-9091-120120+
AETNA 112.00NULLNULLNULLNULL
AETNA 1 111.00NULLNULLNULLNULL
AETNA 6 142.00NULLNULLNULLNULL
AETNA 6 434.00NULLNULLNULLNULL
AETNA MCAR 75.00NULLNULLNULLNULL
AMERCIAS 75.00NULLNULLNULLNULL
AMERCIAS 1353.00NULLNULLNULLNULL
ANTHEM 142.00NULLNULLNULLNULL
BC BS NC 8.00NULLNULLNULLNULL
BC BS NC 10.00NULLNULLNULLNULL
BC BS NC 23.00NULLNULLNULLNULL
BC BS NC 111.00NULLNULLNULLNULL
BC BS NC 117.00NULLNULLNULLNULL
BC BS NC 136.00NULLNULLNULLNULL
BC BS NC 142.00NULLNULLNULLNULL
BC BS NC 168.00NULLNULLNULLNULL
BC BS NC 176.00NULLNULLNULLNULL
BC BS NC 200.00NULLNULLNULLNULL
BC BS NC 222.00NULLNULLNULLNULL
BC BS NC 256.00NULLNULLNULLNULL
April 20, 2011 at 8:24 am
jrbass81 (4/20/2011)
Lets try this way...below is my code and a few results....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...
Please could you remove the commented-out code from your query unless it's useful comments, then post it into a code window? This is found by clicking on 'IFCode', then scroll down to 'Specified SQL Code'. It would be helpful if you could format it nicely too. Why? With no DML/DDL to work against, all we've got is your code and an unformatted output set.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 8:45 am
I reformated the code for you for readability but I have a couple questions. What do you mean when you say you only want the ins companies. What problem exactly are you encoutering when running the query?
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+'
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)
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)
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)
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)
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'
GROUP BY
INS.CARRIER_ID,
ZERO.BAL0,
ONE.BAL1,
TWO.BAL2,
THREE.BAL3,
FOUR.BAL4
ORDER BY
INS.CARRIER_ID
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 8:50 am
Dan I have reposted the code and results but I will answer your question
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
Does that make any sence to you?
Please respond to newest post...
April 20, 2011 at 9:04 am
Let's have a look at a single one of those date-range specific queries:
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)
GROUP BY
CHRG0.PTID,
INS0.CARRIER_ID,
CHRG0.CHRG_BAL
At a guess, I'd say you don't want CHRG_BAL in the GROUP BY.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 20, 2011 at 9:07 am
Thanks to all that replied I have figured it out with some clues from Dan...thanks guys and gals...
April 20, 2011 at 9:15 am
You might want to rewrite it to make it more readable and five times faster:
SELECT
PATID,
CARRIER_ID,
DateRange,
[BAL] = SUM(BAL)
FROM (
SELECT
CHRG.PTID AS PATID,
INS.CARRIER_ID,
[DateRange] = CASE
WHEN (DATEDIFF(DD,CHRG.D_SERVICEFROM, GETDATE()) <=30) THEN '0-30'
WHEN (DATEDIFF(DD,CHRG.D_SERVICEFROM, GETDATE()) BETWEEN 31 AND 60) THEN '31-60'
WHEN (DATEDIFF(DD,CHRG.D_SERVICEFROM, GETDATE()) BETWEEN 61 AND 90) THEN '61-90'
WHEN (DATEDIFF(DD,CHRG.D_SERVICEFROM, GETDATE()) BETWEEN 91 AND 120) THEN '91-120'
ELSE '120+'
END,
SUM(CAST(CHRG.CHRG_BAL AS MONEY)) AS BAL
FROM ARCHRG99 CHRG
JOIN PPCARR99 INS ON INS.CARRIER_ID = CHRG.ACCT_PLAN1
WHERE CHRG.CHRG_BAL <> '0.00'
AND CHRG.CHRG_BAL NOT LIKE '-%'
GROUP BY
CHRG.PTID,
INS.CARRIER_ID
) d
GROUP BY
PATID,
CARRIER_ID,
[DateRange]
ORDER BY
PATID,
CARRIER_ID,
[DateRange]
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply