Calculationg times

Hi !
Following problem:
Table Times
USERID varchar
IN datetime
OUT datetime
THRA 18.05.05 18:01 18.05.05 22:00
I calculate the Hours, Minutes from IN to OUT with following function:
[dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT))
CREATE FUNCTION [dbo].GetHoursFromMinutes(@.pminutes INT)
RETURNS DECIMAL(18,2)
BEGIN
DECLARE @.hours INT,@.minutes INT
SET @.hours =@.pMinuten/60
SET @.minutes =@.pminutes%60
RETURN @.hours + (@.minutes *0.01)
END
This works. My challenge now. From 19:00 in the evening to 06:00 in the
morning they get an extra charge for nightwork. So I need an way to find out
if and how many minutes are in this timespan.
Nice would be a SQL formulat to calculate this.
Thanks for help.
ThomasCheck out this function I found on internet
CREATE FUNCTION dbo.presentDiffInHHMMSS
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
Usage:
DECLARE @.dt DATETIME
SET @.dt = '2001-04-30 17:04:32'
PRINT dbo.presentDiffInHHMMSS(@.dt, GETDATE())
DROP FUNCTION dbo.presentDiffInHHMMSS
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:5A6A2008-7CE9-4EFE-9E96-F12E64649C03@.microsoft.com...
> Hi !
> Following problem:
> Table Times
> USERID varchar
> IN datetime
> OUT datetime
> THRA 18.05.05 18:01 18.05.05 22:00
> I calculate the Hours, Minutes from IN to OUT with following function:
> [dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT))
> CREATE FUNCTION [dbo].GetHoursFromMinutes(@.pminutes INT)
> RETURNS DECIMAL(18,2)
> BEGIN
> DECLARE @.hours INT,@.minutes INT
> SET @.hours =@.pMinuten/60
> SET @.minutes =@.pminutes%60
> RETURN @.hours + (@.minutes *0.01)
> END
> This works. My challenge now. From 19:00 in the evening to 06:00 in the
> morning they get an extra charge for nightwork. So I need an way to find
out
> if and how many minutes are in this timespan.
> Nice would be a SQL formulat to calculate this.
> Thanks for help.
> Thomas|||Yes with this I can calculate the timespan between two times but I need the
minutes
of a timespan within another timespan (19:00 til 06:00)
Thomas
"Uri Dimant" wrote:

> Check out this function I found on internet
> CREATE FUNCTION dbo.presentDiffInHHMMSS
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
> Usage:
> DECLARE @.dt DATETIME
> SET @.dt = '2001-04-30 17:04:32'
> PRINT dbo.presentDiffInHHMMSS(@.dt, GETDATE())
> DROP FUNCTION dbo.presentDiffInHHMMSS
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:5A6A2008-7CE9-4EFE-9E96-F12E64649C03@.microsoft.com...
> out
>
>|||Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
Begin
Declare @.TotalHours Int
Declare @.TotalMinutes Int
Declare @.WholeDays Int
--Handle scenario where Start and EndDate are on the same day
If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
If DatePart(hh, @.StartDate) < 6 Begin
Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
Else If DatePart(hh, @.StartDate) < 19 Begin
Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Float))
As DateTime))
Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
Set @.TotalHours = @.TotalMinutes / 60
Set @.TotalMinutes = @.TotalMinutes % 60
End
If DatePart(hh, @.EndDate) > 19 Begin
Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
Else Begin
--Determine the number of whole days between the two date
Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.EndDate))
--If there are no days, then start at zero
If @.WholeDays < 0
Set @.WholeDays = 0
--We know that in each full day, there are 6 hours in the morning
--and 5 hours in the evening
Set @.TotalHours = @.WholeDays * 11
Set @.TotalMinutes = 0
--Determine the number of "magic" hours in the start date
If DatePart(hh, @.StartDate) < 19
Set @.TotalHours = @.TotalHours + 5
Else Begin
Set @.TotalHours = @.TotalHours + (24 - DatePart(hh, @.StartDate))
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
--determine the number of "magic" hours on the end date
If DatePart(hh, @.EndDate) >= 6
Set @.TotalHours = @.TotalHours + 6
Else Begin
Set @.TotalHours = @.TotalHours + DatePart(hh, @.EndDate)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
--Adjust the counts if @.TotalMinutes is greater than sixty
If @.TotalMinutes / 60 >= 1 Begin
Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
Set @.TotalMinutes = @.TotalMinutes % 60
End
Return @.TotalHours + (@.TotalMinutes * .01)
End
HTH
Thomas|||You could also refactor this solution a bit with some recursion:
Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
Begin
Declare @.Result Decimal(18,2)
Declare @.TotalHours Int
Declare @.TotalMinutes Int
Declare @.WholeDays Int
Set @.Result = 0
Set @.TotalHours = 0
Set @.TotalMinutes = 0
--Handle scenario where Start and EndDate are on the same day
If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
If DatePart(hh, @.StartDate) < 6 Begin
Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
Else If DatePart(hh, @.StartDate) < 19 Begin
Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Float))
As DateTime))
Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
Set @.TotalHours = @.TotalMinutes / 60
Set @.TotalMinutes = @.TotalMinutes % 60
End
If DatePart(hh, @.EndDate) > 19 Begin
Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
Else Begin
--Determine the number of whole days between the two date
Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.EndDate))
--If there are no days, then start at zero
If @.WholeDays < 0
Set @.WholeDays = 0
--We know that in each full day, there are 6 hours in the morning
--and 5 hours in the evening
Set @.TotalHours = @.WholeDays * 11
Set @.TotalMinutes = 0
--Get total hours from StartDate to the end of the StartDate's day
Set @.Result = GetCountSpecialHours(@.StartDate
, DateAdd(ss, -1, DateAdd(d, 1,
Cast(Floor(Cast(@.StartDate As Float))))))
--Get total hours from the beginning of the EndDate's day to the EndDate
Set @.Result = @.Result + GetCountSpecialHours(Cast(Floor(Cast(@.En
dDateAs
Float)))
, @.EndDate)
End
--Adjust the counts if @.TotalMinutes is greater than sixty
If @.TotalMinutes / 60 >= 1 Begin
Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
Set @.TotalMinutes = @.TotalMinutes % 60
End
Return @.Result + @.TotalHours + (@.TotalMinutes * .01)
End
HTH
Thomas|||I can't compile, few errors:
returns
as
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23K4Wgf$TFHA.548@.tk2msftngp13.phx.gbl...
> You could also refactor this solution a bit with some recursion:
> Create Function GetCountSpecialHours(
> @.StartDate DateTime
> , @.EndDate DateTime
> )
> Return Decimal(18,2)
> Begin
> Declare @.Result Decimal(18,2)
> Declare @.TotalHours Int
> Declare @.TotalMinutes Int
> Declare @.WholeDays Int
> Set @.Result = 0
> Set @.TotalHours = 0
> Set @.TotalMinutes = 0
> --Handle scenario where Start and EndDate are on the same day
> If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
> If DatePart(hh, @.StartDate) < 6 Begin
> Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> Else If DatePart(hh, @.StartDate) < 19 Begin
> Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As
> Float))
> As DateTime))
> Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
> Set @.TotalHours = @.TotalMinutes / 60
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> If DatePart(hh, @.EndDate) > 19 Begin
> Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> Else Begin
> --Determine the number of whole days between the two date
> Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1,
> @.EndDate))
> --If there are no days, then start at zero
> If @.WholeDays < 0
> Set @.WholeDays = 0
> --We know that in each full day, there are 6 hours in the morning
> --and 5 hours in the evening
> Set @.TotalHours = @.WholeDays * 11
> Set @.TotalMinutes = 0
> --Get total hours from StartDate to the end of the StartDate's day
> Set @.Result = GetCountSpecialHours(@.StartDate
> , DateAdd(ss, -1, DateAdd(d, 1,
> Cast(Floor(Cast(@.StartDate As Float))))))
> --Get total hours from the beginning of the EndDate's day to the
> EndDate
> Set @.Result = @.Result + GetCountSpecialHours(Cast(Floor(Cast(@.En
dDateAs
> Float)))
> , @.EndDate)
> End
> --Adjust the counts if @.TotalMinutes is greater than sixty
> If @.TotalMinutes / 60 >= 1 Begin
> Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> Return @.Result + @.TotalHours + (@.TotalMinutes * .01)
> End
>
> HTH
>
> Thomas
>
>|||Simple problem...This section:
Is missing the word "As" after it...it should read:
Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
As
Begin
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23RApBr$TFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I can't compile, few errors:
> returns
> as
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23K4Wgf$TFHA.548@.tk2msftngp13.phx.gbl...
>|||Thanks a lot. I thought that there is not a really 'easy' solution.
Its sometimes really hard for me to find a function for things which are
absolutly easy doing it by 'brain' and paper ;) !
Do you use this function in an application ?
The next step for me is now to integrate the holydays which are also 100%
(read them from a (calendartable) and to get the start and end points from
variables cause they change from business to business. Going to be a monster
function for such an 'easy' thing.
Thomas
"Thomas Coleman" wrote:

> Create Function GetCountSpecialHours(
> @.StartDate DateTime
> , @.EndDate DateTime
> )
> Return Decimal(18,2)
> Begin
> Declare @.TotalHours Int
> Declare @.TotalMinutes Int
> Declare @.WholeDays Int
> --Handle scenario where Start and EndDate are on the same day
> If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
> If DatePart(hh, @.StartDate) < 6 Begin
> Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> Else If DatePart(hh, @.StartDate) < 19 Begin
> Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Flo
at))
> As DateTime))
> Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
> Set @.TotalHours = @.TotalMinutes / 60
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> If DatePart(hh, @.EndDate) > 19 Begin
> Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> Else Begin
> --Determine the number of whole days between the two date
> Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.End
Date))
> --If there are no days, then start at zero
> If @.WholeDays < 0
> Set @.WholeDays = 0
> --We know that in each full day, there are 6 hours in the morning
> --and 5 hours in the evening
> Set @.TotalHours = @.WholeDays * 11
> Set @.TotalMinutes = 0
> --Determine the number of "magic" hours in the start date
> If DatePart(hh, @.StartDate) < 19
> Set @.TotalHours = @.TotalHours + 5
> Else Begin
> Set @.TotalHours = @.TotalHours + (24 - DatePart(hh, @.StartDate))
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> --determine the number of "magic" hours on the end date
> If DatePart(hh, @.EndDate) >= 6
> Set @.TotalHours = @.TotalHours + 6
> Else Begin
> Set @.TotalHours = @.TotalHours + DatePart(hh, @.EndDate)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> --Adjust the counts if @.TotalMinutes is greater than sixty
> If @.TotalMinutes / 60 >= 1 Begin
> Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> Return @.TotalHours + (@.TotalMinutes * .01)
> End
>
> HTH
>
> Thomas
>
>|||In regards to using that function, I just whipped up that function to solve
your
particular problem. Holidays should be a snap actually. One simple solution
is
to subtract the number of holidays between the two dates from @.WholeDays.
Thomas
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:B1A0FD1D-5AF8-4A61-98D9-47F6AF2D2B87@.microsoft.com...
> Thanks a lot. I thought that there is not a really 'easy' solution.
> Its sometimes really hard for me to find a function for things which are
> absolutly easy doing it by 'brain' and paper ;) !
> Do you use this function in an application ?
> The next step for me is now to integrate the holydays which are also 100%
> (read them from a (calendartable) and to get the start and end points from
> variables cause they change from business to business. Going to be a monst
er
> function for such an 'easy' thing.
> Thomas