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

muk gravatar image

muk
400 31 33 37

(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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x587
x555
x373
x344
x22

asked: Aug 23, 2012 at 02:36 PM

Seen: 840 times

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