Ventas - Ventas por cliente y articulo

Hola!, paso de forma muy rapída para compartirles esta consulta que me han solicitado mucho los agentes de ventas y que a ustedes les puede servir mucho de igual manera, saludos!.

select a.CardCode,

c.CardName,

a.DocDate,

DATEPART(month , a.DocDate) AS Periodo,

DATEPART(YEAR , a.DocDate) AS Año,

case a.DocSubType 

when 'DN' THEN 'Nota de débito'

when '--' THEN 'Factura'

END AS [Tipo de dcumento], 

a.DocNum,

a.DocEntry IrDocumento,

b.ItemCode,

case a.DocType When 'S' THEN b.dscription ELSE d.ItemName  END AS ItemName, 

b.Price,

case a.CANCELED

WHEN 'C' THEN case a.DocType When 'S' THEN -1 ELSE b.Quantity * -1  END

WHEN 'N' THEN case a.DocType When 'S' THEN 1 ELSE b.Quantity END

ELSE case a.DocType When 'S' THEN 1 ELSE b.Quantity END

END as Quantity,

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * - 1 ELSE (b.Price * 1) * e.rate  * -1 END

ELSE case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

WHEN 'N' THEN

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1)  ELSE (b.Price * 1) * e.rate END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  ELSE (b.Price * b.Quantity) * e.rate END

END

ELSE

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1)  ELSE (b.Price * 1) * e.rate END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  ELSE (b.Price * b.Quantity) * e.rate END

END

END

AS [Total MXN],

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1) / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity)  / e.rate* -1 END

END

WHEN 'N' THEN

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1)  / e.rate END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) ELSE (b.Price * b.Quantity) / e.rate END

END

ELSE

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1)  / e.rate END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) ELSE (b.Price * b.Quantity) / e.rate END

END

END

 AS [Total USD],

a.CANCELED,

a.Comments

from OINV a

inner join INV1 b on  a.docentry = b.docentry

inner join OCRD c on a.CardCode = c.CardCode

left join OITM d on b.ItemCode = d.ItemCode 

left join ORTT e on a.CreateDate = e.RateDate and e.Currency = 'USD'

where a.docdate between [%0]  and [%1]

UNION ALL

select a.CardCode,

c.CardName,

a.DocDate,

DATEPART(month , a.DocDate) AS Periodo,

DATEPART(YEAR , a.DocDate) AS Año,

case a.ObjType 

when 14 THEN 'Nota de Crédito'

END AS [Tipo de dcumento], 

a.DocNum,

a.DocEntry IrDocumento,

b.ItemCode,

case a.DocType When 'S' THEN b.dscription ELSE d.ItemName  END AS ItemName, 

b.Price,

case a.CANCELED

WHEN 'C' THEN case a.DocType When 'S' THEN 1 ELSE b.Quantity  END

WHEN 'N' THEN case a.DocType When 'S' THEN -1 ELSE b.Quantity * - 1 END

ELSE case a.DocType When 'S' THEN -1 ELSE b.Quantity * -1 END

END as Quantity,

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) ELSE (b.Price * 1) * e.rate   END

ELSE case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)   ELSE (b.Price * b.Quantity) * e.rate  END

END

WHEN 'N' THEN

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * -1 ELSE (b.Price * 1) * e.rate * -1 END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity) * -1  ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

ELSE

case a.DocType When 'S'

        THEN case a.CurSource WHEN 'L' THEN  (b.Price * 1) * -1  ELSE (b.Price * 1) * e.rate * -1 END

ELSE          case a.CurSource WHEN 'L' THEN  (b.Price * b.Quantity)  * -1 ELSE (b.Price * b.Quantity) * e.rate * -1 END

END

END

AS [Total MXN],

case a.CANCELED WHEN 'C'

THEN case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1)  ELSE (b.Price * 1) / e.rate  END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity)   ELSE (b.Price * b.Quantity)  / e.rate  END

END

WHEN 'N' THEN

case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1)  / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) * -1 ELSE (b.Price * b.Quantity) / e.rate * -1 END

END

ELSE case a.DocType When 'S'

 THEN

case a.CurSource WHEN 'C' THEN (b.Price * 1) * -1 ELSE (b.Price * 1)  / e.rate * -1 END

ELSE

case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity) * -1 ELSE (b.Price * b.Quantity) / e.rate * -1 END

END

END

 AS [Total USD],

--d.ItemName, 

--b.Price,

--b.Quantity * -1,

--case a.CurSource WHEN 'L' THEN  b.Price * b.Quantity * -1 ELSE (b.Price * b.Quantity * -1) * e.rate END AS [Total MXN],

--case a.CurSource WHEN 'C' THEN (b.Price * b.Quantity * -1) / e.rate ELSE (b.Price * b.Quantity * -1) END AS [Total USD],

a.CANCELED,

a.Comments

from ORIN a

--left join RIN1 e on a.Docentry = e.BaseEntry

inner join RIN1 b on  a.docentry = b.docentry

inner join OCRD c on a.CardCode = c.CardCode

left join OITM d on b.ItemCode = d.ItemCode 

left join ORTT e on a.CreateDate = e.RateDate and e.Currency = 'USD'

where a.docdate between [%0]  and [%1]