Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Group by 15 minute intervals

Posted on 2006-11-28
5
Medium Priority
?
1,409 Views
Last Modified: 2008-01-09

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
Comment
Question by:sbagireddi
  • 3
  • 2
5 Comments
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 18033361
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
 
LVL 16

Assisted Solution

by:Hillwaaa
Hillwaaa earned 1000 total points
ID: 18033371
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
 
LVL 16

Expert Comment

by:Hillwaaa
ID: 18033817
Just replace @temp with START_TIME and you should be fine.
0
 

Accepted Solution

by:
satishpc earned 1000 total points
ID: 18035314
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
 

Expert Comment

by:satishpc
ID: 18035320
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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