It seems like you want something like
select *
from (select person_id,
rating_date,
rating,
lag(rating) over (partition by person_id
order by rating_date) prior_rating
from rating)
where rating != prior_rating
or prior_rating is null
order by person_id, rating_date
which yields the following
SQL> ed
Wrote file afiedt.buf
1 select person_id,
2 rating_date,
3 rating
4 from (select person_id,
5 rating_date,
6 rating,
7 lag(rating) over (partition by person_id
8 order by rating_date) prior_rating
9 from rating)
10 where rating != prior_rating
11 or prior_rating is null
12* order by person_id, rating_date
SQL> /
PERSON_ID RATING_DAT RATING
---------- ---------- --------
1 2010-08-04 A3
1 2010-08-14 A1
2 2010-08-04 G2
2 2010-08-14 G1
2 2010-08-20 G4
3 2010-08-04 G1
3 2010-08-14 G3
7 rows selected.
There is really no other way than to use a case statement if you want the plain English version of all the integer values used by SQL Server (at least to my limited knowledge of T-SQL). This is a common thing needed by a lot of DBAs and there are plenty of scripts out there on the subject. I found the script below from Michelle Ufford to be quite useful in getting a good view of the schedule of jobs running. Since maintenance plans end up as SQL Agent jobs this will show you that as well.
The output you get from the script will give you the frequency, subFrequency, scheduled time and next run time and date. I just took her script and put it as a stored procedure on my server so I can easily run it when needed. You could also put it as a view if you wanted too. Since other folks can come behind me and add jobs and such I like to check it every-now-and-then to see what is going on.
/*
Description: SQL Agent Information
Source: SQLFool, http://sqlfool.com/2012/09/sql-agent-job-script-2/
*/
SET NOCOUNT ON;
DECLARE @jobHistory TABLE (
job_id UNIQUEIDENTIFIER
,success INT
,cancel INT
,fail INT
,retry INT
,last_run_status CHAR(12)
,last_execution_id INT
,last_duration CHAR(8)
,last_execution_start DATETIME
);
WITH lastExecution
AS (
SELECT job_id
,MAX(instance_id) AS last_instance_id
FROM msdb.dbo.sysjobhistory
WHERE step_id = 0
GROUP BY job_id
)
INSERT INTO @jobHistory
SELECT sjh.job_id
,SUM(CASE
WHEN sjh.run_status = 1
AND step_id = 0
THEN 1
ELSE 0
END) AS success
,SUM(CASE
WHEN sjh.run_status = 3
AND step_id = 0
THEN 1
ELSE 0
END) AS cancel
,SUM(CASE
WHEN sjh.run_status = 0
AND step_id = 0
THEN 1
ELSE 0
END) AS fail
,SUM(CASE
WHEN sjh.run_status = 2
THEN 1
ELSE 0
END) AS retry
,CASE sjh.run_status
WHEN 0
THEN 'Failed'
WHEN 1
THEN 'Succeeded'
WHEN 2
THEN 'Retry'
WHEN 3
THEN 'Canceled'
WHEN 4
THEN 'In Progress'
ELSE 'Unknown'
END AS last_run_status
,MAX(CASE
WHEN sjh.step_id = 0
THEN instance_id
ELSE NULL
END) last_execution_id
,SUBSTRING(CAST(MAX(CASE
WHEN le.job_id IS NOT NULL
THEN sjh.run_duration
ELSE NULL
END) + 1000000 AS VARCHAR(7)), 2, 2) + ':' + SUBSTRING(CAST(MAX(CASE
WHEN le.job_id IS NOT NULL
THEN sjh.run_duration
ELSE NULL
END) + 1000000 AS VARCHAR(7)), 4, 2) + ':' + SUBSTRING(CAST(MAX(CASE
WHEN le.job_id IS NOT NULL
THEN sjh.run_duration
ELSE NULL
END) + 1000000 AS VARCHAR(7)), 6, 2) AS last_duration
,MAX(CASE
WHEN le.last_instance_id IS NOT NULL
THEN CONVERT(DATETIME, RTRIM(run_date)) + ((run_time / 10000 * 31200) + ((run_time % 10000) / 100 * 60) + (run_time % 10000) % 100) / (86399.9964)
ELSE '1900-01-01'
END) AS last_execution_start
FROM msdb.dbo.sysjobhistory AS sjh
LEFT JOIN lastExecution AS le ON sjh.job_id = le.job_id
AND sjh.instance_id = le.last_instance_id
GROUP BY sjh.job_id
,sjh.run_status;
/* We need to parse the schedule into something we can understand */
DECLARE @weekDay TABLE (
mask INT
,maskValue VARCHAR(32)
);
INSERT INTO @weekDay
SELECT 1
,'Sunday'
UNION ALL
SELECT 2
,'Monday'
UNION ALL
SELECT 4
,'Tuesday'
UNION ALL
SELECT 8
,'Wednesday'
UNION ALL
SELECT 16
,'Thursday'
UNION ALL
SELECT 32
,'Friday'
UNION ALL
SELECT 64
,'Saturday';
/* Now let's get our schedule information */
WITH myCTE
AS (
SELECT sched.name AS 'scheduleName'
,sched.schedule_id
,jobsched.job_id
,CASE
WHEN sched.freq_type = 1
THEN 'Once'
WHEN sched.freq_type = 4
AND sched.freq_interval = 1
THEN 'Daily'
WHEN sched.freq_type = 4
THEN 'Every ' + CAST(sched.freq_interval AS VARCHAR(5)) + ' days'
WHEN sched.freq_type = 8
THEN REPLACE(REPLACE(REPLACE((
SELECT maskValue
FROM @weekDay AS x
WHERE sched.freq_interval & x.mask <> 0
ORDER BY mask
FOR XML RAW
), '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '') + CASE
WHEN sched.freq_recurrence_factor <> 0
AND sched.freq_recurrence_factor = 1
THEN '; weekly'
WHEN sched.freq_recurrence_factor <> 0
THEN '; every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' weeks'
END
WHEN sched.freq_type = 16
THEN 'On day ' + CAST(sched.freq_interval AS VARCHAR(10)) + ' of every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months'
WHEN sched.freq_type = 32
THEN CASE
WHEN sched.freq_relative_interval = 1
THEN 'First'
WHEN sched.freq_relative_interval = 2
THEN 'Second'
WHEN sched.freq_relative_interval = 4
THEN 'Third'
WHEN sched.freq_relative_interval = 8
THEN 'Fourth'
WHEN sched.freq_relative_interval = 16
THEN 'Last'
END + CASE
WHEN sched.freq_interval = 1
THEN ' Sunday'
WHEN sched.freq_interval = 2
THEN ' Monday'
WHEN sched.freq_interval = 3
THEN ' Tuesday'
WHEN sched.freq_interval = 4
THEN ' Wednesday'
WHEN sched.freq_interval = 5
THEN ' Thursday'
WHEN sched.freq_interval = 6
THEN ' Friday'
WHEN sched.freq_interval = 7
THEN ' Saturday'
WHEN sched.freq_interval = 8
THEN ' Day'
WHEN sched.freq_interval = 9
THEN ' Weekday'
WHEN sched.freq_interval = 10
THEN ' Weekend'
END + CASE
WHEN sched.freq_recurrence_factor <> 0
AND sched.freq_recurrence_factor = 1
THEN '; monthly'
WHEN sched.freq_recurrence_factor <> 0
THEN '; every ' + CAST(sched.freq_recurrence_factor AS VARCHAR(10)) + ' months'
END
WHEN sched.freq_type = 64
THEN 'StartUp'
WHEN sched.freq_type = 128
THEN 'Idle'
END AS 'frequency'
,ISNULL('Every ' + CAST(sched.freq_subday_interval AS VARCHAR(10)) + CASE
WHEN sched.freq_subday_type = 2
THEN ' seconds'
WHEN sched.freq_subday_type = 4
THEN ' minutes'
WHEN sched.freq_subday_type = 8
THEN ' hours'
END, 'Once') AS 'subFrequency'
,REPLICATE('0', 6 - LEN(sched.active_start_time)) + CAST(sched.active_start_time AS VARCHAR(6)) AS 'startTime'
,REPLICATE('0', 6 - LEN(sched.active_end_time)) + CAST(sched.active_end_time AS VARCHAR(6)) AS 'endTime'
,REPLICATE('0', 6 - LEN(jobsched.next_run_time)) + CAST(jobsched.next_run_time AS VARCHAR(6)) AS 'nextRunTime'
,CAST(jobsched.next_run_date AS CHAR(8)) AS 'nextRunDate'
FROM msdb.dbo.sysschedules AS sched
INNER JOIN msdb.dbo.sysjobschedules AS jobsched ON sched.schedule_id = jobsched.schedule_id
WHERE sched.enabled = 1
)
/* Finally, let's look at our actual jobs and tie it all together */
SELECT job.name AS [jobName]
,l.name AS [jobOwner]
,CASE
WHEN job.enabled = 1
THEN 'Enabled'
ELSE 'Disabled'
END AS [jobStatus]
,COALESCE(sched.scheduleName, '(unscheduled)') AS [scheduleName]
,COALESCE(sched.frequency, '') AS [frequency]
,COALESCE(sched.subFrequency, '') AS [subFrequency]
,COALESCE(SUBSTRING(sched.startTime, 1, 2) + ':' + SUBSTRING(sched.startTime, 3, 2) + ' - ' + SUBSTRING(sched.endTime, 1, 2) + ':' + SUBSTRING(sched.endTime, 3, 2), '') AS [scheduleTime] -- HH:MM
,COALESCE(SUBSTRING(sched.nextRunDate, 1, 4) + '/' + SUBSTRING(sched.nextRunDate, 5, 2) + '/' + SUBSTRING(sched.nextRunDate, 7, 2) + ' ' + SUBSTRING(sched.nextRunTime, 1, 2) + ':' + SUBSTRING(sched.nextRunTime, 3, 2), '') AS [nextRunDate]
/* Note: the sysjobschedules table refreshes every 20 min, so nextRunDate may be out of date */
,COALESCE(jh.success, 0) AS [success]
,COALESCE(jh.cancel, 0) AS [cancel]
,COALESCE(jh.fail, 0) AS [fail]
,COALESCE(jh.retry, 0) AS [retry]
,jh.last_run_status AS [lastRunStatus]
,CASE job.notify_level_email
WHEN 0
THEN 'None'
WHEN 1
THEN 'Succeeds'
WHEN 2
THEN 'Fails'
WHEN 3
THEN 'Completion'
ELSE 'Unknown'
END AS [EmailNotification]
,CASE job.notify_level_eventlog
WHEN 0
THEN 'None'
WHEN 1
THEN 'Succeeds'
WHEN 2
THEN 'Fails'
WHEN 3
THEN 'Completion'
ELSE 'Unknown'
END AS [EventLogNotification]
FROM msdb.dbo.sysjobs AS job
INNER JOIN master.sys.syslogins l ON l.sid = job.owner_sid
LEFT JOIN myCTE AS sched ON job.job_id = sched.job_id
LEFT JOIN @jobHistory AS jh ON job.job_id = jh.job_id
LEFT JOIN msdb.dbo.sysoperators oper ON job.notify_email_operator_id = oper.id
WHERE job.enabled = 1 -- do not display disabled jobs
AND jh.last_execution_start > '1900-01-01'
ORDER BY jobName
OPTION (RECOMPILE);
GO
Best Answer
I'm not sure what that status value means, but most of my articles are listed with status 57 and they are working just fine.
I'm guessing that there's a bitwise value of 32 which is missing (why else go from 16 to 64) and I'm guessing that 24 should actually be 32 as 24 isn't a valid bitwise value.
I'll see if I can get it confirmed that the 24 should actually be 32.