Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sum function seems not to be working correctly

Author  Topic 

Damian39
Starting Member

33 Posts

Posted - 2014-09-23 : 11:34:10
Hello all,
I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:

IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C

select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB
INTO #TMP1C
from Vantage.dbo.SBB_BASE (NOLOCK)
where PRIN_SBB in (6000,7500)
AND VIP_FLG_SBB in ('E','H','0','1','9','B','C','F','G','L','M','N','O','P','Q','R','S',
'U','V','W','X','Y','Z')


Select DISTINCT
O.Prin_oci,
O.SYS_OCI,
O.AGNT_OCI,
O.SUB_ACCT_NO_OCI,
A.RES_NAME_SBB,
CASE WHEN A.EXT_STAT_SBB = '' THEN 'ACTIVE'
WHEN A.EXT_STAT_SBB = 'C' THEN 'VOL_DISCO'
WHEN A.EXT_STAT_SBB = 'E' THEN 'NON-PAY_DISCO'
WHEN A.EXT_STAT_SBB = 'Z' THEN 'CHARGED_OFF'
ELSE '' END AS 'ACCOUNT_STATUS',
CASE WHEN A.VIP_FLG_SBB = '0' THEN 'VIP_TENANT'
WHEN A.VIP_FLG_SBB = '1' THEN 'GOVT_RESI'
WHEN A.VIP_FLG_SBB = '9' THEN 'GOVT_COMM'
WHEN A.VIP_FLG_SBB = 'B' THEN 'BULK_MASTER'
WHEN A.VIP_FLG_SBB = 'C' THEN 'COMM_GOVT'
WHEN A.VIP_FLG_SBB = 'E' THEN 'COMCAST_EMPLOYEE'
WHEN A.VIP_FLG_SBB = 'F' THEN 'COMP_SERVICE'
WHEN A.VIP_FLG_SBB = 'G' THEN 'COMM_GOVT'
WHEN A.VIP_FLG_SBB = 'H' THEN 'COMCAST_EMPTENT'
WHEN A.VIP_FLG_SBB = 'L' THEN 'LIBRARY_RES'
WHEN A.VIP_FLG_SBB = 'M' THEN 'APT_MANAGER'
WHEN A.VIP_FLG_SBB = 'N' THEN 'NON_BB_TENANT'
WHEN A.VIP_FLG_SBB = 'O' THEN 'BRGND_EMPLOYEE'
WHEN A.VIP_FLG_SBB = 'P' THEN 'TENANT_MANAGER'
WHEN A.VIP_FLG_SBB = 'Q' THEN 'RECIPROCAL_TENANT'
WHEN A.VIP_FLG_SBB = 'R' THEN 'RECIPROCAL'
WHEN A.VIP_FLG_SBB = 'S' THEN 'SCHOOL_RES'
WHEN A.VIP_FLG_SBB = 'U' THEN 'SEASONAL'
WHEN A.VIP_FLG_SBB = 'V' THEN 'VIP'
WHEN A.VIP_FLG_SBB = 'W' THEN 'COM_DQ_EXEMPT'
WHEN A.VIP_FLG_SBB = 'X' THEN 'SPECT/STRT/NBC'
WHEN A.VIP_FLG_SBB = 'Y' THEN 'RETIRED_EMPLOYEE'
WHEN A.VIP_FLG_SBB = 'Z' THEN 'SPEC/ST/NBC-TEN'
ELSE '' END AS 'VIP_FLG_SBB',
H.Addr1_HSE as ADDR1,
H.Res_Addr_2_HSE as APT,
H.Res_city_HSE as CITY,
H.Postal_cde_HSE as ZIP_CODE,
CASE WHEN O.LOB_ACT_OCI = 'C' THEN 'VIDEO'
WHEN O.LOB_ACT_OCI = 'I' THEN 'HSD'
WHEN O.LOB_ACT_OCI = 'T' THEN 'VOICE'
ELSE '' END AS 'LOB',

MAX(O.CYC_CHG_DTE_OCI) AS CYC_CHG_DTE_OCI,
SUM(O.CHARGE_AMT_OCI) AS CHARGE_AMT_OCI,
O.SERV_CDE_OCI,
O.DSC_CDE_OCI,
cd.ONLINE_DESC_ALA,
A.CUR_BAL_SBB,
ctd.SPA_FLG_CTD

INTO #TMP2C

FROM #TMP1C as A (nolock) inner join Vantage.dbo.OCI_CUR_ITEM AS O (NOLOCK)
ON A.PRIN_SBB = O.PRIN_OCI
AND A.SUB_ACCT_NO_SBB = O.SUB_ACCT_NO_OCI

inner join Vantage.dbo.HSE_Base as H (nolock) on O.HSE_KEY_OCI = H.HSE_KEY_HSE

inner join Vantage.dbo.CTD_DISPLAY as CTD (nolock)
on A.PRIN_SBB = ctd.PRIN_CTD
and A.VIP_FLG_SBB = ctd.CDE_VALUE_CTD

inner join Vantage.dbo.ALA_SERV_CODE as CD (nolock)
on o.PRIN_OCI = cd.PRIN_ALA
and o.SERV_CDE_OCI = cd.SERV_CDE_ALA

inner join Vantage.dbo.PAJ_ADJUST as AJ (nolock)
on cd.PRIN_ALA = aj.PRIN_PAJ
and cd.SERV_CDE_ALA = aj.SERV_CDE_PAJ


WHERE CTD.SPA_FLG_CTD = 'P'
AND CTD.CDE_TBL_NO_CTD = '24'
AND O.LOB_ACT_OCI NOT IN ('','N','Y','Z')
AND O.CHARGE_AMT_OCI <> 0
AND O.SUB_ACCT_NO_OCI = '8495600010006437'

group BY
O.Prin_oci,
O.SYS_OCI,
O.AGNT_OCI,
O.SUB_ACCT_NO_OCI,
A.RES_NAME_SBB,
A.EXT_STAT_SBB ,
A.VIP_FLG_SBB,
H.Addr1_HSE,
H.Res_Addr_2_HSE,
H.Res_city_HSE,
H.Postal_cde_HSE,
O.LOB_ACT_OCI,
O.CYC_CHG_DTE_OCI,
O.CHARGE_AMT_OCI,
O.SERV_CDE_OCI,
O.DSC_CDE_OCI,
cd.ONLINE_DESC_ALA,
A.CUR_BAL_SBB,
ctd.SPA_FLG_CTD

SELECT distinct AB.Prin_oci,
AB.SYS_OCI,
AB.AGNT_OCI,
AB.SUB_ACCT_NO_OCI,
AB.RES_NAME_SBB,
AB.ACCOUNT_STATUS ,
AB.VIP_FLG_SBB,
AB.ADDR1,
AB.APT,
AB.CITY,
AB.ZIP_CODE,
AB.LOB,
AB.CYC_CHG_DTE_OCI,
AB.CHARGE_AMT_OCI,
AB.SERV_CDE_OCI,
AB.ONLINE_DESC_ALA,
AB.DSC_CDE_OCI,
DC.DSC_CDE_DSC,
SUM(DC.DSC_VAL_DSC) AS DSC_VAL_DSC,
DC.LOB_DSC,
DC.STMT_DESCR_DSC,
AB.CUR_BAL_SBB

FROM #TMP2C AS AB INNER JOIN Vantage.dbo.DSC_DISCOUNT AS DC (NOLOCK)
ON AB.PRIN_OCI = DC.PRIN_DSC
AND AB.SYS_OCI = DC.SYS_DSC
AND AB.AGNT_OCI = DC.AGNT_DSC
AND AB.DSC_CDE_OCI = DC.DSC_CDE_DSC

where dc.CDE_TBL_NO_DSC = '44'
and ab.spa_flg_ctd = 'P'


group BY
AB.Prin_oci,
AB.SYS_OCI,
AB.AGNT_OCI,
AB.SUB_ACCT_NO_OCI,
AB.RES_NAME_SBB,
AB.ACCOUNT_STATUS ,
AB.VIP_FLG_SBB,
AB.ADDR1,
AB.APT,
AB.CITY,
AB.ZIP_CODE,
AB.LOB,
AB.CYC_CHG_DTE_OCI,
AB.CHARGE_AMT_OCI,
AB.SERV_CDE_OCI,
AB.ONLINE_DESC_ALA,
AB.DSC_CDE_OCI,
DC.DSC_CDE_DSC,
DC.DSC_VAL_DSC,
DC.LOB_DSC,
DC.STMT_DESCR_DSC,
AB.CUR_BAL_SBB

Not entirely sure what I am doing wrong, or why the numbers are coming back so high. Any and all help is greatly appreciated. Thanks!

Damian39

Damian

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-23 : 13:58:06
Make sure your join(s) are one to one. Otherwise, you'll get some rows more than once which could lead to higher totals.
Go to Top of Page
   

- Advertisement -