Skip to Content
avatar image
Former Member

Need query help with updation

Hi experts


i had add some more fields and modify the Query , but its again showing Duplication of Items

Pls help me to update the Query



select Distinct OITM.ItemCode,OITM.ItemName,sum(OutQty)'Net Out From System',SUM(OITW.OnHand) as 'Stock in Hand' 
,OITW.AvgPrice,OINM.Warehouse,OINM.DocDate
from OINM
Inner join OITM on OINM.ItemCode=OITM.ItemCode
inner JOIN OITW on OINM.ItemCode = OITW.ItemCode and OINM.Warehouse = OITW.WhsCode
where Warehouse = 'JB-WH' AND DocDate BETWEEN '2016-01-01 00:00:00.000' AND '2017-01-31 00:00:00.000' 
group by OITM.ItemCode,OITM.ItemName,OITW.AvgPrice,OINM.Warehouse,OINM.DocDate
order by [Net Out From System] asc

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    avatar image
    Former Member
    Feb 06, 2017 at 08:19 AM

    Hello,

    Please update your SP's parameter

    @whs VARCHAR with @whs VARCHAR(50)

    Thanks

    Engr. Taseeb Saeed

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 01, 2017 at 05:55 AM

    Dear Shahan Shams,

    You need to group it on Item Code and Item Name, and sum all the Quantites you required, adn get the Min of document date as well Max of document date.

    It will give you the Figure of Items from a specific date range.

    Thanks

    Engr.Taseeb Saeed

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 01, 2017 at 02:29 AM

    Hi,

    It will repeat if your out date is different. Try this,

    select Distinct OITM.ItemCode,OITM.ItemName,sum(OutQty)'Net Out From System',OITW.OnHand as 'Stock in Hand' ,OITW.AvgPrice,OINM.Warehouse,OINM.DocDate from OINM Inner join OITM on OINM.ItemCode=OITM.ItemCode inner JOIN OITW on OINM.ItemCode = OITW.ItemCode and OINM.Warehouse = OITW.WhsCode where Warehouse = '01' AND DocDate BETWEEN '2016-01-01 00:00:00.000' AND '2017-01-31 00:00:00.000' group by OITM.ItemCode,OITM.ItemName,OITW.AvgPrice,OINM.Warehouse,OINM.DocDate,OITW.OnHand order by OITM.ItemCode, [Net Out From System] asc

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Rajan

      I try with your new Query. still items are repeating.

      as per you say if i give doc date and if the out qty is on different date, then how can i get the.data with filtering way

      in a single line.

      Pls could u help in solving this by the said way

      Regards

  • Feb 02, 2017 at 07:49 AM

    Hi,

    It is not possible to get single line. because your out qty date is different. Try below query to exclude 0 out qty. So that number of lines will be less.

    select Distinct OITM.ItemCode,OITM.ItemName,sum(OutQty)'Net Out From System',OITW.OnHand as 'Stock in Hand' ,OITW.AvgPrice,OINM.Warehouse,OINM.DocDate from OINM Inner join OITM on OINM.ItemCode=OITM.ItemCode inner JOIN OITW on OINM.ItemCode = OITW.ItemCode and OINM.Warehouse = OITW.WhsCode where Warehouse = '01' AND DocDate BETWEEN '2016-01-01 00:00:00.000' AND '2017-01-31 00:00:00.000' and OutQty <>0 group by OITM.ItemCode,OITM.ItemName,OITW.AvgPrice,OINM.Warehouse,OINM.DocDate,OITW.OnHand order by OITM.ItemCode, [Net Out From System] asc

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 02, 2017 at 12:25 PM

    Have you checked my latest reply?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Mr Rajan

      Thanks for the Support

      I had check the lasted query update which u had provided, it also shows multi line for single items and as per u said the doc date are different, but i need it to be in single, so that my user will be easy to trace and minimize his work

      Regards

  • avatar image
    Former Member
    Feb 05, 2017 at 04:54 AM
    alter PROCEDURE [dbo].[SP-InventoryOutwithAveragePrice&Instock]
     @DateFrom DateTime,
     @DateTo DateTime,
     @whs VARCHAR
    
    AS  
     Begin  
     select 
    Distinct OITM.ItemCode
    ,OITM.ItemName
    ,sum(OutQty)'Net Out From System'
    ,SUM(OITW.OnHand) as 'Stock in Hand' 
    ,OITW.AvgPrice
    ,OINM.Warehouse
    ,MIN(OINM.DocDate) [From Date]
    ,MAX(OINM.DocDate) [To Date]
    
    FROM OINM
    Inner join OITM on OINM.ItemCode=OITM.ItemCode
    inner JOIN OITW on OINM.ItemCode = OITW.ItemCode and OINM.Warehouse = OITW.WhsCode
    WHERE 
    Warehouse = @whs 
    AND DocDate BETWEEN @DATEFROM AND @DATETO 
    GROUP BY OITM.ItemCode
    		,OITM.ItemName
    		,OITW.AvgPrice
    		,OINM.Warehouse
    HAVING sum(OutQty) > 0
    ORDER BY [Net Out From System] 
    END
    

    Alter your SP with this.

    Thanks

    Engr Taseeb Saeed

    Add comment
    10|10000 characters needed characters exceeded