SQL Server TSQL cumulative SELECT

Topic

New Reply

I have a table t1 with the following data:

Slsp dayOfMonth quota
PAM 1 1000
PAM 2 1100
PAM 3 1050
PAM 4 1150
PAM 5 1090
PAM 6 1110
PAM 7 1120
PAM 8 1000
PAM 9 1050
PAM 10 1110
…

I would like to create a select from t1 with the following results:

Slsp cumDay cumQuota
PAM 1 1000
PAM 2 2100
PAM 3 3150
PAM 4 4300
PAM 5 5390
PAM 6 6500
PAM 7 7620
PAM 8 8620
PAM 9 9670
PAM 10 10780

…

each day the Quota adds to the previous day. This will be used in a chart to help salesmen meet their monthly quota.

Reply | Quote

Replies

You need a UDF besides the query, to have the result you want. Something like this would achieve it:

Code:

[COLOR=black][SIZE=2][SIZE=2]CREATE [/SIZE][/SIZE][SIZE=2][SIZE=2]FUNCTION[/SIZE][/SIZE][SIZE=2] dbo[/SIZE][SIZE=2][SIZE=2].[/SIZE][/SIZE][SIZE=2]CalculateCumQuota[/SIZE][SIZE=2][SIZE=2]([/SIZE][/SIZE][SIZE=2]@day [/SIZE][SIZE=2][SIZE=2]int[/SIZE][/SIZE][SIZE=2][SIZE=2])[/SIZE][/SIZE][/COLOR]
[SIZE=2][SIZE=2][COLOR=black]RETURNS [/COLOR][/SIZE][/SIZE][SIZE=2][SIZE=2][COLOR=black]int[/COLOR][/SIZE]
[SIZE=2][COLOR=black]As[/COLOR][/SIZE]
[SIZE=2][COLOR=black]BEGIN[/COLOR][/SIZE]
[/SIZE][COLOR=black][SIZE=2][SIZE=2]RETURN [/SIZE][/SIZE][SIZE=2][SIZE=2]([/SIZE][/SIZE][SIZE=2][SIZE=2]SELECT [/SIZE][/SIZE][SIZE=2][SIZE=2]SUM[/SIZE][/SIZE][SIZE=2][SIZE=2]([/SIZE][/SIZE][SIZE=2]quota[/SIZE][SIZE=2][SIZE=2]) [/SIZE][/SIZE][SIZE=2][SIZE=2]FROM[/SIZE][/SIZE][SIZE=2] T1 [/SIZE][SIZE=2][SIZE=2]WHERE [/SIZE][/SIZE][SIZE=2][SIZE=2]([/SIZE][/SIZE][SIZE=2]T1[/SIZE][SIZE=2][SIZE=2].[/SIZE][/SIZE][SIZE=2][SIZE=2]dayOfMonth[/SIZE][/SIZE][SIZE=2][SIZE=2]<=[/SIZE][/SIZE][SIZE=2] @day[/SIZE][/COLOR][SIZE=2][SIZE=2][COLOR=black]))[/COLOR][/SIZE]
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=black]END[/COLOR][/SIZE]
[SIZE=2][COLOR=black]go[/COLOR][/SIZE]
[/COLOR][/SIZE]

Now the query:[/COLOR]

Code:

[SIZE=2][COLOR=black][SIZE=2]SELECT [/SIZE][/COLOR][/SIZE][COLOR=black][SIZE=2]Slsp[/SIZE][SIZE=2][SIZE=2], [/SIZE][/SIZE][SIZE=2][SIZE=2]dayOfMonth[/SIZE][/SIZE][SIZE=2][SIZE=2]As[/SIZE][/SIZE][SIZE=2] cumDay[/SIZE][SIZE=2][SIZE=2],[/SIZE][/SIZE][SIZE=2] dbo[/SIZE][SIZE=2][SIZE=2].[/SIZE][/SIZE][SIZE=2]CalculateCumQuota[/SIZE][SIZE=2][SIZE=2]([/SIZE][/SIZE][SIZE=2][SIZE=2]dayOfMonth[/SIZE][/SIZE][SIZE=2][SIZE=2])[/SIZE][/SIZE][SIZE=2][SIZE=2]as[/SIZE][/SIZE][SIZE=2] CumQuota [/SIZE][SIZE=2][SIZE=2]FROM[/SIZE][/SIZE][SIZE=2] T1[/SIZE][/COLOR][SIZE=2][SIZE=2][COLOR=black];[/COLOR][/SIZE]
[/SIZE]

I suggest you replace dbo by an existing schema in your database or if it works for you, leave it like this.

Reply | Quote