Skip to Content
0
Former Member
Nov 29, 2011 at 10:08 PM

Help with Query

34 Views

Hi Guys,

I've created a query, basically a Sales Analisys that is showing the current stock of the Item Sold. I need that the amount of Stock(OITM.OnHand) to be shown only the first time, on the first row of the item sold, and the others rows of the same item sold with a value in null or zero. How can I achieved that?

Eg:

ITEM QUANTITY STOCK

ABC 1 10

ABC 5 0

ABC 2 0

This is the code

/* SELECT * FROM OINV T0 WHERE T0.DocDate>='[%0]' AND T0.DocDate<='[%1]' */
SELECT * FROM (
SELECT CASE WHEN T0.DocSubType='DN' THEN 'NOTA DE DEBITO' ELSE 'FACTURA' END as 'Tipo de Documento', CASE WHEN T0.DocType='I' THEN T1.ItemCode ELSE '' END as 'Código', T1.Dscription as 'Descripción',  CONVERT(VARCHAR,T12.ItmsGrpNam) as 'Grupo de Artículo', T1.Quantity as 'Cantidad Vendida', 
CASE WHEN T1.Quantity=0 THEN 
   (CASE WHEN T0.DocCur='USD' THEN T1.LineTotal
    ELSE T1.LineTotal/T7.Rate END)
ELSE 
   (CASE WHEN T1.Currency='USD' THEN T1.Price
    ELSE T1.Price/T7.Rate END)     
END as 'Precio Unitario', CASE WHEN T0.DiscPrcnt>0 THEN (T1.LineTotal - ((T1.LineTotal*T0.DiscPrcnt)/100)) ELSE T1.LineTotal END AS 'Total Vendido', '' as 'Promedio de Ventas', T10.OnHand as 'Inventario'
FROM OINV T0 LEFT OUTER JOIN OCRD T5 ON  T0.CardCode=T5.CardCode LEFT OUTER JOIN OTER T6 ON T5.Territory=T6.territryID LEFT OUTER JOIN CRD1 T8 ON T0.CardCode=T8.CardCode AND T8.AdresType='B' LEFT OUTER JOIN OSLP T4 ON T0.SlpCode=T4.SlpCode, INV1 T1 LEFT OUTER JOIN OITM T10 ON T1.ItemCode=T10.ItemCode LEFT OUTER JOIN OMRC T11 ON T10.FirmCode=T11.FirmCode LEFT OUTER JOIN OITB T12 ON T10.ItmsGrpCod=T12.ItmsGrpCod, ORTT T7 WHERE T0.DocEntry=T1.DocEntry AND T0.DocDate=T7.RateDate AND  (T0.DocDate>='[%0]' AND T0.DocDate<='[%1]') and T0.DocType='I'

UNION ALL

SELECT  'NOTA DE CREDITO' as 'Tipo de Documento', CASE WHEN T0.DocType='I' THEN T1.ItemCode ELSE '' END as 'Código', T1.Dscription as 'Descripción', CONVERT(VARCHAR,T12.ItmsGrpNam) as 'Grupo de Artículo',  -T1.Quantity as 'Cantidad Vendida',
CASE WHEN T1.Quantity=0 THEN 
   (CASE WHEN T0.DocCur='USD' THEN T1.LineTotal
    ELSE T1.LineTotal/T7.Rate END)
ELSE 
   (CASE WHEN T1.Currency='USD' THEN T1.Price
    ELSE T1.Price/T7.Rate END)     
END as 'Precio Unitario', CASE WHEN T0.DiscPrcnt>0 THEN (-1)*(T1.LineTotal - ((T1.LineTotal*T0.DiscPrcnt)/100)) ELSE (-T1.LineTotal) END AS 'Total Vendido', '' as 'Promedio de Ventas', T10.OnHand as 'Inventario'
FROM ORIN T0 LEFT OUTER JOIN OCRD T5 ON  T0.CardCode=T5.CardCode LEFT OUTER JOIN OTER T6 ON T5.Territory=T6.territryID LEFT OUTER JOIN CRD1 T8 ON T0.CardCode=T8.CardCode AND T8.AdresType='B' LEFT OUTER JOIN OSLP T4 ON T0.SlpCode=T4.SlpCode, RIN1 T1 LEFT OUTER JOIN OITM T10 ON T1.ItemCode=T10.ItemCode LEFT OUTER JOIN OMRC T11 ON T10.FirmCode=T11.FirmCode LEFT OUTER JOIN OITB T12 ON T10.ItmsGrpCod=T12.ItmsGrpCod, ORTT T7 WHERE T0.DocEntry=T1.DocEntry AND T0.DocDate=T7.RateDate AND  (T0.DocDate>='[%0]' AND T0.DocDate<='[%1]') and T0.DocType='I') Z

Thanks for your kind help.