YTD problem - adding extra field to table  
Author Message
KevinYoder





PostPosted: Thu Jun 09 05:13:43 CDT 2005 Top

SQL Server Developer >> YTD problem - adding extra field to table

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

SQL Server248  
 
 
Jacco





PostPosted: Thu Jun 09 05:13:43 CDT 2005 Top

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
>