Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Group by 15 minute intervals

Posted on 2006-11-28
5
Medium Priority
?
1,400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

718 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