September 13, 2006 at 7:06 am
Hi Every body
I need a query on table which looks like
Date Unit Status
2006-07-12 Unit1 S
2006-07-13 Unit1 F
2006-07-14 unit1 F
2006-07-15 Unit1 F
2006-07-16 Unit1 S
2006-07-12 Unit1 S
2006-07-13 Unit2 F
2006-07-14 unit2 F
2006-07-15 Unit2 F
2006-07-16 Unit2 F
If it was the data of the table. i need to report using reporting services Such that the uSer will be asked a parameter value which is no of failures Consecutively
Ex:
if the user enters 3 where unit1 and unit2 failed 3 times consecutively according to the date
I need to report
2006-07-13 Unit1 F
2006-07-14 unit1 F
2006-07-15 Unit1 F
2006-07-13 Unit2 F
2006-07-14 unit2 F
2006-07-15 Unit2 F
If the user enters 4 where the unit2 failed 4 times consecutively
2006-07-13 Unit2 F
2006-07-14 unit2 F
2006-07-15 Unit2 F
2006-07-16 Unit2 F
Please help me out to write query which reports like this
Regards
Raj Deep.A
September 13, 2006 at 8:41 am
SELECT c.[Date], c.Unit, c.Status
FROM [Table] c
INNER JOIN (SELECT a.[Date],a.Unit
FROM [Table] a
INNER JOIN [Table] b
ON b.Unit = a.Unit
AND b.Status = 'F'
AND b.[Date] >= a.[Date]
AND b.[Date] <= DATEADD(day,@NoOfFailures,a.[Date])
WHERE a.Status = 'F'
GROUP BY a.[Date],a.Unit
HAVING COUNT(*) = @NoOfFailures) d
ON c.Unit = d.Unit
AND c.Status = 'F'
AND c.[Date] >= d.[Date]
AND c.[Date] <= DATEADD(day,@NoOfFailures,d.[Date])
Far away is close at hand in the images of elsewhere.
Anon.
September 14, 2006 at 1:19 am
It Works.Thank you very much David
Regards
Raj Deep.A
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply