Average of the last 3 entries

• Corobori

  Ten Centuries

  Points: 1316

  More actions

  July 18, 2005 at 2:00 pm

  #167592

  Hi,

  I need to retrieve the average of the last 3 entries per subscriber when the average is 1 and when the date is smaller or equal to a given date.

  For example for Subscriber 1 withe date 3/31/2005 the result should be 16666, and for subscriber 2 5666 and nothing for #3

  create table #tblTempRetros(DateMonthEnd datetime, Subscriber int, Amount int)

  insert into #tblTempRetros VALUES ('12/31/2004', 1, 15000)

  insert into #tblTempRetros VALUES ('12/31/2004', 2, 5000)

  insert into #tblTempRetros VALUES ('12/31/2004', 3, 5000)

  insert into #tblTempRetros VALUES ('1/31/2005', 1, 15000)

  insert into #tblTempRetros VALUES ('2/28/2005', 1, 20000)

  insert into #tblTempRetros VALUES ('2/28/2005', 3, 20000)

  insert into #tblTempRetros VALUES ('2/28/2005', 2, 7000)

  insert into #tblTempRetros VALUES ('3/31/2005', 1, 15000)

  insert into #tblTempRetros VALUES ('3/31/2005', 2, 5000)

  insert into #tblTempRetros VALUES ('3/31/2005', 3, 9000)

  create table #tblTempSubscriber (Subscriber int, Average int)

  insert into #tblTempSubscriber VALUES (1,1)

  insert into #tblTempSubscriber VALUES (2,1)

  insert into #tblTempSubscriber VALUES (3,0)

  
  Jean-Luc
  www.corobori.com
  

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  July 18, 2005 at 2:16 pm

  #575327

  HTH

  Can you explain how do you get 5666 fro 2

   

  DECLARE @MyDate DATETIME SET @MyDate = '3/31/2005'

  SELECT A.Subscriber, AVG(CASE WHEN B.Average = 1 THEN A.Amount ELSE 0 END)

  FROM

   #tblTempRetros A

  JOIN

   #tblTempSubscriber B

  ON

   A.Subscriber = B.Subscriber AND

   DateMonthEnd <= @MyDate

  WHERE

   3  > (SELECT COUNT(DISTINCT DateMonthEnd)

    FROM

     #tblTempRetros B

    WHERE

     B.DateMonthEnd > A.DateMonthEnd)

  GROUP BY  A.Subscriber

  Regards,
  gova

• Roy Rogers

  SSCertifiable

  Points: 7712

  More actions

  July 18, 2005 at 2:55 pm

  #575334

  My Mistake.

  Correct answer is

   SELECT A.Subscriber, AVG(CASE WHEN S.Average = 1 THEN A.Amount ELSE 0 END) AvgAmount

   FROM

    #tblTempRetros A

   JOIN

    #tblTempSubscriber S

   ON

    A.Subscriber = S.Subscriber 

   WHERE

    3  > (SELECT COUNT(DISTINCT DateMonthEnd)

     FROM

      #tblTempRetros B

     WHERE

      B.Subscriber = A.Subscriber AND

      B.DateMonthEnd > A.DateMonthEnd )

   AND A.DateMonthEnd <= '3/31/2005'

   GROUP BY A.Subscriber

   ORDER BY 1

  Regards,
  gova

• Corobori

  Ten Centuries

  Points: 1316

  More actions

  July 18, 2005 at 6:36 pm

  #575363

  Thanks, it's working as expected, just added S.Average=1 AND

  
  Jean-Luc
  www.corobori.com
  

• Corobori

  Ten Centuries

  Points: 1316

  More actions

  July 19, 2005 at 9:30 am

  #575528

  I need an extra step. I want to link this query to another table where when passing the date its retrieving the total value and the average

  So for Subscriber 1 with date 3/31/2005 the result should be 16666 for the average and 44000 for the total value

  create table #tblTempSituation(DateMonthEnd datetime, Subscriber int, TotalValue int)

  insert into #tblTempSituation VALUES ('12/31/2004', 1, 60000)

  insert into #tblTempSituation VALUES ('1/31/2005', 1, 70000)

  insert into #tblTempSituation VALUES ('2/28/2005', 1, 65000)

  insert into #tblTempSituation VALUES ('3/31/2005', 1, 66000)

  insert into #tblTempSituation VALUES ('12/31/2004', 2, 32000)

  insert into #tblTempSituation VALUES ('1/31/2005', 2, 34000)

  insert into #tblTempSituation VALUES ('2/28/2005', 2, 54000)

  insert into #tblTempSituation VALUES ('3/31/2005', 2, 44000)

  
  Jean-Luc
  www.corobori.com