Click here to Skip to main content
15,440,884 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 30-Dec-20 23:22pm
v3
Comments
Maciej Los 31-Dec-20 1:53am    
You need to define "nearest date".

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[^]
 
Share this answer
 
v2
Comments
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.
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.
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[^]
 
Share this answer
 
Comments
Maciej Los 31-Dec-20 5:38am    
Short And To The Point!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900