Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Portland, Or
    Posts
    21

    Unanswered: finding average earning over period of employment

    It has been a while since I have had to write a really advanced query and I was wondering if I could get a little bit of help from someone

    find the average monthly earning while you have been an affiliate

    affiliate_id, transaction_dt, earnings are the only fields that you have to worry about. I can obviously find the average earnings for a period of a year but, I would like something like

    spavgmonthlyearning affiliate_id

    any help would be greatly appreciate even a point in the right direction would be fantastic.
    Stephen Rich
    http://www.teniosoft.com
    Web Applications, Client Server Databases for Linux and Windows

  2. #2
    Join Date
    Mar 2003
    Location
    Portland, Or
    Posts
    21
    so this will do it.
    is this pure sql or will it only run on sqlserver
    thank you for your help

    DECLARE @maxdate as datetime
    DECLARE @mindate as datetime
    set @maxdate = (select max(TRANS_DT) from dbo.affiliate_log)
    set @mindate = (select min(trans_dt) from dbo.affiliate_log)
    select sum(earning)/datediff(M,@mindate,@maxdate)
    from dbo.affiliate_log where affiliate_id = 1
    Stephen Rich
    http://www.teniosoft.com
    Web Applications, Client Server Databases for Linux and Windows

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select DATEPART(m,date_field), avg(sales)
    from table
    group by DATEPART(m, date_field)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It depends on what you mean by "average".

    The simple average monthly earning would just be the total earnings divided by the total months.

    select affiliate_id,
    sum(earnings)/datediff(month, min(transaction_dt), max(transaction_dt))
    from yourtable
    group by affiliate_id

    Or if they skip some months:

    select affiliate_id,
    sum(earnings)/count(distinct convert(varchar(7), transaction_dt, 120))
    from yourtable
    group by affiliate_id
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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