Hi All,
I have table like below but i need to calculate divison of two columns based on group by deptno,locationid,pid,sid.But here i have different empids.I need output mentioned below.Here empids are staticand those will not change.For particular combination of group by columns will get only 2 empids or some time may be will get only 1.so in that case anything by null or zero should be zero.
TableName:
Need sql qury for below output:In my calculation im considering group by deptno,locationid,pid,sid.But i need to take empid 2 value divided by empid 1(453/200=2.2)value for corresponding group by columns and for some another different group set combination i need to take empid 4 value divided byempid 3 value f(147/741=0.19)for division formula calculation.Below i just given result value as just for forumula purpose.
Output:
Deptno locationid pid sid Result
30 20 10 50 453/200
30 20 40 60 147/741
Any one can guide on sql query for the above output..
This might get you started:
with cte
as (select empid
,deptno
,locationid
,pid
,sid
,cast([value] as decimal) as [value]
,row_number() over(partition by deptno
,locationid
,pid
,sid
order by empid
)
as rn
from yourtable
)
select a.deptno
,a.locationid
,a.pid
,a.sid
,case
when a.[value]=0
then 0
else isnull(b.[value],0)
/a.[value]
end as [value]
from cte as a
left outer join cte as b
on b.deptno=a.deptno
and b.locationid=a.locationid
and b.pid=a.pid
and b.sid=a.sid
and b.rn=2
where a.rn=1
;
Hi Bitsmed,
Actually i have provided data as tabke in the example,i have getiing those column values from different tables so i have written multiple inner conditions as per my requiremnt and finally i got one data based on my query so i written one more select statement on that table that is mentioned below.Assume table name testing is below is script for retrieving data as per my requirement.Can you help me how i can apply yours code mentioned above to the below script.
the below cript i have used to retrieve my table data.
select Empid,Deptno,locationid,pid,sid,
sum(value)
from testing
group by
deptno, locationid,pid,sid;
That would be something like:
with cte
as (select empid
,deptno
,locationid
,pid
,sid
,cast(sum([value]) as decimal) as [value]
,row_number() over(partition by deptno
,locationid
,pid
,sid
order by empid
)
as rn
from testing
group by empid
,deptno
,locationid
,pid
,sid
)
select a.deptno
,a.locationid
,a.pid
,a.sid
,case
when a.[value]=0
then 0
else isnull(b.[value],0)
/a.[value]
end as [value]
from cte as a
left outer join cte as b
on b.deptno=a.deptno
and b.locationid=a.locationid
and b.pid=a.pid
and b.sid=a.sid
and b.rn=2
where a.rn=1
;
another way
create table #empTbl
(
Empid int,
DeptNo int,
LocationId int,
Pid int,
Sid int,
Value int
)
insert into #empTbl
values
(1,30,20,10,50,200),
(2,30,20,10,50,453),
(3,30,20,40,60,741),
(4,30,20,40,60,147);
select * from #empTbl
select e.*,
cast(Lead(e.Value )
over
(
partition by e.DeptNo, e.LocationID, e.Pid, e.Sid order by e.DeptNo, e.LocationID, e.Pid, e.Sid
) as varchar(50)) +'/' +cast( e.Value as varchar(10)) as Div,
cast( Lead(e.Value ) over(partition by e.DeptNo, e.LocationID, e.Pid, e.Sid order by e.DeptNo, e.LocationID, e.Pid, e.Sid ) /e.Value as decimal(5,1)) as Result
from #empTbl as e