on 09-26-2014 7:12 PM
Saludos Estimados
Requiero su valioso apoyo: cree esta consulta para que me despliegue las ultimas 3 compras por articulo
SELECT TOP 3 T2.ItemCode as 'MODELO', T2.ItemName as 'DESCRIPCIÓN', T2.OnHand as 'STOCK', T3.Price AS 'CIF', T0.DocNum AS 'ENTRADA', T0.CardCode AS 'PROVEEDOR', T0.DocDate AS 'FECHA', T1.Quantity AS 'CANTIDAD COMPRADA', T1.Price 'PRECIO COMPRA' FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN ITM1 T3 ON T2.ItemCode = T3.ItemCode INNER JOIN OPLN T4 ON T3.PriceList = T4.ListNum
WHERE T1.ItemCode >= '[%0]' and T1.ItemCode <= '[%1]' and T4.ListNum ='5'
ORDER BY 5 DESC
Si coloco una sola referencia me funciona bien, pero al colocar para seleccionar un rango de artículos solo trae 3 por el TOP que coloque.
Como hago para traer diferentes articulos pero solo las 3 ultimas compras?
Gracias de antemano!
Buenos días
Prueba tu informe de la siguiente manera
declare
@Var int
,@Item1 nvarchar(20)
,@Item2 nvarchar(20)
,@ItemCode nvarchar(20)
set @Var=(select top 1 T0.TransNum from OINM T0 where T0.ItemCode='[%0]' or T0.ItemCode='[%1]')
set @Item1='[%0]'
set @Item2='[%1]'
if object_id('tempdb..##Top3Compras') is not null
begin
drop table ##Top3Compras
end
create table ##Top3Compras
(
[Modelo] [nvarchar](30)
,[Descripcion] [nvarchar](100)
,[Stock] [numeric](19,6)
,[CIF] [numeric](19,6)
,[Entrada] [int]
,[Proveedor] [nvarchar](20)
,[Fecha] [datetime]
,[Cantidad] [numeric](19,6)
,[Precio_Compra] [numeric](19,6)
)
declare Curr Cursor for
select T0.ItemCode from OITM T0 where T0.ItemCode>=@Item1 and T0.ItemCode<=@Item2
open Curr
fetch next from Curr into @ItemCode
while @@FETCH_STATUS = 0
begin
insert into ##Top3Compras
SELECT TOP 10
T2.ItemCode,
T2.ItemName,
T2.OnHand,
T3.Price,
T0.DocNum,
T0.CardCode,
T0.DocDate,
T1.Quantity,
T1.Price
FROM OPDN T0 INNER JOIN PDN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN ITM1 T3 ON T2.ItemCode = T3.ItemCode INNER JOIN OPLN T4 ON T3.PriceList = T4.ListNum
WHERE T1.ItemCode=@ItemCode and T4.ListNum ='5'
ORDER BY 5 DESC
fetch next from Curr into @ItemCode
end
close Curr
deallocate Curr
select *
from ##Top3Compras
drop table ##Top3Compras
Saludos
FLR
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
100 | |
11 | |
11 | |
6 | |
6 | |
5 | |
4 | |
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.