Cartesian product between items list and date range

Question

With a Items List and a Date Range I need a table with all Items in all days between dates. I think I should use a cross join, but I don't know how can I get the dates in range. Sample data:

Start Date: 2014-06-01 End Date: 2014-06-03

I need this result:

Date         Item
2014-06-01   123456
2014-06-01   546578
2014-06-01   644821
2014-06-02   123456
2014-06-02   546578
2014-06-02   644821
2014-06-03   123456
2014-06-03   546578
2014-06-03   644821

Can anyone give-me some help? (It's in MS SQL Server)

have you tried Googling "SQL Server Cartesian Product"? The first result is exactly what you need. — cadrell0, Jun 05 '14 at 14:41
Probably my results are not your results. It's the first thing I've done. But, thank you anyway - I'll try harder... — PJLG, Jun 05 '14 at 14:52
See http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function for a start. — podiluska, Jun 05 '14 at 14:59

score 0 · Accepted Answer · edited May 23 '17 at 12:20

You can do so like this:

DECLARE @items TABLE (item INT)
INSERT INTO @items
        ( item )
VALUES  ( 123456  ),(546578),(644821)

DECLARE @startdate DATETIME = '2014-06-01'
DECLARE @enddate DATETIME = '2014-06-03'

;WITH mycte AS
(
  SELECT @startdate DateValue
  UNION ALL
  SELECT  DateValue + 1
  FROM    mycte   
  WHERE   DateValue + 1 <= @enddate
)

SELECT  DateValue, item
FROM    mycte
CROSS JOIN @items
OPTION (MAXRECURSION 0)

Reference - Used Dan Atkinson's post here.

Cartesian product between items list and date range

1 Answers1

Linked