Solved

MSSQL : group data daily with variable start hour

Posted on 2014-04-23
10
518 Views
Last Modified: 2014-05-11
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
0
Comment
Question by:vojkocendak
  • 5
  • 4
10 Comments
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 40019580
The first thing that comes to mind is to use DATEADD in the GROUP BY.  Let's say that you had an int parameter named start_hour that would be 7 for 7 AM, 8 for 8 AM, etc.  The GROUP BY might be:

GROUP BY DATEPART(YEAR , DATEADD(hour, 0 - @start_hour, V.DateTimeField)),
            DATEPART(MONTH , DATEADD(hour, 0 - @start_hour, V.DateTimeField)),
            DATEPART(DAY , DATEADD(hour, 0 - @start_hour, V.DateTimeField))

 Then, for example, if @start_hour was 8, 7 AM on 04/24 would become 11 PM on 04/23 (so it would be grouped with 04/23), while 8 AM on 04/24 would become midnight on 04/24.

 I haven't tested that, but it seems like it should work.

 If your start "hour" could really be any time, like 7:20 AM or 8:45 AM, then you could convert the parameter to minutes or seconds and use that with DATEADD.

 James
0
 

Author Comment

by:vojkocendak
ID: 40019642
Thank you,
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 ...
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 40021231
Hi,

If you group by dateadd( day, datediff( day, 0, V.DateTimeField ), 0 )

or dateadd( day, datediff( day, 0, dateadd( hour, -8, V.DateTimeField )), 0 )
   
should do the trick.

A bit nicer than having to extract all the date parts ...

HTH
  David
0
 

Author Comment

by:vojkocendak
ID: 40043046
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),DATEPART(MONTH,V.CAS),DATEPART(DAY,V.CAS)
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,-8,V.CAS)),DATEPART(MONTH,DATEADD(hour,-8,V.CAS)),DATEPART(DAY,DATEADD(hour,-8,V.CAS))
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 40044234
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
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Accepted Solution

by:
vojkocendak earned 0 total points
ID: 40044538
side question:

If for May : >= 1.5 08:00:00 ... < 1.June 08:00:00 sums per day

Yes, of course it's bizarre, but some companies prefer to use start the day from 08am to 08am next day, or whatever.

I found potential solution:

http://stackoverflow.com/questions/2026204/sql-server-group-results-on-time-interval

select
CONVERT(CHAR(10),DATEADD(hh,-8,[CAS]),120) AS MyDate
       ,COUNT(1) AS cnt
       ,SUM(V.ST39_poraba) AS poraba
FROM dbo.MERITVE as V
Where V.cas>='2014-05-01 08:00:00' and V.Cas<'2014-06-01 08:00:00' // data for may
GROUP BY CONVERT(CHAR(10),DATEADD(hh,-8,[Cas]),120)
order by 1

CHAR(10) : '2014-05-01' ...
0
 
LVL 34

Expert Comment

by:James0628
ID: 40045044
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
0
 

Author Comment

by:vojkocendak
ID: 40045500
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
0
 
LVL 34

Expert Comment

by:James0628
ID: 40045855
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
0
 

Author Closing Comment

by:vojkocendak
ID: 40057063
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now