Get the total based on date range

Papil11 21 Reputation points
2020-09-10T15:06:26.677+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,824 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jingyang Li 5,891 Reputation points
    2020-09-10T16:06:01.393+00:00 
    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')
    0 comments
  2. Guoxiong 8,126 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
);
    0 comments
  3. MelissaMa-MSFT 24,176 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.

    0 comments