July 18, 2005 at 2:00 pm
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
July 18, 2005 at 2:16 pm
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
July 18, 2005 at 2:55 pm
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
July 18, 2005 at 6:36 pm
Thanks, it's working as expected, just added S.Average=1 AND
Jean-Luc
www.corobori.com
July 19, 2005 at 9:30 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply