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
Sastry Duvvuri 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