Clever Geek Handbook

How to get the result of this column in SQL Server

Try going back to the dataset below in SQL Server. I have 2 columns TrailerPosition and Divider and want to return Zone as well. The zone will be calculated as starting with Zone 1 and then changing to zone 2 in the entry after the divider = 1. And then up to 3 after the next entry where Divider = 1. The screenshot below looks like the column I'm trying to return.

any ideas how this can be done in SQL Server?

Test data for below:

declare @t table (TrailerPosition nvarchar(5),Divider bit);
insert into @t values ('01L',0),('01R',0),('02L',0),('02R',1),('03L',1),('03R',0),('04L',0),('04R',0),('05L',1),('05R',1),('06L',0),('06R',0),('07L',0),('07R',0),('08L',0),('08R',0),('09L',0),('09R',0),('10L',0),('10R',0),('11L',0),('11R',0),('12L',0),('12R',0),('13L',0),('13R',0),('14L',0),('14R',0),('15L',0),('15R',0);

enter image description here

+3


source to share


2 answers


If 2012+, window functions would fit well here.

Select TrailerPosition
      ,Divider
      ,Zone    = 1+sum(Flag) over (Order By TrailerPosition)
 From (
        Select *
              ,Flag = case when Lag(Divider,1) over (Order By TrailerPosition) =1 and Divider=0 then 1 else 0 end
         From  YourTable
      ) A


Returns

enter image description here

+4


source


So, Zone = 1 + The number of previous rows with a divisor of 0 and the previous row with a divisor of 1.

UPDATED



SELECT TrailerPosition, Divider,
     (SELECT COUNT(*) 
     FROM @MyTable T1
     WHERE (T1.TrailerPosition <= t0.TrailerPosition)
        AND (T1.Divider = 0)        
        AND (SELECT Divider 
             FROM @MyTable t2
             WHERE T2.TrailerPosition = 
                 (SELECT MAX(T3.TrailerPosition) 
                  FROM @MyTable T3 
                  WHERE T3.TrailerPosition < T1.TrailerPosition)) = 1) + 1 
                     AS Zone
 FROM @MyTable t0
0


source






More articles:


All Articles