Sum the duration by month between date ranges RRS feed

  • Question

  • User1042252450 posted


    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.


    Rangasamy V

    Monday, February 29, 2016 2:31 PM


  • 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.

    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
    where Timewhen between @begin and @end
    GROUP BY YEAR(Timewhen), DATENAME(month,Timewhen)

    Best Regards,

    Weibo Zhang

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