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.
8,551 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Jingyang Li 4,366 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')
    
    No comments

  2. Guoxiong 7,681 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
    );
    
    No comments

  3. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    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.

    No comments