Day wise and date range calculation with looping or dynamic data

Transact-SQL
1

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) combining both Table 1 and Table 2

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

2

Couple of thoughts (not sure they answer the whole of your question)

Instead of BETWEEN I query for

SomeDate >= StartDate
AND SomeDate < StopDate

The StopDate is the day AFTER the cutoff. Selecting "close to midnight" is dangerous as SQL uses rounding on the milliseconds, which varies according to the precision of the date/time that you use ... and it is capabl of rounding to TOMORROW!! if you get too close to midnight.

I precalculate the Start/StopDate to make whole days (for a datetime variable), so using your example my Start would be 2014-12-22 and my Stop would be 2015-01-22 - i.e. I want to include anything with a date of 2014-12-22 through 2015-01-21 - excluding anything which is either 2014-12-21 or earlier, or 2015-01-22 or later.

SUM(Case when sometable.FAILUREDATE >= @StartDate
              AND sometable.FAILUREDATE < @StopDate
         Then 1 Else 0 END)

Rather than using CONVERT with format specifiers we only ever use String Dates in "yyyymmdd" (or "yyyymmdd hh:mm:ss.sss") [note: no punctuation in the date] format. SQL guarantees that these are converted unambiguously (an alterntaive is the ISO format "YYYY-MM-DDThh:mm:ss.sss" - the punctuation, and the "T", are required)

To convert a date/time to remove the date then this, rather obtuse, code will do the trick. It is very efficient, compared to any form of converting the String dates and back again, as it only uses integer arithmetic:

DATEADD(Day, DATEDIFF(Day, 0, MyDate), 0)

e.g.

SELECT DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)

In SQL 2012 you can just cast a DATETIME to a DATE to get rid of the time portion.

You can then subtract 31 days, or add one (to get "tomorrow" for your StopDate)

3

Hi Kristen,

Thanks for your reply.

I will split my question into multiple parts.

First
how do i loop and pass date dynamically in the Datediff for a period of one month?

I have created a Sql Fiddle
http://sqlfiddle.com/#!3/2ee295

SELECT Order1,COUNT(UNit.UNIT) As Units,sum(CASE
WHEN (datediff(dd,INSV_DATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,INSV_DATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,INSV_DATE,'2015-01-21'))END) as Days31
FROM UNIT WHERE Unit.INSV_DATE < '2015-01-21' AND
UNIT.MODEL in('Toyota') AND(UNIT.Customer in('Jona' ))
group by [Order1],customer

I want the 31 days output calculated for day wise.

The output should be like

Date Order1 Unit Day31
May20 90909 5 128
May19 90909 4 124
May17 90909 2 62

I actually want to do something like the following.

SELECT Order1,COUNT(UNit.UNIT)  As Units,sum(CASE 
WHEN (datediff(dd,INSV_DATE,'2015-05-20')) >= 31 THEN 31
WHEN (datediff(dd,INSV_DATE,'2015-05-20')) < 0 THEN 0
ELSE (datediff(dd,INSV_DATE,'2015-05-20'))END) as Days31
FROM UNIT WHERE Unit.INSV_DATE < '2015-05-20' AND 
UNIT.MODEL in('Toyota')	AND(UNIT.Customer in('Jona' )) 
group by [Order1],customer

SELECT Order1,COUNT(UNit.UNIT)  As Units,sum(CASE 
WHEN (datediff(dd,INSV_DATE,'2015-05-19')) >= 31 THEN 31
WHEN (datediff(dd,INSV_DATE,'2015-05-19')) < 0 THEN 0
ELSE (datediff(dd,INSV_DATE,'2015-05-19'))END) as Days31
FROM UNIT WHERE Unit.INSV_DATE < '2015-05-19' AND 
UNIT.MODEL in('Toyota')	AND(UNIT.Customer in('Jona' )) 
group by [Order1],customer

SELECT Order1,COUNT(UNit.UNIT)  As Units,sum(CASE 
WHEN (datediff(dd,INSV_DATE,'2015-05-18')) >= 31 THEN 31
WHEN (datediff(dd,INSV_DATE,'2015-05-18')) < 0 THEN 0
ELSE (datediff(dd,INSV_DATE,'2015-05-18'))END) as Days31
FROM UNIT WHERE Unit.INSV_DATE < '2015-05-18' AND 
UNIT.MODEL in('Toyota')	AND(UNIT.Customer in('Jona' )) 
group by [Order1],customer

Running the same query for everyday with the different date.

If you can guide me in making a day wise query that will be great.

Thank you.

4

SELECT
Order1,
COUNT(UNit.UNIT) As Units,
sum(CASE
WHEN (datediff(dd,INSV_DATE,[Calendar Day column])) >= 31 THEN 31
WHEN (datediff(dd,INSV_DATE,[Calendar Day column])) < 0 THEN 0
ELSE (datediff(dd,INSV_DATE,[Calendar Day column]))END) as Days31

FROM #UNIT UNIT [, Calender Table]
WHERE Unit.INSV_DATE < [Calendar Day column]
AND UNIT.MODEL in('Toyota')
AND(UNIT.Customer in('Jona' ))
group by [Order1],customer

replace all the thing in bold

5 
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]

This query worked for me.