Calling all hardcore SQL experts! A challenge

[_V_]

This might be simple if you know how, but I'm a bit stumped.

Requirement:

Using pure ANSI-92 SQL (select, joins, sub queries, unions, that sort of thing), but no loops (while, for, repeat) and no cursors and no proprietary extension functions, expand two date parameters into individual dates. You can use any extra "lookup" join tables you might need.

E.g. given 20/12/2008 and 04/01/2009 return

20/12/2008
21/12/2008
22/12/2008
23/12/2008
24/12/2008
25/12/2008
26/12/2008
27/12/2008
28/12/2008
29/12/2008
30/12/2008
31/12/2008
01/01/2009
02/01/2009
03/01/2009
04/01/2009


I appreciate you might need a special table to join on containing monotonically increasing values to expand two parameters into multiple rows.

Maybe it's impossible (or impractical) without procedural loops or cursors?

If so, fine, I'll do it in code in the application rather than in the database.

TIA

[BrilloPad]

This might be simple if you know how, but I'm a bit stumped.

Requirement:

Using pure ANSI-92 SQL (select, joins, sub queries, unions, that sort of thing), but no loops (while, for, repeat) and no cursors and no proprietary extension functions, expand two date parameters into individual dates. You can use any extra "lookup" join tables you might need.

E.g. given 20/12/2008 and 04/01/2009 return

20/12/2008
21/12/2008
22/12/2008
23/12/2008
24/12/2008
25/12/2008
26/12/2008
27/12/2008
28/12/2008
29/12/2008
30/12/2008
31/12/2008
01/01/2009
02/01/2009
03/01/2009
04/01/2009


I appreciate you might need a special table to join on containing monotonically increasing values to expand two parameters into multiple rows.

Maybe it's impossible (or impractical) without procedural loops or cursors?

If so, fine, I'll do it in code in the application rather than in the database.

TIA

Why no while loop? Its the easiest way and anything else will just obstrufucate the code - or is that the aim?

[_V_]

Why no while loop? Its the easiest way and anything else will just obstrufucate the code - or is that the aim?

The database is the most widely used in the world. Is it Oracle? No. Is it SQL Server? No Is it MySQL? No.

It's SQLite. http://www.sqlite.org/mostdeployed.html

It has no loops, no cursors, no nuffink.

http://www.sqlite.org/lang.html

[BrilloPad]

http://www.sqlite.org/lang_keywords.html

There is a "FOR" statement - can't that be used?

[_V_]

http://www.sqlite.org/lang_keywords.html

There is a "FOR" statement - can't that be used?

I don't think the FOR is a loop in SQLite.

It is used when specifying triggers.

Note - I was hoping there was a std way of expanding two dates using maybe a lookup table?

[BrilloPad]

Do you know anything about the 2 given dates? Their min/max or the max of the difference between them?

I was sort of thginking of having a table with an entire universe of dates then doing a "between". Or using several "if" statements. Neither is nice!

[London75]

Create a table with sequential numbers in so single column, integers. You need as many numbers as your biggest date range.

Then in the query join to the table (and I'm not familiar with sqllite but in SQL Server it would be as follows). The join is a cartesian but syntax may vary on how to implement, you might need a special operator in SQLLite

select
t1.*,
dateadd(d,-t2.seq,<upperdate>)
from
table1 t1,
table2 t2
where
dateadd(d,-t2.seq,<upperdate>) >= <lowerdate>

The great thing is, no amendments are required to the table although CPU is higher each time it's run. But hey, quad core 3GHz who cares any more. I know I gave up caring a couple of years ago!

[_V_]

Create a table with sequential numbers in so single column, integers. You need as many numbers as your biggest date range.

Then in the query join to the table (and I'm not familiar with sqllite but in SQL Server it would be as follows). The join is a cartesian but syntax may vary on how to implement, you might need a special operator in SQLLite

select
t1.*,
dateadd(d,-t2.seq,<upperdate>)
from
table1 t1,
table2 t2
where
dateadd(d,-t2.seq,<upperdate>) >= <lowerdate>

The great thing is, no amendments are required to the table although CPU is higher each time it's run. But hey, quad core 3GHz who cares any more. I know I gave up caring a couple of years ago!

Nice work!

Exactly the sort of solution I was looking for. I knew about this technique but didn't know quite how to implement it.

It means I can now restrict and sort the data how I want it in the SQL.

[Spacecadet]

same but slightly different approach from London75's is to create a "dates" table which contains all dates which you could possibly be interested in (years 1900 to 2100), great thing about this is that it can be expanded to include lots of date type stuff

SQLDate as date -- primary key/ clustered index
DisplayDate as varchar -- you can have as many types of these as you like depending on the formats you want to display the date in
Weekday as boolean -- flag to specify a weekday/weekend
PublicHoliday as boolean -- public holiday flag
DayName as Varchar -- monday, tuesday, wednesday etc...
ShortDayName as Varchar -- Mon, Tue
MonthName as Varchar -- January, Feburary etc...
ShortMonthName as varchar
DayNum as int -- 1, 2, 3, .... 365
WeekNum as int -- 1, 2, 3, ...52
MonthNum as int -- 1, 2,... 12

-- then add some company specific columns
FinancialYear as int
FinancialPeriod as int
SalesYear as int
SalesPeriod as int
BudgetYear as int
BudgetPeriod as int

when joining on to another table with dates to get a full range of dates with nothing left out you can then use an outer join:

select *
from detailsTable A
full outer join DatesTable D
on A.dateCol = D.SQLDate

if you wanted only week days then you can filter on your dates table

where D.weekday = True

As you have the weekend and public holiday flags then these columns can be used be other systems for conditional formatting

[t0bytoo]

It ain't sql, but you've got to admin that Ruby is pretty sexy for this kind of thing:

(date_start..date_end).map{|d| d.month == 12 ? d : d.at_beginning_of_month}.uniq