Sql-server – Select multiple rows based on date interval from two fields

sql serverview

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.

Best Answer

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
Related Question