Hi guys, the below query works fine until I try to put it into SSRS when it complains that I have already defined the @ variables. Can you help please?
declare @week int
declare @month int
declare @rest int
set @week=(SELECT count(vrs.name0) from v_r_system vrs
inner join v_gs_workstation_status gsw on vrs.resourceid=gsw.resourceid
where DATEDIFF(day, gsw.LastHWScan, GETDATE()) <=7 and active0=1)
set @month=(SELECT count(vrs.name0)
from v_r_system vrs
inner join v_gs_workstation_status gsw on vrs.resourceid=gsw.resourceid
where (DATEDIFF(day, gsw.LastHWScan, GETDATE()) >7 and DATEDIFF(day, gsw.LastHWScan, GETDATE()) <=30))
set @rest=(SELECT count(vrs.name0)
from v_r_system vrs
inner join v_gs_workstation_status gsw on vrs.resourceid=gsw.resourceid
where DATEDIFF(day, gsw.LastHWScan, GETDATE()) >30 and active0=1)
SELECT count(vrs.name0) as 'Total',@week as 'Week',@Month as 'Month',@rest as 'Rest'
from v_r_system vrs
left outer join v_gs_workstation_status gsw on vrs.resourceid=gsw.resourceid
where active0=1