9

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 2 '14 at 9:41
  • 1
    Use a calendar table, select from that and then join to your frequencies by date social.technet.microsoft.com/wiki/contents/articles/… – Mark Sinkinson Sep 2 '14 at 9:47
  • I am using query to fetch the result from the main table. – Arvin Sep 2 '14 at 9:49
  • 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 2 '14 at 12:20
  • 1
    @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 2 '14 at 12:30
12

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);

SQLfiddle 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);

SQLfiddle example

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

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
  • 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 2 '14 at 12:53
  • @AaronBertrand The range is pretty small here,but any link to alternatives?For my curiosity. – Mihai Sep 2 '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 2 '14 at 13:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.