on 01-07-2015 9:32 AM
Dear All,
Any one can help me to convert below mention SQL query to hanna ?
With PO_Sale as (
Select OITM .ItemCode ,OITM .ItemName ,IBT1 .BatchNum ,
sum(case when OINM .TransType IN (15,16) then OINM .OutQty - OINM .InQty else 0 end) as [Sale Qty],
sum(case when OINM .TransType IN (15,16) then OINM .Price * (OINM .OutQty - OINM .InQty) else 0 end)/sum(case when OINM .TransType IN (15,16) then OINM .OutQty - OINM .InQty else 0 end) as [Avg Sale Price] ,
sum(case when OINM .TransType IN (15,16) then OINM .Price * (OINM .OutQty - OINM .InQty) else 0 end) as [Sale Total],
sum(case when OINM .TransType IN (59,60) then OINM .InQty - OINM .OutQty else 0 end) as [PO Qty],
sum(case when OINM .TransType IN (59,60) then OINM .TransValue else 0 end)/sum(case when OINM .TransType IN (59,60) then OINM .InQty - OINM .OutQty else 0 end) as [Avg PO Cost] ,
sum(case when OINM .TransType IN (59,60) then OINM .TransValue else 0 end) as [Cost of PO]
from IBT1 inner join OINM on IBT1 .BaseEntry = OINM .CreatedBy and IBT1 .BaseNum = OINM .BASE_REF and IBT1 .ItemCode = OINM .ItemCode and
IBT1 .BaseLinNum = OINM .DocLineNum and IBT1 .BaseType = OINM .TransType and IBT1 .WhsCode = OINM .Warehouse
inner join OITM on OITM .ItemCode = OINM .ItemCode
where OINM .TransType in (15,16,59,60)
group by OITM .ItemCode ,OITM .ItemName ,IBT1 .BatchNum
),RawMaterial_Lab_Foh as
(
Select IBT1 .BatchNum ,OITM .ItemCode as [Material Code],OITM .ItemName as[Material Name],OITB .ItmsGrpCod ,OITB .ItmsGrpNam
,sum(WOR1 .IssuedQty) as [Issued Qty] ,
sum(isnull(WOR1 .CompTotal/nullif(WOR1 .IssuedQty,0),0)) as [Unit Cost], sum(WOR1 .CompTotal) as [Total Cost]
from (select Distinct BatchNum ,BsDocEntry ,BsDocType from IBT1 where IBT1 .BsDocType = 202 ) IBT1
inner join WOR1 on IBT1 .BsDocEntry = WOR1 .DocEntry and IBT1 .BsDocType = 202 and WOR1 .IssueType = 'B'
inner join OITM on OITM .ItemCode = WOR1 .ItemCode
inner join OITB on OITM .ItmsGrpCod = OITB .ItmsGrpCod
group by IBT1 .BatchNum ,OITM .ItemCode,OITM .ItemName ,OITB .ItmsGrpCod ,OITB .ItmsGrpNam
union all
Select IBT1 .BatchNum ,OITM .ItemCode ,OITM .ItemName,OITB .ItmsGrpCod ,OITB .ItmsGrpNam
,sum(OINM .OutQty - OINM .InQty) as [Issued Qty] , avg(OINM .CalcPrice) as [Unit Cost], sum(OINM .TransValue)*-1 as [Total Cost]
from (Select Distinct BatchNum ,BsDocEntry,BsDocType,ItemCode From IBT1 where IBT1 .BsDocType = 202)IBT1
inner join OINM on IBT1 .BsDocEntry = OINM .AppObjAbs and IBT1 .BsDocType = OINM .ApplObj and IBT1 .ItemCode <> OINM .ItemCode
inner join OITM on OITM .ItemCode = OINM.ItemCode
inner join OITB on OITM .ItmsGrpCod = OITB .ItmsGrpCod
group by IBT1 .BatchNum ,OITM .ItemCode ,OITM .ItemName,OITB .ItmsGrpCod ,OITB .ItmsGrpNam
)
Select PO_Sale .*,RawMaterial_Lab_Foh.[Material Code] ,RawMaterial_Lab_Foh .[Material Name],
RawMaterial_Lab_Foh.ItmsGrpCod,RawMaterial_Lab_Foh .ItmsGrpNam ,RawMaterial_Lab_Foh .[Issued Qty] ,OBTN.MnfDate,OBTN.ExpDate,
RawMaterial_Lab_Foh .[Unit Cost] ,RawMaterial_Lab_Foh .[Total Cost]
from OBTN Left outer Join PO_Sale on OBTN .DistNumber = PO_Sale .BatchNum
left outer join RawMaterial_Lab_Foh on OBTN .DistNumber = RawMaterial_Lab_Foh .BatchNum
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Please remove your UDF's and UDO's from the query and post it again.
Regards,
Manish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
10 | |
6 | |
5 | |
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.