cancel
Showing results for 
Search instead for 
Did you mean: 

problem with creating sql expression fields

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi..

To create command level parameters you dont need to declare in command.

--Remove the declaration of parameters from command.

--In the Add command window right hand side there is a button Create.

--Using tha above button create parameters and define the datatype of the parameters.

--Use the created parameters in your add command and click on ok.

It creates command level parameters.

Regards,

Sastry

Former Member
0 Kudos

hi Sastry

you mean i have to create these two @WeekNo, @YearNo parameters as i've used them in my comman??

Former Member
0 Kudos

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

Answers (0)