February 16, 2018 at 3:31 pm
I have a little problem on my join condition, my script is:
SELECT
DISTINCT
(Case when bo.u_tpfact = 'Utente' then 'U'
When bo.u_tpfact = 'Convenção' then 'C'
When bo.u_tpfact = 'Entidade' then 'E'
When bo.u_tpfact = 'Convenção+Utente' then 'C+U'
When bo.u_tpfact = 'Convenção+Entidade' then 'C+E'
END) [Faturar_a],
Bo.obranome, BO.nome, bo2.identificacao1 [Entidade], BO.tabela1 [Convenção],bii.design [descricaoexame],
Isnull((SELECT TOP 1 u_vpart FROM cl2 INNER JOIN cl ON cl2.cl2stamp = cl.clstamp WHERE cl.nome = bo2.identificacao1 AND cl.no = bo2.u_entfact ),0.00) [Prct particular],
Isnull((SELECT TOP 1 u_vconv FROM cl2 INNER JOIN cl ON cl2.cl2stamp = cl.clstamp WHERE cl.nome = bo2.identificacao1 AND cl.no = bo2.u_entfact ),0.00) [Prct Convenção],
isnull((SELECT TOP 1 u_vsns FROM cl2 INNER JOIN cl ON cl2.cl2stamp = cl.clstamp WHERE cl.nome = bo2.identificacao1 AND cl.no = bo2.u_entfact ),0.00) [Prct SNS],
/*Valor do Exame*/
(select sum(ettdeb) from bi where bi.bistamp = bii.bistamp and bi.bistamp = fi.bistamp ) [ValorExame],
/* Calculo comissões*/
ISNULL(
(CASE WHEN RTRIM(BO.tabela1) <> 'SNS' AND BO.tabela1 <> ''
AND
(
(SELECT top 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and ft.ndoc in ( 3) ) = 1
OR
(SELECT top 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and ft.ndoc in( 5) ) = 1
)
then
ROUND(Isnull((SELECT TOP 1 u_vconv FROM cl2 INNER JOIN cl ON cl2.cl2stamp = cl.clstamp WHERE cl.nome = bo2.identificacao1 AND cl.no = bo2.u_entfact ),0.00)
* (select sum(ettdeb) from bi where bi.bistamp = bii.bistamp and bi.bistamp = fi.bistamp ) ,2)
WHEN RTRIM(BO.tabela1) = 'SNS' AND
(
(SELECT top 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and ft.ndoc in( 3) ) = 1
OR
(SELECT top 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and ft.ndoc in( 5) ) = 1
)
THEN
ROUND(Isnull((SELECT TOP 1 u_vsns FROM cl2 INNER JOIN cl ON cl2.cl2stamp = cl.clstamp WHERE cl.nome = bo2.identificacao1 AND cl.no = bo2.u_entfact ),0.00)
* (select sum(ettdeb) from bi where bi.bistamp = bii.bistamp and bi.bistamp = fi.bistamp ) ,2)
WHEN RTRIM(BO.tabela1) = '' AND
(
(SELECT top 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and ft.ndoc in( 5) ) = 1
OR
(SELECT top 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and ft.ndoc in( 3) ) = 1
)
THEN
ROUND(Isnull((SELECT TOP 1 u_vpart FROM cl2 INNER JOIN cl ON cl2.cl2stamp = cl.clstamp WHERE cl.nome = bo2.identificacao1 AND cl.no = bo2.u_entfact ),0.00)
* (select sum(ettdeb) from bi where bi.bistamp = bii.bistamp and bi.bistamp = fi.bistamp ) ,2)
END ), 0.00) [ValorComissao],
(CASE WHEN (SELECT TOP 1 pn.pago FROM fi inner join ft on fi.ftstamp=ft.ftstamp
INNER JOIN pn ON pn.fistamp = fi.fistamp
where fi.bistamp = bii.bistamp and fi.ndoc in (3,5) ) = 1 THEN 'Pago' ELSE 'Pendente' END ) [Situacao],
(Case when ft.anulado = 1 then 'Anulada' else 'OK' end) [Status Fatura],
(Case when bo.fechada = 1 then 'Fechado' else 'OK' end) [Status Exame]
FROM
bi bii right JOIN bo ON bii.bostamp = bo.bostamp
right join bo2 ON bo2.bo2stamp = bo.bostamp
full outer join fi on bii.bistamp = fi.bistamp
full outer join ft on fi.ftstamp = ft.ftstamp
full outer join pn on fi.fistamp = pn.fistamp
WHERE
bo.dataobra BETWEEN '20180101' and '20180216'
AND bo2.identificacao1 LIKE '%'+ '%' + '%'
And ft.ndoc in (1,2,3,5)
Group by
Bo.obranome, BO.nome, bo2.identificacao1 , BO.tabela1,bii.design,
bo2.u_entfact,bii.bistamp, fi.bistamp, ft.anulado, bo.fechada,bo.u_tpfact
order by obranome asc
For Each BO [Header table] i have always lines on my BI [Lines of document], then the first join are correct.
My problem is whe n i make join on table [FT, FI, PN], because theses tables only have relation on [BO and BI] if i have an invoice.
I need to return all values from my BO and BI [Tables] even if not exits on tables : FI, PN,FT.
someone could give me help.
Best regards,
Luis
February 16, 2018 at 11:13 pm
Try changing this full outer join fi on bii.bistamp = fi.bistamp
full outer join ft on fi.ftstamp = ft.ftstamp
full outer join pn on fi.fistamp = pn.fistamp
to this left join fi on bii.bistamp = fi.bistamp
left join ft on fi.ftstamp = ft.ftstamp
left join pn on fi.fistamp = pn.fistamp
February 17, 2018 at 8:06 am
Hello,
Thanks for your reply.
Best regards,
Luis
February 20, 2018 at 9:07 am
Hello,
The LEFT join condition doesn´t have solve the problem. I keep the original query and i have adding UNION ALL changing my Where condition and finally i solve the problem.
Thanks,
Luis Santos
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply