October 21, 2008 at 5:15 pm
Hello Comunity
I have the following script :
SELECT (case when lp.u_pagpagar=0 and lp.u_pagchpd=0 then '222' else case when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=1 then '268' else
case when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=0 then '222' else '223' end end end) as Conta,
lp.no,
lp.nome,
sum(case when lo.cm=22 or lo.cm=4 then lp.evalor else 0 end) as edeb,
sum(case when lo.cm=28 then lp.evalor else 0 end) as ecred
FROM lp (nolock),
lo (nolock)
WHERE lp.lpstamp=lo.lpstamp
AND lp.letra=lo.letra
AND lp.no like substring('3000',1,5)
AND lo.cm in(28,22,4)
AND year(lo.data)<=2008
group by lp.u_pagpagar,lp.u_pagchpd,lp.u_eodc,lp.no,lp.nome
my problem is that must use group by because i use SUM, and the result show :
conta forn nome DebitoPeriodo CreditoPeriodo Saldo
22130185SUPPLIER NAME 9867735.71 13516877.11-3649141.40
22330185SUPPLIER NAME 2432941.84 2432941.84 0.00
22330185SUPPLIER NAME 284886.66 3147021.32-1862134.66
22930185SUPPLIER NAME 659187.49 0.00 659187.49
22930185SUPPLIER NAME 5723335.67 0.00 6382523.16
the result i want is only one line with sum of DebitoPeriodo, CreditoPeriodo and Saldo for each conta
like wou see i have 2 times conta (account) 223 and 229.
Someone could help me?
Many thanks
Luis Santos
October 21, 2008 at 6:49 pm
Since you're not showing some fields you're using hte group by - there's no obvious way to resolve the duplicates. The bottom line is that you need to come up with a specific method whereby you can pick just one of the two. The most common way is to set up a predictable sort order for the duplicates and use the ROW_NUMBER() function (new to 2005) to assign then unique ID's. You can then field by row_number 1.
If you don't quite get what I am talking about, post some more specifics about what is causing them to duplicate (like some of the data showing for them), and I will give you an example.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 21, 2008 at 11:11 pm
luissantos (10/21/2008)
Hello ComunityI have the following script :
SELECT (case when lp.u_pagpagar=0 and lp.u_pagchpd=0 then '222' else case when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=1 then '268' else
case when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=0 then '222' else '223' end end end) as Conta,
lp.no,
lp.nome,
sum(case when lo.cm=22 or lo.cm=4 then lp.evalor else 0 end) as edeb,
sum(case when lo.cm=28 then lp.evalor else 0 end) as ecred
FROM lp (nolock),
lo (nolock)
WHERE lp.lpstamp=lo.lpstamp
AND lp.letra=lo.letra
AND lp.no like substring('3000',1,5)
AND lo.cm in(28,22,4)
AND year(lo.data)<=2008
group by lp.u_pagpagar,lp.u_pagchpd,lp.u_eodc,lp.no,lp.nome
my problem is that must use group by because i use SUM, and the result show :
conta forn nome DebitoPeriodo CreditoPeriodo Saldo
22130185SUPPLIER NAME 9867735.71 13516877.11-3649141.40
22330185SUPPLIER NAME 2432941.84 2432941.84 0.00
22330185SUPPLIER NAME 284886.66 3147021.32-1862134.66
22930185SUPPLIER NAME 659187.49 0.00 659187.49
22930185SUPPLIER NAME 5723335.67 0.00 6382523.16
the result i want is only one line with sum of DebitoPeriodo, CreditoPeriodo and Saldo for each conta
like wou see i have 2 times conta (account) 223 and 229.
Someone could help me?
Many thanks
Luis Santos
take the required field in the SUM. as if you want conta not to repeat take it in the SUM(conta)
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
October 22, 2008 at 6:14 am
Luis --
Since you want to Group by Conta, you need that logic in your GROUP BY clause. I would rewrite your query as:
SELECT
case
when lp.u_pagpagar=0 and lp.u_pagchpd=0 then '222'
when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=1 then '268'
when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=0 then '222'
else '223'
end as Conta,
lp.no,
lp.nome,
sum(case when lo.cm=22 or lo.cm=4 then lp.evalor else 0 end) as edeb,
sum(case when lo.cm=28 then lp.evalor else 0 end) as ecred
FROM
lp (nolock),
lo (nolock)
WHERE lp.lpstamp=lo.lpstamp
AND lp.letra=lo.letra
AND lp.no like substring('3000',1,5)
AND lo.cm in(28,22,4)
AND year(lo.data)<=2008
GROUP BY
case
when lp.u_pagpagar=0 and lp.u_pagchpd=0 then '222'
when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=1 then '268'
when lp.u_pagpagar=1 and lp.u_pagchpd=0 and lp.u_eodc=0 then '222'
else '223'
end,
lp.no, lp.nome
Hope that helps.
Eric
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply