March 16, 2009 at 10:58 am
Hello comunity
I need to return 2 columns on my SELECT the first is "SaldoCCO" and also "SaldoLR", and with this script i can resolve that only for "SaldoCCO":
SELECT No,nome, SUM(Saldo) as 'SaldoCCO'
from
(select * from (SELECT cc.datalc as Data , cast(cc.[no] as varchar) as No,cc.nome as Nome ,
cmdesc as Documento,cast(cc.nrdoc as varchar) as NoDoc, cc.recibado,cc.origem,
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' aND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf) else (cc.ecred-cc.ecredf) end end)as PENDENTESCRD,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0 then cc.edeb+
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) +
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0 then cc.edeb-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231'AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end end)as PENDENTESDEB,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0 then cc.edeb+
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) +
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0 then cc.edeb-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end end)
-
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' aND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf) else (cc.ecred-cc.ecredf) end end)
as Saldo
FROM CC (nolock)
WHERE
/*(cc.[no] = 725) and*/ (cc.datalc between '20080101' and '20081231')
) derrivedtable
--where ROUND(derrivedtable.pendentesCRD,2) > 0.01 or ROUND(derrivedtable.pendentesDEB,2) >0.01
--order by derrivedtable.data
) teste
GROUP BY no,nome
I need to return "SALDOLR" this second Query :
SELECT 'LR' as 'Tipo',no, nome, SUM(saldo) AS 'saldoLR' FROM
(SELECT no, nome,EVALOR AS 'Saldo' FROM LR (NOLOCK)
WHERE LR.u_pagchpd = 0 AND LR.U_PAGPAGAR = 0 AND LR.STATUS <7 AND lr.edata<='20081231') letras
GROUP BY no,nome
My question is how to join both query to return 1 line for each no,nome and two columns like i sais :
"SaldoCCO" and "SaldoLR"
Many thanks
Luis Santos
March 16, 2009 at 11:18 am
Try using UNION or UNION ALL to join the results of your two queries..
March 16, 2009 at 11:23 am
This query would be a heck of a lot simpler - and probably much faster - if all those repetitive correlated subqueries were replaced by derived tables:
SELECT No,nome, SUM(Saldo) as 'SaldoCCO'
from
(select * from (SELECT cc.datalc as Data , cast(cc.[no] as varchar) as No,cc.nome as Nome ,
cmdesc as Documento,cast(cc.nrdoc as varchar) as NoDoc, cc.recibado,cc.origem,
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0 then cc.ecred-
erl.SumEREC -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0 then cc.ecred-
erl.SumEREC -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf) else (cc.ecred-cc.ecredf) end end)as PENDENTESCRD,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0 then cc.edeb+
erl.SumEREC +
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0 then cc.edeb-
erl.SumEREC -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231'AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end end)as PENDENTESDEB,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0 then cc.edeb+
erl.SumEREC +
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0 then cc.edeb-
erl.SumEREC -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end end)
-
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0 then cc.ecred-
erl.SumEREC -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0 then cc.ecred-
ISNULL(erl.SumEREC,0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf) else (cc.ecred-cc.ecredf) end end)
as Saldo
FROM CC (nolock)
LEFT JOIN (SELECT ISNULL(SUM(EREC), 0) AS SumEREC, CCSTAMP
FROM RL (nolock)
WHERE RDATA > '20081231'
GROUP BY CCSTAMP) erl
ON erl.CCSTAMP = CC.CCSTAMP
WHERE
/*(cc.[no] = 725) and*/ (cc.datalc between '20080101' and '20081231')
) derrivedtable
--where ROUND(derrivedtable.pendentesCRD,2) > 0.01 or ROUND(derrivedtable.pendentesDEB,2) >0.01
--order by derrivedtable.data
) teste
GROUP BY no,nome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 16, 2009 at 11:40 am
Hello
The UNION operator dosn´t solve my problem because i need 2 specific columns, like i said.
Also, the second post reply don´t work.
I need to have the following :
no nome SaldoCCO SaldoLR
1000 xpto 10000 100
1001 abc 500 0
etc....
I need to put on the first SELECt line the following:
Select no, nome, Sum(saldoCCO) as 'SaldoCCO', Sum(SaldoLR) as 'SaldoLR' FROM
(SELECT * FROM....................
my problem is how can insert the second select :
SELECT 'LR' as 'Tipo',no, nome, SUM(saldo) AS 'saldoLR' FROM
(SELECT no, nome,EVALOR AS 'Saldo' FROM LR (NOLOCK)
WHERE LR.u_pagchpd = 0 AND LR.STATUS <7 AND lr.edata<='20081231') LETRAS
GROUP BY no,nome
on the first one.
I hope you understand what i need
Many thanks
Luis Santos
March 16, 2009 at 11:58 am
Hi
Here is my understanding, let me know if that works.
You have QUERY 1 that returns:
no nome SaldoCCO
1000 xpto 10000
1001 abc 500
and QUERY 2 that returns
no nome SaldoLR
1000 xpto 100
1001 abc 0
You want:
no nome SaldoCCO SaldoLR
1000 xpto 10000 100
1001 abc 500 0
You need to join your second query with your first query.
SELECT No,nome, SUM(Saldo) as 'SaldoCCO', Query2.SaldoLR
from
(select * from (SELECT cc.datalc as Data , cast(cc.[no] as varchar) as No,cc.nome as Nome ,
cmdesc as Documento,cast(cc.nrdoc as varchar) as NoDoc, cc.recibado,cc.origem,
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' aND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf) else (cc.ecred-cc.ecredf) end end)as PENDENTESCRD,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0 then cc.edeb+
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) +
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0 then cc.edeb-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231'AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end end)as PENDENTESDEB,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0 then cc.edeb+
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) +
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0 then cc.edeb-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end end)
-
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' aND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0 then cc.ecred-
ISNULL((SELECT sum(EREC) FROM RL (nolock) WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock) inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf) else (cc.ecred-cc.ecredf) end end)
as Saldo
FROM CC (nolock)
WHERE
/*(cc.[no] = 725) and*/ (cc.datalc between '20080101' and '20081231')
) derrivedtable
--where ROUND(derrivedtable.pendentesCRD,2) > 0.01 or ROUND(derrivedtable.pendentesDEB,2) >0.01
--order by derrivedtable.data
) teste
--==== SSC Added join on 2nd query
LEFT JOIN (
SELECT 'LR' as 'Tipo',no, nome, SUM(saldo) AS 'saldoLR' FROM
(SELECT no, nome,EVALOR AS 'Saldo' FROM LR (NOLOCK)
WHERE LR.u_pagchpd = 0 AND LR.U_PAGPAGAR = 0 AND LR.STATUS <7 AND lr.edata<='20081231') letras
GROUP BY no,nome) AS Query2
ON Query2.No = teste.No and Query2.nome = teste.No
GROUP BY no,nome
I could not test the query since you only provided the queries.
It would help immensely if you tried to simplify you query when posting here, include table definition and sample data.
Help SSC help you...
March 16, 2009 at 12:03 pm
Hello Maxim
Yes, is what i want, i will try your TSQL.
Many thanks for your reply
Luis Santos
March 17, 2009 at 2:24 am
Here's the same query after 10 minutes spent reformatting it to make it a little more readable:
SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
from
(select * from (
SELECT cc.datalc as Data,
cast(cc.[no] as varchar) as No,
cc.nome as Nome,
cmdesc as Documento,
cast(cc.nrdoc as varchar) as NoDoc,
cc.recibado,
cc.origem,
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0
then cc.ecred - ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' aND RL.CCSTAMP = CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA <='20081231' AND LI.CCSTAMP = CC.CCSTAMP),0)-(cc.ecredf)
else
case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0
then cc.ecred - ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA <='20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else (cc.ecred-cc.ecredf)
end
end) as PENDENTESCRD,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0
then cc.edeb + ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) +
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else
case when origem = 'FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0
then cc.edeb - ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA <= '20081231'AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end
end) as PENDENTESDEB,
(case when origem='FT' and cc.recibado=1 and (cc.edeb) > 0
then cc.edeb + ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) +
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA > '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else
case when origem='FT' and cc.recibado=0 and (cc.edeb-cc.edebf)<0
then cc.edeb - ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.edebf)
else (cc.edeb-cc.edebf)
end
end) -
(case when origem='FT' and cc.recibado=1 and (cc.ecred)>0
then cc.ecred - ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' aND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else
case when origem='FT' and cc.recibado=0 and (cc.ecred-cc.ecredf)<0
then cc.ecred - ISNULL((SELECT sum(EREC) FROM RL (nolock)
WHERE RL.RDATA > '20081231' AND RL.CCSTAMP=CC.CCSTAMP),0) -
ISNULL((SELECT sum(EREC) FROM LI (nolock)
inner join LE (nolock) on LI.LESTAMP=LE.LESTAMP
WHERE LE.DATA <= '20081231' AND LI.CCSTAMP=CC.CCSTAMP),0)-(cc.ecredf)
else (cc.ecred-cc.ecredf)
end
end) as Saldo
FROM CC (nolock)
WHERE (cc.datalc between '20080101' and '20081231')
) derrivedtable -- select * from (
) teste -- SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
GROUP BY no, nome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 3:07 am
Resolving the correlated subqueries as derived tables gives two CASE blocks - I guess debit and credit, and a third block which calculates the difference between the two as 'Saldo':
SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
from
(select * from (
SELECT cc.datalc as Data,
cast(cc.[no] as varchar) as No,
cc.nome as Nome,
cmdesc as Documento,
cast(cc.nrdoc as varchar) as NoDoc,
cc.recibado,
cc.origem,
-- block 1
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.ecred > 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.ecred - cc.ecredf) < 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
ELSE cc.ecred - cc.ecredf
END AS PENDENTESCRD,
-- block 2
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.edeb > 0
THEN cc.edeb + ISNULL(rld.sumEREC,0) + ISNULL(lih.sumEREC,0) - cc.edebf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.edeb - cc.edebf) < 0
THEN cc.edeb - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.edebf
ELSE (cc.edeb-cc.edebf)
END AS PENDENTESDEB,
-- block 2 - block 1 (PENDENTESDEB - PENDENTESCRD)
(CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.edeb > 0
THEN cc.edeb + ISNULL(rld.sumEREC,0) + ISNULL(lih.sumEREC,0) - cc.edebf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.edeb - cc.edebf) < 0
THEN cc.edeb - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.edebf
ELSE cc.edeb - cc.edebf
END) -
(CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.ecred > 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.ecred - cc.ecredf) < 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
ELSE cc.ecred - cc.ecredf
END) AS Saldo
FROM CC (nolock)
LEFT JOIN (SELECT sum(EREC) AS sumEREC, CCSTAMP
FROM RL (nolock)
WHERE RDATA > '20081231'
GROUP BY CCSTAMP
) rld ON rld.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
inner join LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA <= '20081231'
GROUP BY LI.CCSTAMP
) lil ON lil.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
INNER JOIN LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA > '20081231'
) lih ON lih.CCSTAMP = CC.CCSTAMP
WHERE cc.datalc BETWEEN '20080101' AND '20081231'
) derrivedtable -- select * from (
) teste -- SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
GROUP BY no, nome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 4:10 am
Since this is an "onion" select - there's a select of a select of a select - we can use one of them to calculate Saldo instead of having that huge messy block of code reading the tables:
SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
from
(SELECT Data, No, Nome, Documento, NoDoc, recibado, origem,
PENDENTESCRD, PENDENTESDEB, PENDENTESDEB - PENDENTESCRD AS Saldo
FROM (SELECT cc.datalc as Data,
cast(cc.[no] as varchar) as No,
cc.nome as Nome,
cmdesc as Documento,
cast(cc.nrdoc as varchar) as NoDoc,
cc.recibado,
cc.origem,
-- block 1
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.ecred > 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.ecred - cc.ecredf) < 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
ELSE cc.ecred - cc.ecredf
END AS PENDENTESCRD,
-- block 2
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.edeb > 0
THEN cc.edeb + ISNULL(rld.sumEREC,0) + ISNULL(lih.sumEREC,0) - cc.edebf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.edeb - cc.edebf) < 0
THEN cc.edeb - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.edebf
ELSE (cc.edeb-cc.edebf)
END AS PENDENTESDEB
FROM CC (nolock)
LEFT JOIN (SELECT sum(EREC) AS sumEREC, CCSTAMP
FROM RL (nolock)
WHERE RDATA > '20081231'
GROUP BY CCSTAMP
) rld ON rld.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
inner join LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA <= '20081231'
GROUP BY LI.CCSTAMP
) lil ON lil.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
INNER JOIN LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA > '20081231'
) lih ON lih.CCSTAMP = CC.CCSTAMP
WHERE cc.datalc BETWEEN '20080101' AND '20081231'
) derrivedtable -- select * from (
) teste -- SELECT No, nome, SUM(Saldo) as 'SaldoCCO'
GROUP BY no, nome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 4:15 am
Restrict the SELECT column list to the columns we need, and get rid of the unnecessary "onion skin":
SELECT No, Nome, SUM(PENDENTESDEB - PENDENTESCRD) AS SaldoCCO
FROM (SELECT No, cc.nome as Nome,
-- block 1
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.ecred > 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.ecred - cc.ecredf) < 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
ELSE cc.ecred - cc.ecredf
END AS PENDENTESCRD,
-- block 2
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.edeb > 0
THEN cc.edeb + ISNULL(rld.sumEREC,0) + ISNULL(lih.sumEREC,0) - cc.edebf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.edeb - cc.edebf) < 0
THEN cc.edeb - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.edebf
ELSE (cc.edeb-cc.edebf)
END AS PENDENTESDEB
FROM CC (nolock)
LEFT JOIN (SELECT sum(EREC) AS sumEREC, CCSTAMP
FROM RL (nolock)
WHERE RDATA > '20081231'
GROUP BY CCSTAMP
) rld ON rld.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
inner join LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA <= '20081231'
GROUP BY LI.CCSTAMP
) lil ON lil.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
INNER JOIN LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA > '20081231'
) lih ON lih.CCSTAMP = CC.CCSTAMP
WHERE cc.datalc BETWEEN '20080101' AND '20081231'
) derrivedtable -- select * from (
GROUP BY no, nome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 17, 2009 at 4:30 am
Then bring the two queries together:
SELECT q1.No, q1.Nome, q1.SaldoCCO, q2.saldoLR
FROM (SELECT dt.No, dt.Nome, SUM(dt.PENDENTESDEB - dt.PENDENTESCRD) AS SaldoCCO
FROM (SELECT cc.No, cc.nome as Nome,
-- block 1
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.ecred > 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.ecred - cc.ecredf) < 0
THEN cc.ecred - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.ecredf
ELSE cc.ecred - cc.ecredf
END AS PENDENTESCRD,
-- block 2
CASE WHEN origem = 'FT' AND cc.recibado = 1 AND cc.edeb > 0
THEN cc.edeb + ISNULL(rld.sumEREC,0) + ISNULL(lih.sumEREC,0) - cc.edebf
WHEN origem = 'FT' AND cc.recibado = 0 AND (cc.edeb - cc.edebf) < 0
THEN cc.edeb - ISNULL(rld.sumEREC,0) - ISNULL(lil.sumEREC,0) - cc.edebf
ELSE (cc.edeb-cc.edebf)
END AS PENDENTESDEB
FROM CC (nolock)
LEFT JOIN (SELECT sum(EREC) AS sumEREC, CCSTAMP
FROM RL (nolock)
WHERE RDATA > '20081231'
GROUP BY CCSTAMP
) rld ON rld.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
inner join LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA <= '20081231'
GROUP BY LI.CCSTAMP
) lil ON lil.CCSTAMP = CC.CCSTAMP
LEFT JOIN (SELECT sum(EREC) AS sumEREC, LI.CCSTAMP
FROM LI (nolock)
INNER JOIN LE (nolock) ON LI.LESTAMP = LE.LESTAMP
WHERE LE.DATA > '20081231'
) lih ON lih.CCSTAMP = CC.CCSTAMP
WHERE cc.datalc BETWEEN '20080101' AND '20081231'
) dt
GROUP BY dt.no, dt.nome
) q1
LEFT JOIN (SELECT 'LR' as 'Tipo', no, nome, SUM(EVALOR) AS saldoLR --
FROM LR (NOLOCK)
WHERE u_pagchpd = 0 AND U_PAGPAGAR = 0 AND STATUS < 7 AND edata <= '20081231'
GROUP BY no, nome) q2 ON q2.no = dt.no AND q2.nome = dt.nome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply