SQL Server Developer >> YTD problem - adding extra field to table
I would create a view instead of a column that you need to update:
CREATE VIEW year_to_date
AS
SELECT t1.a, t1.b, t1.Month, t1.Value, SUM(t2.Value) AS YTD
FROM TableA t1
INNER JOIN TableA t2
ON t1.A = t2.A
AND t1.B = t2.B
AND t1.Month >= t2.Month
GROUP BY t1.a, t1.b, t1.Month, t1.Value
or if you want an update
UPDATE TableA
SET YTD = (SELECT SUM (t2.Value) AS YTD FROM TableA t2
WHERE TableA.A = t2.A
AND TableA.B = t2.B
AND TableA.Month >= t2.Month)
Where is the fiscal year column in this table btw?
--
Jacco Schalkwijk
SQL Server MVP
> Currrent table (TABLEA)
> A B Month Value YTD(?)
> 1601 60000 1 557.2938 557.29
> 1601 60000 2 557.2938 1114.58
> 1601 60000 3 557.2938 1671.87
> 1601 60000 4 557.2938 2229.16
> 1601 60000 5 557.2938 etc
> 1601 60000 6 557.2938 etc
> 1601 60000 7 557.2938
> 1601 60000 8 557.2938
> 1601 60000 9 557.2938
> 1601 60000 10 557.2938
> 1601 60000 11 557.2938
> 1601 60000 12 557.2938
>
> Need to create an UPDATE query to populate YTD figure based on GROUPING
> column A & column B also by Financial Months - see above for expected
> results.
>
> Any help would be appreciated.
> Thanks in advance
>