CODEWITHSUNDEEP
sql-servert-sql
user55665484375
user55665484375

Reputation: 181

Remove subquery from query involving date between two other dates

I have two tables first one is like this call this table StartEndDates

| StartDate    | EndDate    | SomeOtherData |
|2018-09-08    | 2018-09-23 | data 1        |
|2018-07-08    | 2018-08-01 | data 2        |

The other table is like this call this table DataAndCreatedOn


| Data   | CreatedOn  |
| 1.5    | 2018-09-20 | 
| 2.4    | 2018-07-15 |
| 3.5    | 2018-07-21 |

So now I have a query like this

Select sed.someotherdata
    , (select sum(data) 
       from DataAndCreatedOn 
       where CreatedOn between sed.StartDate and sed.EndDate) as total 
from StartEndDates sed

Is there a way to get rid of the subquery (select sum(data) from DataAndCreatedOn where CreatedOn between sed.StartDate and sed.EndDate) as total and put it as a join somehow?

Upvotes: 0

Views: 57

Answers (2)

JMabee
JMabee

Reputation: 2300

You could also use a CROSS APPLY if you really wanted to get rid of that subquery, although the execution plan is almost identical. As @EzLo pointed out, and as you will see from my example, if you have duplicate SomeOtherData it will alter your results based on if you do a LEFT JOIN or a CROSS APPLY. So it depends on what your are looking to get back from your query. I would also pay attention to query performance:

CREATE TABLE #StartEndDates(StartDate  date, EndDate  date, SomeOtherData varchar(100))
CREATE TABLE #DateAndCreatedOn(Data decimal(10,4), CreatedOn date)

INSERT INTO #StartEndDates VALUES('2018-09-08', '2018-09-23', 'data 1')
INSERT INTO #StartEndDates VALUES('2018-07-08', '2018-08-01', 'data 2')
INSERT INTO #StartEndDates VALUES('2018-07-21', '2018-08-12', 'data 3')

--INSERT THIS ROW TO SEE DUPLICATE SomeOtherData
INSERT INTO #StartEndDates VALUES('2018-07-11', '2018-08-12', 'data 3')


INSERT INTO #DateAndCreatedOn VALUES(1.5,'2018-09-20')
INSERT INTO #DateAndCreatedOn VALUES(2.4,'2018-07-15')
INSERT INTO #DateAndCreatedOn VALUES(3.5,'2018-07-21')



--ORIGINAL QUERY
 Select sed.someotherdata
    , (select sum(data) 
       from #DateAndCreatedOn 
       where CreatedOn between sed.StartDate and sed.EndDate) as total 
from #StartEndDates sed


--LEFT JOIN QUERY
SELECT
    sed.someotherdata,
    SUM(d.data) as total
FROM
    #StartEndDates sed
    LEFT JOIN #DateAndCreatedOn d ON d.CreatedOn BETWEEN sed.StartDate and sed.EndDate
GROUP BY
    sed.someotherdata


--CROSS APPLY QUERY
 SELECT sed.SomeOtherData, x.SM
 FROM #StartEndDates sed
 CROSS APPLY (SELECT SUM(Data) SM  from #DateAndCreatedOn d where d.CreatedOn between sed.StartDate and sed.EndDate) x

Upvotes: 0

EzLo
EzLo

Reputation: 14199

You can use a JOIN and a GROUP BY:

SELECT
    sed.someotherdata,
    SUM(d.data) as total
FROM
    StartEndDates sed
    LEFT JOIN DataAndCreatedOn d ON d.CreatedOn BETWEEN sed.StartDate and sed.EndDate
GROUP BY
    sed.someotherdata

However the rows of StartEndDates will be grouped (you might get less rows compared to your original query if you have duplicate values on someotherdata).

Upvotes: 1

Related Questions