consulta por fecha ventas mes semanal dia y subtotales por cia
Publicado por Gary (1 intervención) el 15/01/2021 22:23:59
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SELECT CASE WHEN GROUPING(AL.NOMBREALMACEN) = 1 THEN 'TOTAL ' + AL.NOMBREALMACEN ELSE ISNULL(AL.NOMBREALMACEN, 'TOTAL') END AS TIENDA, CASE WHEN GROUPING(VEN.NOMVENDEDOR)
= 1 THEN 'TOTAL ' + AL.NOMBREALMACEN ELSE ISNULL(VEN.NOMVENDEDOR, 'TOTAL') END AS VENDEDORES, '01' AS CIA, FORMAT(SUM(AVL.UNIDADESTOTAL), '###,###') AS UNIMES, FORMAT(SUM(AVL.TOTAL),
'###,###,###.00') AS TOTBSMES, FORMAT(SUM(AVL.TOTAL / dbo.F_GET_COTIZACION(AVC.FECHA, 2)), '###,###,###.00') AS TOT$USAMES, ISNULL
((SELECT FORMAT(SUM(AVL.UNIDADESTOTAL), '###,###') AS UND_VTAX
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (YEAR(AVC.FECHA) = DATEPART(YEAR, GETDATE())) AND (MONTH(AVC.FECHA) = DATEPART(MONTH, GETDATE())) AND (DAY(AVC.FECHA) = DATEPART(DAY, GETDATE())) AND
(SUBSTRING(AVL.CODALMACEN, 1, 1) <> 'A')), 0) AS UNIDIA, ISNULL
((SELECT FORMAT(SUM(AVL.TOTAL), '###,###,###.00') AS BS_VTAX
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (YEAR(AVC.FECHA) = DATEPART(YEAR, GETDATE())) AND (MONTH(AVC.FECHA) = DATEPART(MONTH, GETDATE())) AND (DAY(AVC.FECHA) = DATEPART(DAY, GETDATE())) AND
(SUBSTRING(AVL.CODALMACEN, 1, 1) <> 'A')), 0) AS TOTBSDIA, ISNULL
((SELECT FORMAT(SUM(AVL.TOTAL / dbo.F_GET_COTIZACION(AVC.FECHA, 2)), '###,###,###.00') AS BS_VTAX
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (YEAR(AVC.FECHA) = DATEPART(YEAR, GETDATE())) AND (MONTH(AVC.FECHA) = DATEPART(MONTH, GETDATE())) AND (DAY(AVC.FECHA) = DATEPART(DAY, GETDATE())) AND
(SUBSTRING(AVL.CODALMACEN, 1, 1) <> 'A')), 0) AS TOTL$USADIA, ISNULL
((SELECT FORMAT(SUM(AVL.UNIDADESTOTAL), '###,###') AS UND_VTAXX
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (DATEPART(YEAR, AVC.FECHA) >= DATEPART(YEAR, GETDATE())) AND (DATEPART(WW, AVC.FECHA) = DATEPART(WW, GETDATE())) AND (DATEPART(YEAR, AVC.FECHA)
<= DATEPART(YEAR, GETDATE())) AND (DATEPART(WW, AVC.FECHA) = DATEPART(WW, GETDATE())) AND (SUBSTRING(AVL.CODALMACEN, 1, 1) <> 'A')), 0) AS UNISEM, ISNULL
((SELECT FORMAT(SUM(AVL.TOTAL), '###,###,###.00') AS BS_VTAXX
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (DATEPART(YEAR, AVC.FECHA) >= DATEPART(YEAR, GETDATE())) AND (DATEPART(WW, AVC.FECHA) = DATEPART(WW, GETDATE())) AND (DATEPART(YEAR, AVC.FECHA)
<= DATEPART(YEAR, GETDATE())) AND (DATEPART(WW, AVC.FECHA) = DATEPART(WW, GETDATE())) AND (SUBSTRING(AVL.CODALMACEN, 1, 1) <> 'A')), 0) AS TOTBSSEM, ISNULL
((SELECT FORMAT(SUM(AVL.TOTAL / dbo.F_GET_COTIZACION(AVC.FECHA, 2)), '###,###,###.00') AS BS_VTAXX
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (DATEPART(YEAR, AVC.FECHA) >= DATEPART(YEAR, GETDATE())) AND (DATEPART(WW, AVC.FECHA) = DATEPART(WW, GETDATE())) AND (DATEPART(YEAR, AVC.FECHA)
<= DATEPART(YEAR, GETDATE())) AND (DATEPART(WW, AVC.FECHA) = DATEPART(WW, GETDATE())) AND (SUBSTRING(AVL.CODALMACEN, 1, 1) <> 'A')), 0) AS TOT$USASEM
FROM dbo.ALBVENTALIN AS AVL INNER JOINdbo.ALBVENTACAB AS AVC ON AVL.NUMSERIE = AVC.NUMSERIE AND AVL.NUMALBARAN = AVC.NUMALBARAN AND AVL.N = AVC.N INNER JOIN
dbo.ALMACEN AS AL ON AVL.CODALMACEN = AL.CODALMACEN AND AVL.CODALMACEN = AL.CODALMACEN INNER JOIN
dbo.VENDEDORES AS VEN ON AVL.CODVENDEDOR = VEN.CODVENDEDOR AND AVL.CODVENDEDOR = VEN.CODVENDEDOR
WHERE (AVC.FACTURADO = 'T') AND (YEAR(AVC.FECHA) = DATEPART(YEAR, GETDATE())) AND (MONTH(AVC.FECHA) = DATEPART(MONTH, GETDATE())) AND (SUBSTRING(AL.CODALMACEN, 1, 1) <> 'A')
GROUP BY AL.NOMBREALMACEN, VEN.NOMVENDEDOR WITH CUBE
HAVING (SUM(AVL.UNIDADESTOTAL) <> 0) AND (GROUPING(AL.NOMBREALMACEN) <> 1)
ORDER BY AL.NOMBREALMACEN, VEN.NOMVENDEDOR DESC
Valora esta pregunta
0