question

muk avatar image
muk asked

need to take a sum of a sum in a query

Hi all, I have this query Select a.TERM, a.STUDENT_ID, a.AR_TYPE, Sum(a.INVOICE_AMT) - Sum(a.PAYMENT_AMT) As TERM_BALANCE, STUDENT_CURRENT_TYPE_VIEW.STUDENT_CURRENT_TYPE, b.STA_CLASS, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Last Name], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Country, PREFERRED_MAIL_VIEW.[Preferred Email], TERMS.TERM_START_DATE From (Select AR_INVOICES.INV_TERM As TERM, AR_INVOICES.INV_PERSON_ID As STUDENT_ID, AR_INVOICES.INV_AR_TYPE As AR_TYPE, 0 As PAYMENT_AMT, IsNull(AR_INVOICE_ITEMS.INVI_EXT_CHARGE_AMT, 0) - IsNull(AR_INVOICE_ITEMS.INVI_EXT_CR_AMT, 0) As INVOICE_AMT From AR_INVOICE_ITEMS Inner Join AR_INVOICES On AR_INVOICE_ITEMS.INVI_INVOICE = AR_INVOICES.AR_INVOICES_ID Where AR_INVOICES.INV_TERM Is Not Null Union All Select IsNull(AR_PAYMENTS.ARP_TERM, AR_INVOICES.INV_TERM) As TERM, AR_PAYMENTS.ARP_PERSON_ID, AR_PAYMENTS.ARP_AR_TYPE, IsNull(AR_PAYMENT_ITEMS.ARPI_AMT, 0) - IsNull(AR_PAYMENT_ITEMS.ARPI_REVERSAL_AMT, 0) As PAYMENT_AMT, 0 As INVOICE_AMT From AR_PAYMENT_ITEMS Inner Join AR_PAYMENTS On AR_PAYMENT_ITEMS.ARPI_PAYMENT = AR_PAYMENTS.AR_PAYMENTS_ID Left Outer Join AR_INVOICE_ITEMS On AR_PAYMENT_ITEMS.ARPI_INVOICE_ITEM = AR_INVOICE_ITEMS.AR_INVOICE_ITEMS_ID Left Outer Join AR_INVOICES On AR_INVOICE_ITEMS.INVI_INVOICE = AR_INVOICES.AR_INVOICES_ID Where IsNull(AR_PAYMENTS.ARP_TERM, AR_INVOICES.INV_TERM) Is Not Null) As a Left Join STUDENT_CURRENT_TYPE_VIEW On a.STUDENT_ID = STUDENT_CURRENT_TYPE_VIEW.STUDENTS_ID Left Join (Select * From STUDENT_ACAD_LEVELS_VIEW Where STUDENT_ACAD_LEVELS_VIEW.STA_ACAD_LEVEL = 'FP') As b On a.STUDENT_ID = b.STA_STUDENT Left Join PREFERRED_MAIL_VIEW On a.STUDENT_ID = PREFERRED_MAIL_VIEW.ID Left Join TERMS On TERMS.TERMS_ID = a.TERM Group By a.TERM, a.STUDENT_ID, a.AR_TYPE, STUDENT_CURRENT_TYPE_VIEW.STUDENT_CURRENT_TYPE, b.STA_CLASS, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Last Name], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.Country, PREFERRED_MAIL_VIEW.[Preferred Email], TERMS.TERM_START_DATE Having a.STUDENT_ID Is Not Null Order By a.STUDENT_ID And basically what I need to add is a total balance for all terms for a student prior to the current term. I am doing this in SSRS so I was thinking of creating a parameter of current term start date and calculating the total balance prior to the specified current term start date. So a balance for terms
sql-server-2008-r2ssrssql-serverqueryreport
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Fatherjack avatar image
Fatherjack answered
SSRS summing and grouping is a lot less rigid than TSQL. Cant you return your dataset to the report and then create a report or group footed and place the SUM function in there as needed?
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I know I tried to do that but the problem is that my dataset also returns data for the current term which I need to see as well so I can not filter it out. If I group it and do a sum it includes the current term in the sum. Any workaround?
0 Likes 0 ·
cant you group the report data by term and then have a sum for each?
0 Likes 0 ·
Yes but thats not what I need I need a total sum of all terms that are not the current term. doing that gives me the individual sums for each term I need to add these together - current term to get the total I need
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.