I will describe problem briefly.
----------------------------------------------------------------------------------------------------
| Total UnitName UnitValue PartlyStatus PartlyValue CountMetric CountValue | RowNo
| |
| 79 A 7654 B 0 C 360 | 1
| 79 A 7656 B 0 C 360 | 2
| 79 A 7657 B 0 C 360 | 2
| 79 A 7658 B 0 C 360 | 2
| 79 A 7659 B 1 C 240 | 3
| 79 A 7660 B 0 C 360 | 4
| 79 A 7662 B 1 C 240 | 5
| 79 A 7663 B 1 C 240 | 5
| 79 A 7664 B 1 C 240 | 5
| 79 A 7665 B 1 C 240 | 5
| 79 A 7667 B 1 C 240 | 6
| 79 A 7668 B 1 C 240 | 6
| 79 A 7669 B 1 C 240 | 6
| 79 A 7670 B 0 C 360 | 7
| 79 A 7671 B 0 C 360 | 7
| 79 A 7672 B 0 C 360 | 7
---------------------------------------------------------------------------------------------------
I have to create new row in my table in SQL Server Reporting Services(SSRS) if constraint is not satisfied.
Rules that i have to apply:
If UnitValue Numbers are not consecutive, use next row.
If binary values of partlyValue changes, use next row.
I have to write a query that creates a RowNo, which increments if conditions are not satisfied.
The table that i show is a derived result from long query to demonstrate problem. RowNo column is written for showing intended result.
My question is asked for understanding and thinking about elegant approaches to solve problem, so conceptual query examples or solutions are fine for me as long as it puts me in a right direction.
I think you just want window functions. It is a little hard to follow the logic but this does what you want:
select t.*,
sum(case when prev_uv = unitvalue - 1 and
prev_pv = partlyvalue
then 0 -- no new group
else 1
end) over (order by unitvalue) as rowno
from (select t.*,
lag(unitvalue) over (order by unitvalue) as prev_uv,
lag(partlyvalue) over (order by unitvalue) as prev_pv
from t
) t;