Código SQL:
Ver originalDECLARE @fin AS VARCHAR(20), @inicio AS VARCHAR(20),@NUMEROOPI VARCHAR(20);
SELECT @inicio=dateadd(dd,-2, datediff(dd,0,getdate()))
SELECT @fin = DATEADD(s, -1, DATEADD(DAY, 0, CONVERT(DATETIME, CONVERT(DATE, GETDATE()))));
--drop table LOCALHOST.ORIGEN.TMP_TRANRESPROD
IF NOT EXISTS(SELECT TOP 1 * FROM SYSOBJECTS WITH (NOLOCK) WHERE NAME='TMP_TRANRESPROD') BEGIN
CREATE TABLE localhost.origen.[dbo].[TMP_TRANRESPROD] (
[ID] [NUMERIC](18, 0) IDENTITY (1, 1) NOT NULL ,
[CodTabla] [VARCHAR] (100) NULL ,
[CodItem] [VARCHAR](100) NULL,
[Descrip1] [VARCHAR] (50) NULL ,
[Unidad] [VARCHAR] (50) NULL,
[ExActual] [FLOAT] NULL,
[Cant] [FLOAT] NULL,
[Precio] [FLOAT] NULL,
[Utilidad] [FLOAT] NULL,
[Costo] [FLOAT] NULL,
[EsUnid] INT NULL,
) ON [PRIMARY]
ALTER TABLE [dbo].[TMP_TRANRESPROD] WITH NOCHECK ADD
CONSTRAINT [IX_TMP_TRANRESPROD] UNIQUE NONCLUSTERED
(
[ID],
[CodTabla],
[CodItem]
) ON [PRIMARY]
END
DECLARE @varCodUser VARCHAR(100)
SET @varCodUser = 'PERSONAL'
DELETE FROM TMP_TRANRESPROD WHERE CodTabla = @varCodUser
INSERT INTO TMP_TRANRESPROD
SELECT @varCodUser,
SI.CodItem,
SI.Descrip1,
ISNULL((SELECT Unidad FROM SAPROD WHERE CodProd = SI.CodItem),'') AS Unidad,
ISNULL((SELECT Existen FROM SAPROD WHERE CodProd = SI.CodItem),0.00) AS ExActual,
SI.Cantidad*SI.CantMayor*B.Signo AS Cant,
B.Signo* SI.Cantidad*SI.CantMayor*(SI.Precio -
(B.Descto1 + B.Descto2)*SI.Precio/B.Monto+
B.Fletes*SI.Precio/B.Monto) AS Precio,
B.Signo*SI.Cantidad*SI.CantMayor*(SI.Precio -
(B.Descto1 + B.Descto2)*SI.Precio/B.Monto+
B.Fletes*SI.Precio/B.Monto) -
B.Signo*SI.Cantidad*SI.CantMayor*SI.Costo AS Utilidad,
B.Signo*SI.Cantidad*SI.CantMayor*SI.Costo AS Costo,
SI.EsUnid
FROM SAITEMFAC SI, SAFACT B
WHERE (B.FechaE >= @inicio AND B.FechaE <= @fin) AND (B.TipoFac = 'A' OR B.TipoFac = 'B') AND B.Monto <> 0
AND SI.NroLineaC = 0 AND SI.EsUnid = 0
AND (SI.NumeroD = B.NumeroD AND SI.TipoFac = B.TipoFac)
ORDER BY SI.CodItem
SELECT CASE t.EsUnid WHEN 0 THEN ''
WHEN 1 THEN '!'
END + t.CodItem AS CodItem,
ISNULL((SELECT Descrip FROM SAPROD WHERE CodProd = t.CodItem),ISNULL((SELECT Descrip FROM SASERV WHERE CodServ = t.CodItem),'')) AS Descrip1,
t.Unidad,
(SELECT DISTINCT ti.ExActual FROM TMP_TRANRESPROD ti WHERE ti.CodItem = t.CodItem AND ti.EsUnid = t.EsUnid AND CodTabla = @varCodUser) AS ExActual,
SUM(t.Cant) AS TotCant,
SUM(t.Precio) AS TotPrecio,
SUM(t.Costo) AS TotCosto,
SUM(t.Utilidad) AS TotalUtil
FROM TMP_TRANRESPROD t
WHERE (SELECT SUM(ti.Cant) FROM TMP_TRANRESPROD ti WHERE ti.CodItem = t.CodItem AND CodTabla = @varCodUser GROUP BY CodItem) <> 0 AND t.CodTabla = @varCodUser
GROUP BY t.CodItem, t.Unidad, t.EsUnid
ORDER BY t.CodItem
