May 14, 2015 at 10:42 am
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..
May 14, 2015 at 11:32 am
See the link in my signature line for best practices on getting help then post DDL and sample data.
-- Itzik Ben-Gan 2001
May 14, 2015 at 11:48 am
There are few ways of doing this, my guess is that you are not close to any of them but in order to help you find an acceptable solution, you must elaborate on Alan's request and post the DDL (create table), sample data as an insert statement and finally the expected result set. Take care in posting the full problem as piecemeal'ing it doesn't do anything but waste everyone's time.
😎
May 26, 2015 at 1:41 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply