June 4, 2016 at 4:25 pm
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:
EmpidDeptnolocationidpidsidValue
130201050200
230201050453
330204060741
43020406047
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:
DeptnolocationidpidsidResult
30201050453/200
30204060147/741
Any one can guide on sql query for the above output..
June 7, 2016 at 4:28 am
I'm not 100% sure on the logic of your table here. What I've created below definitely isn't scalable if you were to have a department without 2 employees (what even happens then?). I'm assuming that the second employee is also always divided by the first? If this doesn't work, please give more concise aims for your results and the reasoning for your logic, so we can understand it. Maybe some information about how the Employee data is structured and it's relevance:
Create table #Emp (Empid int,
Deptno int,
locationid int,
pid int,
sid int,
value int)
Insert into #Emp
Values (1, 30, 20, 10, 50, 200),
(2, 30, 20, 10, 50, 453),
(3, 30, 20, 40, 60, 741),
(4, 30, 20, 40, 60, 47)
Select O.Deptno,
O.locationid,
O.pid,
O.sid,
cast(cast(E.value as decimal(12,2)) / cast(o.value as decimal(12,2)) as decimal(12,2)) as value
from #Emp O
join #Emp E on O.Empid = E.Empid - 1 and O.Deptno = E.Deptno and O.locationid = E.locationid
where O.Empid % 2 = 1
Drop Table #Emp
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 7, 2016 at 5:15 am
A different interpretation of the requirement:
SELECT
Deptno
,locationid
,pid
,sid
,LAST_VALUE(value) OVER (PARTITION BY locationid, pid, sid ORDER BY Empid RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) * 1.0
/ FIRST_VALUE(value) OVER (PARTITION BY locationid, pid, sid ORDER BY Empid ROWS UNBOUNDED PRECEDING)
FROM #Emp
John
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply