Looking to try to return the dataset below in SQL Server. I have the 2 columns TrailerPosition & Divider and looking to also return Zone. Zone would be calculated as starting with Zone 1 and then would change to zone 2 on the record after divider = 1. And then to 3 after the next record 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 the 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);
