October 27, 2011 at 11:02 am
below is part of code that includes many subselects, sums, etc. here's the problem I have,
in the CHRG0.CHRG_AMT column I have a reverse charge of -289.00 with a CHRG0.CHRG_BAL (balance) of 0.00 and a CHRG0.CHRG_AMT of 289 with a balance of 289. the -289 should cancel out the positive 289 but because the balance of the negative is 0.00 my report doesn't see it as canceling it out. how do I change my Case statement to do so or am I going about it all wrong?
SELECT
CHRG0.PTID PATID0,
CASE
when CHRG0.ACCT_PLAN1 = '' then ''
else CHRG0.ACCT_PLAN1 END AS INS0,
CASE
when CHRG0.N_CHRG_SEQ like '-%' and CHRG0.CHRG_AMT like '-' and CHRG0.CHRG_BAL = '0.00'
then CAST(ISNULL(CHRG0.CHRG_AMT, '0') AS money) else SUM(CAST(ISNULL(CHRG0.CHRG_BAL, '0') AS MONEY)) end AS BAL0
FROM ARCHRG99 CHRG0
WHERE CHRG0.CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,CHRG0.D_POSTING, GETDATE()) <= 30)
and CHRG0.D_POSTING >= @sdate
AND CHRG0.D_POSTING <= @edate
and CHRG0.PRACTICE_ID = @prac
GROUP BY
CHRG0.PTID,
CHRG0.ACCT_PLAN1,
CHRG0.N_CHRG_SEQ,
CHRG0.CHRG_AMT,
CHRG0.CHRG_BAL
below is my entire code if it helps.
declare @sdate datetime,
@edate datetime,
@prac char(10)
--as
set @sdate = '11/01/2011'
set @edate = '12/31/2011'
set @prac = 'CC2'
select
INS,
sum([0-30]) AS [0-30],
sum([31-60]) AS [31-60],
sum([61-90]) AS [61-90],
sum([91-120]) AS [91-120],
sum([120+]) AS [120+],
sum([Total]) as [Total]
from
(
SELECT
CHARG.PTID,
CASE
when CHARG.ACCT_PLAN1 = '' then 'UNKNOWN'
else CHARG.ACCT_PLAN1 END INS,
SUM(ISNULL(ZERO.BAL0, '0')) /COUNT(CHARG.ACCT_ID) AS '0-30',
SUM(ISNULL(ONE.BAL1, '0'))/COUNT(CHARG.ACCT_ID)AS '31-60',
SUM(ISNULL(TWO.BAL2, '0'))/COUNT(CHARG.ACCT_ID)AS '61-90',
SUM(ISNULL(THREE.BAL3, '0'))/COUNT(CHARG.ACCT_ID)AS '91-120',
SUM(ISNULL(FOUR.BAL4, '0'))/COUNT(CHARG.ACCT_ID)AS '120+',
SUM((ISNULL(ZERO.BAL0, '0'))+
(ISNULL(ONE.BAL1, '0'))+
(ISNULL(TWO.BAL2, '0')) +
(ISNULL(THREE.BAL3, '0'))+
(ISNULL(FOUR.BAL4, '0')))/COUNT(CHARG.ACCT_ID) AS 'TOTAL'
FROM ARCHRG99 CHARG
LEFT JOIN (SELECT
CHRG0.PTID PATID0,
CASE
when CHRG0.ACCT_PLAN1 = '' then ''
else CHRG0.ACCT_PLAN1 END AS INS0,
CASE
when CHRG0.N_CHRG_SEQ like '-%' and CHRG0.CHRG_AMT like '-' and CHRG0.CHRG_BAL = '0.00'
then CAST(ISNULL(CHRG0.CHRG_AMT, '0') AS money) else SUM(CAST(ISNULL(CHRG0.CHRG_BAL, '0') AS MONEY)) end AS BAL0
FROM ARCHRG99 CHRG0
WHERE CHRG0.CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,CHRG0.D_POSTING, GETDATE()) <= 30)
and CHRG0.D_POSTING >= @sdate
AND CHRG0.D_POSTING <= @edate
and CHRG0.PRACTICE_ID = @prac
GROUP BY
CHRG0.PTID,
CHRG0.ACCT_PLAN1,
CHRG0.N_CHRG_SEQ,
CHRG0.CHRG_AMT,
CHRG0.CHRG_BAL
) ZERO
ON ZERO.PATID0 = CHARG.PTID
and ZERO.INS0 = RTRIM(CHARG.ACCT_PLAN1)
LEFT JOIN (SELECT
CHRG1.PTID PATID1,
CASE
when CHRG1.ACCT_PLAN1 = '' then ''
else CHRG1.ACCT_PLAN1 END AS INS1,
CASE
when CHRG1.N_CHRG_SEQ like '-%' and CHRG1.CHRG_AMT like '-' and CHRG1.CHRG_BAL = '0.00'
then CAST(ISNULL(CHRG1.CHRG_AMT, '0') AS money) else SUM(CAST(ISNULL(CHRG1.CHRG_BAL, '0') AS MONEY)) end AS BAL1
FROM ARCHRG99 CHRG1
WHERE CHRG1.CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,CHRG1.D_POSTING, GETDATE()) between 31 AND 60)
and CHRG1.D_POSTING >= @sdate
AND CHRG1.D_POSTING <= @edate
and CHRG1.PRACTICE_ID = @prac
GROUP BY
CHRG1.PTID,
CHRG1.ACCT_PLAN1,
CHRG1.N_CHRG_SEQ,
CHRG1.CHRG_AMT,
CHRG1.CHRG_BAL
) ONE
ON ONE.PATID1 = CHARG.PTID
and ONE.INS1 = RTRIM(CHARG.ACCT_PLAN1)
LEFT JOIN (SELECT
CHRG2.PTID PATID2,
CASE
when CHRG2.ACCT_PLAN1 = '' then ''
else CHRG2.ACCT_PLAN1 END AS INS2,
CASE
when CHRG2.N_CHRG_SEQ like '-%' and CHRG2.CHRG_AMT like '-' and CHRG2.CHRG_BAL = '0.00'
then CAST(ISNULL(CHRG2.CHRG_AMT, '0') AS money) else SUM(CAST(ISNULL(CHRG2.CHRG_BAL, '0') AS MONEY)) end AS BAL2
FROM ARCHRG99 CHRG2
WHERE CHRG2.CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,CHRG2.D_POSTING, GETDATE()) BETWEEN 61 AND 90)
and CHRG2.D_POSTING >= @sdate
AND CHRG2.D_POSTING <= @edate
and CHRG2.PRACTICE_ID = @prac
GROUP BY
CHRG2.PTID,
CHRG2.ACCT_PLAN1,
CHRG2.N_CHRG_SEQ,
CHRG2.CHRG_AMT,
CHRG2.CHRG_BAL
) TWO
ON TWO.PATID2 = CHARG.PTID
and TWO.INS2 = RTRIM(CHARG.ACCT_PLAN1)
LEFT JOIN (SELECT
CHRG3.PTID PATID3,
CASE
when CHRG3.ACCT_PLAN1 = '' then ''
else CHRG3.ACCT_PLAN1 END AS INS3,
CASE
when CHRG3.N_CHRG_SEQ like '-%' and CHRG3.CHRG_AMT like '-' and CHRG3.CHRG_BAL = '0.00'
then CAST(ISNULL(CHRG3.CHRG_AMT, '0') AS money) else SUM(CAST(ISNULL(CHRG3.CHRG_BAL, '0') AS MONEY)) end AS BAL3--/COUNT(CHRG3.ACCT_ID) AS BAL3
FROM ARCHRG99 CHRG3
WHERE CHRG3.CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,CHRG3.D_POSTING, GETDATE()) BETWEEN 91 AND 120)
and CHRG3.D_POSTING >= @sdate
AND CHRG3.D_POSTING <= @edate
and CHRG3.PRACTICE_ID = @prac
GROUP BY
CHRG3.PTID,
CHRG3.ACCT_PLAN1,
CHRG3.N_CHRG_SEQ,
CHRG3.CHRG_AMT,
CHRG3.CHRG_BAL
) THREE
ON THREE.PATID3 = CHARG.PTID
and THREE.INS3 = RTRIM(CHARG.ACCT_PLAN1)
LEFT JOIN (SELECT
CHRG4.PTID PATID4,
CASE
when CHRG4.ACCT_PLAN1 = '' then ''
else CHRG4.ACCT_PLAN1 END AS INS4,
CASE
when CHRG4.N_CHRG_SEQ like '-%' and CHRG4.CHRG_AMT like '-' and CHRG4.CHRG_BAL = '0.00'
then CAST(ISNULL(CHRG4.CHRG_AMT, '0') AS money) else SUM(CAST(ISNULL(CHRG4.CHRG_BAL, '0') AS MONEY)) end AS BAL4--/COUNT(CHRG4.ACCT_ID) AS BAL4
FROM ARCHRG99 CHRG4
WHERE CHRG4.CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,CHRG4.D_POSTING, GETDATE()) >=120)
and CHRG4.D_POSTING >= @sdate
AND CHRG4.D_POSTING <= @edate
and CHRG4.PRACTICE_ID = @prac
GROUP BY
CHRG4.PTID,
CHRG4.ACCT_PLAN1,
CHRG4.N_CHRG_SEQ,
CHRG4.CHRG_AMT,
CHRG4.CHRG_BAL
) FOUR
ON FOUR.PATID4 = CHARG.PTID
and FOUR.INS4 = RTRIM(CHARG.ACCT_PLAN1)
WHERE CHARG.CHRG_BAL <> '0.00'
and CHARG.D_POSTING >= @sdate
AND CHARG.D_POSTING <= @edate
and CHARG.PRACTICE_ID = @prac
GROUP BY
CHARG.ACCT_PLAN1,
CHARG.PTID
) RPT_TBL
group by
RPT_TBL.INS
order by
RPT_TBL.INS
October 28, 2011 at 2:59 am
bass8117 (10/27/2011)
below is part of code that includes many subselects, sums, etc. here's the problem I have,in the CHRG0.CHRG_AMT column I have a reverse charge of -289.00 with a CHRG0.CHRG_BAL (balance) of 0.00 and a CHRG0.CHRG_AMT of 289 with a balance of 289. the -289 should cancel out the positive 289 but because the balance of the negative is 0.00 my report doesn't see it as canceling it out. how do I change my Case statement to do so or am I going about it all wrong?
...
There's a lot wrong with the code - I'd recommend you start again from scratch. Bear in mind that a rewrite never takes anything like as long as the first write. Looking at one single block:
SELECT
PATID0 = PTID,
INS0 = CASE when ACCT_PLAN1 = '' then '' else ACCT_PLAN1 END,
BAL0 = CASE
when N_CHRG_SEQ like '-%' and CHRG_AMT like '-' and CHRG_BAL = '0.00' then CAST(ISNULL(CHRG_AMT, '0') AS money)
else SUM(CAST(ISNULL(CHRG_BAL, '0') AS MONEY)) end
FROM ARCHRG99
WHERE CHRG_BAL <> '0.00'
AND (DATEDIFF(DD,D_POSTING, GETDATE()) <= 30)
and D_POSTING >= @sdate
AND D_POSTING <= @edate
and PRACTICE_ID = @prac
GROUP BY PTID, ACCT_PLAN1, N_CHRG_SEQ, CHRG_AMT, CHRG_BAL
1. CASE when ACCT_PLAN1 = '' then '' else ACCT_PLAN1 END
The CASE doesn't do anything. Replace it with INS0 = ACCT_PLAN1
2. Your WHERE clause has condition CHRG_BAL <> '0.00', but the CASE which determines the value of BAL0 has the condition CHRG_BAL = '0.00', so this choice will never be picked.
3. SUM(CAST(ISNULL(CHRG_BAL, '0') AS MONEY)) : CHRG_BAL is in the GROUP BY.
4. CHRG_AMT like '-' : what do you expect this to do?
5. CHRG_BAL = '0.00' : What datatype is CHRG_BAL?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 28, 2011 at 7:31 am
bass8117 (10/27/2011)
below is part of code that includes many subselects, sums, etc. here's the problem I have,in the CHRG0.CHRG_AMT column I have a reverse charge of -289.00 with a CHRG0.CHRG_BAL (balance) of 0.00 and a CHRG0.CHRG_AMT of 289 with a balance of 289. the -289 should cancel out the positive 289 but because the balance of the negative is 0.00 my report doesn't see it as canceling it out. how do I change my Case statement to do so or am I going about it all wrong?
CHRG_BAL looks like a running total. You don't sum running totals. If you want the -289 to cancel out the 289 use the CHRG_AMT.
Actually, looking closer it appears that you're not including the -289 in your report because the balance is zero. It can't cancel out the +289 if it's not in the report. What you probably want is to filter out based on a HAVING clause rather than the WHERE clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 28, 2011 at 10:41 am
CELKO (10/27/2011)
Ty here is no CASE statement in SQL; we have a CASE expression. The minute you make that mistake, we know that you will be making a lot of fundamental errors AND that your design will probably be wrong. Hey, you are ahead of the game; you know you have problems!...
Joe,
If I could prove that CASE is not an expression, would that imply you're making a lot of fundamental errors due to a false declaration of CASE, too?
Simple version: As per BOL, CASE is a function using expressions. 😀
A more detailed explanation: If memory serves right, an expression is either a simple expression or two or more simple expressions joined by operator(s). Since I can't remember WHEN and THEN being valid operators, I would conclude CASE cannot be referred to as an expression either.
But since one of the ways to write a CASE function is similar to the syntax and the logic used in an IF .. THEN .. ELSE statement, I think this might be one of the reasons for CASE being referred to as a statement rather than a function.
If my memory failed (wouldn't be the first time...), please correct me. Meanwhile I tend to disagree that the simple reference of CASE being a statement is an indicator of fundamental errors and probably wrong design.
October 28, 2011 at 1:02 pm
"Never, never use MONEY data types; their math is wrong!"
Bit off topic but I wasn't aware of this and have used MONEY columns occasionally. Could you elaborate and should I be using DECIMAL(18, 4) instead?
October 29, 2011 at 9:35 am
CELKO (10/28/2011)
1) It is proprietary, so porting it is a pain. It is one of the many"Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?
true, but not important
2) Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column. Ask your accounting department how many decimal places you need by law.
True, and extremely important.4 is noat always the right number for scale.
...
4) The MONEY data type has rounding errors.
Using more than one operation (multiplication or division) on money
columns will produce severe rounding errors. A simple way to visualize money arithmetic is to place a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
It can also be rewritten using a little common sense as
Amount = (Portion*gross_amt) / total_amt
to minimize rounding errors.
....
@money_result = 13525.09 -- incorrect
@float_result = 13525.0885 -- incorrect
@all_floats = 13530.5038673171 -- correct, with a -5.42 error
well, the all_floats number is not correct, 13530.5038670000 is closer than 13530.5038673171. But even if one changes the order of operations as mentioned above the money type will deliver a slightly less accurate result (13530.5038) than float will. Working in whole numbers (multiply Portion and gross amt by 100 and total_amt by 10000 before doing any arithmetic) will allow the Money type to do slightly better (the result will be 15350.5039, so the error is reduced by about a factor of 5; this is not a technique recommended for much use, as it's far too easy to get it wrong if the calculations are at all complex - it's much too easy to go astray when doing that sort of thing) but it's still less accurate than using floats. The conversion error for base-two floats is sometimes unacceptably high (that extra .0000003171 is the result of conversion errors) because there is, for example, no exact base-two float representation of 0.1; when is the SQL standard going to get into the modern world and provide for the base-ten floats specified in 2008 revision of the floating point standard so that we can have the convenience of floats and their reduced rounding error compared to decimal(p,s) without the extremely incovenient inaccuracy of representation of our most commonly used number system and the conversion errors that this representation failure engenders?
Tom
October 30, 2011 at 7:16 pm
CELKO (10/29/2011)
The reason MONEY existed in the original Sybase product was to mimic PICTURE display formats in COBOL. It was assumed that the data, computational and presentation layers were a monolithic whole. So if you don't have PICTURE clause, how do you put dollar signs and commas and decimal points in the output? Lots of string handling, version of CONVERT() for currency or a special proprietary data type! They picked the last one in that list.
Yes, we both agree that the MONEY datatype is a complete nonsense, and teh motivation for introducing it was based on a seriously borken architecture.
But, since you shout so often about adhering to standards, I'm going to press yo again on the question I asked before:
When is the SQL standard going to get into the modern world and provide for the base-ten floats specified in 2008 revision of the floating point standard so that we can have the convenience of floats and their reduced rounding error compared to decimal(p,s) without the extremely incovenient inaccuracy of representation of our most commonly used number system and the conversion errors that this representation failure engenders?
For avoidance of any possible ambiguity, the standard I am referring to is IEEE 754-2008.
Tom
October 31, 2011 at 9:48 am
Thanks for all of your advise. I fixed the issue last Friday morning but didn't have time to get on here to let you all know.
I know my code may not be the prettiest, it is down right ugly, but it works and gets me the results I need for the dept that is asking for the report. Thanks again for the feedback.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply