How to write code to get 90 days data with max of each date dynamically

[abc_18]

Hi All,

I have a date column in the table that also contains timestamp values, and that table's jobs run four times each day at the ETL end. As a result, the historical table has many partitions, which might potentially increase the size of the qvd.

Need help with writing code at the extract layer to retrieve data for 90 days with a maximum of each date is needed (max value from out of 4 partition in a day ).

Please suggest.

[Taoufiq_Zarra]

@abc_18  can you share a sample data and the expected output ?

[abc_18]

Hello, Taoufiq

Here is a sample table; you can see that the "orderdate" column contains date values with various timestamps for the same day. Data provided in the table go back more than a year.

Now my requirement is to get data just for 90 days with a maximum of each date, need help in writing code at the extract layer.

For instance, the maximum value for the four partitions as of December 28, 2022, will be

2022-12-28T11:45:03.501+0000

id	pollingstatus	POdate	thresholdvalue	status	orderdate
325375	null	2012-09-04T19:10:00.000+0000	No	Disabled	2022-12-28T11:45:03.501+0000
231567	null	2014-08-19T18:25:00.000+0000	No	Disabled	2022-12-28T11:35:03.501+0000
567890	null	2013-02-17T10:35:00.000+0000	No	Enabled	2022-12-28T11:25:03.501+0000
325375	null	null	No	Enabled	2022-12-28T11:20:03.501+0000
234567	null	null	No	Enabled	2022-12-27T11:45:03.501+0000
325372	null	null	No	Enabled	2022-12-27T11:40:03.501+0000
325370	null	null	No	Enabled	2022-12-27T11:35:03.501+0000

[Luis_Galvan]

You could use a resident table with two/three columns: Date (date value from orderdate), orderdate, id (id field if you require to map the latest id key).

In the script you would do something like this

Maxtms:
Load
    Date(Date#(Left(orderdate,10),'YYYY-MM-DD')) 	as Date,
    Max(orderdate) 			as orderdate_recent_tms,
	Max(id)				as id_recent
resident [original table name]
Group by Date(Date#(Left(orderdate,10),'YYYY-MM-DD'));

... this will, deliberately, give you the latest TMS (timestamp) of each date, and its id value... now if you need the latest 90 days, you can apply where statement after resident and before group by

ex:

Where Date(Date#(Left(orderdate,10),'YYYY-MM-DD')) >= Today()-90

Hope this helps you with your challenge or gives you hints to have it resolved!

Cheers!

Luis G