select t1.Id,t1.Date1,t2.Total
from #mytable1 t1
left join #mytable2 t2 on t1.Date1>=t2.StartDate
and t1.Date1<=ISNULL(t2.EndDate,'2099-12-31')
Get the total based on date range
Hi ,I am looking to get the output as in #mytable. Source is 2 tables #mytable1 and #mytable2. If the date1 falls between start and end period it should give the total else show NULL for rest of the rows. Please help.
DROP TABLE IF EXISTS #mytable1;
CREATE TABLE #mytable1(
Id INT
,Date1 DATE
);
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2018-08-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2018-08-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2018-08-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2018-08-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2019-07-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2019-07-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2019-07-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2019-07-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2020-04-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2020-04-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2020-04-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2020-04-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2020-05-01');
INSERT INTO #mytable1(Id,Date1) VALUES (1,'2020-05-01');
DROP TABLE IF EXISTS #mytable2;
CREATE TABLE #mytable2(
ID INT NOT NULL
,Total INTEGER NOT NULL
,StartDate DATE NOT NULL
,EndDate DATE
);
INSERT INTO #mytable2(ID,Total,StartDate,EndDate) VALUES (1,222,'2019-08-01','2020-04-01');
INSERT INTO #mytable2(ID,Total,StartDate,EndDate) VALUES (1,555,'2020-05-01',NULL);
DROP TABLE IF EXISTS #mytable;
CREATE TABLE #mytable(
Id INT
,Date1 DATE
,Total INTEGER
);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2018-08-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2018-08-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2018-08-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2018-08-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2019-07-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2019-07-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2019-07-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2019-07-01',NULL);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2020-04-01',222);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2020-04-01',222);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2020-04-01',222);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2020-04-01',222);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2020-05-01',555);
INSERT INTO #mytable(Id,Date1,Total) VALUES (1,'2020-05-01',555);
select * from #mytable
3 answers
Sort by: Most helpful
-
Jingyang Li 5,891 Reputation points
2020-09-10T16:06:01.393+00:00 -
Guoxiong 8,206 Reputation points
2020-09-10T16:36:14.077+00:00 SELECT t1.*, t2.Total FROM #mytable1 AS t1 LEFT JOIN #mytable2 AS t2 ON t1.Id = t2.ID AND ( t2.EndDate IS NOT NULL AND t1.Date1 BETWEEN t2.StartDate AND t2.EndDate OR t2.EndDate IS NULL AND t1.Date1 >= t2.StartDate );
-
MelissaMa-MSFT 24,196 Reputation points
2020-09-11T01:58:20.723+00:00 Hi @Papil11 ,
Please also find another method which uses DATEDIFF function from below:
select t1.Id,t1.Date1,t2.Total from #mytable1 t1 left join #mytable2 t2 on DATEDIFF(DAY,t2.StartDate,t1.Date1)>=0 and DATEDIFF(DAY,t1.Date1,ISNULL(t2.EndDate,'2099-12-31'))>=0
You could define the date as '2099-12-31',getdate() as currect date or any other date when the enddate of #mytable2 is null.
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.