SQL Server - Generar Group By: Sumar Datos

Set nocount on;

if OBJECT_ID('tempdb.dbo.#OfertasDeVenta','U') is not null drop table dbo.#OfertasDeVenta;

SELECT (QUT1.DocEntry) AS 'Oferta Venta',(QUT1.TrgetEntry) AS 'Orden Venta', OQUT.CANCELED AS 'Oferta Cancelada', (OQUT.NumAtCard) as 'N° OC', OQUT.CardName AS 'Cliente',

QUT1.LineStatus AS 'Estado Oferta', QUT1.VendorNum AS 'N° Pedido', (QUT1.ItemCode) AS 'Codigo', QUT1.Dscription AS 'Descripcion', (QUT1.Quantity) AS 'Q Solicitada',

(QUT1.LineTotal) AS 'Total $ Solicitado', QUT1.DocDate AS 'Fecha Creacion Oferta', OQUT.U_ETABO AS 'FechaCompromiso', YEAR(OQUT.U_ETABO) AS 'AñoCompomiso',

month(OQUT.U_ETABO)MesCompromiso, DAY(OQUT.U_ETABO)DiaCompromiso, OQUT.U_ETAPU AS 'FechaFinal'

into #OfertasDeVenta

FROM Flexibles_SAP.dbo.OQUT OQUT, Flexibles_SAP.dbo.QUT1 QUT1

WHERE OQUT.DocEntry = QUT1.DocEntry and year(QUT1.DocDate )='2020'

order by QUT1.DocEntry desc

if OBJECT_ID('tempdb.dbo.#OrdenDeVenta','U') is not null drop table dbo.#OrdenDeVenta;

SELECT ORDR.DocNum AS 'N° Orden Venta', ORDR.CANCELED as 'Orden Cancelada', RDR1.VendorNum as 'OP',

ORDR.DocStatus AS 'Estado Orden', ORDR.NumAtCard,

RDR1.ItemCode AS 'Codigo',

ORDR.DocDate AS 'Fecha Orden Venta', RDR1.LineStatus AS 'EstadoLinea', RDR1.Quantity AS 'Q Orden',

RDR1.LineTotal AS 'Total $ OrdenVenta', RDR1.Price AS 'Precio Unitario', RDR1.Currency AS 'Moneda'

into #OrdenDeVenta

FROM Flexibles_SAP.dbo.ORDR ORDR, Flexibles_SAP.dbo.RDR1 RDR1

WHERE ORDR.DocNum = RDR1.DocEntry AND ORDR.NumAtCard=RDR1.BaseAtCard

order by ORDR.DocNum desc

if OBJECT_ID('tempdb.dbo.#Facturacion','U') is not null drop table dbo.#Facturacion;

SELECT INV1.VendorNum, OINV.CANCELED, OINV.DocStatus AS 'Estado',

---OINV.DocNum AS 'N° Factura'

--INV1.BaseAtCard AS 'N° OC',

INV1.BaseAtCard,

INV1.ItemCode AS 'Codigo', SUM(OINV.Weight) AS 'Q Facturado', SUM(INV1.LineTotal) AS 'Total $ Facturado',

INV1.DocDate AS 'FechaFacturacion', YEAR(INV1.DocDate) AS 'AñoFacturacion', Month(INV1.DocDate) AS 'MesFacturacion', DAY(INV1.DocDate) AS 'DiaFacturacion'

into #Facturacion

FROM Flexibles_SAP.dbo.INV1 INV1, Flexibles_SAP.dbo.OINV OINV

WHERE OINV.NumAtCard = INV1.BaseAtCard AND OINV.DocEntry = INV1.DocEntry

GROUP BY

OINV.DocNum , OINV.CANCELED, OINV.DocStatus,

INV1.VendorNum, INV1.BaseAtCard,

INV1.ItemCode,

INV1.DocDate, YEAR(INV1.DocDate), Month(INV1.DocDate), DAY(INV1.DocDate)

Select a.*, b.*, c.*, ----CASE WHEN [N° Factura] is null THEN ('NO') ELSE 'SI' END AS 'Facturado',

COALESCE([Q Facturado]/ NULLIF([Q Solicitada],0), 0) as '%Q'

from #OfertasDeVenta a

Left Join #OrdenDeVenta b ON (a.[Orden Venta] = b.[N° Orden Venta] and a.Codigo = b.Codigo )

Left Join #Facturacion c ON   ( b.Codigo=c.Codigo and b.NumAtCard=c.BaseAtCard)--FALTA IGUALAR CON B

where a.[Oferta Venta] in ('12284')

order by a.[Oferta Venta] desc