So I have a table like this in MS SQL Server 2014:

ID| From  | To   | Diff |Tenths   
1 |19.023 |19.121|0.098 |0   
1 |19.121 |19.220|0.099 |1   
1 |19.220 |19.321|0.101 |2   
1 |19.321 |19.419|0.098 |3   
1 |19.419 |19.522|0.103 |4   
1 |19.522 |19.622|0.1   |5  
1 |19.622 |19.722|0.1   |6  
1 |19.722 |19.723|0.01  |7  

I am trying to do this:

Per ID-Ensure the difference is 0.1 for all tenths unless the diff is <=0.01 in which case I want it to be added to the previous entry.

The resulting set would look something like this:

ID| From  | To   | Diff|Tenths   
1 |19.023 |19.123|0.1  |0   
1 |19.123 |19.223|0.1  |1   
1 |19.223 |19.323|0.1  |2   
1 |19.323 |19.423|0.1  |3   
1 |19.423 |19.523|0.1  |4   
1 |19.523 |19.623|0.1  |5  
1 |19.623 |19.724|0.101|6 

I tried using CASE statement like this but it only gave me the first 2 rows, stuck in trying to figure out how to make it to work for the entire ID

select *, 
CASE when tenths=0 then NFrom else lag(Nfrom) over (partition by ID order by ID,tenths,From)+0.1 end Nfrom2,  
CASE WHEN tenths=0 then Nto+0.1 else lag(nto) over (partition by ID order by ID,tenths,from)+0.1 end Nto2   
   select *,
   CASE WHEN tenths=0 then From else Null end NFrom, 
   CASE when tenths=0 then from+0.1 else NULL end NTo
From testtable

Is there any way to do this?

  • I recreated the table on my environment and I want to help you out. Can you rephrase "Per ID-Ensure the difference is 0.1 for ..." because I don't get it. Give me just 1 row as it it and as it should be. Thank you Nov 17, 2020 at 18:48
  • @FrancescoMantovani In the above example I am only showing data for 1 ID but I have an ID=2 and ID=3 and so on, so I want to Partition by ID. I have given a table as it is and as it should be because SQL runs on sets, I cant provide just one row because that will not explain the use case because the last row should be added to the previous row if the difference for it is less than 0.01. I'm really sorry but I'm not sure how to make it any simpler. Hope this helps.
    – SQLserving
    Nov 17, 2020 at 19:04
  • I ask you this because I have created a table but your code is not working. "from+0.1" is underline in red. I cannot even execute the SELECT inside the parenthesis. Refactor that code, please Nov 17, 2020 at 21:51
  • Let me show you, I called the table "dtswizard" snipboard.io/T0sb58.jpg and as you can see there are errors everywhere, I just cannot execute it Nov 17, 2020 at 21:57


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.