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
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
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
AS (SELECT N=0,
@start_date AS [Date]
SELECT n + 1,
Dateadd(dd, n + 1, @start_date)
WHERE Dateadd(dd, n + 1, @start_date) <= @end_date)
OPTION (maxrecursion 400)
please click arrow to the left for sample data transaction history
drop table #transaction_history
create table #transaction_history
[date] date ,
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'
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]
select N+1,DATEADD(dd,N+1,@start_date) from tally_Year
where DATEADD(dd,N+1,@start_date) <= @end_date
from tally_Year a left join #transaction_history b
on a.Date = b.date
option (maxrecursion 400)