Need help with query anyone ???

Question

Need help with query anyone ???

karan_kochhar

Newbie

Points: 7
More actions
August 14, 2014 at 6:33 pm

#292229

hi guys i am trying to write a query which will give me all employees who are not on leave during a given period (start and end date) i have attached the table file with data employee table leave taken tableAny help will be much appreciated

Viewing 4 posts - 1 through 3 (of 3 total)

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

danrst171 Mr or Mrs. 500 Points: 571 More actions · Answer 1

I *think* the below will work, I haven't tested it but if not it shouldn't be far off (it's so close to the weekend :-D). I've guessed the Start and End date columns are varchar values, if not just declare them as their actual type. I also assumed you'd want to include the dates you wanted to search between.

Just pop a start and end date in the variable and alter the columns you want outputting.

DECLARE @Startdate VARCHAR(10)

,@Enddate VARCHAR(10)

SET @Startdate = ''

SET @Enddate = ''

SELECT

[Id Number]

,[Family Name]

,[Given Name]

,[Title]

FROM

LeaveTaken

WHERE

(

[Start Date] BETWEEN @startdate AND @enddate

)

OR

(

[End Date] BETWEEN @startdate AND @enddate

)

OR

(

[Start Date] < @startdate

AND

[End Date] > @enddate

)

-------------------------------------------------
Trainee DBA

danrst171 Mr or Mrs. 500 Points: 571 More actions · Answer 2

I've just thought about it and updated the above. I missed out if someone was on holiday for the entire period that you want to search. I also change the other WHERE criteria to be a BETWEEN instead of greater and less than.

-------------------------------------------------
Trainee DBA

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

SELECT

[Id Number],

[Family Name],

[Given Name],

Title,

[Timesheet Date] = MAX([Timesheet Date]),

[Start Date],

[End Date]

FROM YourVacationTable

WHERE [Start Date] <= @EndDate

AND [End Date] >= @StartDate

GROUP BY [Id Number],[Family Name],[Given Name],Title,[Start Date],[End Date]

^{“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