vojkocendak
asked on
MSSQL : group data daily with variable start hour
We want to group data daily but with start hour let's say 8am .. 7am next day:
Normally we're using for daily group by with start hour = 0:
select
EXTRACT(YEAR FROM V.DateTimeField) as YY,
EXTRACT(MONTH FROM V.DateTimeField) as MM,
EXTRACT(DAY FROM V.DateTimeField) as DD,
EXTRACT(HOUR FROM V.DateTimeField) as HH,
MIN(V.DateTimeField) as CAS_MIN
FROM MYTABLE as V
Where DateTimeField>=:StartTime AND DateTimeField<=:EndTime
GROUP BY DATEPART(YEAR , V.DateTimeField),
DATEPART(MONTH , V.DateTimeField),
DATEPART(DAY , V.DateTimeField)
order by YY,MM,DD asc
How can we make SQL that would group for each day but the start hour as variable ?
thank you,
Vojko Cendak
Normally we're using for daily group by with start hour = 0:
select
EXTRACT(YEAR FROM V.DateTimeField) as YY,
EXTRACT(MONTH FROM V.DateTimeField) as MM,
EXTRACT(DAY FROM V.DateTimeField) as DD,
EXTRACT(HOUR FROM V.DateTimeField) as HH,
MIN(V.DateTimeField) as CAS_MIN
FROM MYTABLE as V
Where DateTimeField>=:StartTime AND DateTimeField<=:EndTime
GROUP BY DATEPART(YEAR , V.DateTimeField),
DATEPART(MONTH , V.DateTimeField),
DATEPART(DAY , V.DateTimeField)
order by YY,MM,DD asc
How can we make SQL that would group for each day but the start hour as variable ?
thank you,
Vojko Cendak
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this is not working for me. Could you please make yourself a full SQL with results.
Here's mine:
=== Working example with hour=0
select
DATEPART(YEAR ,V.CAS) as YY,
DATEPART(MONTH ,V.CAS) as MM,
DATEPART(DAY ,V.CAS) as DD,
SUM(V.ST0_poraba) as V_SUM
FROM dbo.MERITVE as V
Where DATEPART(YEAR,V.CAS)=2014 and DATEPART(MONTH,V.CAS)=5
GROUP BY DATEPART(YEAR,V.CAS),DATEP
order by YY,MM,DD asc
=== Error example with hour=8, I guess
select
DATEPART(YEAR ,V.CAS) as YY,
DATEPART(MONTH ,V.CAS) as MM,
DATEPART(DAY ,V.CAS) as DD,
SUM(V.ST0_poraba) as V_SUM
FROM dbo.MERITVE as V
Where DATEPART(YEAR,V.CAS)=2014 and DATEPART(MONTH,V.CAS)=5
GROUP BY DATEPART(YEAR,DATEADD(hour
order by YY,MM,DD asc
error:
Msg 8120, Level 16, State 1, Line 4
Column 'dbo.MERITVE.Cas' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
thank you
Here's mine:
=== Working example with hour=0
select
DATEPART(YEAR ,V.CAS) as YY,
DATEPART(MONTH ,V.CAS) as MM,
DATEPART(DAY ,V.CAS) as DD,
SUM(V.ST0_poraba) as V_SUM
FROM dbo.MERITVE as V
Where DATEPART(YEAR,V.CAS)=2014 and DATEPART(MONTH,V.CAS)=5
GROUP BY DATEPART(YEAR,V.CAS),DATEP
order by YY,MM,DD asc
=== Error example with hour=8, I guess
select
DATEPART(YEAR ,V.CAS) as YY,
DATEPART(MONTH ,V.CAS) as MM,
DATEPART(DAY ,V.CAS) as DD,
SUM(V.ST0_poraba) as V_SUM
FROM dbo.MERITVE as V
Where DATEPART(YEAR,V.CAS)=2014 and DATEPART(MONTH,V.CAS)=5
GROUP BY DATEPART(YEAR,DATEADD(hour
order by YY,MM,DD asc
error:
Msg 8120, Level 16, State 1, Line 4
Column 'dbo.MERITVE.Cas' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
thank you
Sorry. I thought it would be OK as long as the GROUP BY included the datetime field, but it doesn't work because there are two different datetime values, the original value in the SELECT and the adjusted value in the GROUP BY.
Since you're trying to use the adjusted date in the GROUP BY, and the original date in the SELECT, you will (presumably) end up with situations like some rows that are dated after 8 AM on 05/10, so the adjusted date is still 05/10, and some rows that are dated before 8 AM on 05/11, so the adjusted date for those is also 05/10, so they would be grouped together. But they have 2 different original dates (05/10 and 05/11).
If they're grouped together, you can only have one set of values in YY, MM and DD for those rows, so which date would you want to see -- 05/10 or 05/11 ? Maybe you could just use MIN or MAX on the DATEPART results in the SELECT.
Also, just a side question:
In your example code, you're looking for dates in May, 2014. What if there was a row dated at 7 AM on 05/01. When you subtracted 8 hours, that would be 11 PM on 04/30. Should that row be excluded, since the adjusted date is not in May?
James
Since you're trying to use the adjusted date in the GROUP BY, and the original date in the SELECT, you will (presumably) end up with situations like some rows that are dated after 8 AM on 05/10, so the adjusted date is still 05/10, and some rows that are dated before 8 AM on 05/11, so the adjusted date for those is also 05/10, so they would be grouped together. But they have 2 different original dates (05/10 and 05/11).
If they're grouped together, you can only have one set of values in YY, MM and DD for those rows, so which date would you want to see -- 05/10 or 05/11 ? Maybe you could just use MIN or MAX on the DATEPART results in the SELECT.
Also, just a side question:
In your example code, you're looking for dates in May, 2014. What if there was a row dated at 7 AM on 05/01. When you subtracted 8 hours, that would be 11 PM on 04/30. Should that row be excluded, since the adjusted date is not in May?
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yeah, that will probably run, because it uses the same value (-8 hours) in the SELECT and GROUP BY. If that gives you what you need, great. I was assuming, perhaps incorrectly, that you wanted to use the original values (eg. DATEPART(YEAR ,V.CAS) as YY) in the SELECT, but group by the modified values. Trying to do both at the same time is what was causing the problem.
James
James
ASKER
thank you James,
DATEPART was our starting point, because we're new to this.
But you gave us starting idea with -8 to begin with. How do we proceed with points (we're also new to that one ...)
Vojko
DATEPART was our starting point, because we're new to this.
But you gave us starting idea with -8 to begin with. How do we proceed with points (we're also new to that one ...)
Vojko
If you're grouping by the day and not the hour, did you try David's suggestion for the GROUP BY? That would probably be more efficient than converting the date to a string, although if you're also converting the date to a string in the SELECT, then it may not make much difference if you do it in the GROUP BY too. But I'd give it a try.
As for the points, you could accept your own post with the new code as the solution, and any posts from myself or David that you think helped could be "assists", and split the points however you see fit.
James
As for the points, you could accept your own post with the new code as the solution, and any posts from myself or David that you think helped could be "assists", and split the points however you see fit.
James
ASKER
It started to think with the comments of James and David. The solution I found was from another web source, which helped me to experiment and find right one.
ASKER
We'll try.
To make it clear what we need:
one sql with :StartDate (day 1) to :EndDate (day n) and :StartHour
day 1 - 8am (grouped data from >= 8am and < 8am day 2)
day 2 - 8am ...
day 3 - 8am ...
...
day n - 8am ...