Query ventas por vendedor y por semana

[former_member203638]

Hola a todos, alguien me podría ayudar con un query de ventas el cual le ponga el rango de fechas de mes y el query me saque las ventas agrupadas por semana y por vendedor . Es posible?

Saludos

Alessandro

[felipe_loyolarodriguez · Accepted solution]

Prueba esto:

DECLARE
          @VAR                    INT,
          @F_INI                    DATETIME, 
          @F_FIN                    DATETIME,
          @Date                    DATETIME,
          @pvt_table          NVARCHAR(MAX),
          @sSQL                    NVARCHAR(MAX)
SET @VAR=(SELECT TOP 1 A.TransId FROM JDT1 A WHERE A.RefDate BETWEEN '[%0]' AND '[%1]')
SET @F_INI='[%0]'
SET @F_FIN='[%1]'
IF EXISTS(SELECT 'A' FROM [tempdb].[sys].[tables] WHERE name='##Week')
DROP TABLE ##Week
CREATE TABLE 
          ##Week
                    (
                    Semana NVARCHAR(6)
                    )
SET @Date=@F_INI
WHILE @Date < @F_FIN
BEGIN
          INSERT INTO ##Week
          VALUES ('Sem'+'-'+CONVERT(NCHAR(2),(DATEPART(ww,@Date))))
          SET @Date=DATEADD(ww, 1, @Date)
END
SELECT @pvt_table = COALESCE(@pvt_table + ',[' + A.Semana + ']', '[' + A.Semana + ']')    
FROM ##Week A
ORDER BY A.Semana
SET @sSQL=N'
                    SELECT *
                    FROM (
                              SELECT T1.SlpName AS Vend, SUM(T0.DocTotal-T0.VatSum) AS Venta, ''Sem''+''-''+CONVERT(NCHAR(2),DATEPART(ww,T0.DocDate)) AS Sem
                              FROM OINV T0
                              INNER JOIN OSLP T1 ON T1.SlpCode=T0.SlpCode
                              WHERE T0.DocDate BETWEEN @INI AND @FIN
                              GROUP BY T1.SlpName, T0.DocDate
                              UNION ALL
                              SELECT T1.SlpName AS Vend, -SUM(T0.DocTotal-T0.VatSum) AS Venta, ''Sem''+''-''+CONVERT(NCHAR(2),DATEPART(ww,T0.DocDate)) AS Sem
                              FROM ORIN T0
                              INNER JOIN OSLP T1 ON T1.SlpCode=T0.SlpCode
                              WHERE T0.DocDate BETWEEN @INI AND @FIN
                              GROUP BY T1.SlpName, T0.DocDate
                    ) T0
                    PIVOT (
                              SUM(T0.Venta)
                              FOR T0.Sem IN ('+@pvt_table+')
                    ) AS Pvt
                    ORDER BY 1 
                    '
EXEC sp_executesql @sSQL, N'@Pvt_table NVARCHAR(MAX), @INI DATETIME, @FIN DATETIME', @pvt_table, @F_INI, @F_FIN
DROP TABLE ##Week

Saludos

Felipe

[former_member203638 · Accepted solution]

Exelente Felipe, Eso necesitaba!

Saludos.

Alessandro.

[former_member228470]

Hola Alessandro

Fijate si este query te puede servir.  Trae solo las facturas que no tienen nota de credito y las que estan pagadas.  Tu lo ajustarias a lo que necesitas. Lo de agrupar por vendedor se trabajaria en el layout de impresión.

/* SELECT FROM [DBO.OINV] T0 */    
DECLARE @Desde AS datetime    
/*WHERE*/        
SET @Desde= /* T0.DOCDATE */ '[%0]'   
   
/* SELECT FROM [DBO.OINV] T0 */    
DECLARE @Hasta AS datetime   /*WHERE*/   
SET @Hasta= /* T0.DOCDATE */ '[%1]'   
    
/* SELECT FROM [DBO.OSLP] t2 */     
DECLARE @Vendedor AS varchar(50)   
/*WHERE*/     
SET @Vendedor= /* t2.SLPNAME */ '[%2]'   

select t2.SlpName, T0.CardCode, T0.CardName, t0.DocNum '#Doc',  T0.Docdate, t0.DocTotal,
T3.DocNum 'Recibo', T3.DocDate 'Fecha Recibo', T3.SumApplied 'Pago'

From OINV t0
inner join inv1 T1 on t0.DocEntry = T1.docentry
inner join OSLP t2 on T0.SlpCode = t2.SlpCode
left Join ( Select Y.DocTransId,X.DocNum,X.DocTotal,Y.SumApplied,X.DocDate From ORCT X      
           inner join RCT2 Y On X.DocEntry=Y.DocNum) T3 On T3.DocTransId=T0.TransId  
inner join OITM t4 on t4.ItemCode = T1.ItemCode
inner join OITB t5 on t5.ItmsGrpCod = t4.ItmsGrpCod
where T1.TargetType != 14 and t0.PaidToDate = t0.DocTotal and
T0.DocDate BETWEEN @desde AND @Hasta 
AND (t2.SlpName =@Vendedor  OR @Vendedor='') 
order by t2.SlpName