Help with query to fill in missing records

The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000
On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP