Add next days data before 5 AM under previous day's day

[haneeshmarella]

Hi everyone,

I have the below fields. 

DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY') as MOD3_DATE,
INTERVAL(TIME#(NUM(MOD3_TIME,'000000'),'hhmmss'),'hh:mm:ss') as MOD3_TIME,
TIMESTAMP(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY') + INTERVAL(TIME#(NUM(MOD3_TIME,'000000'),'hhmmss'),'hh:mm:ss')) as MOD3_TIMESTAMP,
WEEK(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as MOD3_WEEK,
WEEKDAY(DATE(DATE#(MOD3_DATE,'YYYYMMDD'),'MM/DD/YYYY')) as MOD3_WEEKDAY

I need to add the data under previous day's day if it is before 5 AM the next day. For example, if MOD3_WEEKDAY is Thursday and has data from 12 AM to 5 AM, it should show up under Wednesday on filtering. 

Any help is highly appreciated. 🙂 

[sunny_talwar · Accepted solution]

May be try this

If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY')) as MOD3_DATE,

WeekDay(
  If(Hour(Time#(Num(MOD3_TIME, '000000'), 'hhmmss')) < 5,
  Date(Date#(MOD3_DATE, 'YYYYMMDD') - 1, 'MM/DD/YYYY'),
  Date(Date#(MOD3_DATE, 'YYYYMMDD'), 'MM/DD/YYYY'))
) as MOD3_WEEKDAY

[haneeshmarella]

Thanks a lot Sunny!