Skip to Content
0
Former Member
Jul 07, 2015 at 07:40 AM

Need Query help

19 Views

Hi experts,

In my opportunity Report i need to add ManufacturerName OMRC FirmName in the following query, Please do needfull.

declare @From Date declare @To Date

set @From = (select MIN(DD.Docdate) from oinm DD where DD.DocDate>='[%0]')

set @To = (select Max(DE.Docdate) from oinm DE where DE.DocDate<='[%1]')

select

t1.OpprId [Oppr.No.],t1.Name [Oppr.Name] ,t1.cardcode [BP Code]

,t1.CardName [BP Name] ,(select

(select ttt.slpname from OSLP ttt where ttt.slpcode= t.SlpCode)

from OPR1 t where t.OpprId =t1.OpprId and t.Line = (select MAX(Line)from OPR1 tt

where tt.OpprId = t1.OpprId ) )[Last Sales Emp -Acc Rep]

,LEFT(t4.descript,CHARINDEX('-',t4.descript)-1)+' '+right(t4.descript,(len(t4.descript)-CHARINDEX('-',t4.descript)))[Territory]

,(select

(select ttt.Descript from OOST ttt where ttt.Num = t.Step_Id)

from OPR1 t where t.OpprId =t1.OpprId and t.Line = (select MAX(Line)from OPR1 tt where tt.OpprId = t1.OpprId ) )[Last Stage]

,CASE when t1.Status = 'L' then 'Lost' when t1.Status ='W' then 'Won' else 'Open' end as [Status]

,convert(varchar(max),convert(decimal(32,0),t1.CloPrcnt))+' '+'%' [Closing %]

,t1.MaxSumLoc [Potential Amount],isnull(t3.lastName,'')+' '+ isnull(t3.middleName,'')+' '+isnull(t3.firstName,'') [Owner Name]

from OOPR t1

left join OSLP t2 on t1.SlpCode =t2.SlpCode

left join OHEM t3 on t1.Owner = t3.empID

left join OTER t4 on t1.Territory = t4.territryID

left join OUSR t5 on t1.UserSign = t5.USERID

left join OOSR t8 on t8.Num=t1.Source

LEFT JOIN OOND T9 ON T9.IndCode=T1.Industry

where

(t1.OpenDate >= @From and t1.oPENDate <= @To)