4

I'm writing a report that needs to display a value per day. I have the start and end date for the query, but I wish to avoid missing days in case the table does not contain a value for a specific date. I was thinking about creating a base date range table that holds all days between start and end, then left join it with the data table to show a value for each day.

I found a few scripts for mySQL, SQL Server, etc.. but none for SQLite. Is there a way to quickly populate a table with a date range?

Thanks

itayw
  • 614
  • 9
  • 20
  • possible duplicate of [Get all dates in date range in SQL Server](http://stackoverflow.com/questions/9140308/get-all-dates-in-date-range-in-sql-server) – Andriy M Apr 29 '12 at 23:36
  • Thanks @AndriyM, but the thread indicated contains a specific example for implementation using T-SQL on MS SQL Server. Unfortunately (or fortunately, depends who you ask) I'm working with SQLite which means I cannot use the solution indicated. – itayw Apr 30 '12 at 06:47
  • I believe something happened to my eyes when I was looking at your question, otherwise I can't explain how I could miss the `sqlite` tag. Sorry. Still, the accepted answer's basic idea is to use a numbers table. Perhaps it could be applied to your situation as well. – Andriy M Apr 30 '12 at 06:59

2 Answers2

3

Here's how you could use a numbers table to expand a range:

SELECT
  datetime('now', N || ' seconds') AS DT
FROM numbers
WHERE N < strftime('%s', 'now', '1 minutes') - strftime('%s', 'now');

In this case, the numbers table is supposed to hold numbers starting from 0.

A numbers table is a tool worth keeping handy for many purposes. You could initialise it like this, for instance:

CREATE TABLE numbers (N int);
/* #0 */ INSERT INTO numbers (N) SELECT 0;
/* #1 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #2 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #3 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #4 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #5 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #6 */ INSERT INTO numbers (N) SELECT N + C FROM numbers, (SELECT COUNT(*) AS C FROM numbers);
/* #… */

Every line #N results in 2N rows in the table, the largest number being 2N-1

A demonstration of the method can be found (and played with) on SQL Fiddle.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • 1
    @mr. kav number tables are very use full, see [What is the best way to create and populate a numbers table?](http://stackoverflow.com/q/1393951/65223) – KM. May 01 '12 at 13:15
0

it depends the level of details you want. If you simply want the dates from A until B you can do this:

declare @start datetime
set @start = '04/20/2012'
while @start <getdate() begin
  print @start
  set @start = dateadd(dd,1,@start)
end

just change the print to a insert on a table.

If you need more details you may need a more elaborate scripts. I can point you to some, just be more specific on your requirements

Diego
  • 34,802
  • 21
  • 91
  • 134
  • Thanks, however I was looking for a query that calculates the dates on the fly to save the iterations processing time. I'm looking to have a second-by-second breakdown of a year for example. – itayw Apr 30 '12 at 06:45