March 2, 2005 at 10:05 am
Hi,
I have data that has account balances that repeat like this:
| ID | DenialDate | DeniedCharge | BilledCharge | Account | DenialCode |
| 130587 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130588 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130589 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130590 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130591 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130592 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130593 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130594 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130595 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130596 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
| 130597 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 |
I only want to sum the balance (DeniedCharge) once in my total. How do I do a nested query to sum only only one of the balances? This is what I tried:
DeniedCharges =Sum((SELECT DS1.DeniedCharge FROM DenialStore DS1 GROUP BY DS1.DeniedCharge,DS1.Account,DS1.DenialDate,DS1.DenialCode))
But I get an error. I also tried:
DeniedCharges =Sum((SELECT MAX(DS1.DeniedCharge) FROM DenialStore DS1 GROUP BY DS1.Account,DS1.DenialDate,DS1.DenialCode))
The nested statement works alone but when I add the outside Sum it doesn't work.
Thank you!
March 2, 2005 at 10:55 am
You need a virtual table that returns the ID's you want to use, then join this to the main table to exclude the dupes in the Sum():
Select Sum(DeniedCharge)
From DenialStore As DS
Inner Join
(
Select Min(ID) As UseThisID
From DenialStore
GROUP BY Account, DenialDate, DenialCode
) vt
On vt.UseThisID = DS.ID
March 2, 2005 at 11:33 am
I agree that a Derived table is your best way. I would probably use:
SELECT DISTINCT DeniedCharge, Account, DenialDate, DenialCode
FROM DenialStore
This should return the equivalent of one, and only one, of the rows in your sample. Using that as the source of your information leads me to something like this:
Select SUM(DeniedCharge) as TotalDeniedCharges
FROM ( SELECT DISTINCT DeniedCharge, Account, DenialDate, DenialCode
FROM DenialStore ) DistinctDenied
March 2, 2005 at 12:02 pm
Thanks. Almost there. In the main statement I group by DenialDate, Location and DenialCode. How do I alter the nested SUM statement so that it will group on the same fields? Here is some more sample data:
| ID | DenialDate | DeniedCharge | BilledCharge | Account | DenialCode | Location |
| 130587 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 | INF CTR |
| 130588 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 | INF CTR |
| 130589 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 | INF CTR |
| 130590 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 | INF CTR |
| 130591 | 21-Dec-04 | ($296.51) | $868.07 | X99999999 | D06 | INF CTR |
| 130592 | 21-Dec-04 | ($1,400.00) | $1,500.00 | X99999977 | D08 | CARD |
| 130593 | 21-Dec-04 | ($1,400.00) | $1,500.00 | X99999977 | D08 | CARD |
| 130594 | 21-Dec-04 | ($1,400.00) | $1,500.00 | X99999977 | D08 | CARD |
| 130595 | 21-Dec-04 | ($1,400.00) | $1,500.00 | X99999977 | D08 | CARD |
| 130596 | 21-Dec-04 | ($200.00) | $215.00 | X99999966 | D08 | CARD |
| 130597 | 21-Dec-04 | ($200.00) | $215.00 | X99999966 | D08 | CARD |
Right now in my results the total sum of DeniedCharge is repeated for all groups. Right now it is like this:
| Location | DenialDate | DenialCode | DeniedCharge |
| INF CTR | 21-Dec-04 | D06 | ($1,896.51) |
| CARD | 21-Dec-04 | D08 | ($1,896.51) |
I'm trying to get this:
| Location | DenialDate | DenialCode | DeniedCharge |
| INF CTR | 21-Dec-04 | D06 | ($296.51) |
| CARD | 21-Dec-04 | D08 | ($1,600.00) |
Thanks so much for your help.
March 2, 2005 at 12:09 pm
I didn't read the whole post... but from your last question :
Select DenialDate, Location, DenialCode, SUM(DeniedCharge) as SumDeniedCharge
GROUP BY DenialDate, Location,DenialCode
March 2, 2005 at 12:37 pm
Thanks Remi. My challenge is that I need to get the DenialCharge only once for each Account,DenialDate,Location,DenialCode group (so just the values $296.51, $1,400.00, and $200.00) and then sum them grouped by Location,DenialDate, and Denial code) to get these results.
| Location | DenialDate | DenialCode | DeniedCharge |
| INF CTR | 21-Dec-04 | D06 | ($296.51) |
| CARD | 21-Dec-04 | D08 | ($1,600.00) |
I don't want to add every DenialCharge, just the first one for each Account,DenialDate,Location,DenialCode group.
March 2, 2005 at 1:14 pm
You mean this?
Select Account, DenialDate, Location, DenialCode, SUM(DeniedCharge) as SumDeniedCharge
GROUP BY Account, DenialDate, Location, DenialCode
Or this?
Select A.Account, A.DenialDate, A.Location, A.DenialCode, SUM(A.DeniedCharge) as SumDeniedCharge
(Select Distinct Account, DenialDate, Location, DenialCode, DeniedCharge) A
GROUP BY A.Account, A.DenialDate, A.Location, A.DenialCode
March 2, 2005 at 3:09 pm
The second statement works but I need to had a HAVING (or WHERE) clause so that it can relate back to the main statement.
This works alone:
SELECT SUM(A.DeniedCharge)
FROM ( SELECT DISTINCT Account, DenialDate, Location, DenialCode, DeniedCharge
FROM DenialStore ) A
GROUP BY A.DenialDate,A.Location, A.DenialCode
But when I use it in my main statement and add a HAVING clause, it returns a null value:
DeniedCharges = SELECT SUM(A.DeniedCharge)
FROM ( SELECT DISTINCT Account, DenialDate, Location, DenialCode, DeniedCharge
FROM DenialStore ) A
GROUP BY A.DenialDate,A.Location, A.DenialCode
HAVING A.DenialDate=DS.DenialDate AND A.Location=L.[CFT Location] AND A.DenialCode=DR.RemitCode
My main statement is grouped like this:
GROUP BY L.[CFT Location]
,DS.DenialDate
,DR.RemitCode
March 2, 2005 at 4:01 pm
Can you post the whole select?
I think you're gonna have to use my select as a derived table and use that table to join to your main select to give the output you need.
March 2, 2005 at 4:06 pm
I really appreciate your help!
Here is a stripped down version (and below that is the entire stored procedure)
SELECT L.[CFT Location]
, DateMonth=CAST(LEFT(DATENAME(m,DS.DenialDate),3)AS varchar) + '-' + CAST((DATEPART(yy,DS.DenialDate)) AS varchar)
, DS.DenialDate
, DR.RemitCode
, DR.DenialDesc
, DeniedCharges = ( SELECT SUM(A.DeniedCharge)
FROM ( SELECT DISTINCT Account, DenialDate, Location, DenialCode, DeniedCharge
FROM DenialStore
) A
GROUP BY A.DenialDate,A.Location, A.DenialCode
HAVING A.DenialDate=DS.DenialDate AND A.Location=L.[CFT Location] AND A.DenialCode=DR.RemitCode
)
, Charges = CAST (Sum(DS.BilledCharge) AS money(10,2))
, [Count] = Count(DS.Account)
INTO #Denial
FROM brenna.DenialStore DS
LEFT JOIN brenna.Location L ON DS.Location = L.[MNE Location]
LEFT JOIN brenna.DenialRemit DR ON DS.DenialCode = DR.RemitCode
WHERE DS.DenialDate >= CONVERT(nvarchar,@RptStartDte,101) AND DS.DenialDate <= CONVERT (nvarchar,@RptEndDte,101)
GROUP BY L.[CFT Location]
,DS.DenialDate
,DR.RemitCode
,DR.DenialDesc
The whole deal:
CREATE PROCEDURE s_DenialMonthSummary
@RptBeginDte smalldatetime,
@RptFinishDte smalldatetime
AS
Declare @RptStartDte smalldatetime
Declare @RptEndDte smalldatetime
SELECT @RptStartDte = @RptBeginDte
SELECT @RptEndDte = @RptFinishDte
IF EXISTS (SELECT id FROM sysobjects WHERE id = object_ID ('tempdb..#Denial'))
DROP TABLE #Denial
SELECT L.[CFT Location]
, DateMonth=CAST(LEFT(DATENAME(m,DS.DenialDate),3)AS varchar) + '-' + CAST((DATEPART(yy,DS.DenialDate)) AS varchar)
, DS.DenialDate
, DR.RemitCode
, DR.DenialDesc
, DeniedCharges = ( SELECT SUM(A.DeniedCharge)
FROM ( SELECT DISTINCT Account, DenialDate, Location, DenialCode, DeniedCharge
FROM DenialStore
) A
GROUP BY A.DenialDate,A.Location, A.DenialCode
HAVING A.DenialDate=DS.DenialDate AND A.Location=L.[CFT Location] AND A.DenialCode=DR.RemitCode
)
, Charges = CAST (Sum(DS.BilledCharge) AS money(10,2))
, [Count] = Count(DS.Account)
INTO #Denial
FROM brenna.DenialStore DS
LEFT JOIN brenna.Location L ON DS.Location = L.[MNE Location]
LEFT JOIN brenna.DenialRemit DR ON DS.DenialCode = DR.RemitCode
WHERE DS.DenialDate >= CONVERT(nvarchar,@RptStartDte,101) AND DS.DenialDate <= CONVERT (nvarchar,@RptEndDte,101)
GROUP BY L.[CFT Location]
,DS.DenialDate
,DR.RemitCode
,DR.DenialDesc
SELECT * FROM #Denial
GO
March 2, 2005 at 6:20 pm
1 - You don't need a temp table to execute that select (unless you plan to alter that data somehow or need to reuse it later in the proc before sending it to the client)
2 - Unless I'm missing something, this is also useless work :
Declare @RptStartDte smalldatetime
Declare @RptEndDte smalldatetime
SELECT @RptStartDte = @RptBeginDte
SELECT @RptEndDte = @RptFinishDte
Just use the parameters passed to the SP.
BTW
SELECT @RptStartDte = @RptBeginDte
SELECT @RptEndDte = @RptFinishDte
can be rewritten like this (just make sur you use SET NOCOUNT ON if you are using .NET) :
SELECT @RptStartDte = @RptBeginDte, @RptEndDte = @RptFinishDte
This will run faster than doing them separatly. There wouldn't any difference here since you do it only once, but in a while loop with multiple variables to set, you'd see one. Also Select @MyVar = 5 is not ANSI 92 standard, the standard is to use SET @MyVar = 5.
3 - I have no time to rebuild the query tonight.. I'll do it tomorrow.
March 3, 2005 at 9:34 am
Thanks for advice. I'm new at this and need all the help I can get.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply