locked
Sum the duration by month between date ranges RRS feed

  • Question

  • User1042252450 posted

    Hello,

    I have an records like below, I have to sum the duration by month wise and my date range will be 12 months (4 quarters) in condition.

    Outage_Date between DATEADD(qq, DATEDIFF(qq,0,DATEADD(qq,-4,'February 2016')), 0) and DATEADD(qq, DATEDIFF(qq,0,DATEADD(qq,0,'February 2016')), -1)

    Code Outage_Date Duration
    43 12/18/2015 108
    43 12/1/2015 104
    43 11/30/2015 54
    43 11/28/2015 37
    43 11/27/2015 521

    Please suggest.

    regards,

    Rangasamy V

    Monday, February 29, 2016 2:31 PM

Answers

  • User-219423983 posted

    Hi v_rangasamy,

    Could you first clarify what the “qq” is?

    I have created a sample demo as below and you could have a look, I hope it would be helpful to you and you could follow the method to build your SQL string. For your case, you could first get the begin date and the end time of the date range and then build the query string with them.

    CREATE TABLE #TEMP(Timewhen DATETIME, Value INT)
    
    INSERT INTO #TEMP VALUES('2016-02-04', 4)
    INSERT INTO #TEMP VALUES('2016-02-06', 4)
    INSERT INTO #TEMP VALUES('2016-02-10', 4)
    
    INSERT INTO #TEMP VALUES('2016-04-08', 4)
    INSERT INTO #TEMP VALUES('2016-04-12', 4)
    
    INSERT INTO #TEMP VALUES('2016-05-01', 4)
    INSERT INTO #TEMP VALUES('2016-05-31', 4)
    
    INSERT INTO #TEMP VALUES('2016-10-14', 4)
    
    INSERT INTO #TEMP VALUES('2016-11-23', 4)
    
    INSERT INTO #TEMP VALUES('2016-12-31', 4)
    
    
    declare @begin datetime = '2016-02-01'
    declare @end datetime='2016-05-01'
    
    --select DATENAME(month,Timewhen) from #TEMP
    
    SELECT YEAR(Timewhen) EventYear, DATENAME(month,Timewhen) EventMonth, SUM(Value) Total
    FROM #TEMP
    where Timewhen between @begin and @end
    GROUP BY YEAR(Timewhen), DATENAME(month,Timewhen)
    
    DROP TABLE #TEMP
    

    Best Regards,

    Weibo Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, March 1, 2016 2:00 AM