Using sp result of SELECT

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
0
Utf
4/28/2010 5:05:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

1 Replies
837 Views

Similar Articles

[PageSpeed] 12

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@lifetimeinc.com> wrote in message 
news:D3919EFE-17E4-45AC-9A82-AB9836BAA32D@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 

0
Tom
4/28/2010 6:51:27 PM
Reply:

Similar Artilces:

using ADO from Excel 2007
We just upgraded to Vista from XP and on some PCs get an error message about MSRDO20.dll not being installed when I try and connect to a SYBASE database using ADO. I have asked the technology group for help installing this (some PCs have it, some don't) My question is - fromteh VBA Developer screen (inside Excel) under Tools - References - which referneces do I need to include to be able to use ADO? What I'm wondering is if in Vista there is a reference I should add in that I have not used before. Is it sufficient that I just downlaod MSRDO20.DLL from the internet an...

Advanced Find Select dropdown disppearing
Hi there, Is it a known issue in 4.0 that when you click on the Select dropdown as soon as you begin to scroll down the list disappears? It's very annoying! If anyone else is having this problem and has a fix for it please reply. Thank you! Suzy ...

On status bar, sum of selected cells doesn't automatically appear.
Excel 2003 - when you use the auto filter - nomally the sum of cells selected automatically shows up on the bottom left hand side of the status bar - eg: 32 of 100 records found.. but this is not always the case when i open or start a new worksheet Can anyone tell me if there is a function to activeate this on my worksheet... thanx Debra Dalgleish explains why and offers some workarounds: http://contextures.com/xlautofilter02.html#Count Chozzie wrote: > > Excel 2003 - > when you use the auto filter - nomally the sum of cells selected > automatically shows up on the bottom l...

OMA is not what it used to be
Hello, I have installed SBS 03 with exchange, and whenever I would log on to OMA I would get that nice welcome screen that I managed to modify and put the company logo on. After installing SP1 that screen is gone, now I just get a pop up that wants my user name, password and domain. Also I cannot do www.mywebsite.com/exchange it wants https:// Please help "Dima" <Dima@discussions.microsoft.com> wrote: >Hello, I have installed SBS 03 with exchange, and whenever I would log on to >OMA I would get that nice welcome screen that I managed to modify and put the >comp...

Before you can use US Payroll Extensions...
A client recently installed GP on a new client workstation with GP 10.00.1410 - they also installed the PayrollSP4 and HRSP4 files for the Human Resources and Payroll Suite. This had previously been installed on all of the other clients, as well as the server. Now, after this one installation, all client workstations are receiving the message: Before you can use US Payroll Extensions, PTO Manager, Advanced HR, or advanced payroll, a system administrator must first log into GP to initialize or upgrade database objects. We've logged into all companies as 'sa' and...

Using LINQ to SQL
Hi, New to LINQ to SQL... When I added a stored procedure to the project, I noticed that the auto-generated code contained a hard-coded reference to the Database name. Is there any way to pass the DB name as a vaiable instead (like from the config file) ? Thanks, Rob Robbie wrote: > Hi, > > New to LINQ to SQL... > > When I added a stored procedure to the project, I noticed that the > auto-generated code contained a hard-coded reference to the Database name. > > Is there any way to pass the DB name as a vaiable instead (like from...

Selecting certain letters in address book closes Outlook
I'm using Outlook 2000 Pro with Windows XT. When you open the address book, then select u,v,w,x,y,or z, Outlook immediately closes. PLEASE HELP! Thank you. Try running Detect and Repair from the Help menu. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After searching google.groups.com and finding no answer ALB <anonymous@discussions.microsoft.com> asked: | I'm using Outlook 2000 Pro with Windows XT. When you open | the address boo...

choice selection button in a cell
Hi, I need to have a choice/radio button in a cell, wherein the user can just choose only one option instead of choosing from the drop down menu that appears against a cell when data validation method is used. Ex : User has to choose one of the 3 options shown in a cell. Can someone in this forum share the knowledge of how to go about achieving the same ? my skills are very limited and have only used the existing functions in xls. -- jkkumar ------------------------------------------------------------------------ jkkumar's Profile: http://www.excelforum.com/member.php?action=getin...

Exchange 5.5 (latest SP and hotfixes) and Outlook 2k and 2003 Send/Receive Issue
I am running Exchange 5.5 Standard with latest of everything. My clients are running either 2k or 2003, most being 2k. I have an issue with all clients when sending receiving e-mail. Mail is no longer instantly sent or received. Mail sent stays in the Outbox until I change folders or click on a different e-mail message. Same way with receiving mail. People will have Outlook open and sitting in Inbox. If they don't move around no messages come in. But the moment they select a new folder/e-mail, etc. a slew of messages come in. What is the problem and how do I correct? Thank you. ...

xml using datasets
hi i am new at this...whats the better way to read xml files..create a dataset and read them using xml only for validation ... or use xmlreader to validate and read them? thanks in advance "Ramakant" <ramakant2k@yahoodontspam.com> wrote in message news:e1WM5dwMFHA.1268@TK2MSFTNGP14.phx.gbl... > hi i am new at this...whats the better way to read xml files..create a > dataset and read them using xml only for validation ... > or > use xmlreader to validate and read them? Ramakant, If you already have an ADO.NET DataSet, then it's more convenient to use ADO.N...

Excel Display formula, not result
I'm modifying a formula and rather than the result, Excel is displaying the formula -- Yes, my equals sign is in place still. The formula I'm modifying is =SUBTOTAL(9,E2:E45) to =SUBTOTAL(3,E2:E45) I'd rather not re'run the subtotal again to get the count I need unless I can get it on the same line as the sum. When I run them separate, I am getting two groups when I only want one. SPenney ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Your cell ma...

need to add large amount of selected users to a distribution group
Is there an easy way to create a distribution group by importing a list of users into it using a .csv? This is on Exchange 5.5. I'm not ready to sit here, choose and select 900 users! "dude" <dude@aol.com> wrote: >Is there an easy way to create a distribution group by importing a list of >users into it using a .csv? This is on Exchange 5.5. I'm not ready to sit >here, choose and select 900 users! You can use the directory import tool. The details are in the maintenance and troubleshooting guide you'll find in the the online docs. You'll still h...

error in using vterm sample
Hello everybody I am trying to use mscomm32 in visual c .net and so I run MSDN sample (VTerm) but it do not receive any thing plaese tell me why? I am using Win2k advance server Thanks a lot ...

Using more than one OR Statement
Hi there I am working on producing a time sheet for my work. I have the following function happening in a box : =IF(OR(B12="A/L",B12="S/L",B12="P/H",B12="FLEXI"),7/24,C12-B12-D12) where B12 = time started, C12 = time finished and D12 = length of unpaid breaks. Currently if someone puts either A/L, S/L, P/H or FLEXI it will show the value of 7.00 in the total colomn. What I would like to happen is that is somoene inputs "FLEXI" to the box that the value 0.00 would be shown. Reason being that tthis is time that a staff membe...

Selection Transparency
I just started using Excel 2007, and I am having trouble actually seeing the range of cells that I am selecting when I click and drag my mouse. Does anyone know if there is a way to make the selection darker and more visible like in Excel 2003? I have searched the options and menus and can't find anything in Excel, so maybe this is a Vista problem. Thanks There is no way to adjust the color for the highlighted cells. However if you are having difficulty, try adjusting the brightness and contrast of your monitor to a level in which you are comfortable. If you are using an LCD M...

How to use my application to download a file on a http server?
Hello,all gurus: download.htm file: <html> <title> </title> <body> <a href = "1.rmvb" >download</a> </body> </html> I use WebBrowser ActiveX in my application. CWebBrowser mWeb; mWeb.Navigate("http://1.1.1.50/web/download.htm",NULL,NULL,NULL,NULL); If I click download on WebBrowser ActiveX,My application will use IE to download.But I want to use my download application to take the place of IE.How to do I should? Thanks. Try using URLDownloadToFile. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Z.Smith" <z...

using "Last" in query desgin
I am trying to build a query that sorts data ascending order by date, so that when I select "Last" in the Total row for other fields in the query I will get the newest value (I have an account field that doesn't change but I want to retrieve the newest account description which can change from one day to the next so I want the desciption of the newest record). The problem is I don't want the date column in the query since I am trying to also trying to group and sum the data and don't want a new row of data for each date value. Any ideas? I would use Max in...

New to powerpoint 2007. How to use macros to move picture.
Hi. I am not very good at ppt but can someone tell me if there is an effect to re-size an image. What I would like to do is have a page that shrinks the image to the top left corner and then displays text. I know there are lots of special effects for images.I want the top corner of the image fixed so that the image retreates up and right. I have been asking this question for over a week now. A reply takes a day. I have a presentation ChildrensTalk.pptm On Slide 8 I have a jpg file called WaterWine.jpg Sub Test() Dim oSh As Shape ' Create a variable to hold a reference '...

Upgradeing a published website using ftp. Many questions
I published my website using vs2010. I checked the box that made it upgradeable. As a check I ftp copied default.aspx and default.aspx.vb to the site replacing the published files. Looks like it still works. So I can update the site files using ftp? I noticed there is no App_Code folder. Suppose I need to update something in that folder, can I? How? I also noticed there is a file: PrecompiledApp.config Might I ever want to delete that file? What is it for? Would it make sense to delete all the files and ftp the entire site to the host. Can a site be uplo...

I cant seem to figure out how to use Proper in excel. The help op.
I keep trying to figure out how to change data in excel so that instead of all CAPS, it will have the first letter be capatalized and the rest small cap. (its names and addresses that I'm trying to merge to a word doc.) The help option is 0 help. It errors and will not tell me how to change the whole doc at all. Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany deborahpib wrote: > I keep trying to figure out how to change data in excel so that > instead of all CAPS, it will have the first letter be capatalized and > the rest small ca...

When Using a PropertySheet in Wizard Mode Can we Change the Order of sheets at runtime
Hey Friends I am creating a Wizard Using Property Sheet According to User Selection of Data in First Page I want that only one of the PropertySheet should be visible with a Fnish Button But I have added 4 More Property Pages Whereas i want only one of them to appear. Take a look at the CPropertySheet::SetActivePage() member function. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "vikrant" <vikrant_kpr@hotmail.com> wrote in message news:OaKnxZauDHA.3532@TK2MSFTNGP11.phx.gbl... > Hey Friends > > I am creating a Wizard Using Prop...

Does anyone know how to get a read receipt from Outlook 97, 98 or 2000 when using Outlook 2003???
Does anyone know how to get a read receipt from Outlook 97, 98 or 2000 when using Outlook 2003??? Is Outlook 2003 backwards compatible??? On Mon, 24 Jan 2005 21:11:53 GMT, Paul wrote: > Does anyone know how to get a read receipt from Outlook 97, 98 or 2000 when > using Outlook 2003??? > Is Outlook 2003 backwards compatible??? Outlook 97 and 98 definitely don't support read-receipts (the Internet standard for read-receipts didn't exist when they shipped). I forget whether Outlook 2000 suports them - but I think not, for the same reason. -- Jeff Stephenson Outlook Develo...

Can I use an animated graphic in Excel 2002?
We are trying to use an alert, animated graphic in our document when a certain condition exists. For example, if a home is not ready to close, then we want an animated graphic to appear (it is alert.gif graphic). Any ideas? ...

Import text from Access results in squares at end of para
I have an Access database that I query from Excel. The fields that are defined as TeXt or Memo fields in Access have squares at the end of each paragraph in Excel. I can manually remove them from Excel, but the next time I refresh the data from Access, then they are back of course. Is there some way to prevent this or to remove the squares via an Excel macro. I tried to record a macro that would remove the squares, but it would not record my keystrokes. Laura You can create an query in access that does not include the memo fields and import that. Alternatively, I seem to remember t...

where do i get info on how to use excel?
i don't have a manual or anything. can i buy one? is there a college course or something? hi, all of the above. most good book stores with have an excel for dummys book. microsoft site has books for all version. college course tend to be generic and way too short. books are better. and cheaper. the net is a good source too. try these sites http://www.mvps.org/dmcritchie/excel/excel.htm#tutorials http://www.j-walk.com/ss/books/xlbook25.htm http://www.cpearson.com/excel/KeyboardShortcuts.htm http://www.contextures.com/ http://www.mrexcel.com http://www.rondebruin.nl/ and you can hang ar...