Free time Btween 2 Tasks - 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

General SQL Server Forums

Database Design and Application Architecture

Free time Btween 2 Tasks

Author

Topic

CMukesh
Starting Member

1 Post

Posted - 2009-03-18 : 06:10:51

Hi All
Need some help in designing one query.
My Table structure is -

Task Table
tskid (Numeric) PK
tskname(varchar2)
StartDate (Date)
EndDate (Date)
Resource(Numeric) FK to resource table

Resource Table

ResourceID (Numeric)
ResourceName (Varchar2)

Now I want to select two dates and pull out the resources who does not have any task assigned between those dates.

Thank you all in anticipation

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-18 : 06:27:30

[code]-- Initialize user supplied parameters
DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = '20090301',
@ToDate = '20090401'

-- Show the expected output
SELECT r.ResourceID,
r.ResourceName
FROM [Resource] AS r
LEFT JOIN Task AS t ON t.[Resource] = r.ResourceID
AND t.StartDate < @ToDate
AND t.EndDate >= @FromDate
WHERE t.TskID IS NULL[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources