SET LANGUAGE N'Russian';

SELECT s.*
FROM (
	SELECT 
		Reportname = c.NAME
		,SubscriptionDesc = su.Description
		,Subscriptiontype = su.EventType
		,su.LastStatus
		,su.LastRunTime
		,Schedulename = sch.NAME
		,ScheduleType = sch.EventType
		,ScheduleFrequency = CASE sch.RecurrenceType
			WHEN 1
				THEN 'Once'
			WHEN 2
				THEN 'Hourly'
			WHEN 4
				THEN 'Daily/Weekly'
			WHEN 5
				THEN 'Monthly'
			END
		,SUBSTRING(su.Parameters, 1, 32000) AS Parameters
		,CASE 
			WHEN LastStatus NOT LIKE 'Mail sent to%'
				AND LastStatus NOT LIKE 'Done%'
				AND LastStatus NOT LIKE 'New Subscription%'
				AND LastStatus NOT LIKE '%has been saved to%'
				THEN 'Error'
			ELSE 'Complete'
			END AS StatusWork
	FROM Reportserver.dbo.Subscriptions su
	JOIN Reportserver.dbo.CATALOG c ON su.Report_OID = c.ItemID
	JOIN Reportserver.dbo.ReportSchedule rsc ON rsc.ReportID = c.ItemID
		AND rsc.SubscriptionID = su.SubscriptionID
	JOIN Reportserver.dbo.Schedule Sch ON rsc.ScheduleID = sch.ScheduleID
	) s
WHERE StatusWork IN (
		SELECT value
		FROM ReportServer.dbo.ParamTable(@type)
		)
	AND LastStatus <> 'Pending'