Solved

Group by 15 minute intervals

Posted on 2006-11-28
5
1,321 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 250 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 250 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now