Check 2 date fields for a particular date?

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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')

  • Bhaskar.Shetty

    Hall of Fame

    Points: 3054

    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

  • mick burden

    SSCarpal Tunnel

    Points: 4432

    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 3 (of 3 total)

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