Help In Doing A Comparison With Current Date

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)

Help In Doing A Comparison With Current Date

Author

Topic

rankone
Starting Member

24 Posts

Posted - 2011-08-16 : 11:13:31

Hello All!

My stored procedure works to calculate total number of days of Holidays taken eliminating Weekends & Other type of holidays from the calculation which is what I want. I now want to work on the existing logic which does a comparison with the current date & shows how many days have already been taken and the ones that have entries in the database but based on the current date they haven't been taken. Such as if there is an entry scheduled for December 12th & today is August 16th, obviously it hasn't been used.

This is my stored procedure:

ALTER Proc [dbo].[Holidays] 
@UserID int
AS
------VARIABLES
DECLARE 
@Hol float,
@HolLeft float
------CREATE TABLE
CREATE TABLE #tmpHolidays
(HolStartDate datetime,
HolEndDate datetime, 
TotalHol float, TotalVacLeft float,)

------------INSERT INTO THE TABLE
INSERT INTO #tmpHol
(HolStartDate, HolEndDate, TotalHol)
SELECT HolStartDate, HolEndDate, CASE Type WHEN 0.5 THEN (Datediff (d, HolStartDate, HolEndDate) + 0.5) - (select count (*) from dbo.Weekends where dbo.Weekends.pPeriod >= dbo.Info. HolStartDate AND dbo.Weekends.pPeriod <= dbo.Info. HolEndDate) 
ELSE
Datediff (d, HolStartDate, HolEndDate + 1) - (select count (*) from dbo.Weekends where dbo.Weekends.pPeriod >= dbo.Info. HolStartDate AND dbo.Weekends.pPeriod <= dbo.Info. HolEndDate) 
END
from dbo.Info
WHERE UserID = @UserID And HolType = 'Holiday'
SET
@TotalHol = (Select Sum(TotalHol) from #tmpHol)
SET 
@TotalHolLeft = (Select TotalHol from dbo.Users WHERE UserID = @UserID) - @TotalHol
Select @TotalHol AS TotalDaysOff, @TotalHolLeft AS TotalHolLeft

-----------DROP TABLE
DROP TABLE #tmpHol

Any ideas?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 04:19:50

i think you need to modify insert like below


INSERT INTO #tmpHol
(HolStartDate, HolEndDate, TotalHol)
SELECT HolStartDate, 
CASE WHEN HolEndDate>GETDATE() THEN GETDATE() ELSE HolEndDate END, CASE Type WHEN 0.5 THEN (Datediff (d, HolStartDate, CASE WHEN HolEndDate>GETDATE() THEN GETDATE() ELSE HolEndDate END) + 0.5) - (select count (*) from dbo.Weekends where dbo.Weekends.pPeriod >= dbo.Info. HolStartDate AND dbo.Weekends.pPeriod <= CASE WHEN HolEndDate>GETDATE() THEN GETDATE() ELSE dbo.Info. HolEndDate END) 
ELSE
Datediff (d, HolStartDate, CASE WHEN HolEndDate>GETDATE() THEN GETDATE() ELSE HolEndDate END + 1) - (select count (*) from dbo.Weekends where dbo.Weekends.pPeriod >= dbo.Info. HolStartDate AND dbo.Weekends.pPeriod <= CASE WHEN HolEndDate>GETDATE() THEN GETDATE() ELSE dbo.Info. HolEndDate END) 
END
from dbo.Info
WHERE UserID = @UserID And HolType = 'Holiday'
AND HolStartDate<=GETDATE()

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources