Stored Procedure with Totals - HELP!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure with Totals - HELP!

Author

Topic

rankone
Starting Member

24 Posts

Posted - 2011-08-18 : 17:03:57

Hey Everyone!

I need help with a stored procedure that I am trying to formulate, I have two stored procedures that work the way I want it to but I want to break the results down further with no luck. One stored procedure gives a list of all the records with all the dates specified. The Second stored procedure takes a total of all the days depending on the VacationType & gives the total figure. I need to formulate a stored procedure that will give the total numbers of days taken off for each record & not the whole combined.

Here are my two stored procedures:
Stored Procedure #1

Stored Procedure #1
ALTER PROCEDURE [dbo].[VDisplay]
@ID int
AS
Select RecordID, AssociateID, EmployeeName, StartDate, EndDate, VacationType, DayType =
Case DayType
When 0.5 THEN 'Half Day' 
ELSE
'Full Day'
END
from dbo. Info
Where ID=@ID

Stored Procedure #2


ALTER Proc [dbo].[VTotal] 
@ID int
AS
------VARIABLES
DECLARE 
@TotalVac float,
@TotalVacLeft float
------CREATE TABLE
CREATE TABLE #tmpDisplay
(StartDate datetime,
EndDate datetime, 
TotalVac float, TotalVacLeft float,)
----------SET VARIABLES

------------INSERT INTO TABLE
INSERT INTO #tmpDisplay
(StartDate, EndDate, TotalVac)
SELECT StartDate, EndDate, CASE DayType WHEN 0.5 THEN (Datediff (d, StartDate, EndDate) + 0.5) - (select count (*) from Vac.CustomHolidays where Vac.CustomHolidays.Period >= dbo.Info.StartDate AND Vac.CustomHolidays.Period <= dbo.Info.EndDate) 
ELSE
Datediff (d,StartDate,EndDate + 1) - (select count (*) from Vac.CustomHolidays where Vac.CustomHolidays.Period >= dbo.Info.StartDate AND Vac.CustomHolidays.Period <= dbo.Info.EndDate) 
END
from dbo.Info
WHERE ID = @ID And VacationType = 'Vacation'
SET
@TotalVac = (Select Sum(TotalVac) from #tmpDisplay)
SET 
@TotalVacLeft = (Select TotalVac from dbo.Users WHERE ID = @ID) - @TotalVac
Select @TotalVac AS TotalDaysTaken, @TotalVacLeft AS TotalDaysLeft

-----------DROP TABLE
DROP TABLE #tmpDisplay

To better illustrate here's an image of the desired result with sample data

[url]http://imageshack.us/f/806/sampledata.jpg/[/url]

[/URL]
[/img]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 02:08:58

from what i understood you need to make second procedure into a user defined function which returns table of total values and call it for each row of resultset of first procedure. you can call a function for each row of resultset using apply operator as follows

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources