SQLTeam.com | Weblogs | Forums

Left join date record

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

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)

great..that's what I want thx a lot

I know you think that's great because it solves your issue but it creates both a resource usage and performance because @harishgg1 used a bloody Recursive CTE to create the dates. I strongly recommend that you do NOT use that method. Even such small row counts are major but totally unnecessary wastes of resources and duration. You might not think so but just imagine if everyone on your server wrote code that did such terrible things.

Here's a link to an article that explains and compares 3 alternate methods. Although not included in that article, I'll also tell you that a well formed Loop and Temp Table can beat a recursive CTE of this nature.

Stop using recursive CTEs that count, PERIOD! They're killers even with small row counts. It's NOT ok to drink poison no matter how small the amount.

hi Jeff

I am providing a different way using CROSS JOIN ...way

please click arrow to the left for drop create data ...
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
-- of course leap years etc etc are there 

select * from #transaction_history
go

declare @start_date date = '20180101'
declare @end_date date = '20181231'

; WITH --===== Classic Cross Join method
cteTally AS
( --=== Pseudo Cursor counts from 0 to the number of days needed
 SELECT TOP (365)
        N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1
   FROM sys.all_columns st1
  CROSS JOIN sys.all_columns st2
)--==== Add the counter value to a start date and you get multiple dates
 SELECT WholeDate = DATEADD(dd,N  ,@start_date),  ISNULL(b.transid,0)      
   FROM cteTally a left join #transaction_history b 
on DATEADD(dd,N  ,@start_date)  = b.date
;

Excellent, Harish. Just a couple of more tweaks so no hard coding of days between dates and to "DRY" the code out just a bit.

DECLARE  @StartDate DATE = '20180101'
        ,@EndDate   DATE = '20181231'
;
--===== Classic Cross Join method
   WITH cteAllDates AS
( --=== Pseudo Cursor (pc) counts from 0 to the number of days needed and adds that to the 
     -- start date to come up with all dates in the desired range.
     -- Calculating the date here also applies "DRY" principles to simplify the outer query.
 SELECT TOP (DATEDIFF(dd,@StartDate,@EndDate)+1) --Number of days in the date range
        WholeDate = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,@StartDate)
   FROM      sys.all_columns pc1
  CROSS JOIN sys.all_columns pc2
)--==== The rest is simple now.  Just do the outer join to the dates we created above.
 SELECT a.WholeDate
      , TransID = ISNULL(b.transid,0)      
   FROM      cteAllDates          a 
   LEFT JOIN #transaction_history b 
     ON a.WholeDate  = b.date
;

Given the question, why can't we just use a LEFT OUTER JOIN?

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

SELECT A.[date]
	, COALESCE(B.transid, 0) as transid
FROM A
	LEFT OUTER JOIN B
    	ON A.[date] = B.[date]
WHERE A.[date] BETWEEN @start_date AND @end_date

What am I missing?

I've tried this before but not working..some record cannot show as should be..
thx

jtaniarto,

I don't understand why it doesn't work. It definitely should.
Most likely I made bad assumptions about the data in table A or B.

Can you give some sample data of your A and B tables? Make sure you include the data that should show but doesn't.
Also give the LEFT OUTER JOIN query that doesn't work.

I agree. Left join should work with Calendar table as the driver

From Calendar c
left join TransactionTable t
on c.date = t.date
where c.date between startDate and endDate

those way only show existing transaction record only on table transaction table. It doesn't show all of the calendar date record. for example
Transaction exists on 2019-12-11 and the result of the query only show 2019-12-11 and not showing the 2019-12-1 to 2019-12-10 and 2019-12-12 until 2019-12-31.

thx

That means that the calendar table is missing a number of dates or that you are not using a LEFT OUTER JOIN.

As you do not provide us with the data from both tables and the exact query you are using as I asked, I can't help you any further.

what kind of data do you need? and email address for the data

thx

Give the CREATE TABLE and INSERT INTO scripts with sample data for both tables.
You can reuse Harish's work "please click arrow to the left for drop create data ..." as a guide for the transaction table and make something similar for the calender table. You only need to give the data for the date column of the calender table.

Give all the records that are in the calendar table for December 1st until the 15th 2019.
Give a small (10) number of records in the transaction table for that period (2019-12-01 until 2019-12-15): give a few transactions that happened on one day and a few transaction that occurred all on other days.

If you can do that, that would be great. Then we can help you further.

There is no need for an e-mail address. Just post it here. Only provide the columns that are really needed (calendar.date, transaction.date, transaction.transaction_id).
That way no business secrets are exposed.