• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1412
  • Last Modified:

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]

 
0
sbagireddi
Asked:
sbagireddi
  • 3
  • 2
2 Solutions
 
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:
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
 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now