3

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.

2 Answers 2

2

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
-1
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;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.