Dates Query

Hi
We have a work schedule table in our database that comprises of an Employee
ID, a Start Date/Time and a Finish Date/Time.
We are trying to design a query that will return the record every Monday
that it is operational.
I.e. if a record existed with the following data:
Employee Start Date/Time Finish Date/Time
Smith 4th April 2005 10:00 26th April 2005 17:00
Jones 18th April 2005 12:00 18th April 2005 16:00
the data would be returned from the query as follows
Smith 4th April 2005
Smith 11th April 2005
Smith 18th April 2005
Smith 26th April 2005
Jones 18th April 2005
5 records returned
From this i will then be able to design a function that displays how many
hours were spent on the activity each w.
Is there anyway of getting a query to do this, or is it something that i
will just have to do in VB with an array?
Thanks in advance for any help.You need a calendar table (see http://www.aspfaq.com/show.asp?id=2519) and
then it simply boils down to:
SELECT e.Employee, c.dt
FROM calendar c
INNER JOIN WorkSchedule ws
ON c.dt BETWEEN CONVERT(CHAR(8), ws.StartDate, 112) AND CONVERT(CHAR(8),
ws.EndDate, 112)
WHERE c.dayname = 'Monday'
Jacco Schalkwijk
SQL Server MVP
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
> Employee ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519
Example:
select ws.employee, ws.start
from work_schedule as ws inner join dbo.calendar as c
on c.dt >= convert(char(8), ws.start, 112) and c.dt < dateadd(day, 1,
convert(char(8), ws.finish, 112))
where c.dt = 'monday'
AMB
"Chris" wrote:

> Hi
> We have a work schedule table in our database that comprises of an Employe
e
> ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Chris
My two cents
CREATE TABLE #dates
(
col CHAR(1) NOT NULL,
sdt DATETIME NOT NULL,
fdt DATETIME NOT NULL
)
INSERT INTO #dates VALUES ('A','20050404','20050426')
SELECT DISTINCT col,dt FROM
(
SELECT col,DATEADD(day,num,'20040331')dt FROM Numbers,#dates
WHERE num <31
) AS D WHERE DATEPART(w,dt)=1
--See Steve Kass's example to find a first,second... monday in the month
create function dbo.NthWDay(
@.first datetime, -- First of the month of interest (no time part)
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of w we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(wday,@.result))%7
end
go
-- Find the 5th Thursday of August, 2002
select dbo.NthWDay('2002/08/01',5,5) as D
select datename(wday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWDay('2002/08/01',5,5) as D
) X
go
drop function NthWDay
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
Employee
> ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Thanks everyone
I had thought about creating a sort of calendar table, but didnt know
whether this was good practise, or the best way of doing it.
I guess it is.
Thanks again
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
> Employee ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>|||Thanks everyone
I had thought about creating a sort of calendar table, but didnt know
whether this was good practise, or the best way of doing it.
I guess it is.
Thanks again
Chris
"Chris" <cw@.community.nospam> wrote in message
news:%23FlMZUBRFHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hi
> We have a work schedule table in our database that comprises of an
> Employee ID, a Start Date/Time and a Finish Date/Time.
> We are trying to design a query that will return the record every Monday
> that it is operational.
> I.e. if a record existed with the following data:
>
> Employee Start Date/Time Finish Date/Time
> Smith 4th April 2005 10:00 26th April 2005 17:00
> Jones 18th April 2005 12:00 18th April 2005 16:00
> the data would be returned from the query as follows
> Smith 4th April 2005
> Smith 11th April 2005
> Smith 18th April 2005
> Smith 26th April 2005
> Jones 18th April 2005
> 5 records returned
> From this i will then be able to design a function that displays how many
> hours were spent on the activity each w.
> Is there anyway of getting a query to do this, or is it something that i
> will just have to do in VB with an array?
>
> Thanks in advance for any help.
>
>
>