Skip to Content
0

Need query help with updation

Jan 31, 2017 at 10:46 AM

172

avatar image
Former Member
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

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

Hello,

Please update your SP's parameter

@whs VARCHAR with @whs VARCHAR(50)

Thanks

Engr. Taseeb Saeed

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

hi taseeb

This works, i had one more information to be get add in the query

OITG.ItmsGrpname

i try to link it with OITM.ItmsGrpcode=OITG.Itmstypcod

it try with both inner join and left outer join and left join

but am getting null value

Regards

0
Former Member
Former Member

Hello,

Please add this join

LEFT OUTER JOIN dbo.OITB ON dbo.OITB.ItmsGrpCod = OITM.ItmsGrpCod 

Thanks,

Engr.Taseeb Saeed

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

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

Show 8 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi taseeb

could u pls edit and provide me the Query

Regards

0
Former Member
Former Member

Dear Shahan,

Try this

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 = '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
ORDER BY [Net Out From System] 
ASC

Thanks

Engr. Taseeb Saeed

0
Former Member
Former Member

Hi taseeb

when i use you latest Query , the stock which has not transcation is also appearing

means, if an item out qty is 0 and if its in stock is avaliable, the item is appering in Report, which is not the correct one.

Regaards

0
Former Member
Former Member

Hello,

You can exclude it by adding having clause as per following code

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 = '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
HAVING sum(OutQty) > 0
ORDER BY [Net Out From System] 


ASC

Thanks

Engr. Taseeb Saeed

0
Former Member
Former Member

Hi taseeb

Thanks for the support

When i try to store your query in procedure and then execute the procedure in a date range there is not data appering

pls see me procedure

Regards

USE [Demo- SAP]
GO
/****** Object:  StoredProcedure [dbo].[SP-InventoryOutwithAveragePrice&Instock]    Script Date: 02/02/2017 11:59:16 AM ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
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] 

0
Former Member
Former Member

Hello,

Are you getting the required result when only execute the query?

Thanks

Engr. Taseeb Saeed

0
Show more comments
Nagarajan K Feb 01, 2017 at 02:29 AM
0

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

Show 1 Share
10 |10000 characters needed characters left 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

0
Nagarajan K Feb 02, 2017 at 07:49 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 02, 2017 at 12:25 PM
0

Have you checked my latest reply?

Show 1 Share
10 |10000 characters needed characters left 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

0
avatar image
Former Member Feb 05, 2017 at 04:54 AM
0
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

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Taseeb

I try with modify your new query, and try the TP by executing, still no data in the Report

Regards

0
Former Member
Former Member

Hello,

Please attache screen shot of SQL Managmenet Studio where are you executing it with only query and as well with SP.

Thanks

Engr. Taseeb Saeed

0
Former Member
Former Member

Hi Taseeeb.

pls find the attach File

Regards

query-output.png (44.9 kB)
spt-output.png (13.7 kB)
0
Former Member
Former Member

please give same date to SP as you have given in query.

Thanks

Eng.Taseeb Saeed

0
Former Member
Former Member

Hi taseeb

i had run the query and TP in the same way

Regards

0
Former Member
Former Member

please close this thread with correct answer.

Thanks

Engr. Taseeb Saeed

0
Show more comments