SQL Server Script to Display Job History

SQL Server Script to Display Job History
I was going through my scripts today and found one that I’d like to share. 
This SQL Server script will display job history.  The benefit of this script over displaying it from the GUI is that you get to see the job durations quickly. 

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

I know I stole the code from the inner-most derived table from someone, but I didn’t make a note of the source. I did a quick search for the source, but I came up with too many possibilities.


Source,

Ramblings of a DBA
http://weblogs.sqlteam.com/tarad/archive/2009/06/10/SQL-Server-Script-to-Display-Job-History.aspx

Intergration Management

The intergartion management process group

1. Develop project charter - Initiating
2. Develop project management plan - Planning Group
3. Direct and Manage project execution - Execution
4. Monitor and control project work - M and C
5. Perform Integrated change controls - M and C
6. Close project or phase - Close

Process Group

Develop Project Charter
------------------------------

<B>Input </B>

1. Project statement of work
2. Business case
3. Contract
4. Enterprises environmental factors
5. Organizational process assets

<B>Tools and Techiniques </B>

Expert judgement

<B> Output </B>

Project Charter


- Creating the project charter involves the high level of planning to assess it is feasible within the given constarints.


<b>Project Charter includes</b>

1. Project Title and Description
2. Project manager assigned and Authority leavel
3. Business Case
4. Resource Preassigned
5. Stakeholders
6. Stakeholders requirements as known
7. Product description and deliverables
8. Measurable Objective
9. Project Approvel Requirement
10. High level project risk
11. Project sponsors authorising this project

The following needs to be done for develop teh project charter

1. Identify stakeholderes
2. Meeting with key stakeholders to confirm high level requirement, project scope, risks assemption and issues
3. Defining product scope
4. Defing project objectives ,constarints and scuccess criteria
5. Documenting risks

<B>Business Case</B>
- Business case captures the business need

Project Selection
---------------------
 The folowing are the categories of the project selection methods

1. Benefit measurement methods (Comparative approach)

- Murder board
- Peer review
- Scoring Models
- Economic models

2. Constrained optimization methods (Mathematical approach)

- Linear programming
- Integer programming
- Dynamic Programming
- Multi objective programming

Economic Models for project selections
- Present value
- Net present value
- Internal rate of retursn
- Playback period
- Cost befefit analysis

Present Value

 PV=FV/(1 + r) power n

FV = Feture value
r = Intrest rate
n = number of time periods


Net Present value
---------------------

 NPV= Total Benifit - Cost over many time period

The project with the greatest NPV is typically selected.

Internal Rate of Returns(IRR)
------------------------------------
 The Highest value of the IRR project will be selected.

Payback Period
-------------------
The shorter backback period project will be selected


Cost benifit analysis
--------------------------

The cost benifit ratio is > 1 = The benifits are grater then Cost
The cost benifit ratio is = 1 = The benifits and Cost are same
The cost benifit ratio is < 1 = The benifits are less then Cost

Typecelly the project with the the value greater then 1 and the larger value project will be selected

Economic value Added (EVA)

This concept is concerned with whether the project returns to the company more value then the initiative cost.

Opportuniuty Cost
-------------------------
            Opportunity given up by selecting one project over another.Project thanjavur is $ 1,000,000,000 is selected and the opportinuty cost is $900,000,000 (for project kumbakonmam)

Sunk Cost
------------
 Sunk cost is already spent cost. Spent cost should not consider to determain whether to continue the project or not?

Law of diminishing returns
---------------------------------
After certain point , adding more inputs will not produce the propositional incresses in the productivity.

Working Capital
--------------------
Current Assets - current liabilities. Thsi is the amount of the money the companty has available to inverst.

Depreciation
----------------
The equipment purchased by the company lose the value over time.It is called the depreciation

There are two forms of depreciations
1. Strigth line depreciation
2.Accelerated depreciation
   - Double declining balance
    <l> http://beginnersinvest.about.com/od/incomestatementanalysis/a/double-declining-balance-depreciation-method.htm</l>

   - Sum of year digit
<l> http://beginnersinvest.about.com/od/incomestatementanalysis/a/sum-of-the-years-digits-depreciation.htm</l>

Constarints and Assemptions
-----------------------------------
They are identified at the highlevel in the initiating  and are refined and documented in detail as part of the scope defin processin project planning.

Once they are identified, constarints and assemptions needs to be managed. The stakeholders , team and sponcers may help to identified the constarint and assemptions.
if the constaint is changed , project management plan may be changed.

Project Statement of work (SOW)

Charter with work under contarct

Enterprise environmental factor

Project management information system

Organizational process asset

  - Process procedures and policies
  - Corporate Knowlwdge Base
  - Historical Informations

Lessons Learned

Develop Project Management Plan
------------------------------------------

- is series of plans and base lines.

Project management processes

PMP Daily activity

1. Formulas
<l> http://pmzilla.com/formulas-pmp-pmp
2. Ritas Chart
<l> https://sites.google.com/site/pgmpguide/pmp-rita-s-process-chart-4th-ed-1

Time Management

Time management process 1. Define activity - planning 2. Sequence Activity - planning 3. Estimate Activity Resource - planning 4. Estimate Activity Duration - planning 5. Develop Schedule - planning 6. Control schedule – planning Schedule Management plan - It is not part of the scheduling process - It is created as part of the “Develop project management plan” process in the integration management The scheduling management plan includes the following 1. Methodology 2. Software needs to be used 3. Rules for estimation( eg days or hrs, include dev + testing) 4. Establishment of schedule baseline for measuring against as part of the Project monitoring and controlling 5. Identification of performance measure that will be used on the project, to identify variance early. 6. Planning for how schedule variance will be managed 7. Identification of schedule change control procedures 8. Reporting format to be used. The schedule management plan is a formal or informal , but it is part of the project management plan. It helps make the scheduling estimating process faster by providing guidelines on how estimated should be stated. During the m&c the schedule management plan if a variance is over the allowable threshold and therefore must be acted upon. Define Activity The process of identifying the specific actions to be performed to produce the project deliverables. Input Scope Baseline Enterprise environmental factor Organizational process Asset Tools and Technique Decomposition Expert judgment Rolling wave planning Templates Output Activity list Activity attributes Activity milestone Rolling wave planning: Better to not estimating lower level instead estimate in the higher level . Sequence Activity The activity and milestone and sequencing them in the order in which work will be performed. The result is network diagram.

Management Framework for PMP framework

1.What is project? 2.What is program? 3.What is portfolio? 4.Who are all stakeholders? 5.Organizational structure? - Matrix Organization - Functional - Projectized organization - Strong matrix Organization - Week matrix - Balanced organization - Progect expediter - Project Coordinator 6. Constraints -Time -Cost -Scope -Quality -Customer Statifcation -Risk -Resources 7.Operational work 8.PMO 9.Project life cyscle 10. Product Life cycle 11.OPM3 12.Objectives 13.Management by Objectives