SQL Query to fill in Null Column values based on closest date to a given date

Question

Post reply

SQL Query to fill in Null Column values based on closest date to a given date

Paul Stasny

Mr or Mrs. 500

Points: 599
More actions
January 12, 2023 at 4:37 am
Go to Answer
#4136655
Greetings,
I am trying to fill in NULL values in a column (Advice) with the value from the closest date (higher or lower) with the same id. Hopefully my example will illustrate this. The table below has two id types (CustId and LeadId) and one will always be null if the other has a value. Each Id group also has a rownum based on Date. There are only about 500 empty values.
I can get the value I want using the script below. I tried self joining the table on custid,leadid and rownum and also using a cte to pull all of the empty values out and then try to update using it but again no luck. I was looking at creating a subquery but can't figure out how to incorporate the order by piece in the query that gives me the result I need I believe I could define variables for the Id and the Dates with the Null and Advice fields and loop through this, but I'm sure there is a better way to achieve this. Any suggestions would be appreciated.
```
Select top 1 Advice from Table where LeadId = 10 and advice is not null
order by ABS(Datediff(minute,'2022-12-01 14:10:37.050','2022-12-02:12:10:31.070)
```

David Burrows

SSC Guru

Points: 65143

January 13, 2023 at 2:48 pm

Answer

#4137416

WITH cte AS (
SELECT[Table].*
FROM (VALUES
(1,NULL,'2022-12-01T14:10:37.050',NULL,1),
(1,NUll,'2022-12-02T12:10:31.070','Hold',2),
(1,NULL,'2022-12-06T11:18:52.040','Load',3),
(NULL,10,'2022-12-04T15:14:14.050','Review',1),
(NULL,10,'2022-12-05T09:22:46.150',NULL,2),
(NULL,10,'2022-12-15T10:15:18.520',NULL,3),
(NULL,10,'2022-12-17T09:22:46.150','Buy',4)
) [Table] (CustId,LeadId,[Date],Advice,[Row])
)
SELECT cte.CustId,cte.LeadId,cte.[Date],cte.Advice,cte.[Row],ISNULL(c.Advice,l.Advice) AS [NewAdvice]
FROM cte
OUTER APPLY (
SELECT TOP(1) cte2.Advice
FROM cte cte2
WHERE cte2.CustId=cte.CustId
AND cte2.Advice IS NOT NULL
ORDER BY ABS(DATEDIFF(minute,cte.[Date],cte2.[Date])) ASC
) c
OUTER APPLY (
SELECT TOP(1) cte2.Advice
FROM cte cte2
WHERE cte2.LeadId=cte.LeadId
AND cte2.Advice IS NOT NULL
ORDER BY ABS(DATEDIFF(minute,cte.[Date],cte2.[Date])) ASC
) l
WHERE cte.Advice IS NULL
ORDER BY CustId DESC,LeadID DESC,cte.[Row]

This reply was modified 1 year, 11 months ago by David Burrows.

Far away is close at hand in the images of elsewhere.
Anon.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

Is it possible to provide an easy digestable test-case?

Table definition

Inserts for testcase

Wanted results

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

Jo Pattyn wrote:

Is it possible to provide an easy digestable test-case?
Table definition
Inserts for testcase
Wanted results

+1, we definitely need this in order to be able to give you a coded solution.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Phil Parkin wrote:

Jo Pattyn wrote:
Is it possible to provide an easy digestable test-case?
Table definition
Inserts for testcase
Wanted results
+1, we definitely need this in order to be able to give you a coded solution.

+100

We will need your help in order to be able to help you, so please help us!

😎

It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Maybe I'm still suffering from insufficient caffeine levels this morning, but I have to admit, David, I'm confused. Why did you change the row numbers from what the op originally posted. And, considering that the op specified WHERE LeadId = 10 in the code that supposedly created the graphic output he posted, how is it possible that ANY rows with a NULL in the LeadID column were returned?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Jeff Moden wrote:

Maybe I'm still suffering from insufficient caffeine levels this morning, but I have to admit, David, I'm confused. Why did you change the row numbers from what the op originally posted. And, considering that the op specified WHERE LeadId = 10 in the code that supposedly created the graphic output he posted, how is it possible that ANY rows with a NULL in the LeadID column were returned?

Sorry my bad, I changed them for testing and forgot to change them back. Changed my post accordingly.

I thought the OP wanted a solution to update all rows with NULL Advice. The OP indicated that the extra SQL supposedly gave him an answer but AFAIK it is malformed and will not work anyway. But hey I am winging this 😉

Far away is close at hand in the images of elsewhere.
Anon.

Paul Stasny Mr or Mrs. 500 Points: 599 More actions · Answer 6

I was able to adapt this to fit my needs. Thanks for your feedback as I had not tried an outer apply with a cte