Problem with SQL query - 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

SQL Server Development (2000)

Problem with SQL query

Author

Topic

greg_quinn
Starting Member

5 Posts

Posted - 2002-02-11 : 05:55:22

Hi,

I have table in which all the timesheet entries for employees are recorded. Every day the employee must fill in the timesheet and then it gets added to the table. So if the employee adds his timesheets for the 1st, 2nd and 3rd of February, Lets assume the 2nd and 3rd of feb are Saturday and Sunday so they don't need to fill in a timesheet.the table will now look like this...

EmployeeID theDate
1 2/1/2002
1 2/4/2002
1 2/5/2002

What I need to do i query which employee has got all their timesheets filled in on a certain set of dates. So in this case we would need to see if EmployeeID 1 has filled in all neccessary timesheets from the 1st to the 5th. I cannot use a <= >= Clause as then the 2nd and 3rd of Feb would invalidate the query. How do I return EmployeeID 1 as having returned all their timesheets? I've tried SELECT EmployeeID From table where theDate = '2/1/2002' AND theDate = '2/4/2002' AND the Date = '2/5/2002' but that doesn't work.

So how would I make sure that EmployeeID 1 only gets returned if he has the above dates entered in the table?

Greg

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-11 : 06:55:11


SELECT EmployeeID
FROM Timesheet
WHERE theDate BETWEEN @firstDate AND @lastDate
GROUP BY EmployeeID
HAVING COUNT(DISTINCT theDate) = DATEDIFF(day, @firstDate, @lastDate) + 1

You can drop the DISTINCT if (EmployeeID, theDate) has a unique constraint.
Usual caveats apply concerning BETWEEN and dates that have a time component.

Edited by - Arnold Fribble on 02/11/2002 07:03:13

greg_quinn
Starting Member

5 Posts

Posted - 2002-02-11 : 07:17:57

SELECT EmployeeID
FROM TimeSheet_ClientHours
WHERE (theDate BETWEEN '2/5/2002' AND '2/10/2002')
GROUP BY EmployeeID
HAVING (COUNT(DISTINCT theDate) = DATEDIFF(day, '2/5/2002', '2/10/2002') + 1)

Hi Arnold,

I tried your query but it returned 0 results. The table I queried against looks like this...

EmployeeID theDate
1 2/6/2002
1 2/7/2002
1 2/8/2002
1 2/9/2002

Is there no basic way I can just return employeeID 1 if he has all these dates? I have generated a string which has all weekdays between two dates, can't I just tack this string onto the query in the form of SELECT Employee From TimeSheet_ClientHours WHERE theDate IN ('2/6/2002, '2/7/2002', '2/8/2002', '2/9/2002') etc? Is there no ways I make the IN clause work so all the dates in the IN clause are required for the employeeID tro be returned?

Greg

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 07:33:19

Hi Greg,

You can store all the weekend dates in a separate table instead of a string and modify your query accordingly, this way.

SELECT EmployeeID FROM Timesheet
WHERE theDate BETWEEN @firstDate AND @lastDate GROUP BY EmployeeID
HAVING
( DATEDIFF(day, @firstDate, @lastDate)= COUNT(DISTINCT theDate) +(select count(*) from weekendates where trndate between @firstdate and @lastdate))

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/11/2002 07:34:21

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-11 : 07:42:51

Sorry Greg! Obviously not very awake today.

greg_quinn
Starting Member

5 Posts

Posted - 2002-02-11 : 08:01:35

quote:

You can store all the weekend dates in a separate table instead of a string and modify your query accordingly, this way.

Hi Nazim,

The reason I cannot store all weekend dates in a table is because they could go back for years. I would prefer to do it with a string cos it gets built at runtime and can be really short, or really long if need be.

Okay, let me try and make this even easier, we have our infamous table...

EmployeeID theDate
1 2/1/2002
1 2/4/2002
1 2/5/2002
2 2/4/2002

Okay, how do I return Employee 1 from this query? Employee 1 has filled in all timesheets up to date. Employee 2 will not be returned cos he did not fill in timesheets for 2/1/2002 and 2/5/2002?

I would think this problem to be really simple, but obviously not :(

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 08:11:20

instead of a regular table you can create a temp table and insert your weekend dates on the fly and after the join drop the table.

SELECT EmployeeID FROM Timesheet
WHERE theDate BETWEEN @firstDate AND @lastDate GROUP BY EmployeeID
HAVING
( DATEDIFF(day, @firstDate, @lastDate)= COUNT(DISTINCT theDate) +(select count(*) from #weekendates where trndate between @firstdate and @lastdate))

Another Option will be if you know the exect no. of weekends in that range of dates , you can substract it straightaway.

SELECT EmployeeID FROM Timesheet
WHERE theDate BETWEEN @firstDate AND @lastDate GROUP BY EmployeeID
HAVING
( DATEDIFF(day, @firstDate, @lastDate)= COUNT(DISTINCT theDate) + @noofdatesinweekend)

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Edited by - Nazim on 02/11/2002 08:16:05

greg_quinn
Starting Member

5 Posts

Posted - 2002-02-11 : 08:35:57

SELECT EmployeeID
FROM TimeSheet_ClientHours
WHERE (theDate BETWEEN '2/5/2002' AND '2/12/2002')
GROUP BY EmployeeID
HAVING (DATEDIFF(day, '2/5/2002', '2/12/2002') = COUNT(DISTINCT theDate) +
(SELECT COUNT(*)
FROM TimeSheet_WeekendDates
WHERE WeekendDate BETWEEN '2/5/2002' AND '2/12/2002'))

Nazim, this query does not work either, here are the two tables I queried against...

#TimeSheet_ClientHours

EmployeeID theDate
1 2/6/2002
1 2/7/2002
1 2/8/2002
1 2/11/2002

*Note : 9th and 10th are a weekend.

#Timesheet_WeekendDates

WeekendDate
2/9/2002
2/10/2002

Query does not work though?

Greg

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 08:40:25

Add one to the count

SELECT EmployeeID FROM Timesheet
WHERE theDate BETWEEN @firstDate AND @lastDate GROUP BY EmployeeID
HAVING
( DATEDIFF(day, @firstDate, @lastDate)= 1+COUNT(DISTINCT theDate) +(select count(*) from #weekendates where trndate between @firstdate and @lastdate))

HTH

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

greg_quinn
Starting Member

5 Posts

Posted - 2002-02-11 : 09:00:26

Hi Nazim,

Thank you very much, the +1 worked great. This is quite a difficult query I must say!!!

Thanks again

Nazim
A custom title

1408 Posts

Posted - 2002-02-11 : 09:07:53

Not Really, if you look at some of the queries Arnold has written recently.

for Sample check this one
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12775

Surely , the Guy Rocks

--------------------------------------------------------------
"Happiness is not something you experience, it's something you remember."

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-11 : 09:13:44

Had some coffee now!


SELECT EmployeeID
FROM Timesheet
WHERE theDate BETWEEN @firstDate AND @lastDate
  AND DATEPART(dw, theDate) <= 5
GROUP BY EmployeeID
HAVING COUNT(DISTINCT theDate) =
  DATEDIFF(day, @firstdate, @lastdate)
    - DATEDIFF(wk, @firstdate, @lastdate)*2
    - CASE WHEN DATEPART(dw, @lastdate) = 7 THEN 1 ELSE 0 END
    - CASE WHEN DATEPART(dw, @firstdate) = 1 THEN 1 ELSE 0 END
    + 1

Relies on @@DATEFIRST = 7

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources