SQL Server SQL - duplicate all rows in a table for each date in a specified range

kb5ga3dv  于 2023-08-02  发布在  其他
关注(0)|答案(2)|浏览(100)

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):

DateProduct_IDProduct_NameUnit_Price
01/01/20231Laptop400.00
01/01/20232Keyboard20.00
01/01/20233Monitor150.00
02/01/20231Laptop400.00
02/01/20232Keyboard20.00
02/01/20233Monitor150.00
03/01/20231Laptop400.00
03/01/20232Keyboard20.00
03/01/20233Monitor150.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.

qyyhg6bp

qyyhg6bp1#

You can use cross join for this. ie:

declare @start Date = '20230701';
declare @end Date = '20230731';

create table products (Product_ID int,  Product_Name varchar(20),   Unit_Price money);
insert into products (Product_ID,   Product_Name,   Unit_Price) values
(1,'Laptop',    400.00),
(2,'Keyboard',  20.00),
(3,'Monitor',150.00);

select d.theDate, p.Product_ID, p.Product_Name, p.Unit_Price
from products p
  cross join
(select top(datediff(d, @start, @end)+1) 
  dateadd(d, row_number() over (order by t1.object_id)-1, @start) as theDate
from sys.all_columns t1 
cross join sys.all_columns t2) d;
theDateProduct_IDProduct_NameUnit_Price
2023-07-011Laptop400.0000
2023-07-012Keyboard20.0000
2023-07-013Monitor150.0000
2023-07-021Laptop400.0000
2023-07-022Keyboard20.0000
2023-07-023Monitor150.0000
2023-07-031Laptop400.0000
2023-07-032Keyboard20.0000
2023-07-033Monitor150.0000
2023-07-041Laptop400.0000
2023-07-042Keyboard20.0000
2023-07-043Monitor150.0000
2023-07-051Laptop400.0000
2023-07-052Keyboard20.0000
2023-07-053Monitor150.0000
2023-07-061Laptop400.0000
2023-07-062Keyboard20.0000
2023-07-063Monitor150.0000
2023-07-071Laptop400.0000
2023-07-072Keyboard20.0000
2023-07-073Monitor150.0000
2023-07-081Laptop400.0000
2023-07-082Keyboard20.0000
2023-07-083Monitor150.0000
2023-07-091Laptop400.0000
2023-07-092Keyboard20.0000
2023-07-093Monitor150.0000
2023-07-101Laptop400.0000
2023-07-102Keyboard20.0000
2023-07-103Monitor150.0000
2023-07-111Laptop400.0000
2023-07-112Keyboard20.0000
2023-07-113Monitor150.0000
2023-07-121Laptop400.0000
2023-07-122Keyboard20.0000
2023-07-123Monitor150.0000
2023-07-131Laptop400.0000
2023-07-132Keyboard20.0000
2023-07-133Monitor150.0000
2023-07-141Laptop400.0000
2023-07-142Keyboard20.0000
2023-07-143Monitor150.0000
2023-07-151Laptop400.0000
2023-07-152Keyboard20.0000
2023-07-153Monitor150.0000
2023-07-161Laptop400.0000
2023-07-162Keyboard20.0000
2023-07-163Monitor150.0000
2023-07-171Laptop400.0000
2023-07-172Keyboard20.0000
2023-07-173Monitor150.0000
2023-07-181Laptop400.0000
2023-07-182Keyboard20.0000
2023-07-183Monitor150.0000
2023-07-191Laptop400.0000
2023-07-192Keyboard20.0000
2023-07-193Monitor150.0000
2023-07-201Laptop400.0000
2023-07-202Keyboard20.0000
2023-07-203Monitor150.0000
2023-07-211Laptop400.0000
2023-07-212Keyboard20.0000
2023-07-213Monitor150.0000
2023-07-221Laptop400.0000
2023-07-222Keyboard20.0000
2023-07-223Monitor150.0000
2023-07-231Laptop400.0000
2023-07-232Keyboard20.0000
2023-07-233Monitor150.0000
2023-07-241Laptop400.0000
2023-07-242Keyboard20.0000
2023-07-243Monitor150.0000
2023-07-251Laptop400.0000
2023-07-252Keyboard20.0000
2023-07-253Monitor150.0000
2023-07-261Laptop400.0000
2023-07-262Keyboard20.0000
2023-07-263Monitor150.0000
2023-07-271Laptop400.0000
2023-07-272Keyboard20.0000
2023-07-273Monitor150.0000
2023-07-281Laptop400.0000
2023-07-282Keyboard20.0000
2023-07-283Monitor150.0000
2023-07-291Laptop400.0000
2023-07-292Keyboard20.0000
2023-07-293Monitor150.0000
2023-07-301Laptop400.0000
2023-07-302Keyboard20.0000
2023-07-303Monitor150.0000
2023-07-311Laptop400.0000
2023-07-312Keyboard20.0000
2023-07-313Monitor150.0000

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.

g6baxovj

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:

declare @start Date = GetDate(), @end Date = DateAdd(day, 30, GetDate());

with
  t1(n) as (select n from(values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n(n)), 
  t2(n) as (select 1 from t1 x cross join t1 y), /* up to 100 rows - expand if more range required */
  t(n) as (select top (DateDiff(day, @start, @end)) Row_Number() over (order by (select null)) - 1 from t2)

select DateAdd(day, n, @start) [Date], p.*
from products p
cross join t
order by [date];

Demo Fiddle

相关问题