Query Help

[Former Member]

Hi,

I'm try to do a query that give me quotes number, invoices number, new clients number , total clients number....all this by salesname and dates ranges.

Something like this:

                                              JAN                                                                        FEB

SalesName |  #quotes | #Invoice | #NewClient | #qutoes | #Invoices | #NewClients | .........................

I have problems with unions

Query invoices:

SELECT DISTINCT T2.[SlpName],(T0.DocNum) as 'Facturas' FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T1.[TargetType] <>'14' and  T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

later a count.

Query quotes:

SELECT DISTINCT T2.[SlpName],(T0.DocNum) as 'Cotizaciones' FROM OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode WHERE T1.[TargetType] <>'14' and  T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

Query number of clients :

SELECT    T0.SlpName, COUNT(T1.CardCode) AS NumClientes

FROM         OSLP AS T0 INNER JOIN

                      OCRD AS T1 ON T0.SlpCode = T1.SlpCode

Query New Clients last 6 months (i prefer this bi dates range)

SELECT     T0.slpName, Count(T1.CardName) AS Clientes

FROM         OSLP AS T0 INNER JOIN

                      OCRD AS T1 ON T0.SlpCode = T1.SlpCode

WHERE      (T1.CreateDate >= GETDATE() - 180)

I Need make a unique query.

Can someone help me, thanks.

[KennedyT21]

Hi Carlos ...

Try This

SELECT DISTINCT T2.[SlpName],count(T0.DocNum) as 'Facturas',''as 'Cotizaciones','' as NumClientes ,'' as Clientes  FROM OINV T0 

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T1.[TargetType] <>'14'   and  T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

group by T2.[SlpName]

Union all

SELECT DISTINCT T2.[SlpName],'',count(T0.DocNum) as 'Cotizaciones','',''

FROM OQUT T0 

INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

  WHERE T1.[TargetType] <>'14'   and T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

  group by T2.[SlpName]

  Union all

SELECT    T0.SlpName,'','', COUNT(T1.CardCode) AS NumClientes,''

FROM         OSLP AS T0 INNER JOIN

                      OCRD AS T1 ON T0.SlpCode = T1.SlpCode  group by SlpName

  Union all

SELECT     T0.slpName,'','','', Count(T1.CardName) AS Clientes

FROM         OSLP AS T0 INNER JOIN

                      OCRD AS T1 ON T0.SlpCode = T1.SlpCode

WHERE      (T1.CreateDate >= GETDATE() - 180) group by SlpName

Regards

Kennedy

[Former Member]

Hi Ken, the query works....but...the results are not in the same cell.....i mean the slpname repeat each seccion, not all are in the same line.

[KennedyT21]

Try This

/************************************************************

* Code kennedy

* Time: 4/16/2015 11:23:50 AM

************************************************************/

SELECT tt.SlpName AS Name

      ,SUM(tt.Facturas) AS 'Facturas'

      ,SUM(tt.Cotizaciones) AS Cotizaciones

      ,SUM(tt.NumClientes) AS NumClientes

      ,SUM(tt.Clientes) AS Clientes

FROM   (

           SELECT DISTINCT T2.[SlpName]

                 ,COUNT(T0.DocNum) AS 'Facturas'

                 ,'' AS 'Cotizaciones'

                 ,'' AS NumClientes

                 ,'' AS Clientes

           FROM   OINV T0

                  INNER JOIN INV1 T1

                       ON  T0.DocEntry = T1.DocEntry

                  INNER JOIN OSLP T2

                       ON  T0.SlpCode = T2.SlpCode

           WHERE  T1.[TargetType]<>'14' -- and  T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

           GROUP BY

                  T2.[SlpName]

           UNION ALL

           SELECT DISTINCT T2.[SlpName]

                 ,''

                 ,COUNT(T0.DocNum) AS 'Cotizaciones'

                 ,''

                 ,''

           FROM   OQUT T0

                  INNER JOIN QUT1 T1

                       ON  T0.DocEntry = T1.DocEntry

                  INNER JOIN OSLP T2

                       ON  T0.SlpCode = T2.SlpCode

           WHERE  T1.[TargetType]<>'14' --and T0.[DocDate] >=[%0] AND  T0.[DocDate] <=[%1]

           GROUP BY

                  T2.[SlpName]

           UNION ALL

           SELECT T0.SlpName

                 ,''

                 ,''

                 ,COUNT(T1.CardCode) AS NumClientes

                 ,''

           FROM   OSLP AS T0

                  INNER JOIN OCRD AS T1

                       ON  T0.SlpCode = T1.SlpCode

           GROUP BY

                  SlpName

           UNION ALL

           SELECT T0.slpName

                 ,''

                 ,''

                 ,''

                 ,COUNT(T1.CardName) AS Clientes

           FROM   OSLP AS T0

                  INNER JOIN OCRD AS T1

                       ON  T0.SlpCode = T1.SlpCode

           WHERE  (T1.CreateDate>=GETDATE()- 180)

           GROUP BY

                  SlpName

       ) AS TT

GROUP BY

       TT.SlpName

Rgds,

Kennedy