DROP TABLE IF EXISTS #tOffers
CREATE TABLE #tOffers(
offerID BIGINT IDENTITY(1,1),
offerStartDate DATETIME,
offerEndDate DATETIME)
INSERT INTO #tOffers
VALUES ('20180101','20180304'),
('20180105','20180415'),
('20180205','20180410'),
('20180305','20180715');
;with dates(d) as (
select cast('20180101' as datetime)
union all
select dateadd(day, 1, d) as d from dates where d<='20181230'
)
SELECT t1.d,COUNT(offerid)
FROM
dates t1
LEFT JOIN
#tOffers t2
ON
t1.d
BETWEEN
t2.offerStartDate AND t2.offerEndDate
group by d OPTION (MAXRECURSION 32767) ;