First time question here, I've been trying to solve myself for a few days without success, hoping someone here can help please?
I have a SQL table of products, prices, etc which I am trying to duplicate for each date in a range that I specify.
Here is the table:
| Product_ID | Product_Name | Unit_Price |
| ------------ | ------------ | ------------ |
| 1 | Laptop | 400.00 |
| 2 | Keyboard | 20.00 |
| 3 | Monitor | 150.00 |
Below is the result I am looking to achieve (the start and end date will be specified in the query):
Date | Product_ID | Product_Name | Unit_Price |
---|---|---|---|
01/01/2023 | 1 | Laptop | 400.00 |
01/01/2023 | 2 | Keyboard | 20.00 |
01/01/2023 | 3 | Monitor | 150.00 |
02/01/2023 | 1 | Laptop | 400.00 |
02/01/2023 | 2 | Keyboard | 20.00 |
02/01/2023 | 3 | Monitor | 150.00 |
03/01/2023 | 1 | Laptop | 400.00 |
03/01/2023 | 2 | Keyboard | 20.00 |
03/01/2023 | 3 | Monitor | 150.00 |
etc.....
I have tried a few different things (cross joins, recursive ctes) but I just can't seem to get it to work for my specific requirements.
Just to add, I am using MS SQL Server 2016.
Any help offered would be very gratefully received! Thanks in advance.
2条答案
按热度按时间qyyhg6bp1#
You can use cross join for this. ie:
fiddle
EDIT: Explanation on how it works. You pass start and end dates as variables. Using sys.all_columns (because it has enough rows for our purposes out of the box) we generate dates from start to end date. row_number() over (order by t1.object_id) -object_id is an arbitrary selected column- we get a sequence like 1,2,3....N and limit the rows using TOP( datediff(d, @start, @end)+1). For 2023\07\01 to 2023\07\02 it would be 2 as an example (TOP(2)). Then we use the value from row_number() which is 1,2, ..., N in dateadd(d, row_number() over (...)-1 ,@start) thus generating dates from @start to @end inclusive.
Last we cross join that 'dates' returned from subquery with the products to get the result.
g6baxovj2#
Here's another simple way to generate a list of numbers you can use for a series of dates and it should be very performant - this example will return from today + 30 days:
Demo Fiddle