Retrieving grouped data

Hi,
I have a table that contains a simple collection of rows, the rows have the
following fields...
URN, Year, Period, Cost, Value
As an example, the data is as follows...
2005|08|089.32|123.45
2005|10|056.68|045.68
2004|10|156.32|068.23
2005|11|123.56|548.12
2005|11|078.23|569.12
2005|7|078.25|875.657
2004|7|009.50|320.512
What I'd like to achieve is a stored procedure that retrieves the data in
the following...
Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
Year -1 | Sum Of Value For Year - 1
I've got the following...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE watson_GetLitresReporting_Monthly
@.intYear As INT
AS
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #thisYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #lastYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear - 1
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT #thisYear.NLPeriod,
SUM(#thisYear.LineValue) AS Value,
SUM(#thisYear.Litres) AS Quatity,
SUM(#lastYear.LineValue) AS LastYear,
SUM(#lastYear.Litres) AS Qty
FROM #thisYear
FULL OUTER JOIN #lastYear
ON #thisYear.NLPeriod = #lastYear.NLPeriod
GROUP BY #thisYear.NLYear,
#thisYear.NLPeriod
ORDER BY #thisYear.NLYear,
#thisYear.NLPeriod DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
But that doesn't work at all.
Could someone explain what I'm doing wrong please?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, what is a URN? To me it, it is a vase with ashes of loved one in
it.|||On Thu, 24 Nov 2005 08:58:08 -0800, JMH wrote:

>Hi,
>I have a table that contains a simple collection of rows, the rows have the
>following fields...
>URN, Year, Period, Cost, Value
>As an example, the data is as follows...
>2005|08|089.32|123.45
>2005|10|056.68|045.68
>2004|10|156.32|068.23
>2005|11|123.56|548.12
>2005|11|078.23|569.12
>2005|7|078.25|875.657
>2004|7|009.50|320.512
>What I'd like to achieve is a stored procedure that retrieves the data in
>the following...
>Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
>Year -1 | Sum Of Value For Year - 1
Hi JMH,
Try if this works:
SELECT COALESCE(a.Period, b.Period) AS Period,
SUM(a.Cost) AS Cost2005,
SUM(a.Value) AS Value2005,
SUM(b.Cost) AS Cost2004,
SUM(b.Value) AS Value2004
FROM (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2005) AS a
FULL OUTER JOIN (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2004) AS b
ON b.Period = a.Period
GROUP BY COALESCE(a.Period, b.Period)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- wrote:
> Also, what is a URN? To me it, it is a vase with ashes of loved one in
> it.
What exactly is wrong with this name? (I, indeed, never allowed
a thought that you are not aware of the very well known abbreviation.)