CODEWITHSUNDEEP
c#sqlasp.netsql-server
JonL
JonL

Reputation: 53

How to display multiple max columns from the max ID

I have the following query in SQL Server:

select 
    Max(STO.LeaveID) as LeaveID, 
    LR.EmployeeName, 
    STO.DateOff, 
    STO.TimeBegin, 
    STO.TimeEnd, 
    STO.PayPeriodEnd, 
    STO.TodayHoursOff, 
    STO.LeaveCode 
from 
    dbo.tblSeperateTimeOff STO
inner join 
    dbo.tblLeaveRequest LR on STO.LeaveID=LR.ID
inner join 
    dbo.tblLeaveApproval LA on STO.LeaveID = LA.LeaveID
where 
    LA.ApprovalDepartment like'%Finance%' 
    and EmployeeName like '%polland%' 
    and LA.IsApprove=1 
    and LA.IsFinalApprove=1 
group by 
    LR.EmployeeName, 
    STO.DateOff, 
    STO.TimeBegin, 
    STO.TimeEnd, 
    STO.PayPeriodEnd, 
    STO.TodayHoursOff, 
    STO.LeaveCode 
order by 
    EmployeeName

The result displayed: 

LeaveID EmployeeName    DateOff     TimeBegin   TimeEnd     PayPeriod   Hours   LeaveCode
88      Polland, Sean   2014-09-08  08:30AM     11:00AM     2014-09-13  2.5     P (Personal Leave Scheduled*)
112     Polland, Sean   2014-09-24                          2014-09-27  8       P (Personal Leave Scheduled*)
121     Polland, Sean   2014-09-25                          2014-09-27  8       P (Personal Leave Scheduled*)
121     Polland, Sean   2014-09-26                          2014-09-27  8       P (Personal Leave Scheduled*)

I would like to get rid of the row with the LeaveID 112 and keep the 88 and the two 121's LeaveID. The reason for this is that I want it to have the same PayPeriod only from the max Leave ID. How would I format the query to make this happen? Thanks.

Upvotes: 1

Views: 62

Answers (3)

jpw
jpw

Reputation: 44891

You can use a correlated subquery to filter for max values for every id. Try adding this to your inner joins:

inner join 
  (
    select max(LeaveID) maxleaveid , PayPeriodEnd
    from tblSeperateTimeOff
    group by PayPeriodEnd
  ) m on sto.leaveid = m.maxleaveid and sto.PayPeriodEnd = m.PayPeriodEnd

I think this is what you need:

select 
    Max(STO.LeaveID) as LeaveID, 
    LR.EmployeeName, 
    STO.DateOff, 
    STO.TimeBegin, 
    STO.TimeEnd, 
    STO.PayPeriodEnd, 
    STO.TodayHoursOff, 
    STO.LeaveCode 
from dbo.tblSeperateTimeOff STO
inner join dbo.tblLeaveRequest LR on STO.LeaveID=LR.ID
inner join dbo.tblLeaveApproval LA on STO.LeaveID = LA.LeaveID
inner join 
  (
    select max(LeaveID) maxleaveid , PayPeriodEnd
    from tblSeperateTimeOff
    group by PayPeriodEnd
  ) m on sto.leaveid = m.maxleaveid and sto.PayPeriodEnd = m.PayPeriodEnd
where 
    LA.ApprovalDepartment like'%Finance%' 
    and EmployeeName like '%polland%' 
    and LA.IsApprove=1 
    and LA.IsFinalApprove=1 
group by 
    LR.EmployeeName, 
    STO.DateOff, 
    STO.TimeBegin, 
    STO.TimeEnd, 
    STO.PayPeriodEnd, 
    STO.TodayHoursOff, 
    STO.LeaveCode 
order by EmployeeName

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Here is one way you could do this.

create table #Something
(
    LeaveID int
    , EmployeeName varchar(25)
    , DateOff date
    , TimeBegin time
    , TimeEnd time
    , PayPeriod date
    , Hours numeric(9,2)
    , LeaveCode varchar(50)
)

insert #Something
select 88, 'Polland, Sean', '2014-09-08', '08:30AM', '11:00AM', '2014-09-13', 2.5, 'P (Personal Leave Scheduled*)' union all
select 112, 'Polland, Sean', '2014-09-24', null, null, '2014-09-27', 8, 'P (Personal Leave Scheduled*)' union all
select 121, 'Polland, Sean', '2014-09-25', null, null, '2014-09-27', 8, 'P (Personal Leave Scheduled*)' union all
select 121, 'Polland, Sean', '2014-09-26', null, null, '2014-09-27', 8, 'P (Personal Leave Scheduled*)';

with SortedResults as
(
    select *
     , DENSE_RANK() over(partition by PayPeriod order by LeaveID desc) as GroupDepth
    from #Something
)

select *
from SortedResults
where GroupDepth = 1

drop table #Something

Upvotes: 1

tom.dietrich
tom.dietrich

Reputation: 8347

The solution is to run a subquery to select the max LeaveID for each PayPeriod and EmployeeName, then inner join against that to filter out the other leaves.

Upvotes: 0

Related Questions