Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Dec 2009
    Location
    White Bear Lake, MN
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server TSQL cumulative SELECT

    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.

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    You need a UDF besides the query, to have the result you want. Something like this would achieve it:
    Code:
    CREATE FUNCTION dbo.CalculateCumQuota(@day int)
    RETURNS int
    As
    BEGIN
    RETURN (SELECT SUM(quota) FROM T1 WHERE (T1.dayOfMonth<= @day))
    END
    go
    
    Now the query:
    Code:
    SELECT Slsp, dayOfMonthAs cumDay, dbo.CalculateCumQuota(dayOfMonth)as CumQuota FROM T1;
    
    I suggest you replace dbo by an existing schema in your database or if it works for you, leave it like this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •