Prev: Join via srting of integers
Next: Importing Excel 2007 into XML Data for Shredding?
From: DavidC on 28 Apr 2010 13:05
I have a stored procedure in a database in SQL 2008 that has multiple SELECT
statements with UNION ALL to get a combined result set. I would like to take
the result set and group it by 1 of the columns (ServiceID) and SUM the
TransAmount column in the returned set (possibly in another sp). What is the
best way to go about this? Below is a small subset of the SP that uses UNION
ALL (it is quite large so I abbreviated it for demo purposes). Thanks.

SELECT dbo.IncExpTrans.PeopleLinkID AS ClientLinkID,
dbo.VendorChecks.CheckDate,
dbo.IncExpTrans.TransDate,
dbo.ServiceCodes.ServiceName AS Expense,
dbo.IncExpTrans.TransAmount,
dbo.IncExpTrans.TransQuantity,
ISNULL(dbo.IncExpTrans.TransRate, 0) AS TransRate,
dbo.Vendors.VendorName AS Payee,
dbo.IncExpTrans.ReferenceNo,
0 AS Payroll,
dbo.IncExpTrans.Is1099 AS Payee1099,
dbo.IncExpTrans.CheckID,
dbo.IncExpTrans.OneTime,
dbo.IncExpTrans.ServiceID,
dbo.VendorChecks.CheckNumber
FROM dbo.IncExpTrans INNER JOIN
dbo.ServiceCodes ON dbo.IncExpTrans.ServiceID =
dbo.ServiceCodes.ServiceID INNER JOIN
dbo.Vendors ON dbo.IncExpTrans.VendorID = dbo.Vendors.VendorID
INNER JOIN
dbo.PeopleLink ON dbo.IncExpTrans.PeopleLinkID =
dbo.PeopleLink.PeopleLinkID INNER JOIN
dbo.VendorChecks ON dbo.IncExpTrans.CheckID =
dbo.VendorChecks.CheckID
WHERE (dbo.IncExpTrans.PayTrans = 1)
AND (dbo.PeopleLink.Branch = 43)
AND (dbo.IncExpTrans.PeopleLinkID = @ClientLinkID)
AND (YEAR(dbo.IncExpTrans.TransDate) = @intYear)
AND (MONTH(dbo.IncExpTrans.TransDate) = @intMonth)

UNION ALL
SELECT dbo.Timesheets.ClientLinkID,
dbo.PayChecks.CheckDate,
CASE WHEN dbo.Timesheets.NonTaxDate IS NOT NULL THEN
dbo.Timesheets.NonTaxDate
ELSE dbo.Timesheets.Week2Ending
END AS TransDate,
dbo.ServiceCodes.ServiceName AS Expense,
CASE WHEN nontaxunits <> 0 THEN nontaxunits * nontaxrate
ELSE GrossPay
END AS TransAmount,
CASE WHEN NonTaxUnits <> 0 THEN NonTaxUnits
ELSE CAST((Week1Units + Week2Units + Week2UnitsOT) AS FLOAT)
END AS TransQuantity,
CASE WHEN NonTaxUnits <> 0 THEN NonTaxRate
ELSE PayRate
END AS TransRate,
dbo.People.FirstName + N' ' + dbo.People.LastName AS Payee,
dbo.Timesheets.TimeNote AS ReferenceNo,
1 AS Payroll,
dbo.Timesheets.Taxable AS Payee1099,
dbo.PayChecks.CheckID,
0 AS OneTime,
dbo.Timesheets.ServiceID,
dbo.PayChecks.CheckNumber
FROM dbo.Timesheets INNER JOIN
dbo.PayChecks ON dbo.Timesheets.CheckID = dbo.PayChecks.CheckID
INNER JOIN
dbo.ServiceCodes ON dbo.Timesheets.ServiceID =
dbo.ServiceCodes.ServiceID INNER JOIN
dbo.PeopleLink ON dbo.PayChecks.PeopleLinkID =
dbo.PeopleLink.PeopleLinkID INNER JOIN
dbo.People ON dbo.PeopleLink.PersonID = dbo.People.PersonID
WHERE (dbo.PeopleLink.Branch = 43)
AND (dbo.Timesheets.ClientLinkID = @ClientLinkID)

....etc

--
David
From: Tom Moreau on 28 Apr 2010 14:51
Try:

create table #t
(
Yada...
)

insert #t
exec MyProc

select
ServiceID
, sum (TransAmount)
from
#t
group by
ServiceID

drop table #t
go


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:D3919EFE-17E4-45AC-9A82-AB9836BAA32D(a)microsoft.com...
I have a stored procedure in a database in SQL 2008 that has multiple SELECT
statements with UNION ALL to get a combined result set. I would like to
take
the result set and group it by 1 of the columns (ServiceID) and SUM the
TransAmount column in the returned set (possibly in another sp). What is
the
best way to go about this? Below is a small subset of the SP that uses
UNION
ALL (it is quite large so I abbreviated it for demo purposes). Thanks.

SELECT dbo.IncExpTrans.PeopleLinkID AS ClientLinkID,
dbo.VendorChecks.CheckDate,
dbo.IncExpTrans.TransDate,
dbo.ServiceCodes.ServiceName AS Expense,
dbo.IncExpTrans.TransAmount,
dbo.IncExpTrans.TransQuantity,
ISNULL(dbo.IncExpTrans.TransRate, 0) AS TransRate,
dbo.Vendors.VendorName AS Payee,
dbo.IncExpTrans.ReferenceNo,
0 AS Payroll,
dbo.IncExpTrans.Is1099 AS Payee1099,
dbo.IncExpTrans.CheckID,
dbo.IncExpTrans.OneTime,
dbo.IncExpTrans.ServiceID,
dbo.VendorChecks.CheckNumber
FROM dbo.IncExpTrans INNER JOIN
dbo.ServiceCodes ON dbo.IncExpTrans.ServiceID =
dbo.ServiceCodes.ServiceID INNER JOIN
dbo.Vendors ON dbo.IncExpTrans.VendorID = dbo.Vendors.VendorID
INNER JOIN
dbo.PeopleLink ON dbo.IncExpTrans.PeopleLinkID =
dbo.PeopleLink.PeopleLinkID INNER JOIN
dbo.VendorChecks ON dbo.IncExpTrans.CheckID =
dbo.VendorChecks.CheckID
WHERE (dbo.IncExpTrans.PayTrans = 1)
AND (dbo.PeopleLink.Branch = 43)
AND (dbo.IncExpTrans.PeopleLinkID = @ClientLinkID)
AND (YEAR(dbo.IncExpTrans.TransDate) = @intYear)
AND (MONTH(dbo.IncExpTrans.TransDate) = @intMonth)

UNION ALL
SELECT dbo.Timesheets.ClientLinkID,
dbo.PayChecks.CheckDate,
CASE WHEN dbo.Timesheets.NonTaxDate IS NOT NULL THEN
dbo.Timesheets.NonTaxDate
ELSE dbo.Timesheets.Week2Ending
END AS TransDate,
dbo.ServiceCodes.ServiceName AS Expense,
CASE WHEN nontaxunits <> 0 THEN nontaxunits * nontaxrate
ELSE GrossPay
END AS TransAmount,
CASE WHEN NonTaxUnits <> 0 THEN NonTaxUnits
ELSE CAST((Week1Units + Week2Units + Week2UnitsOT) AS FLOAT)
END AS TransQuantity,
CASE WHEN NonTaxUnits <> 0 THEN NonTaxRate
ELSE PayRate
END AS TransRate,
dbo.People.FirstName + N' ' + dbo.People.LastName AS Payee,
dbo.Timesheets.TimeNote AS ReferenceNo,
1 AS Payroll,
dbo.Timesheets.Taxable AS Payee1099,
dbo.PayChecks.CheckID,
0 AS OneTime,
dbo.Timesheets.ServiceID,
dbo.PayChecks.CheckNumber
FROM dbo.Timesheets INNER JOIN
dbo.PayChecks ON dbo.Timesheets.CheckID = dbo.PayChecks.CheckID
INNER JOIN
dbo.ServiceCodes ON dbo.Timesheets.ServiceID =
dbo.ServiceCodes.ServiceID INNER JOIN
dbo.PeopleLink ON dbo.PayChecks.PeopleLinkID =
dbo.PeopleLink.PeopleLinkID INNER JOIN
dbo.People ON dbo.PeopleLink.PersonID = dbo.People.PersonID
WHERE (dbo.PeopleLink.Branch = 43)
AND (dbo.Timesheets.ClientLinkID = @ClientLinkID)

....etc

--
David

 | 
Pages: 1
Prev: Join via srting of integers
Next: Importing Excel 2007 into XML Data for Shredding?