Excluding weekends in queries

Hi
How can I exclude wenddays from a query?
I have found "SQLDMOW_WEnds" which seems to be made for that purpose?
Otherwise I guess the only way is to use something like
where day(datefield) <> 6 and day(datefield) <> 7
Any other suggestions?
regards
HenryBuild a calendar table with one column for the calendar data and other
columns to show whatever your business needs in the way of temporal
information. Do not try to calculate holidays in SQL -- Easter alone
requires too much math.
CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
fiscal_year SMALLINT NOT NULL,
fiscal_month SMALLINT NOT NULL,
w_in_year SMALLINT NOT NULL, -- SQL server is not ISO standard
holiday SMALLINT NOT NULL
CHECK(holiday IN (0,1)),
day_in_year SMALLINT NOT NULL,
..);
A calendar table for US Secular holidays can be built from the data at
this website, so you will get the three-day wends:
http://www.smart.net/~mmontes/ushols.html|||hi henry
you can do it as
SELECT datepart(w,datefield) NOT IN (1,7)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Henry" wrote:

> Hi
> How can I exclude wenddays from a query?
> I have found "SQLDMOW_WEnds" which seems to be made for that purpose
?
> Otherwise I guess the only way is to use something like
> where day(datefield) <> 6 and day(datefield) <> 7
> Any other suggestions?
>
>
> regards
> Henry
>
>|||That should be "dw" or "wday" in the DATEPART function; also look up
@.@.DATEFIRST and SET DATEFIRST before hard-coding the IN(1,7) condition.
"Chandra" wrote:
> hi henry
> you can do it as
> SELECT datepart(w,datefield) NOT IN (1,7)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Henry" wrote:
>sql

Exclude weekends

Hi I have simple SELECT query
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
and I want to exclude wends from the Duration field
any suggestions much appreciatedGary
What is a datatype of Duration column?
Is it datetime or int/smallint/tinyint/varchar/char/text/ntext?
Why not to post DDL?
"Gary Spence" <GarySpence@.discussions.microsoft.com> wrote in message
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||You could use the DATEPART function to determine if the day you are quering
is a wday.
...
WHERE DATEPART(dw, yourcolumn) IN (7,1) (Saturday and Sunday in English
settings)
The Values (7,1) depends on your language settings or the settings of your
SET @.@.DATEFIRST
Look in the help of BOL to gather more information about that.
HTH, Jens Suessmeyer.
"Gary Spence" <GarySpence@.discussions.microsoft.com> schrieb im Newsbeitrag
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||check out the datename function so you can exclude saturdays and sundays.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||set datefirst 1 -- monday = 1st day of the w
add following to the WHERE filter, to exclude both StartDateTime and
EndDateTime that falls on Sat. & Sun.
(datepart(dw, StartDateTime) in (6,7) or datepart(dw, EndDateTime) in
(6,7) )
"Gary Spence" <GarySpence@.discussions.microsoft.com> wrote in message
news:21B45505-FD93-4FD3-8479-9AE36D36D4A2@.microsoft.com...
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Hi Gary
Here is the query:
Hi I have simple SELECT query
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
AND DATEPART(dw, StartDateTime) NOT IN (1,7)
AND DATEPART(dw, EndDateTime) NOT IN (1,7)
Please let me know your comments
best Regards,
Chandra
---
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Thanks Chandra
It works great
"Chandra" wrote:
> Hi Gary
> Here is the query:
> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> AND DATEPART(dw, StartDateTime) NOT IN (1,7)
> AND DATEPART(dw, EndDateTime) NOT IN (1,7)
> Please let me know your comments
> --
> best Regards,
> Chandra
> ---
>
> "Gary Spence" wrote:
>|||Hi Gary,
I have seen many replies for your question but those would not help if the
START DATE TIME and END DATE TIME doesn’t belong to same day.
Example:-
START DATE TIME = ’29-Apr-2005 23:50’
END DATE TIME = ’02-May-2005 00:01’
I would prefer to have a function that would calculate duration in minutes
excluding wends, i.e. from SAT 00:00 hrs to SUN 23:59 hrs.
Here is the function:
create function dbo.GetWDayMinutes
(
@.dtStart datetime,
@.dtEnd datetime
)
returns int
as
begin
declare @.dtTemp datetime
declare @.intMinutes int
select @.dtTemp = dateadd(hh, - datepart(hh, @.dtStart), @.dtStart)
select @.dtTemp = dateadd(mi, - datepart(mi, @.dtTemp), @.dtTemp)
set @.intMinutes = 0
while ( @.dtTemp <= @.dtEnd )
begin
if datepart(dw, @.dtTemp) not in (1, 7)
begin
set @.intMinutes = @.intMinutes
+ datediff(mi,
case when @.dtTemp < @.dtStart then @.dtStart else @.dtTemp end,
case when dateadd(dd, 1, @.dtTemp) > @.dtEnd then @.dtEnd else dateadd(dd,
1, @.dtTemp) end)
end
set @.dtTemp = @.dtTemp + 1
end
return @.intMinutes
end
Sample:
select dbo.GetWDayMinutes ('29-Apr-2005 23:50', '02-May-2005 00:01')
this will return 11 minutes.
So you have to rewrite you statement as:
SELECT StartDateTime, EndDateTime, Machines.[Name],
SUM(dbo.GetWDayMinutes (StartDateTime, EndDateTime)) As Duration,
Quantity
,CycleTime
FROM ProductionData
INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
WHERE MachineID = 1 AND Quantity > 50
Hope this helps you.
Best Regards,
Lakshman.
"Gary Spence" wrote:

> Hi I have simple SELECT query
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(DATEDIFF(MINUTE, StartDateTime,EndDateTime)) As Duration, Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> and I want to exclude wends from the Duration field
> any suggestions much appreciated|||Hi Lakshman,
Your solution is to deduct the wend time computation from the duration.
But if to exclude all duration computation that falls on the wend, I've
suggested the following:
SELECT * FROM
( -- testing data
select '29-Apr-2005 23:50' AS d_start, '02-May-2005 00:01' AS d_end
UNION
select '14-Apr-2005 23:50' AS d_start, '15-Apr-2005 00:01' AS d_end -- only
this will record to be returned
UNION
select '11-Jun-2005 23:50' AS d_start, '12-Jun-2005 00:01' AS d_end
) a
WHERE ((datepart(dw,d_start) NOT IN (6,7) AND datepart(dw,d_end) NOT IN
(6,7)))
AND
(6 NOT BETWEEN datepart(dw,d_start) AND (datepart(dw,d_start) +
datediff(day, d_start, d_end)) OR
7 NOT BETWEEN datepart(dw,d_start) AND (datepart(dw,d_start) +
datediff(day, d_start, d_end))
)
"Lakshman" <Lakshman@.discussions.microsoft.com> wrote in message
news:DF45EB13-032D-435C-BBAD-7E51B6C0CDF3@.microsoft.com...
> Hi Gary,
> I have seen many replies for your question but those would not help if the
> START DATE TIME and END DATE TIME doesn't belong to same day.
> Example:-
> START DATE TIME = '29-Apr-2005 23:50'
> END DATE TIME = '02-May-2005 00:01'
> I would prefer to have a function that would calculate duration in minutes
> excluding wends, i.e. from SAT 00:00 hrs to SUN 23:59 hrs.
> Here is the function:
> create function dbo.GetWDayMinutes
> (
> @.dtStart datetime,
> @.dtEnd datetime
> )
> returns int
> as
> begin
> declare @.dtTemp datetime
> declare @.intMinutes int
> select @.dtTemp = dateadd(hh, - datepart(hh, @.dtStart), @.dtStart)
> select @.dtTemp = dateadd(mi, - datepart(mi, @.dtTemp), @.dtTemp)
> set @.intMinutes = 0
> while ( @.dtTemp <= @.dtEnd )
> begin
> if datepart(dw, @.dtTemp) not in (1, 7)
> begin
> set @.intMinutes = @.intMinutes
> + datediff(mi,
> case when @.dtTemp < @.dtStart then @.dtStart else @.dtTemp end,
> case when dateadd(dd, 1, @.dtTemp) > @.dtEnd then @.dtEnd else dateadd(dd,
> 1, @.dtTemp) end)
> end
> set @.dtTemp = @.dtTemp + 1
> end
> return @.intMinutes
> end
>
> Sample:
> select dbo.GetWDayMinutes ('29-Apr-2005 23:50', '02-May-2005 00:01')
> this will return 11 minutes.
> So you have to rewrite you statement as:
> SELECT StartDateTime, EndDateTime, Machines.[Name],
> SUM(dbo.GetWDayMinutes (StartDateTime, EndDateTime)) As Duration,
> Quantity
> ,CycleTime
> FROM ProductionData
> INNER JOIN Machines ON Machines.[ID] = ProductionData.MachineID
> WHERE MachineID = 1 AND Quantity > 50
> Hope this helps you.
> Best Regards,
> Lakshman.
>
> "Gary Spence" wrote:
>