Get nearest date column value from another table in SQL server

A couple of guidelines I would like to add - more about asking questions than this specific problem

Starting with your sample data e.g.

PstngDate	WorkingDayOutput
    12/1/2020	221	   

If I put that into a table and then query it I get the data back as 2020-01-12 i.e. 12th January 2020. Based on the rest of your data it looks like this should mean 1st December 2020.

HINT: Always use ISO 8601 format dates. You are less likely to get errors arising because of ambiguities like this.

HINT: When providing sample data, give us the output from SELECT * FROM [table]. The way you have shared your data with us tells me one of two things - you have either typed this in manually OR you are storing your date data in a string type column (e.g. varchar or nvarchar). Never use strings to store dates, always use an appropriate type such as Date or Datetime. The same applies to numeric data. Only use strings for textual data.

Now to your code...

The first problem I had when I ran your code was an error message

Quote:

Msg 207, Level 16, State 1, Line 39
Invalid column name 'pstng_date'.

This is because the column headers on the sample data you provided do not match the columns in your tables.

HINT: When posting sample data make sure the column headers match the tables you use in your query. Better yet, provide the DDL to create the table and some SQL to insert the values. E.g.

declare @b table(PstngDate date,holidayquantity int,isWorkingDay bit)
insert into @b(PstngDate,holidayquantity,isWorkingDay) values
('2020-12-02',	    20,	            0),
('2020-12-06',	    24	,            0),
('2020-12-13',	31	   ,         0),
('2020-12-19',	82	   ,         0),
('2020-12-22',	507	   ,         0),
('2020-12-27',	537	   ,         0)

After I fixed that I got another error

Quote:

Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ')'.

This is because you used b as the alias for your apply sub-query but b is already the name of one of your tables

HINT: Make table names meaningful, or at least longer than a single character - see how @Maciej-Los has used TableA instead. Then you are less likely to run into this problem if you start using single character alias names.

Next problem was

Quote:

Msg 207, Level 16, State 1, Line 34
Invalid column name 'holidayquantity'.

I assumed this is because the column in table b IS NOT called holidayoutput but is holidayquantity

HINT: Same as above - make sure the column headers match, but also, make sure your posted code compiles, or if it doesn't, then say so up front!

Why am I bothering to tell you all this? Because, by the time I did all that I wasn't really inclined to look at your actual problem and find a solution for you. Most members would have given up after the first error message. Luckily for you, Maciej has more patience than most.

In fact, writing questions in a way to encourage speedy, accurate solutions is so important people write articles about it! For example Some guidelines for posting questions in the forums[^]