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

Thank you

Regards
Raj Deep.A

Regards
Raj Deep.A

CREATE TABLE a(D DATETIME, Unit CHAR(5), Status CHAR(1))
INSERT a VALUES('2006-07-12','Unit1','S')
INSERT a VALUES('2006-07-13','Unit1','F')
INSERT a VALUES('2006-07-14','unit1','F')
INSERT a VALUES('2006-07-15','Unit1','F')
INSERT a VALUES('2006-07-16','Unit1','S')
INSERT a VALUES('2006-07-12','Unit1','S')
INSERT a VALUES('2006-07-13','Unit2','F')
INSERT a VALUES('2006-07-14','unit2','F')
INSERT a VALUES('2006-07-15','Unit2','F')
INSERT a VALUES('2006-07-16','Unit2','F')
go
SELECT * FROM a
go
CREATE PROCEDURE SelectFailures
  @NumFail INT
AS
SELECT a.* FROM a JOIN(
  SELECT Unit, d AS FirstFail,
    (SELECT COALESCE(MIN(d),'99990909') FROM a a2 WHERE a1.Unit =
a2.Unit AND a2.Status = 'S' AND a2.d > a1.d) AS NextNotFail
  FROM a a1 WHERE Status = 'F'
   AND (SELECT COALESCE(MAX(d),'19900102') FROM a a2 WHERE a1.Unit =
a2.Unit AND a2.Status = 'S' AND a2.d < a1.d) >
    (SELECT COALESCE(MAX(d),'19900101') FROM a a2 WHERE a1.Unit =
a2.Unit AND a2.Status = 'F' AND a2.d < a1.d)
) f ON a.Unit = f.Unit AND a.d >= f.FirstFail AND a.d < f.NextNotFail
WHERE (SELECT COUNT(*) FROM a WHERE a.Unit = f.Unit AND a.d >=
f.FirstFail AND a.d < f.NextNotFail) >= @NumFail
  AND (SELECT COUNT(*) FROM a a1 WHERE a1.Unit = f.Unit AND a1.d >=
f.FirstFail AND a1.d <= a.d) <= @NumFail
go
EXEC SelectFailures 1
go
EXEC SelectFailures 2
go
EXEC SelectFailures 3
go
EXEC SelectFailures 4
go
DROP TABLE a
go
DROP PROCEDURE SelectFailures
go

You are an extremely talented programmer.
My perspective is that sql shouldn't require
a query of your complexity to solve this problem.
And I'm sure your well aware of the number of
comparisons involved based on '<' and '>'.

This is nothing more than a DENSE RANK problem.
A DENSE RANK ordered by Unit,Date over the table
is the simplist logical solution. Once you have
this and a ROW NUMBER within the DENSE RANK
you have all the information to answer any question.
Unfortuneatly, sql can't compute this DENSE_RANK()
as there is no column(s) that can encapsulate this
sort order. This was explained here:

http://racster.blogspot.com/2006/09/rac-rank-this.html

Rac will compute the DENSE_RANK() (and ROW_NUMBER()
within it).

CREATE TABLE ##a(D DATETIME, Unit CHAR(5), Status CHAR(1))
INSERT ##a VALUES('2006-07-12','Unit1','S')
INSERT ##a VALUES('2006-07-13','Unit1','F')
INSERT ##a VALUES('2006-07-14','unit1','F')
INSERT ##a VALUES('2006-07-15','Unit1','F')
INSERT ##a VALUES('2006-07-16','Unit1','S')
INSERT ##a VALUES('2006-07-17','Unit1','S') -- correction
INSERT ##a VALUES('2006-07-13','Unit2','F')
INSERT ##a VALUES('2006-07-14','unit2','F')
INSERT ##a VALUES('2006-07-15','Unit2','F')
INSERT ##a VALUES('2006-07-16','Unit2','F')

Exec Rac
@transform='(1) as dumy',
@rows='Unit  & Status & D(date)',
@rowsort='Unit & D',
@pvtcol='Sql*Plus',
@from='##a',
@rowindicators='Status{DENSERANK}_overtable_',@counterdatatype='int',
@rowruns='{Status}(dumy)',@rowrunslabel='ROWCOUNTER',
@defaults1='y',@rowbreak='n',@racheck='y',@defaultexceptions='dumy',
@select='select D,Unit,Status,DENSERANK,1*ROWCOUNTER as ROWCOUNTER
            from rac
             order by rd'

D        Unit   Status DENSERANK   ROWCOUNTER
-------- ------ ------ ----------- -----------
07/12/06 Unit1  S      1           1
07/13/06 Unit1  F      2           1
07/14/06 unit1  F      2           2
07/15/06 Unit1  F      2           3
07/16/06 Unit1  S      3           1
07/17/06 Unit1  S      3           2
07/13/06 Unit2  F      4           1
07/14/06 unit2  F      4           2
07/15/06 Unit2  F      4           3
07/16/06 Unit2  F      4           4

With these ranks even a rookie can formulate a simple query
to get 3 consecutive failures.

Exec Rac
@transform='(1) as dumy',
@rows='Unit  & Status & D(date)',
@rowsort='Unit & D',
@pvtcol='Sql*Plus',
@from='##a',
@rowindicators='Status{DENSERANK}_overtable_',@counterdatatype='int',
@rowruns='{Status}(dumy)',@rowrunslabel='ROWCOUNTER',
@defaults1='y',@rowbreak='n',@racheck='y',
@select='select c.D,c.Unit,c.Status,c.DENSERANK
          from rac as c
           where 1*c.ROWCOUNTER<=3
            and
             c.DENSERANK in
              (select r.DENSERANK
                from rac as r
                 where Status=~F~ and 1*ROWCOUNTER=3)
                  order by rd'

D        Unit   Status DENSERANK
-------- ------ ------ -----------
07/13/06 Unit1  F      2
07/14/06 unit1  F      2
07/15/06 Unit1  F      2
07/13/06 Unit2  F      4
07/14/06 unit2  F      4
07/15/06 Unit2  F      4

This is what sql 'should' be able to do -:)
Interestingly, this is the same logical problem recently
posed by Itzik Ben-Gan:

T-SQL Puzzle Grouping consecutive rows with a common element
http://www.sqlmag.com/Article/ArticleID/93462/sql_server_93462.html

best,
http://racster.blogspot.com

Steve Dassin wrote:

Show quote

Hide quote

It is, in an easy way, you just have to get used to it :-)

SELECT
   d, unit, status
FROM
  (
   SELECT
     d, unit, status,
     COUNT(*) OVER (PARTITION BY Unit, grp) AS cnt
   FROM
    (
     SELECT
       d, unit, status,
       dateadd(DAY, -ROW_NUMBER() OVER (PARTITION BY Unit, status
                                        ORDER BY D), D) AS grp
     FROM a
     WHERE status = 'f'
    ) dt
  ) dt
WHERE cnt >= 4;

If the Dates are not consecutive then it's just a bit more complicated
using 2 Ranks/Row_numbers.

I just wish MS implemented the remaining OLAP-Functions from Standard SQL...

Dieter

Thanks Dieter. I should have realized this numbers game :( :).
Yes MS significantly 'lags' Oracle -:).
In general sql should take notice of other software. Much of OLAP
has been in SAS (Statistical Analysis System) since before electricity.

best,
steve

Show quote

Hide quote

I have several years of experience porting between Oracle nad SQL
Server. As such, I would disagree. We evaluate RDBMS as a whole
package.
As a whole package, SQL Server is a better fit in many situations.
Saying that it lags based on a feature or two sounds strange to me.
Suppose that Intrepid has a bigger muffler than Mustang. So what?

If it has a bigger muffler, and comes in green, then I'll take it.

Seriously though, In what areas would you place SQL Server above Oracle?
I'm not trying to argue, just curious as to how others compare the two
systems.

Show quote

Hide quote

Using SQL Server 2005 you can do this


WITH Ranked(D,Unit,Status,rn,grprn)
AS
(SELECT D,
       Unit,
       Status,
       RANK() OVER(PARTITION BY Unit ORDER BY D),
       RANK() OVER(PARTITION BY Unit ORDER BY D)-
       RANK() OVER(PARTITION BY Unit,Status ORDER BY D)
FROM a),
Grouped(Unit,Status,minrn,maxrn,cnt)
AS
(SELECT Unit,
        Status,
        min(rn),
        max(rn),
        count(*)
FROM Ranked
GROUP BY Unit,Status,grprn)
SELECT r.D,
       r.Unit,
       r.Status
FROM Grouped g
INNER JOIN Ranked r ON r.Unit=g.Unit
                   AND r.rn BETWEEN g.minrn AND g.minrn+@NumFail-1
                   AND r.Status='F'
WHERE g.cnt>=@NumFail
ORDER BY r.Unit,r.D

Great stuff mark!!
I never thought it would be that easy to get anything close to a
dense rank in the correct order that easy. Very cleaver to use a
combination of ranks.
Not quite Rac but pretty damn close and a lot quicker.
I should eat atleast some crow :( :)

best,
steve

**SORT THE PHYSICAL ORDER ON A TABLE'S COLUMNS**
INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION
A .NET Framework error occurred during execution of user defined r
sql statement (how to)
Update Information in SQL from a VBS script