April 21, 2008 at 4:45 pm
When I run the following query, I expect the sum to add up for the column, Sdkretrocomm & it is not... and here is the output..
Please help !!!
Query
select isnull(ea.id,0) as SDKConsultant,
sum(ea.earn) as SDKRetroComm,
coalesce(B.membernumber,0) as ProwessMember,
coalesce(B.RetroBonus,0) as ProwessRetroComm,
Diff = coalesce((ea.earn - b.RetroBonus),0),
Percentage =
case
when b.RetroBonus = 0 then 1
when ea.earn = 0 then 1
when b.RetroBonus is null then 1
when ea.earn is null then 1
when ea.earn > b.RetroBonus then (1-b.RetroBonus/ea.earn)
when b.RetroBonus > ea.earn then (1-ea.earn/b.RetroBonus)
else 0
end
from [hqvm03\hqsqlinst05].sdk.dbo.DecBonusPaidRBTL_TMT Ea
full outer join
(select A.Membernumber, A.RetroBonus from
(select
bb.beenumber as MemberNumber,
pc.initialadjustmentamount as RetroBonus
from beebusiness bb
join Proadjustment pc on pc.beebusinessguid = bb.beebusinessguid
join beeentity be on be.beeentityguid = bb.beeentityguid
where pc.cmmadjustmenttypeguid in (3218165285136582397821897346, 20290615709734463701490426574)
)A)B
on B.Membernumber = Ea.id and ea.level in ('SCON', 'STMG')
group by isnull(ea.id,0), coalesce(B.membernumber,0),
coalesce(B.RetroBonus,0),
coalesce((ea.earn - b.RetroBonus),0),
(case
when b.RetroBonus = 0 then 1
when ea.earn = 0 then 1
when b.RetroBonus is null then 1
when ea.earn is null then 1
when ea.earn > b.RetroBonus then (1-b.RetroBonus/ea.earn)
when b.RetroBonus > ea.earn then (1-ea.earn/b.RetroBonus)
else 0
end)
______
Output
_____
004834026.600048340172.8900
004834067.410048340172.8900
004834078.880048340172.8900
00500689.840050068158.7300
005006830.480050068158.7300
005006834.040050068158.7300
005006884.360050068158.7300
005450433.960054504115.1000
April 21, 2008 at 11:34 pm
can you please post the exact results you got after running above query as number of columns in sql query dont match with your output results?
April 22, 2008 at 6:50 am
it looks like you are grouping the same field that you are summing, so it will never sum correctly
post your exact output and it will be easier to diagnose.
April 22, 2008 at 10:02 am
I prettty much pasted everything that I was seeing & that was that. Anyway, I got this sorted out by creating a derived table after grouping them.
Thanks one & All !!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply