Skip to Content
0
Former Member
Sep 25, 2008 at 09:09 AM

problem with creating sql expression fields

35 Views

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