January 21, 2015 at 8:34 am
Hello,
I have been stuck here now:
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore
, lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
FROM HistoryTable
group by network_group_name
) cr
, (
select pr.group_name, PreRank = row_number() OVER (ORDER BY PreScore desc), PreScore from
(
select group_name,
Avg(case when datediff(day, asAtDate, getdate()) > 7 then sumscore else 0 end) as PreScore
FROM HistoryTable
group by group_name
) pr
) lastWeek
The query consists two parts: from current week and previous week respectively.
each part returns correct result, the final merged result is wrong.
Can anyone point out the root cause and fix it?
Thank you very much.
January 21, 2015 at 8:51 am
Could you please post DDL and sample data (insert script) for your issue.
Also, could you please specify what exactly wrong with current "results" and what your real expected results are (based on data sample).
January 21, 2015 at 8:56 am
Just shoot in the air...
Are you trying to do FULL OUTER JOIN ? :
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc)
, cr.group_name,cr.CurScore
, lastWeek.PreRank
, lastWeek.group_name
,lastWeek.PreScore
from (select group_name,
Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
FROM HistoryTable
group by network_group_name
) cr
full outer join (select pr.group_name, PreRank = row_number() OVER (ORDER BY PreScore desc), PreScore
from
(select group_name,
Avg(case when datediff(day, asAtDate, getdate()) > 7 then sumscore else 0 end) as PreScore
FROM HistoryTable
group by group_name
) pr
) lastWeek
on lastWeek.group_name = cr.group_name
January 21, 2015 at 9:07 am
Thank you very much
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply