I am looking to fill in NULL Start Dates by finding the one date that is shown and using that date within the Numeric Date Group. What I have is this:

What I would like is this:

Note that the Numeric Date is not the same as Start Date so using dateadd(day, [Numeric Date], '1840-12-31') to find the date to CASE statement is not going to work. This is what I have:

SELECT DISTINCT
[Order ID]
,LEFT([Numeric Date ID], 5) 'Numeric Date'
,[Numeric Date ID]
,[Start Date]

FROM Orders
WHERE [Order ID] = '478421'

CodePudding user response：

A simple window function looks like it should do the trick

SELECT
  o.[Order ID],
  CAST(o.[Numeric Date ID] AS int) [Numeric Date],
  o.[Numeric Date ID],
  MIN(o.[Start Date]) OVER (
      PARTITION BY o.[Order ID], CAST(o.[Numeric Date ID] AS int)) [Start Date]
FROM Orders o;

db<>fiddle

Instead of doing LEFT on [Numeric Date ID] it seems better to just cast it to int.

CodePudding user response：

This is one approach:

--Get list of Numeric Date -> Start Date
WITH dates_cte AS(
  SELECT DISTINCT
    [Numeric Date],
    [Start Date]
  FROM dbo.MyTable
)
SELECT
  MyTable.[Order ID]
  MyTable.[Numeric Date]
  MyTable.[Numeric Date ID]
  AllDates.[Start Date]
FROM dbo.MyTable MyTable
JOIN dates_cte AllDates
ON MyTable.[Numeric Date] = AllDates.[Numeric Date]