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:
>