Hi folks. Need help with something conceptual.
My table- zrpt_goals has data of goals based on rpt_group, month, year . I need goals for this month, previous 3 months, and full year.
So I use
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals
where zfyear = 2006
group by zrpt_goal, tgoal
this is working fine. I need to join this to pm table. There are many pmnum mapping to a zrpt_group, and when I join, sum is getting messed up.
I need something like
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals g, pm
where zfyear = 2006
and pm.zrpt_group = g.zrpt_group
group by zrpt_group, tgoal
I have tried using distinct, qualifying the columns. Tried thinking of using intersect. There is one thing which works but doesnt do all i want. If I use corelated query,
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals
where zfyear = 2006
and zrpt_group in
(select zrpt_group from pm)
but I need pm.eqnum which will join with other tables and check conditions but that is the next chapter.
Any ideas. Stuck real bad.
Thanks,
Dhruv.
My table- zrpt_goals has data of goals based on rpt_group, month, year . I need goals for this month, previous 3 months, and full year.
So I use
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals
where zfyear = 2006
group by zrpt_goal, tgoal
this is working fine. I need to join this to pm table. There are many pmnum mapping to a zrpt_group, and when I join, sum is getting messed up.
I need something like
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals g, pm
where zfyear = 2006
and pm.zrpt_group = g.zrpt_group
group by zrpt_group, tgoal
I have tried using distinct, qualifying the columns. Tried thinking of using intersect. There is one thing which works but doesnt do all i want. If I use corelated query,
select zrpt_group, tgoal, sum(case when zfmonth between 5 and 7 then tgoal end), sum(tgoal)
from zrpt_goals
where zfyear = 2006
and zrpt_group in
(select zrpt_group from pm)
but I need pm.eqnum which will join with other tables and check conditions but that is the next chapter.
Any ideas. Stuck real bad.
Thanks,
Dhruv.