Date difference and check minute value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date difference and check minute value

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-21 : 10:50:17

I have two columns a start and and end time.

I first need to parse the table and check for the minute
value 0, 15, 30. This will set my @minutevalue either on the hour, quarter or half

Then upon getting the correct minute value I need to get the time
difference based on @minutevalue

so if:
starttime=8:30
endtime=9:30
@minutevalue= half hour
differece=2

same example setting the @minutevalue on the quarter
difference = 4

I have played with the case and if exists to get the @minutevalue
but am looking for any suggestions to tie this together.
below is my two fields they are both smalldatetime.

starttime endtime
----------------------- -------------------
2001-12-17 06:00:00 2001-12-17 07:00:00
2001-12-17 08:00:00 2001-12-17 09:00:00
2001-12-17 14:00:00 2001-12-17 10:00:00
2001-12-17 08:00:00 2001-12-17 09:00:00
2001-12-17 10:00:00 2001-12-17 11:00:00
2001-12-17 09:00:00 2001-12-17 10:00:00
2001-12-17 13:00:00 2001-12-17 14:00:00
2001-12-17 11:00:00 2001-12-17 12:00:00

THANKS as always in advance

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-21 : 12:41:55

Try something like this...


declare @MinuteFlag int, @Difference float

SELECT @Difference = datediff(mi, '12/21/2001 10:00:00', '12/21/2001 11:30:00')
SELECT @MinuteFlag = datepart(mi, '12/21/2001 10:15:00')

If @MinuteFlag = 0 BEGIN
	SET @MinuteFlag = 60
END

SELECT @Difference / @MinuteFlag

-------------------
It's a SQL thing...

tribune
Posting Yak Master

105 Posts

Posted - 2001-12-21 : 12:57:41

DECLARE @StartTime datetime
DECLARE @EndTime datetime
DECLARE @MinuteValue int
DECLARE @Difference int

SELECT @StartTime = '2001-12-17 06:00:00'
SELECT @EndTime = '2001-12-17 07:30:00'
SELECT @MinuteValue = DateDiff(minute,@StartTime,DateAdd(minute,DatePart(minute,@EndTime),@StartTime))
SELECT @Difference = DateDiff(minute,@StartTime,@EndTime) / (CASE @MinuteValue WHEN 0 THEN 60 ELSE @MinuteValue END)
PRINT @MinuteValue
PRINT @Difference

barmalej
Starting Member

40 Posts

Posted - 2001-12-21 : 14:08:50

Try this. First of all this SELECT checks your @minutevalue against Starttime and calculates then.

SELECT 'Start'=starttime , 'End'=endtime,
'Result'=DATEDIFF(mi, starttime, endtime) / COALESCE(NULLIF(DATEPART (mi, starttime), 0), 60)
FROM YourTable
ORDER BY starttime , endtime

--------------

If you need check against both Starttime and Endtime you may for example UNION both tables and then take MAX() value grouping by starttime, endtime. Something:

SELECT max(tmp.result)
FROM (SELECT .... --Against Starttime
UNION
SELECT ... -- Against Endtime) tmp
GROUP BY tmp.Starttime, tmp.Endtime
ORDER BY tmp.Starttime, tmp.Endtime

There are other ways also as you need MAX of the two values

Hope this will help

skillile
Posting Yak Master

208 Posts

Posted - 2001-12-21 : 14:13:52

thanks everybody, i am playing with all solutions
to find the best way. I think you've given me enough
to get there.

:)

slow down to move faster...

tribune
Posting Yak Master

105 Posts

Posted - 2001-12-21 : 15:32:05

pick me pick me
mine is more efficient :P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources