TSQL for retrieve all clients having invoices and not

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  February 1, 2010 at 6:48 am

  #386215

  Hello comunity

  I have the following TSQL with a derived table, but i want to retrieve all clients (CL) independent that the sames have or not have invoices table (FT), the table (FI) is the lines of my invoice:

  SELECT cl.nome,cl.vendnm AS 'Vendedor', SUM(myft.valor) AS 'Vendas'

  FROM

  (select ft.fdata,ft.no,ft.estab,ft.ftstamp,ft.tipodoc,

  sum(fi.etiliquido) AS 'Valor'

  FROM ft (nolock) inner join fi (nolock) on fi.ftstamp = ft.ftstamp

  where fi.stns = 0

  GROUP BY ft.ftstamp,ft.no, ft.estab,ft.tipodoc,ft.fdata) AS Myft

  right JOIN

  cl ON cl.no=myft.no AND cl.estab = myft.estab

  WHERE myft.tipodoc IN (1,3) AND myft.fdata BETWEEN '20090101' AND

  '20091231'

  GROUP BY cl.vendnm,cl.nome

  The problem that this script cannot retrieve all clients, some with invoice an anothers without invoices.

  Could someone give me an ideia.

  Many thanks

  Luis Santos

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 1, 2010 at 7:14 am

  #1111597

  Your where clause effectively turns your outer join into an inner join. Try the following query:

  with Myft as (

  select

  ft.fdata,

  ft.no,

  ft.estab,

  ft.ftstamp,

  ft.tipodoc,

  sum(fi.etiliquido) AS 'Valor'

  FROM

  ft

  inner join fi

  on (fi.ftstamp = ft.ftstamp)

  where

  fi.stns = 0

  GROUP BY

  ft.ftstamp,

  ft.no,

  ft.estab,

  ft.tipodoc,

  ft.fdata

  )

  SELECT

  cl.nome,

  cl.vendnm AS 'Vendedor',

  SUM(myft.valor) AS 'Vendas'

  FROM

  cl

  left outer join Myft

  ON (cl.no = myft.no

  AND cl.estab = myft.estab

  AND myft.tipodoc IN (1,3)

  AND myft.fdata BETWEEN '20090101' AND '20091231'

  )

  GROUP BY

  cl.vendnm,

  cl.nome;