Find leave taken in alternate intervals within a month

We saw the Rank Function in my previous post for sorting records. In the same way using the function we can get the count of leaves taken in alternate intervals with a month for an employee.

For example:
Leaves taken on 1,2,3,4 of a month and came to office on 5th and again went on leave from 6th. the summary of absence using the query can be obtained.

select min(date_taken) date_from,
       max(date_taken) date_to,
       count(*) num_days
from (
  select date_taken,
         date_taken-row_number() over(order by date_taken) as daysto
         from Absence
     )
group by daysto
order by 1

DATE_FROM	DATE_TO	NUM_SAMPLES
01-DEC-15	04-DEC-15	4
07-DEC-15	10-DEC-15	4
14-DEC-15	16-DEC-15	3
19-DEC-15	20-DEC-15	2

RANK Function

RANK Function:

Each entry can be ranked based on any of the given criteria. For e.g:- grading system in a school.

Syntax:
Function
(arg1,arg2)
OVER(
Partition Clause-Sort as per a particular criteria(eg.job,grade)
Sorting Clause-Normal sort of full record
Windowing Clause- sort based on any one record value.
)


Function -Rank, Dense Rank, Row number

Rank:

1 2 3 3 - Two person are in tie while ranking both will be given same ranking by the next rank will be  added value of one more position i.e

1 2 3 3 5- the 3rd position has a tie and occupies 2 position with same ranking, so the next person will be pushed to 5th in order ranking.

E.g:

Select empno,empname,job,sal,
rank() OVER (order by sal) as rank_sal from emp order by sal.

Dense Ranking:

1 2 3 3 4 -  the 3rd position has a tie and occupies 2 position with same ranking, but the next person will be ranked to next sequence 4th.


Select empno,empname,job,sal,
dense_rank() OVER (order by sal) as rank_sal from emp order by sal.



Row number

1 2 3 4 5 6  -Tie records will be ordered as per the column we add to order and the tie will replaced to sequential

Based on what field we need to position the tie records we can mention in sorting clause.

E.g:

Select empno,empname,job,sal,
row_number() OVER (order by sal,empno) as rank_sal from emp order by sal.

here empno column next to order by clause indicates that the tie should be ranked with precedance of employee number.

Basics of Tables

Concepts in 18 C:

Let see about tables.

In a relational database tables play a vital part to organize data and regularize it as per size and requirement.

Let’s see about permanent tables, temporary tables, external tables, heap organized tables, index-organized tables.

You can define table into two categories: Relational and object tables:

Relational tables are simple and common tables. For example, recording list of students, employees etc. No need to look for dependencies while creating.

Object type tables are table with reference to the application it is related to. The table will be designed for a purpose and also validating or summarizing the dependence’s.

Relational Tables:

S.no	Emp Name	Emp ID	Dept ID
1	a	12	0001
2	b	23	0002

Object Type:

Test	Maths	Science	English	Total
1	100	100	100	300
2	100	100	100	300

create type stud as object (
          maths number(3),
          phy   number(3),
          chem  number(3),
          member function tot return number);

functions can be called inside the object type column.

Ok, Let’s see about table structures:

 heap-organized table :Stores data where ever the storage is available. As normal as our own table at office. Place the things as we go.

index-organized table: Stores data with its primary key as key reference for sequential ordering of storing of data. Or by using pseudocolumn to be referenced to sequence data. Index is not a separate column which points to the data. Instead works as organized data which is sequential and easy to identify in the order we arrange it.

Temporary table:  for a particular session and only be accessed by its owner. While the session gets completed the data will be erased.

 External table: is a read-only table whose metadata is stored in the database but whose data is stored outside the database. For example, the text file needs to be loaded in ETL from a other database and which creates a virtual column in our database while loading.

RTF to Excel Sheet (Separate) O/P

Different Sheet Xls O/P


Form Bi Publisher Report RTF Template if you need to generate output in different excel sheets need to perform the exercise as below.


1. Ensure the grouping of block items.

Different Grouped Data Blocks

2. After Each Grouped Data Block click 'CTRL+ENTER' and place the block you need to be on next sheet.

3.To name the sheets - use the XML tag before the data block:

The output will be generated as below in different sheets.

Happy Learning !!!

Project Management Closure Phase

Closure is the final phase of the project. It requires the consolidation of work done, work required with all the stakeholders. Referred to as approval and sign-off process.

The Key Responsibilities to be a driven are: -

1.                   Is the objective Achieved?

2.                   How to you Measure it?

3.                   Feedback? and

4.                   Lessons learned?

The First key element for Project Wrap up is:

Ensures that the deliverable's are approved, accepted and handed over to the customer. A formal Checklists can be made and signed off from both team and stakeholders will help to avoid further confusions and will motivate the team in further activities. This checklist will be helpful for acceptance, finalizing and terminating resource or de-establishing the team and accounting the final project report.

The Second task is Elevating project success(Measuring): -Accessing, Verifying and Documenting project results.

Measuring: Information based on Objectives set, duration, resource, actual cost etc. to the end status. This will ensure you about project slippage in terms of duration and cost overruns.

The information collected will be compared against baseline project plan that we did in initial and planning phase. The difference between them will help to find out the correction action and put back the project in inline. This helps to identify whether the project scope is achieved or deviated due to some reasons?

Based on this draft we can decide whether the project will be handed over in stages or a single handover. The outstanding documents, rules, drawings, Manuals, unresolved issues should be documented and handed over as well. The unresolved issue should be communicated to the customer/end-user.

Feedback: - Relative it is set to quality and time and cost. This should not show the negative aspect of the project rather than improvising oneself for both team and stake holders as this is a critical step to manage. Acceptance may always have some concerns but make should your feedback lies within the scope and plan made earlier.

Examples :

-          Did the project stay in budget?

-          Did the objective achieved?

-          Stakeholders kept informed at progress of each stages?

-          Team work well together?

-          Agreed project life cycle fallowed?

-          Risk identified and managed effectively?

Constructive feedback's may help to achieve extra skill set and motive each one in the team including project manager and stakeholders.

And now we need to value the lessons learned and the feedback we have got. Most organization avoid this due to lack of time or fear of being blamed etc.

The value of lessons learned will be helpful in next outcomes and improve the performance overall. And also as a project closure part the project manager should have a celebration as well to reward the effort of stake holders.

Project Management Execution Phase

The execution phase of project to check whether the plans are made to met the desired deliverable's.

The findings of internal and external stake holders who are related to the project or in other words who are answerable or questionable part of project are considered to be stake holders. For example internal stake holders is project team, organization etc. External stake holders are client, contractors, customers etc.

Constant communication with them plays a very important in these stages. Effective communication will make sure the facts that the project is falling inline?When it is a large project in an organization the internal stake holders should be assigned with roles, responsibilities and their accountabilities.

Mostly projects fail happens due to  ineffective communications. So the effective communication will be helpful for both the managing the project and the stake holder even though it is a time consuming thing. But can expect for beneficial results.

Before starting the execution phase it is highly recommended to have a initial meetings with all stake holders to discuss about the expectations and concerns.

This will be things to go in track if u take a further step of associating all into a common link and allow access to view the status of the project.

Key Project Team Roles:

Acquiring required skill set plays a important role as in later stages of role confusion may cause a pause in project development.A team Organization chart can be drafted to have a clear view of skill acquired or roles and responsibilities, their reporting to whom and may assign specific task to them.

The team Charter document can be made to outline and also to define the purpose, goals and outcomes including the rules and policies to be fallowed. The overall motive is to have effective performance of team.

Good communications plans help in further clarify objectives, roles and responsibilities. To have a common repository to access the status, achievements, concerns of projects can be made. Commonly we use emails or weekly status meetings.

We can use the below listed technologies/Sites for having a centralized repository to have updates and access to project related documents.

Drop box,

Icloud,

One drive,

Google drive or

Basecamp

 Here the achievements made can also be update to reach all the stakeholders who all are involved as well. And also the related task can a scheduled together easily by identifying each milestones.

The access and approving authority for any documents can be defined. Publishing a project schedule and updating it regularly is a effective way of communication.

Effective communication is about delivering information, giving clear instructions, listening to responses, and providing constructive feedback.

A stakeholder analysis matrix identifies and then categorizes all project stakeholders according to their level of influence and level of interest in the project. Which manage stakeholder expectations throughout the project.

Overall the leadership role and effective situational leadership management and communication in holding trust and making team members to be a part of all informative steps make a successful Project.

Project Management (Risk Analysis)

Project Management -Risk Analysis:

All Projects are exposed to some sort of risks. The risk should be identified addressed and managed. The severity of the risk should be measured and action be taken accordingly as to avoid its impact on overall project deliverable's.

The project risk will vary depending on planning, time, level of experience for other team members and the technology used. Adequate proportion for risk management is required for a successful project. Can verify with a risk management form to be drafted by identifying the risk.

The risk can be technical risk, financial risk or commercial risk etc. The international set standard risks are as follows:

-          Understand and confirm on the objective.

-          Knowing what needs to achieved.

-          Identifying of all risk that may impact on achieving the objective.

-          Involve stakeholders to identify risk factors.

-          Undertake risk assessment -document and analyze the severity of likelihood and impact.

-          Plan the risk Response.

Basically a risk can be responded in 4 ways

-          Accepted

-          Transfer to 3^rd party such as contractor or client as well.

-          Risk can be reduced.

-          Can be avoided.

The risk monitoring review is an ongoing process. The risk management involves to communicate the outcome to stakeholders for proper decision making.

You can draft a design like below to identify the severity of risk and dealing with it:

(Also can call it as risk register)

Risk Identified	Occurrence	Severity	Dependency	Risk Response

for example we can consider the IT Project which i mention in earlier phase.

Risk Identified	Occurrence	Severity	Dependency	Risk Response
Multiple positions for same Employee so grade cannot be applied	Migrating Employees	High	New grade rule and salary process	Refine rule

Can also set the rate for a scale of 1 to 5 for all occurring risks.and inform the higher severity risk to the stakeholders appropriately as it may impact on outcome of project or delay the project or may increase the cost of project.

The acronym PESTLE is mostly considered for analyzing project risk,

P- Political

E- Economical

S- Social

T- Technology

L-Legal

E-Environmental Causes.

The Action taken after finding a risk will be accepting it or transferring it or avoiding it or reducing it.A risk register template should be drafted from our identification session with proposed response.

As a part of risk analysis and plan B should also be drafted after identifying it if its severity is high. For example the plan can revolve around sudden change of team member/stakeholder,change or loss of data.

Even after identifying and drafting a risk response some risk may occur eventually called as residential risk according to its livelihood of occurrence we can avoid it.