CODEWITHSUNDEEP
sqlsql-servert-sql
user1703059
user1703059

Reputation: 94

Count how many times row type 1 is found between row type 2 dates

I'am making a report in which I have to find out how many times there has been a situation where one or more row type 1 is found between row type 2 dates. Data looks like this and is sorted by date:

Date          Row type
2014-01-17    2
2014-01-21    1*
2014-02-06    2
2014-02-11    1*
2014-02-20    1*
2014-03-19    1*
2014-03-19    1* 
2014-03-19    2
2014-03-19    1*
2014-03-19    1*
2014-03-20    1* 
2014-03-20    2
2014-03-20    2
2014-03-21    1*
2014-03-21    2
2014-03-21    2
2014-03-21    1*
2014-03-24    1*
2014-03-24    1*
2014-03-25    2

So here I can see that type 1 rows can be found between type 2 rows five times.

I only have used very basic SQL queries and I have no idea how to proceed with this. Query should be able to run in SSRS report.

Any help ? Or if somebody could point to right direction, I have looked at WITH CRE and RANK OVER but I can not figure out could these be solution to this problem.

Upvotes: 3

Views: 108

Answers (5)

Anto Raja Prakash
Anto Raja Prakash

Reputation: 1388

select sum(cnt) from 
(select a.f1 ,a.f2,case when a.f2<b.f2 then 1 else 0 end as cnt from 
(select *, row_number() OVER( ORDER BY f1) AS Rank from t1 ) a
left join(select *, row_number() OVER( ORDER BY f1) AS Rank from t1 ) b 
on a.Rank = b.Rank+1) as d

Upvotes: 0

MikkaRin
MikkaRin

Reputation: 3084

I hope this query will help you.

It counts not directly what you want, but amount of 1 and 2 in each day

select a,b,max(rn) over(partition by a,b)-min(rn) over(partition by a,b)+1 from (
select *, ROW_NUMBER() over(partition by b order by a) rn from 
(values ('2014-01-17 ',   2),
('2014-01-21',   1),
('2014-02-06', 2),
('2014-02-11',1),
('2014-02-20',1),
('2014-03-19',1),
('2014-03-19',1 ),
('2014-03-19',2),
('2014-03-19',1),
('2014-03-19',1),
('2014-03-20',1 ),
('2014-03-20',2),
('2014-03-20',2),
('2014-03-21',1),
('2014-03-21',2),
('2014-03-21',2),
('2014-03-21',1),
('2014-03-24',1)
) as t(a,b))t
order by a

Upvotes: 0

AK47
AK47

Reputation: 3807

Here it is,...

;With CTE as
(
select *,Row_number() over (order by dateDt) as RNo
 from mtT
)
select COUNT(*) from CTE  as a
inner join CTE as b on a.RNo + 1 = b.RNo
    and a.rowType <> b.rowType
where a.rowType = 1

Upvotes: 1

adrianm
adrianm

Reputation: 14736

WITH AllRowNumbers AS (
    SELECT RowType
          ,ROW_NUMBER() OVER (ORDER BY [Date]) AS RowNumber
    FROM #T
)
,Type2RowNumbers AS (
    SELECT RowNumber
    FROM AllRowNumbers
    WHERE RowType = 2
)
,Gaps AS (
    SELECT RowNumber - ROW_NUMBER() OVER (ORDER BY RowNumber) AS Gap
    FROM Type2RowNumbers
)
SELECT COUNT(DISTINCT Gap) 
FROM Gaps
WHERE Gap > 0

Upvotes: 1

EarlOfEnnui
EarlOfEnnui

Reputation: 555

Not sure this will work with SSRS, but it might help.

SELECT t.dt BeginType2, ndt.dt EndType2, Type1.Cnt FROM tblTesting t
    CROSS APPLY (SELECT TOP 1 dt, RowType 
                FROM tblTesting t2 
                WHERE t2.dt > t.dt AND RowType = 2 
                ORDER BY dt) ndt
    CROSS APPLY (SELECT COUNT(*) Cnt 
                FROM tblTesting t3 
                WHERE t3.dt > t.dt AND t3.dt < ndt.dt AND RowType = 1) Type1
WHERE t.rowType = 2

Upvotes: 0

Related Questions