Select DateTime increments - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Select DateTime increments

Author

Topic

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-09-21 : 13:41:11

Hey,
I have a table with multiple Datetime records and chemistry values. What i want is to have the starting value Min(DateTime), the next 4hrs up to 20hrs then Max(datetime) 'final'.

I've tried using

Where DateTime IN (Select Min(DateTime)
                         , DateAdd(HH,4,Min(DateTime))
                         , DateAdd(HH,8,Min(DateTime))
                         , DateAdd(HH,12,Min(DateTime) 
                         , etc...
                         , Max(DateTime))
                         From Chemistry )

But it doesn't like the Mulitple values.

could someone help me with a code example? Point me in the right direction?

Larry

Everyday life brings me back to reality

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-21 : 14:02:17

One opton might be something like;



WHERE DateTime IN

( 
	SELECT 
		MIN(DateTime) theDate
	FROM Chemistry 

	UNION 

	SELECT 
		MAX(DateTime) 
	FROM Chemistry 
	
	UNION 

	SELECT 
		DateAdd(HH,4,Min(DateTime)) 
	FROM Chemistry 
	
	---etc......
)

HTH.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-09-21 : 14:19:19

Thanks! That seemed to work but when I tried it, I came across somethign I didn't think about. The times may not be "exactly" 4, 8, 12 hrs...etc.

I would need the "closest" datetime to the 4, 8,12 hr range.

here's some sample data.

Select * 
Into 
#Tmp
From (Select 1 as pId, '2011-09-14 02:39' as cDateTime Union All
	  Select 1 , '2011-09-14 04:58' Union All
	  Select 1 , '2011-09-14 10:42' Union All
		Select 1 , '2011-09-15 07:29' Union All
		Select 1 , '2011-09-16 07:57' Union All
		Select 1 , '2011-09-17 08:12' Union All
		Select 1 , '2011-09-18 08:02' Union All
		Select 1 , '2011-09-19 08:01' Union All
        Select 1 , '2011-09-20 08:53' ) a

Select *
From #Tmp

Everyday life brings me back to reality

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2011-09-22 : 11:17:14

Adding the Row_Number function looks like it would help, but I'd still need to find the hour increments

Select *,Row_Number() Over (Partition By pId Order by cDateTime ASC) as SeqNum
From #Tmp

Everyday life brings me back to reality

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources