Calling all SQL Gurus Return the years between two dates

[PCStorm]

We have a need to, with a query, to return the number of rows into a report that represent the number of years between two dates. So, we have a start date of 1/1/1999 to 1/1/2004 and we would need a row for 1/1/2000 and 1/1/2001 and 1/1/2002 and 1/1/2004. Anyone know how to do this without an explicit cursor?

 

[sem]

Have you ever heard about MONTHS_BETWEEN and ADD_MONTHS functions ? As well as that year contains 12 months ?

Regards, Dima

 

[PCStorm]

Of course. I will be a little clearer. Lets say we have a table with two entries

Name1 1/1/2000 1/1/2005
Name2 6/2/1950 1/2/1953

The desired output would be

Name1 1/1/2001
Name1 1/1/2002
Name1 1/1/2003
Name1 1/1/2004
Name2 6/2/1951
Name2 6/2/1952

Of course add_months can give me the next year. And with PL/SQL I could loop through and definitely produce the results I want. But how to do it with a query? Am playing with a self-join to explode the results. Ideas.

 

[carp]

Sounds to me like you might want to make a MY_YEARS table that has as one column: YEAR_DATE that you could populate with
01-JAN-95
01-JAN-96
01-JAN-97
.
.
.

You could expand on this to include other months, etc as desired.

Then your query would be something like

SELECT c.customer_name, y.month_year
FROM customers c,
year_date y,
(SELECT customer_name,
min(customer_date) low_date,
max(customer_date) high_date
FROM customers
GROUP BY customer_name) c1
WHERE c.customer_name = c1.customer_name
AND y.month_year BETWEEN c1.low_date
AND c1.high_date
ORDER BY c.customer_name;

No guarantees - I haven't tested this. But I think it should work.