Left join date

#1

Hi,
I have 2 tables . table A is calendar data it is contain of date of every days in a year ( start from january 1st until 31st December ) and table B is transaction contain of transaction history.
I need to join them based on the date so the result can be as below

date transid
01/11/2019 A0001
02/11/2019 A0002
03/11/2019 0
04/11/2019 A0003
05/11/2019 A0004
06/11/2019 0
07/11/2019 0
so on..
I try to make it by join the date between those table but it is not working. It shows only for existing data on table B it cannot show the all the calendar date . please help

thx

Joe

#2

Joe

Show us your query that is not working.

#3

You need to use a LEFT OUTER JOIN.
Your solution will be something in the line of this:

SELECT D.date_column
      , COALESCE(T.transaction_nr, '0')
FROM date_table as D
    LEFT OUTER JOIN transaction_table AS T
       ON D.date_column = T.transaction_date
#4

hi

i have created a tally table which populates all the dated of year !!

please click arrow to the left for Year data filled using Tally Table 
DECLARE @start_date DATE = '20180101' 
DECLARE @end_date DATE = '20181231' 
-- fo course leap years etc etc  
; 

WITH tally_year 
     AS (SELECT N=0, 
                @start_date AS [Date] 
         UNION ALL 
         SELECT n + 1, 
                Dateadd(dd, n + 1, @start_date) 
         FROM   tally_year 
         WHERE  Dateadd(dd, n + 1, @start_date) <= @end_date) 
SELECT * 
FROM   tally_year 
OPTION (maxrecursion 400)

image
image2860×1523 119 KB

#5

Hi

Here is something
Is this like what you want ????

please click arrow to the left for sample data transaction history 
drop table #transaction_history 
go 


create table #transaction_history 
(
[date] date , 
transid varchar(10)
)
go 

insert into #transaction_history select '2018-01-01','A0001'
insert into #transaction_history select '2018-01-02','A0002'
insert into #transaction_history select '2018-01-04','A0003'
insert into #transaction_history select '2018-01-05','A0004'
insert into #transaction_history select '2018-01-08','A0005'
go
please click arrow to the left for SQL with Year Data using tally table 
declare @start_date date = '20180101'
declare @end_date  date = '20181231'

-- of course leap years etc etc are there 

; with tally_Year as 
(
select N=0,@start_date as [Date]
union all 
select N+1,DATEADD(dd,N+1,@start_date)  from tally_Year
where DATEADD(dd,N+1,@start_date) <= @end_date 
)
select  a.Date,ISNULL(b.transid,0) 
from  tally_Year a left join #transaction_history b 
on a.Date = b.date
option (maxrecursion 400)

image
image574×549 4.66 KB

#6

Hey there, @jtaniarto ... please don't post the same question twice on duplicate posts. It only serves to split up answers that other people have made.

For anyone interested, here's the other post.