on 04-02-2013 4:50 PM
Estimados,
Tengo la siguiente duda cuando ejecuto una consulta en el Query Manager me arroja el siguiente error Acuerdo Global (OOAT) desconozco a que se debe este problema.
Adjunto la consulta.
Espero puedan ayudarme.
Saludos,
Christian Lopez S.
--------------------------------------------------------
DECLARE @FechaInicio DATETIME
DECLARE @FechaFin DATETIME
-- CODIGO CON EL CUAL SE CREA LA FECHA FINAL DEL MES
Set @FechaInicio = (Select DATEADD(DAY,DATEDIFF(DAY, MAX(t0.docdate), [%0]),MAX(t0.docdate)) from OINV t0)
BEGIN TRY
Set @FechaFin = (
Select
CASE
WHEN CAST(MONTH(@FechaInicio) AS INT) = 1
THEN CAST('31/01/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 2
THEN CAST('29/02/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 3
THEN CAST('31/03/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 4
THEN CAST('30/04/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 5
THEN CAST('31/05/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 6
THEN CAST('30/06/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 7
THEN CAST('31/07/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 8
THEN CAST('31/08/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 9
THEN CAST('30/09/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 10
THEN CAST('31/10/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 11
THEN CAST('30/11/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
ELSE
CAST('31/12/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
END)
END TRY
BEGIN CATCH
Set @FechaFin = (
Select
CASE
WHEN CAST(MONTH(@FechaInicio) AS INT) = 1
THEN CAST('31/01/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 2
THEN CAST('28/02/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 3
THEN CAST('31/03/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 4
THEN CAST('30/04/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 5
THEN CAST('31/05/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 06
THEN CAST('30/06/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 7
THEN CAST('31/07/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 8
THEN CAST('31/08/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 9
THEN CAST('30/09/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 10
THEN CAST('31/10/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
WHEN CAST(MONTH(@FechaInicio) AS INT) = 11
THEN CAST('30/11/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
ELSE
CAST('31/12/'+CAST(YEAR(@FechaInicio) AS VARCHAR) AS DATETIME)
END)
END CATCH
/* SE CREA LA TABLA TEMPORAL QUE SE LLENARÁ CON EL PROCEDIMIENTO ALMACENADO */
CREATE TABLE #INF_VENTAS
(
Ndocumento DATETIME,
NVenta_DocNum INT,
NVenta_DocDate DATETIME,
Estado CHAR(1),
Tipo VARCHAR(10),
OC VARCHAR(30),
CodCliente VARCHAR(15),
RazonSocial VARCHAR(100),
Tipo1 VARCHAR(20),
Comentarios VARCHAR(254),
CodigoProducto VARCHAR(20),
LineNum INT,
Dscription VARCHAR(100),
Cantidad FLOAT,
PorDespachar FLOAT,
Precio INT,
moneda VARCHAR(3),
Conversion FLOAT,
Dcto FLOAT,
TotalLinea FLOAT,
Vendedor VARCHAR(155),
CodVendedor INT,
Memo VARCHAR(50),
Zona VARCHAR(200),
Division VARCHAR(20),
CodDivision INT,
NC INT,
FolioNC INT,
FechaNC DATETIME,
DocNum INT,
Factura INT,
ValorFact FLOAT,
FechaFact DATETIME,
CondPago VARCHAR(100),
groupCode SMALLINT,
CB VARCHAR(3),
SubGrupo VARCHAR(40),
CostoUnit FLOAT,
CostoTotal FLOAT,
MargenPesos FLOAT,
MargenPorcentaje FLOAT
)
/* SE INSERTAN LOS REGISTROS DEL PROCEDIMIENTO EN LA TABLA TEMPORAL #INF_VENTAS */
INSERT INTO #INF_VENTAS EXEC InformeDeVentas @FechaInicio, @FechaFin
/* TABLA #PPTO QUE TRAE LOS VALORES REQUERIDOS DE LAx TABLA DE PRESUPUESTO */
SELECT
MAX(U_Mes) AS 'Mes',
CAST(SUM(CAST(U_Total AS INT)) AS INT) AS 'U_Total',
U_CodDiv AS 'U_CodDiv',
U_CodVend AS 'U_CodVend'
INTO #PPTO
FROM [@AT_PTOVENDXDIV]
WHERE U_Ano = YEAR(@FechaInicio) AND U_Mes = MONTH(@FechaInicio)
GROUP BY U_CodDiv, U_CodVend
/* TABLA #SIOSI QUE CONTIENE LOS DATOS REQUERIDOS DE LAS COTIZACIONES CON POSIBLE VENTA */
SELECT
OO.SlpCode,
SUM(O1.MaxSumLoc/1000) AS 'SioSi'
INTO #SIOSI
FROM
OPR1 O1
INNER JOIN OOPR OO ON (OO.OpprId = O1.OpprId AND O1.Step_Id = 8 AND OO.Status = 'o')
WHERE (MONTH(O1.CloseDate) = MONTH(@FechaInicio)) AND (YEAR(O1.CloseDate) = YEAR(@FechaInicio))
GROUP BY OO.SlpCode
/* TABLA TEMPORAL CON LOS DATOS DE SERVICIO TECNICO */
SELECT
1 AS 'N',
MONTH(@FechaInicio) AS 'Mes',
'ST Otros' AS 'Division',
'Servicio Técnico' AS 'Vendedor',
0 AS 'PPTO',
SUM(V.TotalLinea)/1000 AS 'Venta',
0 AS '%',
SUM(V.TotalLinea)/1000 AS 'Faltante / Excedente',
0 AS 'SioSi',
SUM(V.TotalLinea)/1000 AS 'Venta Extimada',
0 AS '% Estimado',
SUM(V.TotalLinea)/1000 AS 'Faltante / Excedente Estimado'
INTO #ST
FROM
#INF_VENTAS V
WHERE Memo = 'Servicio Técnico'
AND
(V.Division <> 'ST Mano de Obra' AND V.Division <> 'ST Repuestos')
UNION
SELECT
1 AS 'N',
MONTH(@FechaInicio) AS 'Mes',
V.Division,
'Servicio Técnico' AS 'Vendedor',
MAX(P.U_Total) AS 'PPTO',
SUM(V.TotalLinea)/1000 AS 'Venta',
((SUM(V.TotalLinea)/1000)*100)/(MAX(P.U_Total)) AS '%',
-((MAX(P.U_Total))-(SUM(V.TotalLinea)/1000)) AS 'Faltante / Excedente',
0 AS 'SioSi',
SUM(V.TotalLinea)/1000 AS 'Venta Estimada',
0 AS '% Estimado',
-((MAX(P.U_Total))-(SUM(V.TotalLinea)/1000)) AS 'Faltante / Excedente Estimado'
FROM
#INF_VENTAS V
LEFT JOIN #PPTO P ON (P.U_CodDiv = CodDivision)
WHERE
V.Memo = 'Servicio Técnico'
AND
(V.Division = 'ST Mano de Obra' OR V.Division = 'ST Repuestos' OR V.Division = 'ST Serv. Internos')
GROUP BY V.Division, V.CodDivision
/* TABLA TEMPORAL CON LOS DATOS DE VENTAS */
SELECT
1 AS 'N',
MONTH(@FechaInicio) AS 'Mes',
MAX(I.ItmsGrpNam) AS 'Division',
O.SlpName AS 'Vendedor',
MAX(P.U_Total) AS 'PPTO',
ISNULL(SUM(V.TotalLinea)/1000,0) AS 'Venta',
CASE WHEN ISNULL(SUM(V.TotalLinea)/1000,0) = 0 THEN 0 ELSE (ISNULL(((SUM(V.TotalLinea)/1000)*100)/(MAX(P.U_Total)),0)) END AS 'Porcentaje',
-((MAX(P.U_Total))-(ISNULL(SUM(V.TotalLinea)/1000,0))) AS 'FaltanteExcedente',
ISNULL(S.SioSi,0) AS 'SioSi',
(ISNULL(SUM(V.TotalLinea)/1000,0))+(ISNULL(SUM(S.SioSi),0)) AS 'VentaEstimada',
CASE WHEN (MAX(P.U_Total)) = 0 THEN 0 ELSE (((ISNULL(SUM(V.TotalLinea)/1000,0))+(ISNULL(SUM(S.SioSi),0)))*100)/(MAX(P.U_Total)) END AS 'PorcentajeEstimado',
-(MAX(P.U_Total)-((ISNULL(SUM(V.TotalLinea)/1000,0))+(ISNULL(SUM(S.SioSi),0)))) AS 'FaltanteExcedenteEstimado'
INTO
#VENTAS
FROM #PPTO P
LEFT JOIN #INF_VENTAS V ON (P.U_CodVend = V.CodVendedor)
LEFT JOIN OITB I ON (P.U_CodDiv = I.ItmsGrpCod)
LEFT JOIN OSLP O ON (P.U_CodVend = O.SlpCode)
LEFT JOIN #SIOSI S ON (S.SlpCode = P.U_CodVend)
WHERE
P.U_CodVend <> 50
GROUP BY
P.U_CodVend, O.SlpName, S.SioSi
CREATE TABLE #TOTALES(
Nivel INT,
Fecha INT,
Division VARCHAR(100),
Vendedor VARCHAR(100),
PPTO INT,
Venta INT,
Porcentaje FLOAT,
FaltanteExcedente FLOAT,
SioSi FLOAT,
VentaEstimada INT,
PorcentajeEstimado FLOAT,
FaltanteExcedenteEstimado INT
)
CREATE TABLE #SUB_TOTALES(
Nivel INT,
Fecha INT,
Division VARCHAR(100),
Vendedor VARCHAR(100),
PPTO INT,
Venta INT,
Porcentaje FLOAT,
FaltanteExcedente FLOAT,
SioSi FLOAT,
VentaEstimada FLOAT,
PorcentajeEstimado FLOAT,
FaltanteExcedenteEstimado FLOAT
)
/* ################################################################################ */
/* ######### DETALLES */
/* ################################################################################ */
--Servicio Técnico
INSERT INTO #TOTALES
SELECT
N,
MONTH(@FechaInicio),
Division,
Vendedor,
PPTO,
Venta,
[%],
[Faltante / Excedente],
SioSi,
[Venta Extimada],
[% Estimado],
[Faltante / Excedente Estimado]
FROM #ST
INSERT INTO #TOTALES
SELECT
3 AS N,
MONTH(@FechaInicio),
'STx',
'TOTAL',
SUM(PPTO),
SUM(Venta),
((SUM(Venta)*100)/SUM(PPTO)),
-((SUM(PPTO))-(SUM(Venta))),
0,
SUM(Venta),
((SUM(Venta)*100)/SUM(PPTO)),
-((SUM(PPTO))-(SUM(Venta)))
FROM #ST
--VENTAS
INSERT INTO #TOTALES
SELECT
N,
Mes,
'Ventas-'+Division,
Vendedor,
PPTO,
Venta,
Porcentaje,
FaltanteExcedente,
SioSi,
VentaEstimada,
PorcentajeEstimado,
FaltanteExcedenteEstimado
FROM #VENTAS WHERE Division = 'Piel'
INSERT INTO #TOTALES
SELECT
3 AS 'N',
MAX(Mes) AS 'Mes',
'Ventas-Px' AS 'Division',
'TOTAL' AS 'Vendedor',
SUM(PPTO) AS 'PPTO',
SUM(Venta) AS 'Venta',
((SUM(Venta))*100)/(SUM(PPTO)) AS 'Porcentaje',
-((SUM(PPTO))-(SUM(Venta))) AS 'FaltanteExcedente',
SUM(SioSi) AS 'SioSi',
SUM(Venta)+SUM(SioSi) AS 'Venta Estimada',
((SUM(Venta)+SUM(SioSi))*100)/(SUM(PPTO)) AS '% Estimado',
-((SUM(PPTO))-(SUM(Venta)+SUM(SioSi))) AS 'Faltante / Excedente'
FROM #VENTAS WHERE Division = 'Piel'
INSERT INTO #TOTALES
SELECT
N,
Mes,
'Ventas-'+Division,
Vendedor,
PPTO,
Venta,
Porcentaje,
FaltanteExcedente,
SioSi,
VentaEstimada,
PorcentajeEstimado,
FaltanteExcedenteEstimado
FROM #VENTAS WHERE Division = 'Urología'
INSERT INTO #TOTALES
SELECT
3 AS 'N',
MAX(Mes) AS 'Mes',
'Ventas-Ux' AS 'Division',
'TOTAL' AS 'Vendedor',
SUM(PPTO) AS 'PPTO',
SUM(Venta) AS 'Venta',
((SUM(Venta))*100)/(SUM(PPTO)) AS '%',
-((SUM(PPTO))-(SUM(Venta))) AS 'Faltante / Excedente',
SUM(SioSi) AS 'SioSi',
SUM(Venta)+SUM(SioSi) AS 'Venta Estimada',
((SUM(Venta)+SUM(SioSi))*100)/(SUM(PPTO)) AS '% Estimado',
-((SUM(PPTO))-(SUM(Venta)+SUM(SioSi))) AS 'Faltante / Excedente'
FROM #VENTAS WHERE Division = 'Urología'
INSERT INTO #TOTALES
SELECT
N,
Mes,
'Ventas-'+Division,
Vendedor,
PPTO,
Venta,
Porcentaje,
FaltanteExcedente,
SioSi,
VentaEstimada,
PorcentajeEstimado,
FaltanteExcedenteEstimado
FROM #VENTAS WHERE Division = 'Visión'
INSERT INTO #TOTALES
SELECT
3 AS 'N',
MAX(Mes) AS 'Mes',
'Ventas-Vx' AS 'Division',
'TOTAL' AS 'Vendedor',
SUM(PPTO) AS 'PPTO',
SUM(Venta) AS 'Venta',
((SUM(Venta))*100)/(SUM(PPTO)) AS '%',
-((SUM(PPTO))-(SUM(Venta))) AS 'Faltante / Excedente',
SUM(SioSi) AS 'SioSi',
SUM(Venta)+SUM(SioSi) AS 'Venta Estimada',
((SUM(Venta)+SUM(SioSi))*100)/(SUM(PPTO)) AS '% Estimado',
-((SUM(PPTO))-(SUM(Venta)+SUM(SioSi))) AS 'Faltante / Excedente'
FROM #VENTAS WHERE Division = 'Visión'
INSERT INTO #TOTALES VALUES
(2,'-','Ventas-Px','-----',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(2,'-','Ventas-Ux','-----',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(2,'-','Ventas-Vx','-----',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(4,'-','Ventas-Vx',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(4,'-','Ventas-Px',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(4,'-','Ventas-Ux',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(2,'-','STx','-----',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #TOTALES VALUES
(4,'-','STx',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
INSERT INTO #SUB_TOTALES
SELECT
3 AS N,
MONTH(@FechaInicio),
'STx',
'TOTAL',
SUM(PPTO),
SUM(Venta),
((SUM(Venta)*100)/SUM(PPTO)),
-((SUM(PPTO))-(SUM(Venta))),
0,
SUM(Venta),
((SUM(Venta)*100)/SUM(PPTO)),
-((SUM(PPTO))-(SUM(Venta)))
FROM #ST
INSERT INTO #SUB_TOTALES
SELECT
3 AS 'N',
MAX(Mes) AS 'Mes',
'Ventas-Px' AS 'Division',
'TOTAL' AS 'Vendedor',
SUM(PPTO) AS 'PPTO',
SUM(Venta) AS 'Venta',
((SUM(Venta))*100)/(SUM(PPTO)) AS 'Porcentaje',
-((SUM(PPTO))-(SUM(Venta))) AS 'FaltanteExcedente',
SUM(SioSi) AS 'SioSi',
SUM(Venta)+SUM(SioSi) AS 'Venta Estimada',
((SUM(Venta)+SUM(SioSi))*100)/(SUM(PPTO)) AS '% Estimado',
-((SUM(PPTO))-(SUM(Venta)+SUM(SioSi))) AS 'Faltante / Excedente'
FROM #VENTAS WHERE Division= 'Piel'
INSERT INTO #SUB_TOTALES
SELECT
3 AS 'N',
MAX(Mes) AS 'Mes',
'Ventas-Ux' AS 'Division',
'TOTAL' AS 'Vendedor',
SUM(PPTO) AS 'PPTO',
SUM(Venta) AS 'Venta',
((SUM(Venta))*100)/(SUM(PPTO)) AS '%',
-((SUM(PPTO))-(SUM(Venta))) AS 'Faltante / Excedente',
SUM(SioSi) AS 'SioSi',
SUM(Venta)+SUM(SioSi) AS 'Venta Estimada',
((SUM(Venta)+SUM(SioSi))*100)/(SUM(PPTO)) AS '% Estimado',
-((SUM(PPTO))-(SUM(Venta)+SUM(SioSi))) AS 'Faltante / Excedente'
FROM #VENTAS WHERE Division= 'Urología'
INSERT INTO #SUB_TOTALES
SELECT
3 AS 'N',
MAX(Mes) AS 'Mes',
'Ventas-Vx' AS 'Division',
'TOTAL' AS 'Vendedor',
SUM(PPTO) AS 'PPTO',
SUM(Venta) AS 'Venta',
((SUM(Venta))*100)/(SUM(PPTO)) AS '%',
-((SUM(PPTO))-(SUM(Venta))) AS 'Faltante / Excedente',
SUM(SioSi) AS 'SioSi',
SUM(Venta)+SUM(SioSi) AS 'Venta Estimada',
((SUM(Venta)+SUM(SioSi))*100)/(SUM(PPTO)) AS '% Estimado',
-((SUM(PPTO))-(SUM(Venta)+SUM(SioSi))) AS 'Faltante / Excedente'
FROM #VENTAS WHERE Division = 'Visión'
INSERT INTO #TOTALES
SELECT
5 AS 'N',
NULL AS 'Mes',
'zTOTAL' AS 'Division',
'-----' AS 'Vendedor',
SUM(V.PPTO) AS 'PPTO',
SUM(V.Venta) AS 'Venta',
((SUM(V.Venta))*100)/(SUM(V.PPTO)) AS '%',
SUM(V.FaltanteExcedente) AS 'F/E',
SUM(V.SioSi) AS 'SioSi',
SUM(V.Venta)+SUM(V.SioSi) AS 'Venta Estimada',
((SUM(V.Venta)+SUM(V.SioSi))*100)/(SUM(V.PPTO)) AS '% Estimado',
-((SUM(V.PPTO))-(SUM(V.Venta)+SUM(V.SioSi))) AS 'Faltante / Excedente'
FROM #SUB_TOTALES V
SELECT * FROM #TOTALES ORDER BY 3,1
Yo Tengo el Mismo Problema Pero Con Una consulta muy sencilla mas navidad
declare @FECHA_INICIO varchar(30) declare @FECHA_FIN varchar(30)
set @FECHA_INICIO = SELECT T0.[DocDate] FROM OINV T0 WHERE T0.[DocDate] =[%0]
set @FECHA_FIN = SELECT T0.[DocDate] FROM OINV T0 WHERE T0.[DocDate] =[%1]
SELECT T0 .[DocNum]
,T0 .[ItemCode]
,T0 .[Quantity]
,T0 .[DiscountPercent]
,T0 .[CardCode]
,(SELECT T1.[CardName] FROM [BD_Bourne].[dbo].[OCRD] T1 WHERE T1.[CardCode] COLLATE Modern_Spanish_CI_AS = T0.[CardCode] COLLATE Modern_Spanish_CI_AS) as Nombre
,T0 .[CondicionPago]
,T0 .[Date]
,T0 .[SalesPersonCode]
,T0 .[FechaEnSAP]
FROM [Sic_Local_Web].[dbo].[Backup_Det_Pedido] T0
where T0 .[Date] between '@FECHA_INICIO' and '@FECHA_FIN'
ORDER BY T0 .[Date] ASC
igual si pongo [%0] en el between simpre me dice Error de Acuerdo Global (OOAT) pero si pongo un valor como '01/07/2014' and '02/07/2014' si funciona , por lo que no tengo idea que sea lo que esta mal
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hola, pruebalo así
declare @FECHA_INICIO datetime declare @FECHA_FIN datetime
set @FECHA_INICIO = (/*SELECT T0.[DocDate] FROM [dbo].[OINV] T0 WHERE T0.[DocDate] =*/'[%0]')
set @FECHA_FIN = (/*SELECT T0.[DocDate] FROM [dbo].[OINV] T0 WHERE T0.[DocDate] =*/'[%1]')
SELECT T0 .[DocNum]
,T0 .[ItemCode]
,T0 .[Quantity]
,T0 .[DiscountPercent]
,T0 .[CardCode]
,(SELECT T1.[CardName] FROM [BD_Bourne].[dbo].[OCRD] T1 WHERE T1.[CardCode] COLLATE Modern_Spanish_CI_AS = T0.[CardCode] COLLATE Modern_Spanish_CI_AS) as Nombre
,T0 .[CondicionPago]
,T0 .[Date]
,T0 .[SalesPersonCode]
,T0 .[FechaEnSAP]
FROM [Sic_Local_Web].[dbo].[Backup_Det_Pedido] T0
where T0 .[Date] between @FECHA_INICIO and @FECHA_FIN
ORDER BY T0 .[Date] ASC
Atte.
No será mucho mas simple guardar eso en un procedimiento almacenado y ejecutarlo?
Otro punto, si cambias el parámetro de SAP [%0] por un dato en duro, 2 o 3, el reporte te trae datos?
Saludos
Felipe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Estimado,
He creado el Procedimiento Almacenado y ahora al realizar la ejecución me aparece el siguiente error.
1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Advertencia: valor NULL eliminado por el agregado u otra operación SET.
'Acuerdo global' (OOAT)
El código es
DECLARE @Fecha DATETIME
SET @Fecha = (SELECT Top 1 DocDate FROM OINV WHERE DocDate = '[%1]')
EXEC Informe_Seguimiento_Ventas @Fecha
Saludos y gracias de antemano.
No existe una tabla donde estén todas las fechas.
Existen 2 opciones para realizar esto:
1: Construir una tabla temporal con todas las fechas
CREATE TABLE
##Temp
(
Fecha DATE
)
DECLARE
@Date DATE
SET @Date='20010101'
WHILE @Date < CAST(GETDATE() AS DATE)
BEGIN
INSERT INTO ##Temp
VALUES (@Date)
SET @Date=DATEADD(dd, 1, @Date)
END
SELECT * FROM ##Temp
DROP TABLE ##Temp
Y extraer de ahí la fecha que quieras
2: Ejecutar tu SP de esta manera:
DECLARE @Fecha DATETIME
SET @Fecha = (/*SELECT Top 1 T.DocDate FROM [dbo].[OINV] T WHERE T.DocDate = */ '[%1]')
EXEC Informe_Seguimiento_Ventas @Fecha
Y podrás colocar cualquier fecha
Saludos
User | Count |
---|---|
92 | |
11 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.