1

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.

nedensel
  • 43
  • 8
  • 1
    What version of SQL Server is this for? You've tagged it SSRS-2008. Can we assume you are using SQL Server 2008 also? Later versions provide better windowed function support as Gordon's answer uses so it will probably have a bearing on your final solution. – Alan Schofield Feb 04 '20 at 11:45
  • I am creating reports for SQL Server 2008, customers upgrade to 2016 soon. Later version answers are appreciated too. At the moment, i am using SQL Server 2016 to try Gordon's answer. Thanks for reply :) – nedensel Feb 04 '20 at 11:49

2 Answers2

1

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you Gordon, I adjusted query slightly and it worked for my query in 2016. I will try to solve this for SQL Server 2008. Since i am a little bit inexperienced in SQL, its very valuable to me learn new approaches. Thank you very much!!! – nedensel Feb 04 '20 at 12:14
  • 1
    @nedensel . . . I don't think there is an efficient way to do this in SQL Server 2008. – Gordon Linoff Feb 04 '20 at 15:51
0

You need to write functions in your solution explorer.

user2713535
  • 41
  • 7
  • SSRS Solution provides expressions field in cells that you can manipulate data. I am working on different approaches to solve. However, if similar problem experienced by someone and solved in query itself, its better for me than using expressions. Thank you anyway :) – nedensel Feb 04 '20 at 11:01