Summing unique values within a temp table

Transact-SQL
1

Hello all,
I'm working on a query involving individual work orders. The purpose of the query is to identify accounts that have been backdated within a given month. Each instance of backdating may or may not result in a credit being given to the customer. The issue I am encountering is trying to sum the monetary value for each instance. For example, a customer comes in and states they should not have been billed for certain services and equipment they no longer have or receiving, the rep backdates the customers account by crediting a specific rate code associated with said services and equipment. Each rate code will appear within the ledger on it's own line which my current query is retrieving, however, I would like to create a new temp table in which I sum each of the rate code values in order to only pull back one line rather than multiple lines for the same account. Has anyone done this before, or have any ideas as to the best way I should approach this? All help is appreciated. Thanks!

Damian39

2

With no table information, no input data and no output example it is hard to tell but how about something like:

SELECT RateCode, SUM(MyValue) FROM MyTable GROUP BY RateCode;

3

Hi djj55,
I realize it was a tall order and not providing the table information, or output examples didn't help, but the query is quite long, and I wanted to provide the scenario I'm querying first, and then answer questions as they came along. I have done a group by which includes the rate code, but the values are still being separated.

SELECT DISTINCT A.ReportDate,A.CorpHouseCust,A.AcctCorp,A.House,A.Cust,A.CustomerName,A.ResPhone,A.BusinessPhone, C.CTYPE, ISNULL(CD2.LONGDESC, ' ') AS LONGDESC, INFODDPFlorida.dbo.udf_ConcatAddress(H.STNUM,H.FRACT,H.DIR,H.NAME,'','') AS [ServiceAddress], H.APT + ' ' + H.APTN AS [ServiceAptAndNumber],Z.CITY AS [ServiceCity], Z.STATE AS [ServiceState],H.ZIPCODE + '-' + H.ZIP4 AS [ServiceZipAndZip4], A.OrderNo, A.EmailAddr, A.JobType,A.OrderDate, A.ScheduleDate, A.CompleteDate, A.StartStopBillDate, DATEDIFF(d, A.StartStopBillDate, A.CompleteDate) AS [DIFFERENCE], A.Tech, A.JobNo, --A.OrderRsnDesc, B.RATE_CODE, --A.RATECODE_DESC, SUM(B.SAMT) AS AMOUNT, C.DDP_CYCLE AS BILL_CYCLE, --C.BDATE AS BILL_DATE, C.RATE AS MONTHLY_RATE, OP.PerNr AS EMPLOYEE_NUMBER, A.OprID, OP.AmdocsOprId, OP.AmdocsSalesId, OP.AmdocsSalesId2, A.SalesRep, ISNULL (OP.FullName,' ') AS OP_NAME, OP.DEPARTMENT, ISNULL (OP.Supervisor,' ') AS SUP_NAME, ISNULL (OP.Manager,' ') AS MGR_NAME

INTO #TMP7O

FROM #TMP5O A WITH(NOLOCK) INNER JOIN #TMP6O B WITH (NOLOCK)
ON A.ACCTCORP = B.ACCTCORP AND
A.HOUSE = B.HOUSE AND
A.CUST = B.CUST AND
A.RATE_CODE = B.RATE_CODE

					INNER JOIN INFODDPFlorida.GGS.IDST_CUSTOMER C

ON A.ACCTCORP=C.ACCTCORP AND
A.HOUSE=C.HOUSE AND
A.CUST=C.CUST

                    INNER JOIN INFODDPFlorida.GGS.IDST_HOUSE H

ON C.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE

                    LEFT JOIN INFODDPFlorida.GGS.IDST_ZIP Z

ON H.ACCTCORP=Z.ACCTCORP AND
H.ZIPCODE BETWEEN Z.ZIPBEG AND Z.ZIPEND

                    inner JOIN Reporting.dbo.UserBaseAll OP

ON A.OprID = OP.AmdocsOprId COLLATE SQL_Latin1_General_CP1_CS_AS

                    LEFT JOIN [INFODDPFlorida].[ggs].[IDST_CODE_36] CD2

ON A.ACCTCORP = CD2.ACCTCORP AND
C.CTYPE = CD2.CODE AND
CD2.TABL = '115'

WHERE C.STAT = '5'
AND OP.PerNr <> 0
AND OP.Manager <> 'Doe, John'
AND OP.Department like '%Customer Service Center%'

GROUP BY
A.ReportDate,
A.CorpHouseCust,
A.AcctCorp,
A.House,
A.Cust,
A.CustomerName,
A.ResPhone,
A.BusinessPhone,
C.CTYPE,
CD2.LONGDESC,
H.STNUM,
H.FRACT,
H.DIR,
H.NAME,
H.APT,
H.APTN,
Z.CITY,
Z.STATE,
H.ZIPCODE,
A.OrderNo,
H.ZIP4,
A.EmailAddr,
A.JobType,A.OrderDate,
A.ScheduleDate,
A.CompleteDate,
A.StartStopBillDate,
A.StartStopBillDate,
A.CompleteDate,
A.Tech,
A.JobNo,
B.RATE_CODE,
--A.RATECODE_DESC,
B.SAMT,
C.DDP_CYCLE,
C.RATE,
OP.PerNr,
A.OprID,
OP.AmdocsOprId,
OP.AmdocsSalesId,
OP.AmdocsSalesId2,
A.SalesRep,
OP.FullName,
OP.DEPARTMENT,
OP.Supervisor,
OP.Manager
4

Maybe try a cte or subquery with the sum.

5

Here is the current output I'm receiving using the code I pasted in my previous post. I'm trying to understand how I would use a sum sub-query in order to bring back one total sum of rate codes.

6

Not sure is this what you required.

SELECT col1, col2, total = sum(col3), grand_total = sum(sum(col3)) OVER()
FROM    table
GROUP BY col1, col2
7

I think you should definitely move the SUM to a derived table / subquery. It also lets you get rid of the monstrous GROUP BY, which will kill performance.

SELECT DISTINCT A.ReportDate,A.CorpHouseCust,A.AcctCorp,A.House,A.Cust,A.CustomerName,A.ResPhone,A.BusinessPhone,
C.CTYPE, ISNULL(CD2.LONGDESC, ' ') AS LONGDESC, INFODDPFlorida.dbo.udf_ConcatAddress(H.STNUM,H.FRACT,H.DIR,H.NAME,'','') AS [ServiceAddress],
H.APT + ' ' + H.APTN AS [ServiceAptAndNumber],Z.CITY AS [ServiceCity],
Z.STATE AS [ServiceState],H.ZIPCODE + '-' + H.ZIP4 AS [ServiceZipAndZip4],
A.OrderNo,
A.EmailAddr,
A.JobType,A.OrderDate,
A.ScheduleDate,
A.CompleteDate,
A.StartStopBillDate,
DATEDIFF(d, A.StartStopBillDate, A.CompleteDate) AS [DIFFERENCE],
A.Tech,
A.JobNo,
--A.OrderRsnDesc,
A.RATE_CODE,
--A.RATECODE_DESC,
B_TOT.AMOUNT,
C.DDP_CYCLE AS BILL_CYCLE,
--C.BDATE AS BILL_DATE,
C.RATE AS MONTHLY_RATE,
OP.PerNr AS EMPLOYEE_NUMBER,
A.OprID,
OP.AmdocsOprId,
OP.AmdocsSalesId,
OP.AmdocsSalesId2,
A.SalesRep,
ISNULL (OP.FullName,' ') AS OP_NAME,
OP.DEPARTMENT, 
ISNULL (OP.Supervisor,' ') AS SUP_NAME,
ISNULL (OP.Manager,' ') AS MGR_NAME

INTO #TMP7O

FROM #TMP5O A WITH(NOLOCK) INNER JOIN (
    SELECT ACCTCORP, HOUSE, CUST, RATE_CODE, SUM(SAMT) AS AMOUNT
    FROM #TMP6O WITH (NOLOCK)
    GROUP BY ACCTCORP, HOUSE, CUST, RATE_CODE
) AS B_TOT
ON	A.ACCTCORP	=	B_TOT.ACCTCORP AND
A.HOUSE	=	B_TOT.HOUSE AND
A.CUST	=	B_TOT.CUST AND
A.RATE_CODE	=	B_TOT.RATE_CODE
					INNER JOIN INFODDPFlorida.GGS.IDST_CUSTOMER C
ON A.ACCTCORP=C.ACCTCORP AND 
A.HOUSE=C.HOUSE AND 
A.CUST=C.CUST

                    INNER JOIN INFODDPFlorida.GGS.IDST_HOUSE H
ON C.ACCTCORP=H.ACCTCORP AND
C.HOUSE=H.HOUSE

                    LEFT JOIN INFODDPFlorida.GGS.IDST_ZIP Z
ON H.ACCTCORP=Z.ACCTCORP AND
H.ZIPCODE BETWEEN Z.ZIPBEG AND Z.ZIPEND

                    inner JOIN Reporting.dbo.UserBaseAll OP
ON A.OprID = OP.AmdocsOprId COLLATE SQL_Latin1_General_CP1_CS_AS

                    
                    LEFT JOIN [INFODDPFlorida].[ggs].[IDST_CODE_36] CD2
ON A.ACCTCORP = CD2.ACCTCORP AND
C.CTYPE = CD2.CODE AND
CD2.TABL = '115'

WHERE C.STAT = '5'
AND OP.PerNr <> 0
AND OP.Manager <> 'Doe, John'
AND OP.Department like '%Customer Service Center%'
1 Like