Sql Querry based on condition [message #629518] |
Wed, 10 December 2014 06:30 |
|
asbh123
Messages: 26 Registered: November 2014 Location: mumbai
|
Junior Member |
|
|
Hi,
I have table like below.I need count of typeofemployees and its aggrigated value like below shown in output.
1)While taking counts and aggregating values rwe will consider the emptype should be both permanent and contract for the particular empid.
2)If in case if any particular empiid belongs to contract emptype
then it should not display in myoutput.
3)If any empid have only permanent then it will consider that record to display in output.
Please see output result for clarification...
table:
Country firstsdate lastdate emp_id dept_id TypeofEmployee Val
US 1-Jan-14 31-Jan-14 1 10 Contract 10
US 1-Jan-14 31-Jan-14 1 11 Contract 20
US 1-Jan-14 31-Jan-14 1 11 Permanent 20
US 1-Jan-14 31-Jan-14 1 12 Permanent 30
US 1-Jan-14 31-Jan-14 2 20 Permanent 40
US 1-Jan-14 31-Jan-14 3 30 Contract 50
US 1-Jan-14 31-Jan-14 3 31 Permanent 60
US 1-Jan-14 31-Jan-14 3 32 Permanent 70
US 1-Jan-14 31-Jan-14 4 41 Contract 80
Output:
country firstsdate lastdate emp_id Permcount Contcount Permvalu ConValue
US 1-Jan-14 31-Jan-14 1 2 1 50 30
US 1-Jan-14 31-Jan-14 2 1 0 40 0
US 1-Jan-14 31-Jan-14 3 2 1 130 50
|
|
|
|
Re: Sql Querry based on condition [message #630895 is a reply to message #629533] |
Tue, 06 January 2015 03:03 |
|
asbh123
Messages: 26 Registered: November 2014 Location: mumbai
|
Junior Member |
|
|
Hi,
The below one is the script worked for me...
select country,firstsdate,lastdate,emp_id,dept_id,typeofemployee,Val,
count(case typeofemployee when 'Permanent' then 1 end) over (partition by emp_id) permanent_count,
count(case typeofemployee when 'Contract' then 1 end) over (partition by emp_id) contract_count,
sum(case typeofemployee when 'Permanent' then val end) over (partition by emp_id) permanent_value,
sum(case typeofemployee when 'Contract' then val end) over (partition by emp_id) contract_value,
row_number() over (partition by emp_id order by null) rn
from employee
|
|
|