Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

problem with creating sql expression fields

hi guys

i have a storedprocedure but i'm not allowed to add new storedprocedures in the database so i created a command for the report and now my problem i'm having difficulties creating parameters:

declare @WeekNo varchar(10),

declare @YearNo varchar(10)

select distinct vw_wrkWorkOrders.SectionDescription,

round((select convert(float,count(1)) from vw_wrkWorkOrders a

where vw_wrkWorkOrders.SectionDescription = a.SectionDescription

and (convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and

convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/

(select case COUNT(1) when 0 then 1 else count(1) end

from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription)*100,1) as 'Job Requested',

round((select convert(float,count(1)) from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and

a.UserDefinedStateDescription in ('Closed','Cancelled') and

(convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and

convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/

(select case count(1) when 0 then 1 else count(1) end

from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and

a.UserDefinedStateDescription in ('Closed','Cancelled'))*100,1) as 'Job Completed',

round((select convert(float,count(1)) from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and

a.UserDefinedStateDescription in ('Scheduled, Awaiting Execution') and

(convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and

convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/

(select case count(1) when 0 then 1 else count(1) end

from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and

a.UserDefinedStateDescription in ('Scheduled, Awaiting Execution'))*100,1) as 'Job Scheduled',

round((select convert(float,count(1)) from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and

a.UserDefinedStateDescription in ('Complete/Awaiting Feedback') and

(convert(varchar(10),datepart(ww,a.CompleteByDate)) in (@WeekNo) and

convert(varchar(10),datepart(yy,a.CompleteByDate)) in (@YearNo)))/

(select case count(1) when 0 then 1 else count(1) end

from vw_wrkWorkOrders a where vw_wrkWorkOrders.SectionDescription = a.SectionDescription and

a.UserDefinedStateDescription in ('Complete/Awaiting Feedback'))*100,1) as 'OutStanding'

from vw_wrkWorkOrders

where vw_wrkWorkOrders.SectionDescription in ('Civil\Building\Carpentry','Electrical','Mechanical','Painting',

'Plumbing','Welding')

i have to create the two parameters @WeekNo, @YearNo which are in the query

replied

Hi..

Yes. You have to create these two parameters at command level and place the parameters in your command whereever you want to evaluate.

For Example : If you create a parameter by name YearNo. Then in your command you will have to use as follows :

Select * from <Table> where <TableField> = {?YearNo}

Hope this will help you.

Regards,

Sastry

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question