x

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

more ▼

asked Aug 23, 2012 at 02:36 PM in Default

avatar image

muk
440 33 35 40

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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?

more ▼

answered Aug 23, 2012 at 05:10 PM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

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?

Aug 23, 2012 at 06:42 PM muk

cant you group the report data by term and then have a sum for each?

Aug 23, 2012 at 07:00 PM Fatherjack ♦♦

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

Aug 23, 2012 at 07:01 PM muk
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x740
x650
x426
x408
x28

asked: Aug 23, 2012 at 02:36 PM

Seen: 1008 times

Last Updated: Aug 23, 2012 at 07:01 PM

Copyright 2016 Redgate Software. Privacy Policy