SQL Server - Select results by a group expression but filter them based on another group expression

Question

I am dealing with a problem where I need to aggregate data based on a criteria. The situation is a follow:

I have consumption data from an application on database and I need to show it to be displayed in a table. People can aggregate data based on minute, hour, day and month. When people aggregate data by minute, for example, data will be display in a table to show at max 10 rows (but here is the tricky part, the rows are hour based in this example). So instead of saying I want the first 10 rows (of the minutes aggregation) I have to find what is the number of entries equivalent to display the results of 10 distinct hours.

I have tried to make the query and I am at this point to better illustrate the problem (query won't run):

SELECT  count(*) as COUNT,
  year([CONSUMPTION_DATE]) as YEAR_BY_MINUTE, 
  month([CONSUMPTION_DATE]) as MONTH_BY_MINUTE, 
  day([CONSUMPTION_DATE]) as DAY_BY_MINUTE,
  DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_BY_MINUTE,
  DATEPART ( minute , [CONSUMPTION_DATE]) as MINUTE_BY_MINUTE
  FROM [CONSUMPTION_TABLE] 
  where (YEAR_BY_MINUTE, MONTH_BY_MINUTE, DAY_BY_MINUTE, HOUR_BY_MINUTE) IN 

        (select top 10  year([CONSUMPTION_DATE]) as YEAR_BY_HOUR, 
            month([CONSUMPTION_DATE])as MONTH_BY_HOUR, 
            day([CONSUMPTION_DATE]) as DAY_BY_HOUR,
            DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_BY_HOUR
            FROM [CONSUMPTION_TABLE]
            where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
            GROUP BY year([CONSUMPTION_DATE]), 
                     month([CONSUMPTION_DATE]), 
                     day([CONSUMPTION_DATE]),
                     DATEPART ( hour , [CONSUMPTION_DATE])
        )

  GROUP BY  year([CONSUMPTION_DATE]), 
  month([CONSUMPTION_DATE]), 
  day([CONSUMPTION_DATE]),
  DATEPART ( hour , [CONSUMPTION_DATE] ),
  DATEPART ( minute , [CONSUMPTION_DATE] )

I also have been trying to go by another way but query is running with error thought:

;with MinuteGroup as 
        (
            select  year([CONSUMPTION_DATE]) as YEAR_GROUP_MINUTE, 
                    month([CONSUMPTION_DATE])as MONTH_GROUP_MINUTE, 
                    day([CONSUMPTION_DATE]) as DAY_GROUP_MINUTE,
                    DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_GROUP_MINUTE,
                    DATEPART ( minute , [CONSUMPTION_DATE] ) as MINUTE_GROUP_MINUTE
                FROM [CONSUMPTION_TABLE]
                where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                group by    year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]),
                            DATEPART ( hour , [CONSUMPTION_DATE] ),
                            DATEPART ( minute , [CONSUMPTION_DATE] )
        )
    select * from MinuteGroup where 
        (YEAR_GROUP_MINUTE, MONTH_GROUP_MINUTE, DAY_GROUP_MINUTE, HOUR_GROUP_MINUTE) 
            in (    select  year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]) ,
                            DATEPART ( hour , [CONSUMPTION_DATE] ) 
                    FROM [CONSUMPTION_TABLE]
                    where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                    group by year([CONSUMPTION_DATE]), 
                             month([CONSUMPTION_DATE]), 
                             day([CONSUMPTION_DATE]),
                             DATEPART ( hour , [CONSUMPTION_DATE])   
                )

Is another way easier to make this or what do I have to change in the queries to make them work?

PS: I will have to transfer this to HQL so if it will be easier I will also accept that answers.

EDIT: As is hard to visualize what I am trying to achieve here is an image :

(the unit time I am aggregating will be a simple number of 1,2,3,4,5,...)

DDL, **comsumable** sample data ([Forum Etiquette: how to post Sample data for a T-SQL question](http://www.sqlservercentral.com/articles/Best+Practices/61537/)) and expected output is going to be really helpful here. I don't really understand what you're really trying to achieve here. — Thom A, Mar 20 '18 at 17:03
I don't get what you mean by *number of entries equivalent to display the results of 10 distinct hour*, all minutes within the last 10 hours? And what if you want hours or days? — dnoeth, Mar 20 '18 at 17:04
@dnoeth If i want hours then i will be displaying rows based on days. I have edited my question and display an image with an illustration of what I am trying to achieve. — R. Godinho, Mar 20 '18 at 17:14
Looks life different queries with different `group by`. And instead of limiting the result better calculate the `where [CONSUMPTION_DATE] between ...` accordingly to exclude data you don't want to show.. — dnoeth, Mar 20 '18 at 17:25
Even with your images I still have no idea what you're asking, and what is HQL? — Tab Alleman, Mar 20 '18 at 17:43
@dnoeth yes i guess you already got the situation here. My thought was make the where condition in the subquery part and limit with offset and fetch so after that i was able to limit about the days and so. The `[CONSUMPTION_DATA]` is in `yyyy-MM-dd hh:mm:ss` format so maybe your saying i use that as the result of the subquery instead? — R. Godinho, Mar 21 '18 at 08:09

R. Godinho · Answer 1 · 2018-03-21T12:27:42.670

Well after a while searching and taking advantage of other solutions I have come up with an sql query where I could get the desired result, so I thought it would be nice to post it here so you can understand better the problem and help you in case you are in the same situation. The final query has stay as:

;with MinuteGroup as 
        (
            select  count(*) as COUNT,
                    year([CONSUMPTION_DATE]) as YEAR_GROUP_MINUTE, 
                    month([CONSUMPTION_DATE])as MONTH_GROUP_MINUTE, 
                    day([CONSUMPTION_DATE]) as DAY_GROUP_MINUTE,
                    DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_GROUP_MINUTE,
                    DATEPART ( minute , [DATE_SELECTION_START] ) as MINUTE_GROUP_MINUTE
                FROM [CONSUMPTION_TABLE]
                where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                group by    year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]),
                            DATEPART ( hour , [CONSUMPTION_DATE] ),
                            DATEPART ( minute , [CONSUMPTION_DATE] )
        ),
        HourGroup as 
        (
            select top 10 year([CONSUMPTION_DATE]) as YEAR_GROUP_MINUTE, 
                    month([CONSUMPTION_DATE])as MONTH_GROUP_MINUTE, 
                    day([CONSUMPTION_DATE]) as DAY_GROUP_MINUTE,
                    DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_GROUP_MINUTE
                FROM [CONSUMPTION_TABLE]
                where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                group by    year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]),
                            DATEPART ( hour , [CONSUMPTION_DATE] )
        )
    select  MinuteGroup.COUNT,
            MinuteGroup.YEAR_GROUP_MINUTE,
            MinuteGroup.MONTH_GROUP_MINUTE,
            MinuteGroup.DAY_GROUP_MINUTE,
            MinuteGroup.HOUR_GROUP_MINUTE,
            MinuteGroup.MINUTE_GROUP_MINUTE
    from MinuteGroup join HourGroup 
                on  HourGroup.YEAR_GROUP_MINUTE = MinuteGroup.YEAR_GROUP_MINUTE
                and HourGroup.MONTH_GROUP_MINUTE = MinuteGroup.MONTH_GROUP_MINUTE
                and HourGroup.DAY_GROUP_MINUTE = MinuteGroup.DAY_GROUP_MINUTE
                and HourGroup.HOUR_GROUP_MINUTE = MinuteGroup.HOUR_GROUP_MINUTE

And like this instead of having the first 10 rows of this image I have 20 which corresponds to 10 disting hours:

SQL Server - Select results by a group expression but filter them based on another group expression

1 Answers1