CODEWITHSUNDEEP
sqloracle-databasewindow-functions
John
John

Reputation: 299

Window function filtering

It it possible to filter with window functions?

I have the below table

ID    AssignedDate    StartDate    EndDate   userid   Role
1     1/1/17            1/1/18      2/1/18     rdsd    Red
1     1/15/17           1/1/18      2/1/18     adfd    Red
* 1     12/15/17          1/1/18      2/1/18     dfge    Red

I would like to see one row per ID and a new column called FirstAssgnDate

First - take all assigned Date that happened before the startDate then take the MAX assignedDate when role = REd

Output should be:

Table

ID    FirstAssgnDate    StartDate    EndDate   userid   Role
1     12/15/17            1/1/18      2/1/18    dfge    Red

*This row should bre returned.
I was thinking something like below?

  Select *
  max(assignedDate) Keep(when AssignedDate < StartDate) As FirstAssgnDate
  from tablea

Upvotes: 0

Views: 124

Answers (1)

Nikhil Shetkar
Nikhil Shetkar

Reputation: 346

I dont think so we need to use window function simple aggregate function would work , if you only need to check those records with role as Red and assigneddate < startdate (atleast this is what I understood from your question) we can have these in where clause filters as below :

    select id,max(assigneddate),startdate,enddate,userid,role
    from tablea
    where asssigneddate < startdate and 
    role = 'Red'
    group by id,startdate,enddate,userid,role;

Upvotes: 1

Related Questions