Stored Procedure with Totals
-
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
-
If you want a really a good tested answer, the graphic helps a bit but a lot of well meaning folks just won't touch it. Please see the farticle at the first link in my signature line below. I've found that most folks will really bust a hump for you if you post sample data that way.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hey Jeff, I sorted this out, but thanks for your reply. I'll keep that in mind next time.
-
..Please see the farticle at
Got a slight wind problem Jeff? 😀
-
Phil Parkin (8/19/2011)
..Please see the farticle at
Got a slight wind problem Jeff? 😀
Heh... I've made that recommendation so many times that it has, indeed, become a bit of a naturally occurring bodily function. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
[/URL]Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply