hi
then how are you counting 10 11 13 14 as ... 4
is it because .. all A's continously .. ..
Yes.
hi
here is the SQL .. hope this helps Njoy !!
; with cte as
(
select *,ROW_NUMBER() over(order by dates ) as rn from @t
) , cte_lag as
(
select row_number() over(order by a.dates ) as rn1, a.scanCode, a.dates,case when a.flag <> b.flag then 1 else 0 end as goingto_sum,a.flag as curr_flag from cte a left join cte b on a.rn = b.rn +1
) , cte_sum_prev as
( select b.curr_flag,SUM(t.goingto_sum) as grp,b.scanCode,b.dates from cte_lag b inner join cte_lag t on b.rn1 >= t.rn1 group by b.scanCode,b.dates,b.curr_flag
)
select 'SQL Output',
scanCode
, curr_flag
, min(dates) as start_date
, max(dates) as end_date
, count(grp)
from
cte_sum_prev
where
curr_flag = 'A'
group by
scanCode, curr_flag,grp
1 Like
Once a master knows the real problem then it is easy for him to give better solution.
Now the solution is brief and elegant.
Not having server at home tested in a online compiler.
and the result is exactly what i wanted.
I wll enjoy ....and get appreciation from my boss.
Very best regards.
How about my million dollars
Solution to my initial requirement.