Field to display Previous 30 Day Total
- by whytheq
I've got this table:
CREATE TABLE #Data1
(
[Market] VARCHAR(100) NOT NULL,
[Operator] VARCHAR(100) NOT NULL,
[Date] DATETIME NOT NULL,
[Measure] VARCHAR(100) NOT NULL,
[Amount] NUMERIC(36,10) NOT NULL,
--new calculated fields
[DailyAvg_30days] NUMERIC(38,6) NULL DEFAULT 0
)
I've populated all the fields apart from DailyAvg_30days.
This field needs to show the total for the preceding 30 days e.g.
1. if Date for a particular record is 2nd Dec then it will be the total for the period 3rd Nov - 2nd Dec inclusive.
2. if Date for a particular record is 1st Dec then it will be the total for the period 2nd Nov - 1st Dec inclusive.
My attempt to try to find these totals before updating the table is as follows:
SELECT
a.[Market],
a.[Operator],
a.[Date],
a.[Measure],
a.[Amount],
[DailyAvg_30days] = SUM(b.[Amount])
FROM
#Data1 a
INNER JOIN #Data1 b
ON
a.[Market] = b.[Market] AND
a.[Operator] = b.[Operator] AND
a.[Measure] = b.[Measure] AND
a.[Date] >= b.[Date]-30 AND
a.[Date] <= b.[Date]
GROUP BY
a.[Market],
a.[Operator],
a.[Date],
a.[Measure],
a.[Amount]
ORDER BY 1,2,4,3
Is this a valid approach or do I need to approach this from a different angle?
