Problem with SUM

Other SQL Server Topics
1

Hi Folks.

I'm having a problem properly applying SUM to the following..

dbo.ipcfn_inv_ttl(HHDh.hh_invoice, HHDh.hh_date, 1, 1) - dbo.ipcfn_hh_st(HHDh.hh_invoice, HHDh.hh_date) AS LineTotal

I've tried wrapping the whole thing in SUM, as well as, wrapping up the individual pieces between the minus sign. I'm not sure where I'm going wrong, can someone help enlighten me?

Here is the full code...

SELECT DISTINCT 
                         TOP (100) PERCENT HHDh.hh_bname AS hh_name,  
                         '1' as hd_firstn, dbo.ipcfn_inv_ttl(HHDh.hh_invoice, HHDh.hh_date, 1, 1) - dbo.ipcfn_hh_st(HHDh.hh_invoice, HHDh.hh_date) AS LineTotal
FROM            dbo.rainvhhd AS HHDh INNER JOIN
                         dbo.rainvhdt AS HDTh ON HHDh.hh_invoice = HDTh.hd_invoice AND HHDh.hh_date = HDTh.hd_date INNER JOIN
                         dbo.ricustmr AS Ch ON Ch.cu_id = HHDh.hh_cuid INNER JOIN
                                                                                                --AND Ch.cu_dept = HHDh.hh_dept 
                         dbo.racustar AS Ah ON Ah.ar_ID = Ch.cu_ARID AND ch.cu_acid = Ah.ar_acid INNER JOIN
                                                                                                 --Ah.ar_account = Ch.cu_account AND Ah.ar_account = HHDh.hh_account INNER JOIN
                         dbo.riskugrp AS Uh ON Uh.sg_skugrup = HDTh.hd_sku --INNER JOIN
                        -- dbo.ravolcls AS Vh ON Vh.vc_code = Uh.sg_volcls
                                                                                                CROSS APPLY
                                                                                                ris_mast LEFT OUTER JOIN
                                                                                                dbo.ravolcls Trns ON Trns.vc_code = dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0)and 
                                                                                                 trns.vc_revtype = dbo.amfn_RevType(hd_sku, hd_trn_cd, hd_type, hd_note, ris_cuscod,'','','') LEFT OUTER JOIN
                                                                                                dbo.ravolcls Adj ON Adj.vc_code = dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0) and Adj.vc_revtype = dbo.amfn_RevType(hd_sku, hd_adj_cd, hd_type, hd_note, ris_cuscod,'','','') LEFT OUTER JOIN
                                                                                                dbo.ravolcls IMD ON IMD.vc_code =dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0) and IMD.vc_revtype = dbo.amfn_RevType(hd_sku, 'IC', hd_type, hd_note, ris_cuscod,'','','') LEFT OUTER JOIN
                                                                                                dbo.ravolcls Blank ON Blank.vc_code = dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0) and blank.vc_revtype = ''
WHERE        (HHDh.hh_HCInvoice <> '') AND (HHDh.hh_HCDate BETWEEN CONVERT(DATETIME, '2023-06-04  00:00:00', 102) AND CONVERT(DATETIME, '2023-06-10 00:00:00', 102)) 
and hd_sku <> '      LBS ' and hd_sku <> '       RC '  and hd_sku <> '   HANGER ' AND (HDTh.hd_trn_cd <> 'ANC')
GROUP BY hh_bname, hh_invoice, hh_date
2

What problem, specifically? Why make us guess??

1 Like
3

Sorry... result sets are not accurate. Without the SUM I have accurate results...

dbo.ipcfn_inv_ttl(HHDh.hh_invoice, HHDh.hh_date, 1, 1) - dbo.ipcfn_hh_st(HHDh.hh_invoice, HHDh.hh_date) AS LineTotal

image

4

When I try to SUM it get's ugly...

SUM(dbo.ipcfn_inv_ttl(HHDh.hh_invoice, HHDh.hh_date, 1, 1) - dbo.ipcfn_hh_st(HHDh.hh_invoice, HHDh.hh_date)) AS LineTotal

image

Apparently there's a rule that a 'New User' can't imbed more than one image in a post :confused:

5

Images are discouraged and results should be provided as text. We also need DDL, test data and your query along with expected results for the test data. Please read the following:

How to post a T-SQL question on a public forum | spaghettidba

All those scalar functions, especially with the JOINS, look hideous from a performance perspective:

Refactor SQL Server scalar UDF to inline TVF to improve performance (mssqltips.com)

With the information provided so far my best guess is you are trying to SUM some values by Invoice and Date.

You could try wrapping the results you think are correct in a CTE and then try to sum the CTE.

1 Like
6

Since the base query gives you the correct results, add an outer query to do the SUMs on those results:


SELECT
    hh_name, hd_firstn, SUM(LineTotal) AS LineTotal

FROM (

SELECT DISTINCT 
                         TOP (100) PERCENT HHDh.hh_bname AS hh_name,  
                         '1' as hd_firstn, dbo.ipcfn_inv_ttl(HHDh.hh_invoice, HHDh.hh_date, 1, 1) - dbo.ipcfn_hh_st(HHDh.hh_invoice, HHDh.hh_date) AS LineTotal
FROM            dbo.rainvhhd AS HHDh INNER JOIN
                         dbo.rainvhdt AS HDTh ON HHDh.hh_invoice = HDTh.hd_invoice AND HHDh.hh_date = HDTh.hd_date INNER JOIN
                         dbo.ricustmr AS Ch ON Ch.cu_id = HHDh.hh_cuid INNER JOIN
                                                                                                --AND Ch.cu_dept = HHDh.hh_dept 
                         dbo.racustar AS Ah ON Ah.ar_ID = Ch.cu_ARID AND ch.cu_acid = Ah.ar_acid INNER JOIN
                                                                                                 --Ah.ar_account = Ch.cu_account AND Ah.ar_account = HHDh.hh_account INNER JOIN
                         dbo.riskugrp AS Uh ON Uh.sg_skugrup = HDTh.hd_sku --INNER JOIN
                        -- dbo.ravolcls AS Vh ON Vh.vc_code = Uh.sg_volcls
                                                                                                CROSS APPLY
                                                                                                ris_mast LEFT OUTER JOIN
                                                                                                dbo.ravolcls Trns ON Trns.vc_code = dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0)and 
                                                                                                 trns.vc_revtype = dbo.amfn_RevType(hd_sku, hd_trn_cd, hd_type, hd_note, ris_cuscod,'','','') LEFT OUTER JOIN
                                                                                                dbo.ravolcls Adj ON Adj.vc_code = dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0) and Adj.vc_revtype = dbo.amfn_RevType(hd_sku, hd_adj_cd, hd_type, hd_note, ris_cuscod,'','','') LEFT OUTER JOIN
                                                                                                dbo.ravolcls IMD ON IMD.vc_code =dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0) and IMD.vc_revtype = dbo.amfn_RevType(hd_sku, 'IC', hd_type, hd_note, ris_cuscod,'','','') LEFT OUTER JOIN
                                                                                                dbo.ravolcls Blank ON Blank.vc_code = dbo.amfn_RevClass(hd_sku, hd_type, hd_note, hh_healthcare, hh_HCRepl,hh_taxable, ris_cuscod,0,sg_volcls,0) and blank.vc_revtype = ''
WHERE        (HHDh.hh_HCInvoice <> '') AND (HHDh.hh_HCDate BETWEEN CONVERT(DATETIME, '2023-06-04  00:00:00', 102) AND CONVERT(DATETIME, '2023-06-10 00:00:00', 102)) 
and hd_sku <> '      LBS ' and hd_sku <> '       RC '  and hd_sku <> '   HANGER ' AND (HDTh.hd_trn_cd <> 'ANC')
GROUP BY hh_bname, hh_invoice, hh_date

) AS derived

ORDER BY hh_name