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