CODEWITHSUNDEEP
sqlt-sql
Node17
Node17

Reputation: 537

Looping through two tables to generate a new table

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

Date |

Day (datetime)

Numbers |

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions