Search code examples
sqlteradatateradata-sql-assistant

Replicate values across a grouping set with multiple groups within the grouping set by date

Below is the sample source data:

enter image description here

The expected output is as follows:

enter image description here

The data is basically grouped by A_ID,B_ID,C_ID and then within that for each time GR_CNT is NULL the corresponding values for GR_MATERIAL and GR_SERIAL is replicated for the above rows. LAST_DT and DATE_TIME is sorted in ascending order.
Below is the query I tried , but I'm not sure how to take the values of GR_MATERIAL and GR_SERIAL which lie midway in the data.

sel a.*,coalesce(max(GR_MATERIAL) over (partition by A_ID, B_ID, C_ID order by LAST_DT,DATE_TIME), -1) as col2,<br>
coalesce(max(GR_SERIAL) over (partition by A_ID, B_ID, C_ID order by LAST_DT,DATE_TIME), -1) as col3<br>
from table a<br>

Creating two new columns for GR_MATERIAL and GR_SERIAL with the required values will also do.

I am using TD16

Solution

  • Without ROWS your current query returns the Max over all rows, i.e. a Group Max.

    You probably want

    LAST_VALUE(GR_MATERIAL IGNORE NULLS) 
over (partition by A_ID, B_ID, C_ID 
      order by LAST_DT DESC, DATE_TIME DESC)

    This replaces Nulls with the previous value.