Insert Missing Dates from a query

Question

How can i insert missing dates from a query i created. The result below:

Date          Frequency
2014-05-18    5
2014-05-20    7
2014-05-25    7
2014-05-27    6

I want the result to have missing dates with 0 value as shown below:

Date          Frequency
2014-05-18    5
2014-05-19    0
2014-05-20    7
2014-05-21    0
2014-05-22    0
2014-05-23    0
2014-05-24    0
2014-05-25    7
2014-05-26    0
2014-05-27    6

Please note that I have read only access to the server.

are you using any query to fetch the result? or do you have a date range defined. can you add your query or table — vijayp, Sep 02 '14 at 09:41
Use a calendar table, select from that and then join to your frequencies by date http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx — Mark Sinkinson, Sep 02 '14 at 09:47
If you have read only access, then you are not supposed to insert or update the database. Instead ask your DBA team to help you out. — Kin Shah, Sep 02 '14 at 12:20
@Kin I think the question means they want to insert rows into the result set, rather than insert rows into an actual database table. — Mark Sinkinson, Sep 02 '14 at 12:30

Aaron Bertrand · Accepted Answer · 2021-09-08T14:38:05.540

Here is an example using a calendar table (which you really should have). This example just populates 2014 but you can stuff it with as many years as you like...

CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY);
INSERT dbo.Calendar(d) SELECT TOP (365)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20140101')
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number;

Now the query is simple:

DECLARE @s DATE = '20140518', @e DATE = '20140527';
SELECT c.d, Frequency = COALESCE(s.Frequency,0)
  FROM dbo.Calendar AS c
  LEFT OUTER JOIN dbo.splunge AS s
  ON c.d = s.[date]
  WHERE c.d >= @s
    AND c.d < DATEADD(DAY, 1, @e);

db<>fiddle example

If you can't create a calendar table (and don't have a numbers table handy either), then you can just put it inline:

DECLARE @s DATE = '20140518', @e DATE = '20140527';
SELECT c.d, Frequency = COALESCE(s.Frequency,0)
  FROM 
(
   SELECT TOP (DATEDIFF(DAY, @s, @e)+1)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, @s)
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number
) AS c(d)
  LEFT OUTER JOIN dbo.splunge2 AS s
  ON c.d = s.[date]
  WHERE c.d >= @s
    AND c.d < DATEADD(DAY, 1, @e);

db<>fiddle example

For more on generating sets (of dates, numbers, etc.) see this series:

And more on calendar tables:

score 0 · Answer 2 · answered Apr 09 '21 at 11:38

I just needed the query to return all the dates in the period I wanted. Without the joins. Thought I'd share for those wanting to put them in your query. Just change the 365 to whatever timeframe you are wanting.

DECLARE @s DATE = GETDATE()-365, @e DATE = GETDATE();
SELECT TOP (DATEDIFF(DAY, @s, @e)+1)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, @s)
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number

Mihai · Answer 3 · 2014-09-02T11:09:47.743

0

DECLARE @t TABLE(Dt Date,Frequency int)
INSERT INTO @t VALUES
('2014-05-18',5),('2014-05-20',7),('2014-05-25',7),('2014-05-27',6)



DECLARE @startDate DATE, @endDate DATE
SELECT @startDate = '2014-05-18', @endDate = '2014-05-27' --yyyy-mm-dd
;WITH Calender AS (
    SELECT @startDate AS CalanderDate
    UNION ALL
    SELECT DATEADD(day,1,CalanderDate) FROM Calender
    WHERE DATEADD(day,1,CalanderDate) <= @endDate
)
INSERT INTO @t SELECT
    Dt = CalanderDate,Frequency = 0

FROM Calender c
LEFT JOIN @t t 
ON t.Dt = c.CalanderDate
WHERE t.dt IS NULL
option (maxrecursion 0)

SELECT * FROM @t ORDER BY dt

FIDDLE

2014-05-18  5
2014-05-19  0
2014-05-20  7
2014-05-21  0
2014-05-22  0
2014-05-23  0
2014-05-24  0
2014-05-25  7
2014-05-26  0
2014-05-27  6

edited Sep 02 '14 at 11:09

answered Sep 02 '14 at 10:32

Mihai

1,543
1
13
16

The recursive CTE approach gets exponentially more expensive as the date range gets wider. There are more efficient ways to derive sets for this purpose. – Aaron Bertrand Sep 02 '14 at 12:53
@AaronBertrand The range is pretty small here,but any link to alternatives?For my curiosity. – Mihai Sep 02 '14 at 12:55
1

Yes, here, it happens to be a small range. The problem is people learn this approach and then apply it in much larger scales where it becomes a problem. Why use a slow approach just because it is "ok" in this case? See my answer. – Aaron Bertrand Sep 02 '14 at 13:01

Insert Missing Dates from a query

3 Answers3

Linked

Related