Need help adding GrandTotal to the following querie

• montse 22199

  Old Hand

  Points: 321

  More actions

  January 11, 2013 at 3:12 pm

  #287717

  Hello Experts, the following query is working fine. The only thing is not doing is calculating the Grand Total for "Net Loan Amount".

  Since Im kind of new in T-sql I am not sure how to use the With Rollup option since I have to many rows in the 'group by' clause.

  Please help!!! Thanks a lot

  Declare @Fecha_ Datetime

  Set @Fecha_ ='08/10/2010'

  SELECT A.[Date Received],

  B.FldString21634 AS SSN,

  B.FldString21635 AS CredID,

  A.[Date Loan Period Start],

  A.[Date Loan Period Ends],

  '$'+ CONVERT(varchar(20), SUM(CAST(A.[Net Loan Amount] AS Money)),1) AS Total,

  C.tName AS Name_

  FROM A INNER JOIN B ON A.ContactID = B.nID LEFT OUTER JOIN

  C ON A.ContactID = C.aCustID

  WHERE (A.[Date Received] = @Fecha_) AND (A.Description = 'Direct Subsidized MPN Stafford Loan' OR

  A.Description = 'Direct Unsubsidized MPN Stafford Loan')

  GROUP BY A.[Date Loan Period Start], A.[Date Loan Period Ends], B.FldString21634,

  B.FldString21635, C.tName, A.[Date Received]

  ORDER BY Name_

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  January 11, 2013 at 4:06 pm

  #1576525

  montse 22199 (1/11/2013)

  ---

  Hello Experts, the following query is working fine. The only thing is not doing is calculating the Grand Total for "Net Loan Amount".

  Since Im kind of new in T-sql I am not sure how to use the With Rollup option since I have to many rows in the 'group by' clause.

  Please help!!! Thanks a lot

  Declare @Fecha_ Datetime

  Set @Fecha_ ='08/10/2010'

  SELECT A.[Date Received],

  B.FldString21634 AS SSN,

  B.FldString21635 AS CredID,

  A.[Date Loan Period Start],

  A.[Date Loan Period Ends],

  '$'+ CONVERT(varchar(20), SUM(CAST(A.[Net Loan Amount] AS Money)),1) AS Total,

  C.tName AS Name_

  FROM A INNER JOIN B ON A.ContactID = B.nID LEFT OUTER JOIN

  C ON A.ContactID = C.aCustID

  WHERE (A.[Date Received] = @Fecha_) AND (A.Description = 'Direct Subsidized MPN Stafford Loan' OR

  A.Description = 'Direct Unsubsidized MPN Stafford Loan')

  GROUP BY A.[Date Loan Period Start], A.[Date Loan Period Ends], B.FldString21634,

  B.FldString21635, C.tName, A.[Date Received]

  ORDER BY Name_

  Howdy... what company is this for?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
  "Change is inevitable... change for the better is not".
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  Intro to Tally Tables and Functions