Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Thursday, March 7, 2013 2:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:26 AM
Points: 97, Visits: 504
Hi, I am in need of some Sum and Grouping help. The below query is returning multiple rows and I only want one returned With the open amounts Summed and 1 voucher amount representing a summary of the three below rows.

Thanks for any help you can provide.



SELECT     'SourceName' AS SourceDB
, 'NAME' AS 'SiteName'
, RTRIM(APIBH.IDVEND) AS VendorNumber
, RTRIM(APIBH.IDINVC) AS VoucherNumber
, APIBH.AMTGROSDST AS VoucherTotalDomestic
, (APIBH.AMTGROSDST * APIBH.EXCHRATEHC) AS VoucherTotalUSD
, (APIBH.AMTGROSDST - ISNULL(APTCP.AMTPAYM, 0)- APTCP.AMTERNDISC) AS OpenAmountDomestic
, ((APIBH.AMTGROSDST * APIBH.EXCHRATEHC) - ISNULL(APTCP.AMTPAYM, 0)* APIBH.EXCHRATEHC)- (APTCP.AMTERNDISC * APIBH.EXCHRATEHC) AS OpenAmountUSD
, dbo.udf_convert_int_date(APIBH.DATEBUS) AS PostedDate
, dbo.udf_convert_int_date(APTCR.DATEBUS) AS AppliedDate
, dbo.udf_convert_int_date(APIBH.DATEINVC) AS AgingDate
, dbo.udf_convert_int_date(APIBH.DATEDUE) AS DueDate
, dbo.udf_convert_int_date(APIBH.DATEINVC) AS DocumentDate
, NULL AS ReceivedDate
, CASE WHEN (APTCR.DATERMIT) = 0 THEN NULL ELSE dbo.udf_convert_int_date(APTCR.DATERMIT) END AS PaidDate
, CASE WHEN (APIBH.DATEDISC) = 0 THEN NULL ELSE dbo.udf_convert_int_date(APIBH.DATEDISC) END AS DiscountDate
, CONVERT(bigint, CASE WHEN APIBH.AMTGROSDST - ISNULL(APTCP.AMTPAYM, 0) = 0 THEN dbo.udf_convert_int_date(APTCR.DATERMIT) ELSE GETDATE() END - dbo.udf_convert_int_date(APIBH.DATEINVC)) AS AgeDays
, CONVERT(bigint, dbo.udf_convert_int_date(APIBH.DATEDUE) - CASE WHEN APIBH.AMTGROSDST - ISNULL(APTCP.AMTPAYM, 0) = 0 THEN dbo.udf_convert_int_date(APTCR.DATERMIT)
ELSE GETDATE() END) AS DueDays
, APIBH.CODECURN AS CurrencyCode
, APIBH.IDTRX, ISNULL(APTCP.AMTPAYM, 0) AS PayAmt
,APIBH.EXCHRATEHC AS EffectiveExchangeRate
FROM MyTable.APIBH AS APIBH LEFT OUTER JOIN
MyTable.APTCP AS APTCP ON APIBH.IDVEND = APTCP.IDVEND AND APIBH.IDINVC = APTCP.IDINVC INNER JOIN
MyTable.APTCR AS APTCR ON APTCP.BATCHTYPE = APTCR.BTCHTYPE AND APTCP.CNTBTCH = APTCR.CNTBTCH AND
APTCP.CNTRMIT = APTCR.CNTENTR
WHERE (1 = 1)
AND (APIBH.ERRBATCH = 0)
--AND (APIBH.FISCYR >= '2010')
AND (APIBH.IDTRX <> 32)
AND APIBH.IDINVC = '010106'

Post #1428276
Posted Thursday, March 7, 2013 3:27 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 10:32 AM
Points: 546, Visits: 1,063
What about the rest of the columns? Do you still want all of those? You're grouping by all the columns and some of those columns have different values so you'll always get multiple rows until you narrow it down.
Post #1428297
Posted Thursday, March 7, 2013 5:49 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 6:26 AM
Points: 97, Visits: 504
Yes, I do want all of those columns. I have decided that I'm going to try and handle for this in SSIS. I thought I was going crazy that I couldn't make this happen.
Post #1428335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse