Difficult Combining Rows queston...followup

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in grouped chunks, like below
30 minute chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:29:59, 5, 32, 19
1, 2005-10-05 06:30, 2005-10-05 06:59:59, 5, 29, 20
1, 2005-10-05 07:00, 2005-10-05 07:29:59, 5, 25, 36
1, 2005-10-05 07:30, 2005-10-05 07:59:59, 5, 34, 98
1 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
2 hour chunks
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
Since originally posting my question, I have learned from John Bell
that I can use this to solve the 1 hour problem at hand.
SELECT GROUPID,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime) AS StartTime,
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(minute,-DATEPART(minute,Starttime),Starttime),
DATEADD(millisecond,-3,DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,Starttime),Starttime)))
This makes sense for the hour instance... The hour instance also seems
to be the easiest one to solve. This query simply selects and groups
the start times to starttime-it's own minutes, end time to
starttime-it's own minutes + 1 hour. That works our very well for the
one hour case.
When I move to a two hour time window, we run into problems. Using that
exact query replacing "hour,1" for "hour, 2" will produce results that
look like this
groupID, StartTime, EndTime, Min, Max, Points
1, 2005-10-05 06:00, 2005-10-05 07:59:59, 5, 34, 168
1, 2005-10-05 07:00, 2005-10-05 08:59:59, 7, 32, 150
1, 2005-10-05 08:00, 2005-10-05 09:59:59, 6, 36, 172
This is where it gets confusing. The problem with that is that the
group by is still grouping in one hour chunks, because the 'adjusted'
start time for the second hour is not the same as the 'adjusted' start
time for the first hour. The start and end time might look ok, but the
rest of the data does not. Also, since this is a GROUP BY clause, a
working query should not produce overlapping results(in this case,
seemingly overlapping).
So for this 2 hour case (and upwards) I am looking for a solution to
get that start time query to go back
It's almost like i need to do something like IF statments in my
SELECT... not sure if that is possible or not.
Similar problems occur when you go to do half hour groupings. All four
15 minute chunks get floored and grouped together, even though you can
easily get the end time to report x:29:29.
What a mess, it seems like I am trying to do the impossible.
Any suggestions? Please ask me to clarify if necessary.
Jason
Got it. Well part of it. Here is the multiple hour verison. Yay for
integer division.
--SELECT in TWO HOUR INCREMENTS
SELECT groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))
AS Startime,
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
AS Endtime,
MIN([Min]) as MinSpeed,
MAX([Max]) as MaxSpeed,
SUM([Points]) as Total
FROM TABLE_NAME
WHERE groupID='1'
GROUP BY groupID,
DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime)),
DATEADD(millisecond,-3,DATEADD(hour,2,DATEADD(hour,-(DATEPART(hour,StartTime))+(DATEPART(hour,StartTim e)/2)*2,DATEADD(minute,-DATEPART(minute,StartTime),StartTime))))
This will work for 1,2,3,4, if you replace all instances of 2 with
1,2,3,4, etc. which can be done in code very easily.
Now to figure out the 30 minute version.
This query is so repetetive, I wish I could use variable names instead
of rewriting the whole thing... the EndTime calculation uses the whole
start time calculation. and the GROUP BY clauses are copies of what is
above. Using AS does not seem to work in these cases. Oh well.
Jason
|||On 10 Nov 2005 12:35:53 -0800, jasonsgeiger@.gmail.com wrote:

>Greetings,
>I'm working to combine rows based on a time window and I am hoping to
>be able to write a stored procedure to do this for me, rather than have
>parse through all this data in my program. I'm not very well versed
>with T-SQL syntax.. just enough to get by selecting using inner joins,
>updating and inserting... thats about it. (Hence why I am here.)
(snip)
Hi Jason,
I just posted a reply to your message in the original thread (in
microsoft.public.sqlserver.programming).
Please don't post multiple copies of the same question. I'd hate to see
someone else spend time to figure this out, becuase he or she is not
aware that I have already answered the question.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)