Help Doing A Comparison Calculation With Current Date

Question

Help Doing A Comparison Calculation With Current Date

staindshady_03

SSChasing Mays

Points: 642
More actions
August 16, 2011 at 9:22 am

#255782

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

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Add in a Where clause that only selects dates that are prior to the current day?

Without the structure and sample data for your UserInfo table (which is what it looks like it's selecting from), I can't really suggest more than that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

It has to be easier than this. You don't need a table of weekends, you can calculate them:

DECLARE

@HolStartDate DATETIME = '17/08/2011',

@HolEndDate DATETIME = '27/08/2011'

SELECT DATEADD(dd,rn,@HolStartDate)

FROM (SELECT top 100 rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM sys.columns) [days] -- source of rows (tally table)

WHERE rn <= DATEDIFF(dd,@HolStartDate,@HolEndDate)

AND NOT DATENAME(weekday,DATEADD(dd,rn,@HolStartDate)) IN ('saturday','sunday')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GSquared SSC Guru Points: 260824 More actions · Answer 3

Chris, I think he's probably using some variation of a Calendar Table. Those are the best way to calculate things like working days and such, and it looks like a version of that. If you have one, you avoid calculating weekends, as part of avoiding the overly complex calculation of holidays. It's fun to write calculations for "1st Mondays" and "Last Thursdays" and things like that, but it performs horribly compared to a Calendar Table.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

GSquared (8/16/2011)
Chris, I think he's probably using some variation of a Calendar Table. Those are the best way to calculate things like working days and such, and it looks like a version of that. If you have one, you avoid calculating weekends, as part of avoiding the overly complex calculation of holidays. It's fun to write calculations for "1st Mondays" and "Last Thursdays" and things like that, but it performs horribly compared to a Calendar Table.

You are absolutely right of course Gus. I'm making an assumption and may well be wrong.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

staindshady_03 SSChasing Mays Points: 642 More actions · Answer 5

I got it working, I basically put a Where Clause ColName >= GetDate()

Thanks for all your replies.