Need Consecutive Query

  • 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

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

  • 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