Weekday and holiday function - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Weekday and holiday function

Author

Topic

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 04:01:35

Hi, I am a newbie to SQL but I managed (with help from google and a colleague) to create SQL statement that works for us.
I am getting the Employee, Jobnr, abesence, start and stop date and days total.
I have read the Topic (Need function for weekdays)and also http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx

My problem as SQL beginner is that I do not know where to put the weekday function in my existing code

My code:
DECLARE @addvalue float
DECLARE @addvalue3 float
SET @addvalue = 1.0
SET @addvalue3 = 0.5

Select
substring (core_absence.startdate,1,8) as Startdate,
substring (core_absence.stopdate,1,8) as Stopdate,
core_user."5E0B2F9d_A66C_4B98_BE7E_91E808BA1BE7" as JOBNR,
substring(core_user.username,11,6)as Employee,
core_absence.absencetype as AbsenceType,

CASE
WHEN patindex('%middag%',core_absence.absencetype) > 0 THEN DateDiff(day, convert(datetime, Substring(startdate,1,8),112),
convert(datetime, Substring(stopdate,1,8),112))+ @addvalue3

ELSE DateDiff(day, convert(datetime, Substring(startdate,1,8),112),
convert(datetime, Substring(stopdate,1,8),112))+ @addvalue

END AS DaysTotal

FROM core_absence, core_user

WHERE core_absence.username = core_user.id AND
'20081001'<= Startdate AND Stopdate <= '20081031z'

ORDER BY Employee

BR
Bente

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 04:05:14

When I get it right that DaysTotal is without saturday and sunday next step will be holidays.. but lets start with the weekdays :-D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 05:19:23

so you need only weekdays to be counted for DaysTotal?

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 05:22:51

Yes :-D Hard to explain when I am not used to SQL and english :-D
Right now it counts all days between 2 dates, but for now I only want to count the weekedays between 2 dates.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 05:33:29

did you try the simple 1 line solution that Jeff posted in the link?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 05:38:46

This one - DateDiff(dd, @start, @end) - DateDiff(ww, @start, @end)*2
No haven't tried anything as I am not sure where to put it - before FROM or ?? :-D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 06:12:04

[code]Select
substring (core_absence.startdate,1,8) as Startdate,
substring (core_absence.stopdate,1,8) as Stopdate,
core_user."5E0B2F9d_A66C_4B98_BE7E_91E808BA1BE7" as JOBNR,
substring(core_user.username,11,6)as Employee,
core_absence.absencetype as AbsenceType,

CASE
WHEN patindex('%middag%',core_absence.absencetype) > 0 THEN DateDiff(day, convert(datetime, Substring(startdate,1,8),112),
convert(datetime, Substring(stopdate,1,8),112))- DateDiff(ww, convert(datetime, Substring(startdate,1,8),112), convert(datetime, Substring(stopdate,1,8),112))*2+ @addvalue3

ELSE DateDiff(day, convert(datetime, Substring(startdate,1,8),112),
convert(datetime, Substring(stopdate,1,8),112))- DateDiff(ww, convert(datetime, Substring(startdate,1,8),112), convert(datetime, Substring(stopdate,1,8),112))*2+ @addvalue

END AS DaysTotal

FROM core_absence, core_user

WHERE core_absence.username = core_user.id AND
'20081001'<= Startdate AND Stopdate <= '20081031z'

ORDER BY Employee[/code]

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 07:17:10

Works like a charm :-D Thanks.
Think if I had a little more experience with SQL I would have known where to put it :-D

Now next are the holidays.. I have thought about having a ekstra table with my holidays and then something with JOIN - is that a good idea or is there something easier?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 08:19:47

Yup.that should be fine.and Jeff in that link has explained what to do in query to exclude holidays too.

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 08:32:57

Yes I have read that as well. I will give it a try - and post a new topic if I can't get it to work. Thanks again. BR Bente

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-29 : 13:58:39

When designing a holiday table think of a universal holiday table. Not a year to year one.

For example:


Description            HolidayMonth    ISFIXED       HOLIDAYDAY,  HOLIDAYWEEK      DAYOFWEEK       Lastof
-----------------      --------------  -----------   -----------  ---------------  -------------   ------------
New Year               1               1             1            0                0               0
MLK day                1               0             0            3                1               0
--(Martin luther king day is the 3rd Monday of January)
Presidents day         2               0             0            3                1               0
--(President day is the 3rd Monday of Feburary)
Memorial day           5               0             0            1                1               1
--(Memorial day is the last(1) Monday of May )
..
..
..
..

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 14:33:42

Ok - that way I don't need to edit that table every year :-D I have some questions :-)
1. I understand the Description, the H.Mont, the isfixed, h.day, h.week and day of week. But what is Lastof?
2. I create that holiday calender how? By inserting some create table in the top of my existing code?
3. Afterwards, can I use the code Jeff wrote in Topic 49698?

Sorry for asking so much :-D Have a nice evening...

BenMie
Starting Member

8 Posts

Posted - 2008-10-29 : 14:35:07

Oh, lastof = last day of a month or a week - right?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources