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.

--CELKO-- wrote:
    What exactly is wrong with this name? (I, indeed, never allowed
a thought that you are not aware of the very well known abbreviation.)

On Thu, 24 Nov 2005 08:58:08 -0800, JMH wrote:

Show quoteHide quoteHi 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

Dynamic Stored Procedure
Dynamic WHERE & Dates
with(updlock) - How many times on same table in single update?
Is there alternative to T-SQL USE <dbname> where <dbname> is varia
Using set theory in SQL Server 2005