Databases
StackOverflow2013
Postsdbo
POT-SQL query update null values
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT-SQL query update null values
    primarykey
    data
    text
    <p>I have a very specific problem in T-SQL.</p> <p>If I can solve this example case I give you I think I will be able to solve my original case.</p> <p>Having this data in a table:</p> <pre><code>DECLARE @Test TABLE ( Value INT ,Date DATETIME2(7) ); INSERT INTO @Test VALUES (NULL, '2011-01-01 10:00'), (NULL, '2011-01-01 11:00'), (2, '2011-01-01 12:00'), (NULL, '2011-01-01 13:00'), (3, '2011-01-01 14:00'), (NULL, '2011-01-01 15:00'), (NULL, '2011-01-01 16:00'), (4, '2011-01-01 17:00'), (NULL, '2011-01-01 18:00'), (5, '2011-01-01 19:00'), (6, '2011-01-01 20:00') </code></pre> <p>I need to select this output:</p> <pre><code>Value Date 2 2011-01-01 10:00 2 2011-01-01 11:00 2 2011-01-01 12:00 2 2011-01-01 13:00 3 2011-01-01 14:00 3 2011-01-01 15:00 3 2011-01-01 16:00 4 2011-01-01 17:00 4 2011-01-01 18:00 5 2011-01-01 19:00 6 2011-01-01 20:00 </code></pre> <p>To give some explanation. If value is NULL somewhere I need to update with the value from the previous hour. If there are several null values in a row the closest earlier hour with a non null value propagates and fills all these null values. Also if the first hour of the day is null then the earliest hour on the day with a non null value propagates downwards like 2 in this case. In your case you can assume that at least one value is non null value.</p> <p>My ambition is to solve this with Common table expressions or something similar. With the cursor way I think I would have the solution in short bit of time if I try but my attempts with CTEs and recursive CTEs have failed so far.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJohn
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POT-SQL query update null values
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POT-SQL query update null values
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POT-SQL query update null values
      1. This table or related slice is empty.
      1. VTUpMod
    1. COwhat have you tried? It is helpful to post what you have done so far - you might be close and need just a few suggestions to correct your existing query.
      singulars
      1. POT-SQL query update null values
      1. USTaryn
    2. COSince I don't know how many null values there are in a row a single join on previous hour is not enough. There for I thought maybe I could solve it with recursion some way. I have only used CTE recursion the classic hierarchy way but my idea is that if I can update one null value each time in the recursion maybe I could fill them all. Actually I lost it pretty early when trying this idea.. So I don't believe there is much that will be of any help.
      singulars
      1. POT-SQL query update null values
      1. USJohn
    3. COIn your example, the `Value`s are non-decreasing as `Date` increases. Is this coincidence, or will it always be the case?
      singulars
      1. POT-SQL query update null values
      1. USAakashM
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload