Developer Forum

HI all

I have a table that holds reporting data, there is a value field and a
report year field. I would like to have the values of current year and
previous year and the difference of a particular product on one line.
BAsically similar to a pivot I gues where the sumamry is on the value column
by year
the example table:

CREATE TABLE [dbo].[datReportData](
[ReportDataId] [int] IDENTITY(1,1) NOT NULL,
[ReportId] [int] NOT NULL,
[MeasureId] [int] NOT NULL,
[Value] [float] NOT NULL CONSTRAINT [DF_datReportData_Value] DEFAULT
((0)),
[ReportYear] [int] NOT NULL CONSTRAINT [DF_datReportData_ReportYear]
DEFAULT ((0)),
[ReportMonth] [int] NOT NULL CONSTRAINT [DF_datReportData_ReportMonth]
DEFAULT ((0)),
[ReportDay] [int] NOT NULL CONSTRAINT [DF_datReportData_ReportDay] DEFAULT
((0)),
)


Thanks
Robert

Join the table against itself., but require that the currentYear.year
-1 = last_year...

Let me know if you have any trouble understanding the code.


You will also want to add an ISNULL(sum(value), 0)
so that every month is included even if no value is present.


TRY that out....


DECLARE @startYear INT
DECLARE @startMonth INT
DECLARE @endYear INT
DECLARE @endMonth INT

SET @startYear = 2003
SET @startMonth = 01
SET @endYear = 2006
SET @endMonth = 12

SELECT
CY_DATA.[MeasureId],
CY_DATA.[ReportYear],
CY_DATA.[ReportMonth],
CY_DATA.Value_Sum as CY_Sum,
LY_DATA.Value_Sum as LY_Sum,
(CY_DATA.Value_Sum - LY_DATA.Value_Sum) as YOY_Change,
(CY_DATA.Value_Sum / LY_DATA.Value_Sum)-1 as YOY_Change_PCT
FROM
(SELECT
[MeasureId],
[ReportYear],
[ReportMonth],
SUM([Value]) as Value_Sum
FROM [dbo].[datReportData]
WHERE [ReportYear] BETWEEN @startYear+1 AND @endYear
AND [ReportMonth] BETWEEN @startMonth AND @endMonth
GROUP BY
[MeasureId],
[ReportYear],
[ReportMonth]) as CY_DATA
LEFT OUTER JOIN
(SELECT
[MeasureId],
[ReportYear],
[ReportMonth],
SUM([Value]) as Value_Sum
FROM [dbo].[datReportData]
WHERE [ReportYear] BETWEEN @startYear AND @endYear-1
AND [ReportMonth] BETWEEN @startMonth AND @endMonth
GROUP BY
[MeasureId],
[ReportYear],
[ReportMonth]) as LY_DATA
ON (
(CY_DATA.[MeasureId] = LY_DATA.[MeasureId] ) AND
((CY_DATA.[ReportYear]-1) = LY_DATA.[ReportYear]) AND
(CY_DATA.[ReportMonth] = LY_DATA.[ReportMonth])
)


Regards,
John