Estoy buscando hacer un listado que compare los precios de los artículos de las listas de precios:
SELECT T0.ItemCode,T0.ItemName,T0.LstEvlPric,T0.LastPurCur,
T1.Price aS 'Base DSI (CD)', T1.Currency,
T2.Price aS 'Precio de Lista', T2.Currency,
T3.Price aS 'Precio a Distribuidores', T3.Currency,
T4.Price aS 'Precio a Nuevos Clientes', T4.Currency,
T5.Price aS 'Precio para Proyectos', T5.Currency,
T6.Price aS 'Precio de compra a Fike', T6.Currency,
T7.Price aS 'Precio para Recargas', T7.Currency
FROM OITM T0
LEFT JOIN ITM1 T1 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=1
LEFT JOIN ITM1 T2 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=2
LEFT JOIN ITM1 T3 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=3
LEFT JOIN ITM1 T4 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=4
LEFT JOIN ITM1 T5 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=5
LEFT JOIN ITM1 T6 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=6
LEFT JOIN ITM1 T7 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=7
Solo obtengo los precios de la primera lista, por favor pueden ayudarme a corregir para que obtenga todos los precios ??
Gracias
SELECT
T0.[ItemCode]'Cód.',
T0.[ItemName]'Descripción',
T2.[FirmName]'Fabricante',
T0.[OnHand]'Stock',
T1.[ItmsGrpNam]'Grupo',
T0.AvgPrice 'Costo',
(SELECT DISTINCT D0.Price FROM ITM1 D0 where D0.ItemCode=T0.ItemCode and D0.[PriceList] = '1') as 'Precio Lista 1',
(SELECT DISTINCT D0.Price FROM ITM1 D0 where D0.ItemCode=T0.ItemCode and D0.[PriceList] = '2') as 'Precio Lista 2',
FROM
OITM T0
INNER JOIN OITB T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN OMRC T2 ON T0.[FirmCode] = T2.[FirmCode]
Hola @victorellana te invito a que pases por la sección de presentaciones, de esa manera te podemos apoyar mejor.
En principio tu código esta casi correcto, solo en los joins todos apuntan a t1. y deberían apuntar a cada tabla t1, t2, t3, t4, t5, t6, t7
SELECT T0.ItemCode,T0.ItemName,T0.LstEvlPric,T0.LastPurCur,
T1.Price aS 'Base DSI (CD)', T1.Currency,
T2.Price aS 'Precio de Lista', T2.Currency,
T3.Price aS 'Precio a Distribuidores', T3.Currency,
T4.Price aS 'Precio a Nuevos Clientes', T4.Currency,
T5.Price aS 'Precio para Proyectos', T5.Currency,
T6.Price aS 'Precio de compra a Fike', T6.Currency,
T7.Price aS 'Precio para Recargas', T7.Currency
FROM OITM T0
LEFT JOIN ITM1 T1 ON T0.ItemCode=T1.ItemCode AND T1.PriceList=1
LEFT JOIN ITM1 T2 ON T0.ItemCode=T2.ItemCode AND T2.PriceList=2
LEFT JOIN ITM1 T3 ON T0.ItemCode=T3.ItemCode AND T3.PriceList=3
LEFT JOIN ITM1 T4 ON T0.ItemCode=T4.ItemCode AND T4.PriceList=4
LEFT JOIN ITM1 T5 ON T0.ItemCode=T5.ItemCode AND T5.PriceList=5
LEFT JOIN ITM1 T6 ON T0.ItemCode=T6.ItemCode AND T6.PriceList=6
LEFT JOIN ITM1 T7 ON T0.ItemCode=T7.ItemCode AND T7.PriceList=7