Populating Values Based on Max and Min values surrounding it

Database
Enthusiast

Populating Values Based on Max and Min values surrounding it

I'll be honest, I wasn't even sure how to title this, but my problem is this, and I'll give a quick background for context. I'm looking at records of phone calls, and trying to count the # of calls a customer makes while in a particular state. If a customer makes a call on the mobile network (Tech = 'cell'), then the state populates. If they make the call over a Wifi network (Tech = 'wifi'), the state does not populate. However, if a call is made on Feb 2nd through the cell network at 2PM, then 3 calls are made between 2 and 3 PM on wifi, and another call is made on the cell network at 3:30 pm, it's reasonable to assume that all 5 calls were made in the same state as the 1st and 5th call indicated.

 

Here's the data I have (not actual customer data of course), and here's what I'm trying to get to based on the date and time of the call (? represents a null value). I'm playing around with max/min type expressions, but can't figure it out. I realize this would require some business rules, but any help on the logic would be appreciated so I can tweak as appropriate. 

 

Current Result

Phone_No, Call_Date_Time, Tech, State

4255551212, 2018-02-02 00:15:08, Cell, WA

4255551212, 2018-02-02 00:15:15, Wifi, ?

4255551212, 2018-02-02 00:15:20, Wifi, ?

4255551212, 2018-02-02 00:15:25, Wifi, ?

4255551212, 2018-02-02 00:15:40, Cell, WA

 

Expected Result

Phone_No, Call_Date_Time, Tech, State

4255551212, 2018-02-02 00:15:08, Cell, WA

4255551212, 2018-02-02 00:15:15, Wifi, WA

4255551212, 2018-02-02 00:15:20, Wifi, WA

4255551212, 2018-02-02 00:15:25, Wifi, WA

4255551212, 2018-02-02 00:15:40, Cell, WA

 

 

 


Accepted Solutions
Junior Contributor

Re: Populating Values Based on Max and Min values surrounding it

Seems like you want to assign the latest State to the following rows with NULLs.

This is very simple with one of those nice OLAP functions:

 

 

LAST_VALUE(State IGNORE NULLS)
OVER (PARTITION BY Phone_No
      ORDER BY Call_Date_Time
      ROWS UNBOUNDED PRECEDING)

Of course yo still have to decide what happens when the next cell call is not from WA, FIRST_VALUE can be used to find the next state...

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: Populating Values Based on Max and Min values surrounding it

Seems like you want to assign the latest State to the following rows with NULLs.

This is very simple with one of those nice OLAP functions:

 

 

LAST_VALUE(State IGNORE NULLS)
OVER (PARTITION BY Phone_No
      ORDER BY Call_Date_Time
      ROWS UNBOUNDED PRECEDING)

Of course yo still have to decide what happens when the next cell call is not from WA, FIRST_VALUE can be used to find the next state...

Enthusiast

Re: Populating Values Based on Max and Min values surrounding it

Thank you! I'm not really sure how to apply this script. How would I add this in to the select statement so that it updates the cell_state field (or even create it's own column for "Revised_State").

 

 


@dnoethwrote:

Seems like you want to assign the latest State to the following rows with NULLs.

This is very simple with one of those nice OLAP functions:

 

 

LAST_VALUE(State IGNORE NULLS)
OVER (PARTITION BY Phone_No
      ORDER BY Call_Date_Time
      ROWS UNBOUNDED PRECEDING)

Of course yo still have to decide what happens when the next cell call is not from WA, FIRST_VALUE can be used to find the next state...


 

Junior Contributor

Re: Populating Values Based on Max and Min values surrounding it

You simply add this to your Select as a new column :-)

Highlighted
Enthusiast

Re: Populating Values Based on Max and Min values surrounding it

Awesome, thank you sir!