sql server - day wise and date range calculation

Question

I am using Sql Server 2012.

This is how I calculate the ratio of failures in an order:

31 Days Table 1 query

sum(CASE 
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days

1 . How do i loop and pass dates dynamically in the Datediff?

31 Failures Table 2 query

SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)) 
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,

31 Day Cal(Formula)

((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]

This works fine when done for a specific order.

I want a similar kind of calculation done for day wise and month wise.

2. what approach should I be using to achieve day wise and month wise calculation?

I do also have a table called Calender with the list of dates that i can use.

I would really appreciate any help regarding this..Thank you..

Answer 1

 DECLARE @StartDate date = 'Jan 1, 2015'
    DECLARE @EndDate   date = DATEADD(DAY, 30, @StartDate)

    ;WITH cte AS (
        SELECT      @StartDate  AS ReportDate
        UNION ALL
        SELECT      DATEADD(DAY, 1, ReportDate)
        FROM        cte
        WHERE       ReportDate < @EndDate
    )

    Select T1.[date],T1.Fail31,T2.days31,
    ((365*(Convert(decimal (8,1),T1.Fail31)/T2.days31))) [31Fly]

    from 
    (
    SELECT cte.ReportDate as [date],

    SUM(Case when HISTORY.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, cte.ReportDate, 102)) 
    AND CONVERT(DATETIME, cte.ReportDate, 102)Then 1 Else 0 END) As Fail31

        FROM HISTORY left JOIN UNIT ON HISTORY.UNIT = UNIT.UNIT

        CROSS JOIN cte

        WHERE 

          UNIT.INSV_DATE < cte.ReportDate 
                    AND UNIT.MODEL in('Toyota')
                    AND(UNIT.Customer in('Jona' ))

                    group by 

                    cte.ReportDate  ) T1

                    Inner Join 

                    (SELECT cte.ReportDate as [date1],


    COUNT(UNIT.UNIT)  As Units,

     sum(CASE 
    WHEN (datediff(dd,INSV_DATE,cte.ReportDate)) >= 31 THEN 31
    WHEN (datediff(dd,INSV_DATE,cte.ReportDate)) < 0 THEN 0
    ELSE (datediff(dd,INSV_DATE,cte.ReportDate))END) as days31

        FROM UNIT

        CROSS JOIN cte

        WHERE 

          UNIT.INSV_DATE < cte.ReportDate 
                    AND UNIT.MODEL in('Toyota')
                    AND(UNIT.Customer in('Jona' ))

                    group by 

                    cte.ReportDate
    ) T2 on T1.[date] = t2.[date1]
                    Order by [date]