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
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
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)
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)
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.