Group by 15 minute intervals


Hello There,

As you can see I am compiling the results and grouping by hour.  I would need them grouped in 15 minute intervals.
I would appreciate if you could help me

 

    -- Get all records for week and user

    SELECT  

        DATEPART(MONTH, START_TIME) as [MON],  

        DATEPART(WEEKDAY, START_TIME) as [Week Day#],  

        DATENAME(WEEKDAY, START_TIME) as [Week Day],  

        DATEPART(HH,START_TIME)AS [Time],

        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2) as [Start Hour],

        SUM(CASE WHEN WORKSTEP_NAME = 'VESTING' Then 1 ELSE 0 END) AS 'Vesting',

        SUM(CASE WHEN WORKSTEP_NAME = 'LEGAL' Then 1 ELSE 0 END) AS 'Legal',

        SUM(CASE WHEN WORKSTEP_NAME = 'VOLUNTARYLIENS' Then 1 ELSE 0 END) AS 'Vol Liens'

    INTO #temp

    FROM  

        dbo.RPT_WORKITEM wi (NOLOCK),  

        dbo.RPT_PROCESSTEMPLATE ptemp (NOLOCK)

    WHERE  

        wi.PROCESS_TEMPLATE_ID = ptemp.PROCESS_TEMPLATE_ID AND

        wi.STATUS = 'I_COMPLETED' AND

        ptemp.PROCESS_TEMPLATE_NAME = 'ProcessFASTSearchOrder'

            and START_TIME >= '2006-1-1'

    GROUP BY  

        DATEPART(MONTH, START_TIME),

        DATENAME(WEEKDAY, START_TIME),

        DATEPART(HH,START_TIME),

        DATEPART(WEEKDAY, START_TIME),

        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2)  

 

 

    -- Show all records  

    SELECT  

        a.[MON]    AS [Month]

--       ,a.[Week Day]    AS [Week Day]

       ,a.[Start Hour]  AS [Start Hour]

       ,a.[Vesting]     AS [Vesting]

       ,a.[Legal]       AS [Legal]

       ,a.[Vol Liens]   AS [Vol Liens]

            ,[Vol Liens] + [Legal] + [Vesting] as [Total Tasks]

    FROM [#temp] a

    ORDER BY MON, [Week Day#], a.[Time]

    ORDER BY MON, a.[Time]

 
LVL 8
sbagireddiAsked:
Who is Participating?
 
satishpcCommented:
You can try this. It depends on what title an value you want for the column. If you want a customised text for each quarter hour, then Hilwaa's solution would be great. My query will show you just 1/2/3/4 for the respective quarter.


SELECT  
        DATEPART(MONTH, START_TIME) as [MON],  
        DATEPART(WEEKDAY, START_TIME) as [Week Day#],  
        DATENAME(WEEKDAY, START_TIME) as [Week Day],  
        DATEPART(HH,START_TIME)AS [Time],
        (datepart(mi, last_update_date)/15)+1 [Quarter hour],
        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2) as [Start Hour],
        SUM(CASE WHEN WORKSTEP_NAME = 'VESTING' Then 1 ELSE 0 END) AS 'Vesting',
        SUM(CASE WHEN WORKSTEP_NAME = 'LEGAL' Then 1 ELSE 0 END) AS 'Legal',
        SUM(CASE WHEN WORKSTEP_NAME = 'VOLUNTARYLIENS' Then 1 ELSE 0 END) AS 'Vol Liens'
    INTO #temp
    FROM  
        dbo.RPT_WORKITEM wi (NOLOCK),  
        dbo.RPT_PROCESSTEMPLATE ptemp (NOLOCK)
    WHERE  
        wi.PROCESS_TEMPLATE_ID = ptemp.PROCESS_TEMPLATE_ID AND
        wi.STATUS = 'I_COMPLETED' AND
        ptemp.PROCESS_TEMPLATE_NAME = 'ProcessFASTSearchOrder'
            and START_TIME >= '2006-1-1'
    GROUP BY  
        DATEPART(MONTH, START_TIME),
        DATENAME(WEEKDAY, START_TIME),
        DATEPART(HH,START_TIME),
        DATEPART(WEEKDAY, START_TIME),
        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2)
        (datepart(mi, last_update_date)/15)+1,
0
 
HillwaaaCommented:
Hi sbagireddi,

try using the following in your select and group:

    case
        when datepart(minute,@temp) < 15 then '00'
        when datepart(minute,@temp) < 30 then '15'
        when datepart(minute,@temp) < 45 then '30'
        else '45'
    end as [Start QuarterHour]

Cheers,
Hillwaaa
0
 
HillwaaaCommented:
So it would become:

    SELECT  
        DATEPART(MONTH, START_TIME) as [MON],  
        DATEPART(WEEKDAY, START_TIME) as [Week Day#],  
        DATENAME(WEEKDAY, START_TIME) as [Week Day],  
        DATEPART(HH,START_TIME)AS [Time],
    case
        when datepart(minute,@temp) < 15 then '00'
        when datepart(minute,@temp) < 30 then '15'
        when datepart(minute,@temp) < 45 then '30'
        else '45'
    end as [Start QuarterHour],
        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2) as [Start Hour],
        SUM(CASE WHEN WORKSTEP_NAME = 'VESTING' Then 1 ELSE 0 END) AS 'Vesting',
        SUM(CASE WHEN WORKSTEP_NAME = 'LEGAL' Then 1 ELSE 0 END) AS 'Legal',
        SUM(CASE WHEN WORKSTEP_NAME = 'VOLUNTARYLIENS' Then 1 ELSE 0 END) AS 'Vol Liens'
    INTO #temp
    FROM  
        dbo.RPT_WORKITEM wi (NOLOCK),  
        dbo.RPT_PROCESSTEMPLATE ptemp (NOLOCK)
    WHERE  
        wi.PROCESS_TEMPLATE_ID = ptemp.PROCESS_TEMPLATE_ID AND
        wi.STATUS = 'I_COMPLETED' AND
        ptemp.PROCESS_TEMPLATE_NAME = 'ProcessFASTSearchOrder'
            and START_TIME >= '2006-1-1'
    GROUP BY  
        DATEPART(MONTH, START_TIME),
        DATENAME(WEEKDAY, START_TIME),
        DATEPART(HH,START_TIME),
        DATEPART(WEEKDAY, START_TIME),
        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2) ,
    case
        when datepart(minute,@temp) < 15 then '00'
        when datepart(minute,@temp) < 30 then '15'
        when datepart(minute,@temp) < 45 then '30'
        else '45'
    end
    -- Show all records  

    SELECT  
        a.[MON]    AS [Month]
--       ,a.[Week Day]    AS [Week Day]
       ,a.[Start Hour]  AS [Start Hour]
       ,a.[Start QuarterHour] as [Start QuarterHour]
       ,a.[Vesting]     AS [Vesting]
       ,a.[Legal]       AS [Legal]
       ,a.[Vol Liens]   AS [Vol Liens]
            ,[Vol Liens] + [Legal] + [Vesting] as [Total Tasks]
    FROM [#temp] a
    ORDER BY MON, [Week Day#], a.[Time]
    ORDER BY MON, a.[Time]
0
 
HillwaaaCommented:
Just replace @temp with START_TIME and you should be fine.
0
 
satishpcCommented:
sorry, had the wrong field name in the earlier query

SELECT  
        DATEPART(MONTH, START_TIME) as [MON],  
        DATEPART(WEEKDAY, START_TIME) as [Week Day#],  
        DATENAME(WEEKDAY, START_TIME) as [Week Day],  
        DATEPART(HH,START_TIME)AS [Time],
      (datepart(mi, START_TIME)/15)+1 [Quarter hour],
        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2) as [Start Hour],
        SUM(CASE WHEN WORKSTEP_NAME = 'VESTING' Then 1 ELSE 0 END) AS 'Vesting',
        SUM(CASE WHEN WORKSTEP_NAME = 'LEGAL' Then 1 ELSE 0 END) AS 'Legal',
        SUM(CASE WHEN WORKSTEP_NAME = 'VOLUNTARYLIENS' Then 1 ELSE 0 END) AS 'Vol Liens'
    INTO #temp
    FROM  
        dbo.RPT_WORKITEM wi (NOLOCK),  
        dbo.RPT_PROCESSTEMPLATE ptemp (NOLOCK)
    WHERE  
        wi.PROCESS_TEMPLATE_ID = ptemp.PROCESS_TEMPLATE_ID AND
        wi.STATUS = 'I_COMPLETED' AND
        ptemp.PROCESS_TEMPLATE_NAME = 'ProcessFASTSearchOrder'
            and START_TIME >= '2006-1-1'
    GROUP BY  
        DATEPART(MONTH, START_TIME),
        DATENAME(WEEKDAY, START_TIME),
        DATEPART(HH,START_TIME),
        DATEPART(WEEKDAY, START_TIME),
        SUBSTRING(RIGHT(convert(varchar(30),START_TIME,100),7),1,2)+' '+RIGHT(convert(varchar(30),START_TIME,100),2)
      (datepart(mi, START_TIME)/15)+1,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.