Query Help

Question

Query Help

mbrady5

SSCrazy

Points: 2835
More actions
March 7, 2013 at 2:49 pm

#269389

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'

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

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.

mbrady5 SSCrazy Points: 2835 More actions · Answer 2

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.