Reputation: 537
I want to do a table valued function that returns a date and a number present for that day from a given numbers table.
I already have a function that you provide with a start and finish date to get all the dates required (dbo.Dates(@start, @finish) , and the number table, which has the numbers that i need for each day.
The tables are as follows
Day (datetime)
number int name varchar
The output I require is below.
Date | Number ------------------ 20120501 | 1 20120501 | 2 20120501 | 3 20120502 | 1 20120502 | 2 20120502 | 3 20120503 | 1 ....
Upvotes: 0
Views: 75
Reputation: 1271231
If you need all numbers on all days, then use cross join:
select d.date, n.number
from dbo.Dates(@start, @finish) cross join
Numbers n
You then just need to put the "create function" wrapper around this to get a table valued function.
Upvotes: 2