SQL Show Number of Months and Years Between Two Dates

Question

I have a table that shows the start date and the end date for a product. I have the total value and the product name in the table. I would like to be able to show the months and years between the two dates while including the product name and the average of the value over the time period. I have been trying to work with a query that solves the issue for a single product. I need the result to show across multiple products. I'm getting an error saying that I cannot query more than one item in the sub query. Is it possible to get the result I'm looking for? Thank you for your help. It’s greatly appreciated.

SQL to display both month and year between two dates

Example of Product Data

    Product    Value Start Date End Date
    Widget A    100 1/1/2012    5/1/2013
    Widget B    500 2/1/2010    6/1/2012

Desired Results

    Product     Date       Month Year    Value
    Widget A    1/1/2012    Jan 2012     5.88 
    Widget A    2/1/2012    Feb 2012     5.88 
    Widget A    3/1/2012    Mar 2012     5.88 
    Widget A    4/1/2012    Apr 2012     5.88 
    Widget A    5/1/2012    May 2012     5.88 
    Widget A    6/1/2012    Jun 2012     5.88 
    Widget A    7/1/2012    Jul 2012     5.88 
    Widget A    8/1/2012    Aug 2012     5.88 
    Widget A    9/1/2012    Sep 2012     5.88 
    Widget A    10/1/2012   Oct 2012     5.88 
    Widget A    11/1/2012   Nov 2012     5.88 
    Widget A    12/1/2012   Dec 2012     5.88 
    Widget A    1/1/2013    Jan 2013     5.88 
    Widget A    2/1/2013    Feb 2013     5.88 
    Widget A    3/1/2013    Mar 2013     5.88 
    Widget A    4/1/2013    Apr 2013     5.88 
    Widget A    5/1/2013    May 2013     5.88 
    Widget B    2/1/2010    Feb 2010     17.24 
    Widget B    3/1/2010    Mar 2010     17.24 
    Widget B    4/1/2010    Apr 2010     17.24 
    Widget B    5/1/2010    May 2010     17.24 
    Widget B    6/1/2010    Jun 2010     17.24 
    Widget B    7/1/2010    Jul 2010     17.24 
    Widget B    8/1/2010    Aug 2010     17.24 
    Widget B    9/1/2010    Sep 2010     17.24 
    Widget B    10/1/2010   Oct 2010     17.24 
    Widget B    11/1/2010   Nov 2010     17.24 
    Widget B    12/1/2010   Dec 2010     17.24 
    Widget B    1/1/2011    Jan 2011     17.24 
    Widget B    2/1/2011    Feb 2011     17.24 
    Widget B    3/1/2011    Mar 2011     17.24 
    Widget B    4/1/2011    Apr 2011     17.24 
    Widget B    5/1/2011    May 2011     17.24 
    Widget B    6/1/2011    Jun 2011     17.24 
    Widget B    7/1/2011    Jul 2011     17.24 
    Widget B    8/1/2011    Aug 2011     17.24 
    Widget B    9/1/2011    Sep 2011     17.24 
    Widget B    10/1/2011   Oct 2011     17.24 
    Widget B    11/1/2011   Nov 2011     17.24 
    Widget B    12/1/2011   Dec 2011     17.24 
    Widget B    1/1/2012    Jan 2012     17.24 
    Widget B    2/1/2012    Feb 2012     17.24 
    Widget B    3/1/2012    Mar 2012     17.24 
    Widget B    4/1/2012    Apr 2012     17.24 
    Widget B    5/1/2012    May 2012     17.24 
    Widget B    6/1/2012    Jun 2012     17.24

score 1 · Answer 1 · answered Jan 29 '14 at 16:12

You need to cross join to a numbers/dates table.

If you don't have one however they are relatively inexpensive to create on the fly using a system table and ROW_NUMBER()

WITH Product AS
(   SELECT  Product, 
            Value = CAST(Value AS DECIMAL(10, 2)), 
            StartDate = CAST(StartDate AS DATE), 
            EndDate = CAST(EndDate AS DATE)
    FROM    (VALUES
                ('Widget A', 100, '20120101', '20130501'),
                ('Widget b', 500, '20100201', '20120601')
            ) t (Product, Value, StartDate, EndDate)
), Numbers AS
(   SELECT  Number = ROW_NUMBER() OVER(ORDER BY a.object_id) - 1
    FROM    sys.all_objects a
)
SELECT  p.Product,
        d.[Date],
        MonthYear = LEFT(DATENAME(MONTH, d.[Date]), 3) + ' ' + DATENAME(YEAR, d.[Date]), 
        Value = CAST(p.Value / (1 + DATEDIFF(MONTH, p.StartDate, p.EndDate)) AS DECIMAL(5, 2))
FROM    Product p
        CROSS JOIN Numbers n
        OUTER APPLY (SELECT Date = DATEADD(MONTH, n.Number, p.StartDate)) d
WHERE   d.[Date] <= p.EndDate
ORDER BY p.Product, [Date];

Example on SQL Fiddle

For some further reading on generating and using a numbers/dates table, both static and on the fly, take a look at this series:

score 0 · Accepted Answer · answered Jan 29 '14 at 16:19

Step one, get a list of dates. The monthly dates in your output have to come from somewhere...that's how SQL works. Creating and maintaining a "calendar table" is very common, but there are other ways too.

CREATE TABLE months ([Date] date)
--Populate table with a list of first-of-months

SELECT
  p.[Product],
  m.[Date],
  LEFT(DATENAME(month,m.[Date]),3) AS [Month]
  YEAR(m.[Date]) AS [Year]
  p.[Value]/(DATEDIFF(month,p.[Start Date],p.[End Date])+1) AS [Value]
FROM months m
INNER JOIN products p ON (m.[Date] BETWEEN p.[Start Date] AND p.[End Date])

SQL Show Number of Months and Years Between Two Dates

2 Answers2