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 2000On 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
Showing posts with label sumamount. Show all posts
Showing posts with label sumamount. Show all posts
Monday, March 19, 2012
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
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
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 2000On 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
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 2000On 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
Subscribe to:
Posts (Atom)