August 8, 2005 at 12:48 pm
Hi,
Here is my situation:
create table #tblTempRetros(DateMonthEnd datetime, Stock int, Subscriber int, Amount int)
insert into #tblTempRetros VALUES ('1/31/2005', 1,1, 15000)
insert into #tblTempRetros VALUES ('1/31/2005', 1,2, 5000)
insert into #tblTempRetros VALUES ('1/31/2005', 1,3, 5000)
insert into #tblTempRetros VALUES ('2/28/2005', 1,1, 20000)
insert into #tblTempRetros VALUES ('2/28/2005', 1,3, 20000)
insert into #tblTempRetros VALUES ('2/28/2005', 1,2, 7000)
insert into #tblTempRetros VALUES ('3/31/2005', 1,1, 15000)
insert into #tblTempRetros VALUES ('3/31/2005', 1,2, 5000)
insert into #tblTempRetros VALUES ('3/31/2005', 1,3, 9000)
create table #tblTempSubscriber (Subscriber int, MinimumValue int)
insert into #tblTempSubscriber VALUES (1,50000)
insert into #tblTempSubscriber VALUES (2,20000)
insert into #tblTempSubscriber VALUES (3,30000)
SELECT #tblTempRetros.Stock, CASE WHEN SUM(#tblTempRetros.Amount) >= MinimumValue THEN SUM(#tblTempRetros.Amount)
ELSE 0 END AS iMin
FROM #tblTempRetros INNER JOIN
#tblTempSubscriber ON #tblTempRetros.Subscriber = #tblTempSubscriber.Subscriber
GROUP BY #tblTempSubscriber.MinimumValue, #tblTempRetros.Stock
drop table #tblTempRetros
drop table #tblTempSubscriber
The result is
1 0
1 34000
1 50000
I would like the result to be:
1 54000
This is a simplified version of the real situation but basically I want to have total for that stock where the sum for a subscriber is greater than a threshold. In the sample the 0 is for subscriber #2 whose total for this period is 17000 whereas the threshold is 20000
Jean-Luc
www.corobori.com
August 8, 2005 at 2:15 pm
In your example, how do you figure the result should b
1 5400 ?
in your query you should modify it to be
SELECT #tblTempRetros.Stock, #tblTempSubscriber.MinimumValue, sum(#tblTempRetros.Amount) AS iMin
FROM #tblTempRetros INNER JOIN
#tblTempSubscriber ON #tblTempRetros.Subscriber = #tblTempSubscriber.Subscriber
GROUP BY #tblTempSubscriber.MinimumValue, #tblTempRetros.Stock
having sum(#tblTempRetros.Amount) >= #tblTempSubscriber.MinimumValue
using the having clause eliminates subscriber 2 because its sum is not greater or equal to minValue.
But since you are grouping by MinValue you see 2 rows one for Subscriber 1, and 1 for subscriber 2 because of different minimum values.
August 8, 2005 at 4:00 pm
It should be : 1 84 000 (the sum of 34000 + 50000)
The query you have is giving me:
1 34000
1 50000
Jean-Luc
www.corobori.com
August 8, 2005 at 4:38 pm
Okay,
So you want to aggregate by the stock id.
but how do you want to calculate by the minimum amount which is a subscriber level attribute.
the previous query returned
1 34000
1 50000
because the group by included #tblTempSubscriber.MinimumValue which is by subscriber.
-- If you run
SELECT #tblTempRetros.stock, sum(amount) as sumamount, minimumValue
FROM #tblTempRetros INNER JOIN
#tblTempSubscriber ON #tblTempRetros.Subscriber = #tblTempSubscriber.Subscriber
group by #tblTempRetros.Subscriber,minimumValue, #tblTempRetros.stock
You get
stock Subscriber amount minimumValue
1 1 50000 50000
1 2 17000 20000
1 3 34000 30000
so the value for subscriber 2 should not be included in the result.
So take that query add the having clause and use it as a derived table.
select dt.stock, sum(dt.sumamount)
from (SELECT #tblTempRetros.stock, sum(amount) as sumamount, minimumValue
FROM #tblTempRetros
INNER JOIN #tblTempSubscriber ON #tblTempRetros.Subscriber = #tblTempSubscriber.Subscriber
group by #tblTempRetros.Subscriber,minimumValue, #tblTempRetros.stock
having sum(#tblTempRetros.Amount) >= #tblTempSubscriber.MinimumValue
) as dt
group by dt.stock
stock amount
1 84000
Ray
August 10, 2005 at 8:21 am
Working fine, thanks Ray
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