April 15, 2009 at 1:04 am
Hi ,
for a process the time frame is
-StartDate-|-EndDate-|--Starttime-|-EndTime-
-15/04/09-|-16/04/09-|---10:00---|--14:30--
both days the total hrs spent on the process is 9 hrs
Now how to get the result via query as 9 hrs?
Plz help me...
Thanks in advance..
April 15, 2009 at 1:17 am
Hi
How do you get 9 hrs. Is it stored somewhere in the db.
"Keep Trying"
April 15, 2009 at 1:53 am
New Commer (4/15/2009)
Hi ,for a process the time frame is
-StartDate-|-EndDate-|--Starttime-|-EndTime-
-15/04/09-|-16/04/09-|---10:00---|--14:30--
both days the total hrs spent on the process is 9 hrs
Now how to get the result via query as 9 hrs?
Plz help me...
Thanks in advance..
hmm ... how do you determine the process time to 9 hrs ??
I hope you have your date or time columns stored using the (small)datetime datatype.
This way you have all datetime related functions to your availability!
Check this little test
Declare @SSC table (IdNo int identity(1,1) not null, StartDate datetime not null, EndDate datetime NULL, StartTime datetime not null, EndTime datetime NULL)
Insert into @SSC ( StartDate, EndDate, StartTime,EndTime )
values ('2009-04-15', '2009-04-16', '10:00:00', '14:30:00' )
Insert into @SSC ( StartDate, EndDate, StartTime,EndTime )
values ('2009-04-14', '2009-04-15', '09:00:00', '12:30:00' )
Select *
, StartDate + StartTime as StartDateTime
, EndDate + EndTime as StartDateTime
, datediff(hh, StartDate + StartTime , EndDate + EndTime ) as ElapsedFullHours
from @SSC
Select *
from @SSC
where datediff(hh, StartDate + StartTime , EndDate + EndTime ) = 27
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2009 at 3:01 am
Hi i got tht 9 hrs by manually calculating the hrs between 10:00 to 2:30 for 2 days. For a day its 4.30 hrs, so for 2 days its 9 hrs.
April 15, 2009 at 3:07 am
HI ALZDBA, Thks for ur Query.
But ur query is not giving me the required result,
for i need to get only the hrs as single result n not any other col values.
i tried talking onle the datediff func alone but tht too not helping me.
Plz Help me...
April 15, 2009 at 4:34 am
Do you include weekends and national holidays in your calculation, or just working days?
April 15, 2009 at 4:57 am
No ll not consider the holidays for only the day on which the process gonna take place ll be entered into table.
April 15, 2009 at 5:28 am
If you're sure you don't need to consider holidays and weekends then the following will give the duration in hours, only counting the time interval (@StartTime, @EndTime) on each day.
/* Test data */
CREATE TABLE #TestData (
Id int IDENTITY(1, 1) PRIMARY KEY,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
INSERT INTO #TestData (StartDate, EndDate, StartTime, EndTime)
SELECT '20090415', '20090416', '10:00', '14:30' UNION ALL
SELECT '20090415', '20090415', '08:00', '16:50' UNION ALL
SELECT '20090415', '20090416', '14:00', '10:30'
/* Specify time range to include */
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime = '10:00', @EndTime = '14:30'
/* Calculate duration in hours for test data */
SELECT
CAST(DATEDIFF(minute, @StartTime, @EndTime) * DATEDIFF(day, StartDate, EndDate)
+ DATEDIFF(minute,
CASE WHEN StartTime < @StartTime THEN @StartTime
WHEN StartTime < @EndTime THEN StartTime
ELSE @EndTime END,
CASE WHEN EndTime < @StartTime THEN @StartTime
WHEN EndTime < @EndTime THEN EndTime
ELSE @EndTime END
) AS numeric(10, 2)
) / 60 AS DurationHours
FROM #TestData
April 15, 2009 at 10:46 pm
Dear Andrew
i am bit confused, with declaration of
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime = '10:00', @EndTime = '14:30'
I am unable to modify without this declaration. the values of start and end time both should be taken frm table, ihave an idea like the below, can it be modified? plz guide,
Select (Convert(numeric(10,0),DateDiff(day,startdate,enddate]) + 1)) * Convert(numeric(10,2),(endtime-starttime)) where id = 3
so that let the difference of date be multiplied with the differences of time which should be hours.
but wenever i put select (Endtime-starttime) its giving a big value which is not correct hours. i know i may be wrong somwhre kindly help me to get rid of this...plzzzz..
April 16, 2009 at 12:37 am
You need to add the date column to your time column or you will end up with negative times
Work with datetime datatype so at the end you just need to use
Select datepart(hh, @datetimeResult) as NoHours
DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @TimeDiff datetime
SELECT @StartTime = '10:00', @EndTime = '14:30'
Select @TimeDiff = @EndTime - @StartTime
Select convert(char(23), @StartTime, 121) StartTime
, convert(char(23), @EndTime, 121) EndTime
, convert(char(23), @TimeDiff, 121) TimeDiff
/*
results:
StartTime EndTime TimeDiff
----------------------- ----------------------- -----------------------
1900-01-01 10:00:00.000 1900-01-01 14:30:00.000 1900-01-01 04:30:00.000
(1 row(s) affected)
*/
SELECT @StartTime = '10:00', @EndTime = '02:30'
Select @TimeDiff = @EndTime - @StartTime
Select convert(char(23), @StartTime, 121) StartTime
, convert(char(23), @EndTime, 121) EndTime
, convert(char(23), @TimeDiff, 121) TimeDiff
/*
StartTime EndTime TimeDiff
----------------------- ----------------------- -----------------------
1900-01-01 10:00:00.000 1900-01-01 02:30:00.000 1899-12-31 16:30:00.000
(1 row(s) affected)
*/
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 16, 2009 at 2:47 am
New Commer (4/15/2009)
Dear Andrewi am bit confused, with declaration of
DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime = '10:00', @EndTime = '14:30'
I am unable to modify without this declaration. the values of start and end time both should be taken frm table, ihave an idea like the below, can it be modified? plz guide,
Select (Convert(numeric(10,0),DateDiff(day,startdate,enddate]) + 1)) * Convert(numeric(10,2),(endtime-starttime)) where id = 3
so that let the difference of date be multiplied with the differences of time which should be hours.
but wenever i put select (Endtime-starttime) its giving a big value which is not correct hours. i know i may be wrong somwhre kindly help me to get rid of this...plzzzz..
Sorry, I think I misinterpreted your requirements previously. I thought that the combination of the StartDate and StartTime columns defined the start of a process and the combination of the EndDate and EndTime columns defined the end of a process, and you wanted to calculate the cumulative duration for the process between these two date/times, but only considering the time interval 10:00 to 14:30 on any particular day.
Now, I believe that you want to calculate the total time for a process that runs during the same time interval on a number of consecutive days. This second requirement is much simpler and you almost got the query correct. This query should give the results you are expecting.
SELECT CAST(DATEDIFF(minute, StartTime, EndTime) * (1 + DATEDIFF(day, StartDate, EndDate)) AS numeric(10, 2)) / 60 AS DurationHours
FROM #TestData
Normally I don't advocate splitting datetime values into 2 columns that store the date and time components separately, but if I have now interpreted your requirements correctly, having separate columns for date and time is the correct data model.
April 16, 2009 at 3:14 am
Hello,
Try this
CREATE TABLE #TestData (
Id int IDENTITY(1, 1) PRIMARY KEY,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
INSERT INTO #TestData (StartDate, EndDate, StartTime, EndTime)
SELECT '20090415', '20090416', '10:00', '14:30' UNION ALL
SELECT '20090415', '20090415', '08:00', '16:50' UNION ALL
SELECT StartDate, EndDate,
CONVERT(NVARCHAR,StartTime,108) AS StartTime,
CONVERT(NVARCHAR,EndTime,108) AS EndTime,
ROUND(CAST(DATEDIFF(MINUTE,StartTime,EndTime) AS DECIMAL(8,2))/CAST(60 AS DECIMAL(8,2)),2) * (DATEDIFF(DAY,StartDate,EndDate) + 1) AS HoursWork
FROM #TestData
Hope helpful...
April 16, 2009 at 3:25 am
kokkula (4/16/2009)
Hello,Try this
CREATE TABLE #TestData (
Id int IDENTITY(1, 1) PRIMARY KEY,
StartDate datetime NOT NULL,
EndDate datetime NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL
)
INSERT INTO #TestData (StartDate, EndDate, StartTime, EndTime)
SELECT '20090415', '20090416', '10:00', '14:30' UNION ALL
SELECT '20090415', '20090415', '08:00', '16:50' UNION ALL
SELECT StartDate, EndDate,
CONVERT(NVARCHAR,StartTime,108) AS StartTime,
CONVERT(NVARCHAR,EndTime,108) AS EndTime,
ROUND(CAST(DATEDIFF(MINUTE,StartTime,EndTime) AS DECIMAL(8,2))/CAST(60 AS DECIMAL(8,2)),2) * (DATEDIFF(DAY,StartDate,EndDate) + 1) AS HoursWork
FROM #TestData
Hope helpful...
If rounding is required, it should be done after multiplying by the number of days, not before, otherwise you will be multiplying the fractional rounding error.
April 16, 2009 at 3:38 am
I agree with the solution of Andrew. BUT when I had this problem in my company, I was asked to return the exact Sum of time as TIME.
For example, if someone worked One hour and 30 Minutes, the result should NOT be 1.5 (Mathematical Fraction). It should be 1:30 and so on.
For this reason, I creted the loop on the sum of all times to get it into the format. I have changed the code of ALZDBA to get it for you...
Declare @LoginTime numeric(18,2)
Declare @SSC table (IdNo int identity(1,1) not null, StartDate datetime, EndDate datetime, StartTime datetime not null, EndTime datetime NULL)
Insert into @SSC ( StartDate,EndDate,StartTime,EndTime )
values ('2009-04-15', '2009-04-16', '10:00:00', '14:15:00' )
Insert into @SSC ( StartDate,EndDate,StartTime,EndTime )
values ('2009-04-14', '2009-04-15','09:00:00', '12:00:00' )
Insert into @SSC ( StartDate,EndDate,StartTime,EndTime )
values ('2009-04-14', '2009-04-14','09:00:00', '10:00:00' )
--SELECT Sum(CAST(DATEDIFF(minute, StartTime, EndTime) * (1 + DATEDIFF(day, StartDate, EndDate)) AS numeric(10, 2)) / 60) AS DurationHours
--FROM @SSC
Select @LoginTime = Sum(c1*c2) from (
Select Sum(DateDiff(second,StartTime,EndTime)) c1, DateDiff(dd,StartDate,EndDate)+1 c2
from @SSC
Group By StartDate,EndDate,StartTime,EndTime
)aa
Select @LoginTime = @LoginTime /60
--Select @LoginTime
Declare @a7 varchar(100)
Declare @a11 numeric(18,2)
Declare @a6 int
Set @a6 = 0
Set @a7 = ''
while 1=1
begin
Set @a11 = Cast(@LoginTime/60 as numeric(18,2))
if @a11 >= 1
begin
Set @a6 = @a6 + 1
Set @LoginTime = @LoginTime - 60
end
else
begin
Set @a7 = Cast(@a6 as varchar(10)) + ':' + Case when Len(Cast(Cast(@LoginTime as int) as Varchar(10))) > 1 then Cast(Cast(@LoginTime as int) as Varchar(10)) else '0' + Cast(Cast(@LoginTime as int) as Varchar(10)) end
BREAK
end
end
Select @a7
Atif Sheikh
April 16, 2009 at 4:05 am
Wow, a WHILE loop just to convert a decimal fraction to a string representation of hours and minutes.
I have 2 comments:
1) Formatting should be done on the front end rather than the server, if possible.
2) If you have to do the formatting on the server, don't use a WHILE loop for this. The following query will format the duration as HHH:MM
SELECT Id, DurationMinutes, CONVERT(varchar(10), DurationMinutes / 60) + ':' + RIGHT('0' + CONVERT(varchar(2), DurationMinutes % 60), 2) AS DurationFormatted
FROM (
SELECT Id, DATEDIFF(minute, StartTime, EndTime) * (1 + DATEDIFF(day, StartDate, EndDate)) AS DurationMinutes
FROM #TestData
) Duration
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply