Select multiple rows based on date interval from t

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select multiple rows based on date interval from t

Author

Topic

Pedrusco
Starting Member

1 Post

Posted - 2015-04-07 : 00:09:58

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:


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 that, because these 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.

I would apreciate any help

rsankhwaya
Starting Member

4 Posts

Posted - 2015-04-07 : 05:23:42

Try This

SELECT  CAST('A' AS NVARCHAR(10)) AS Product,CAST(20.50 AS NUMERIC(18,2)) AS price,CAST(' 2014-08-10' AS DATETIME) AS initial_date,
        CAST('2014-10-01' AS DATETIME) AS final_date INTO #tempProduct
INSERT INTO #tempProduct (Product,price,initial_date,final_date) VALUES ('B',50,'2015-01-15','2015-02-20')        

SELECT * FROM master..spt_values WHERE type = 'P'
        
SELECT Product,price,Dates FROM #tempProduct AS T
  inner join master..spt_values as N
    on N.number between 0 and datediff(MONTH, T.initial_date, T.final_date)
    cross apply (select dateadd(MONTH, N.number, T.initial_date)) as D(Dates)
where N.type ='P' 
DROP TABLE #tempProduct

thanks
Ravindra Prajapat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources