15,847,263 members
See more:
I have two tables A and B,

Table A

```PstngDate	WorkingDayOutput
12/1/2020	221
12/3/2020	327
12/4/2020	509
12/5/2020	418
12/7/2020	390
12/8/2020	431
12/9/2020	244
12/10/2020	246
12/11/2020	314
12/12/2020	301
12/14/2020	411
12/15/2020	530
12/16/2020	554
12/17/2020	300
12/18/2020	375
12/23/2020	402
12/24/2020	302
12/25/2020	269
12/26/2020	382
12/28/2020	608```

Table B

```PstngDate	HolidayOutput	isWorkingDay
12/2/2020	    20	            0
12/6/2020	    24	            0
12/13/2020	31	            0
12/19/2020	82	            0
12/22/2020	507	            0
12/27/2020	537	            0```

Expected output:

```PstngDate	WorkingDayOutput	HolidayOutput
12/1/2020	221	                    20
12/3/2020	327
12/4/2020	509
12/5/2020	418	                    24
12/7/2020	390
12/8/2020	431
12/9/2020	244
12/10/2020	246
12/11/2020	314
12/12/2020	301	                    31
12/14/2020	411
12/15/2020	530
12/16/2020	554
12/17/2020	300
12/18/2020	375	                    589
12/23/2020	402
12/24/2020	302
12/25/2020	269
12/26/2020	382	                    537
12/28/2020	608	```

I want to join TableB to TableA with nearest lesser date column. If you see Expectedoutput table, day 18 row of holidayoutput column is taking sum of day19 and day22 of table B.

What I have tried:

SQL
```select a.*, coalesce(b.holidayquantity, 0) as holidayquantity
from a
outer apply (
select top (1) b.*
from b
where b.pstng_date >= a.pstng_date
order by b.pstng_date
) b```
Posted
Updated 31-Dec-20 0:22am
v3
Maciej Los 31-Dec-20 1:53am
You need to define "nearest date".

## Solution 1

If i understand you well...

Try this:
SQL
```;WITH CTE AS
(
SELECT PstngDate, LEAD(PstngDate) OVER(ORDER BY PstngDate) nextdate, WorkingDayOutput
FROM TableA
)
SELECT a.PstngDate, a.WorkingDayOutput, SUM(b.HolidayOutput) HolidayOutput
FROM CTE a LEFT JOIN TableB b ON  b.PstngDate > a.PstngDate AND b.PstngDate < a.nextdate
GROUP BY a.PstngDate, a.WorkingDayOutput
ORDER BY a.PstngDate```

Example: db<>fiddle[^]

v2
jazlaansam 31-Dec-20 5:26am
Data collection method is important
Member 14636607 31-Dec-20 5:56am
Thanks bro... Its working
Maciej Los 31-Dec-20 7:26am
You're very welcome.

## Solution 2

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.

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.
SQL
```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[^]