Select multiple rows based on date interval from two fields

Question

I am still learning SQL and I would like to do something that I dont know if it is possible. I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:

Original:

 Product|price|initial_date|final_date
 A|20.50|2014年08月10日|2014年10月01日
 B|50|2015年01月15日|2015年02月20日

Resulting view:

 Product|price|Date
A|20.5|2014年08月01日
A|20.5|2014年09月01日
A|20.5|2014年10月01日
B|50|2015年01月01日
B|50|2015年02月01日

I would like to do it, because this dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.

Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.

By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.

Answer 1

Do you have a calendar table ? I am building a kind of calendar table with object cteSeries. Then adjust the Initial_Date and Final_Date to hold BOM (beginning of Month YYYY-MM-01) in cteSample , fields: Initial_Date_M,Final_Date_M

DECLARE @tSample TABLE
(
 Product VARCHAR(10)
 ,Price DECIMAL(18,2)
 ,Initial_Date DATE
 ,Final_Date DATE)
INSERT INTO @tSample (Product,price,initial_date,final_date)
VALUES ('A',20.50,'2014-08-10','2014-10-01')
 ,('B',50,'2015-01-15','2015-02-20');
DECLARE
@dStart AS DATE ='20140101'
,@dEnd AS DATE ='20160101'
-- building a calendar table - 
;WITH cteSeries 
AS
(SELECT @dStart AS cteDate
 UNION ALL
 SELECT DATEADD(MONTH,1,cteDate)
 FROM cteSeries
 WHERE
 cteDate < @dEnd
 )
--SELECT * FROM cteSeries OPTION (MAXRECURSION 0)
, cteSample
AS
(SELECT Product ,Price ,Initial_Date ,Final_Date 
 , DATEADD(MONTH , DATEDIFF(MONTH , 0,Initial_Date), 0) AS Initial_Date_M
 , DATEADD(MONTH , DATEDIFF(MONTH , 0,Final_Date), 0) Final_Date_M
 FROM @tSample
)
SELECT
 S.Product
 ,S.Price
 ,cteDate AS Date
 --,*
FROM
 cteSample AS S
 INNER JOIN cteSeries AS C
 ON S.Initial_Date_M<=C.cteDate
 AND S.Final_Date_M >= C.cteDate

and the output :

 Product Price Date
 A 20.50 2014年08月01日
 A 20.50 2014年09月01日
 A 20.50 2014年10月01日
 B 50.00 2015年01月01日
 B 50.00 2015年02月01日

Answer 2

Declare @i INT;
SELECT @i = 0;
CREATE table #temp(
numbers int not null);
while @i<10
BEGIN
insert into #temp values(@i)
SELECT @i = @i+1;
END
SELECT Product, Price, DATEADD(MM,a.numbers,DATEADD(m, DATEDIFF(m, 0,initialDate), 0)) 
FROM #ProdDate, #temp a
where a.numbers <= DATEDIFF(mm,initialDate,finalDate)
drop table #temp;

• Paul White: Please see How do I format my posts using Markdown or HTML? and How to Answer in the help center. In particular, code-only answers without explanation and/or helpful references are rarely well-received by the community here.