May 22, 2013 at 11:53 pm
Hi everyone, I've created the table below to keep a record of office staff's annual leave (or vacation) each line in the table consists of the persons name, their staff code, the start of their leave and then the end date of their leave. I need to query the data in 2 ways, first of all I need to supply a date and return everyone that is off on that day, the second query I need to supply a person's name and a date to see if that individual is off on that date. Is this even possible? I don't even know where to start. Can some kind soul point me in the right direction please?
USE resource
GO
CREATE TABLE leavecard(wtname VARCHAR(50), ein VARCHAR(20),startdate datetime,endate datetime,RecID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED )
GO
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Chris Searle','802502270','01/Apr/2013','20/Apr/2013')
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Mike Smith','802502276','03/Feb/2013','12/Feb/2013')
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Tony Jones','802502277','05/Jan/2013','11/Jan/2013')
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Mike Smith','802502276','28/Feb/2013','02/Mar/2013')
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Tony Jones','802502277','20/Feb/2013','22/Feb/2013')
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Tony Jones','802502277','19/Apr/2013','01/May/2013')
INSERT INTO leavecard(wtname,ein,startdate,endate)
VALUES('Chris Searle','802502270','23/Apr/2013','09/May/2013')
May 23, 2013 at 12:12 am
First Query
declare @LeaveDate datetime
SET @LeaveDate = '04/19/2013'
select * from leavecard where @LeaveDate between StartDate and EnDate
Second Query
declare @LeaveDate datetime
declare @Name varchar(50)
SET @LeaveDate = '04/19/2013'
set @Name = 'Chris Searle'
select * from leavecard where @LeaveDate between StartDate and EnDate AND WTNAME = @Name
May 23, 2013 at 12:25 am
That's great Bhaskar, I've been looking at this on and off for days not realising how simple the solution was. Thank you so much for suppling the solution so quickly
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply