Previous day max record

[Anonymous]

Hi All,

Find the below table and am looking for the solution

DW_LOAD_DATE,  NUM,COUNTER

7/8/2015 11:10:20 AM,1,1

7/8/2015 12:05:11 AM,1,1

7/9/2015 01:15:11 PM,2,3

7/9/2015 02:15:11 PM,2,3

7/10/2015 01:15:11 PM,2,3

From above DW_LAOD_DATE how to fetch only highligheted record.Basically the max time stamp on proevious day.

Kindly help on this

-Jay

[Gysbert_Wassenaar]

See attached example.

[swuehl]

=Timestamp(max({<DW_LOAD_DATE = {">=$(=daystart(today()-1))<=$(=dayend(today()-1))"}>} DW_LOAD_DATE))

[Anonymous]

Hi Wassenaar,

Thank you for reply. Actually I need to filter out that particular record in WHERE condition and it will be single record

in the application. Let me know if you need more info

All I want is previous day record

[Anonymous]

HI Swuhel,

   Thank you for your reply, I need to filter out yesterday's record in script level. I guess I was not clear while posting

-Jay

[PrashantSangle]

Hi,

try

where FLOOR(DW_LOAD_DATE)=FLOOR(TODAY()-1)

Regards

[swuehl · Accepted solution]

Maybe like

Set TimeStampFormat = 'M/D/YYYY hh:mm:ss TT';

INPUT:

LOAD * INLINE [

DW_LOAD_DATE,  NUM,COUNTER

7/8/2015 11:10:20 AM,1,1

7/8/2015 12:05:11 AM,1,1

7/9/2015 01:15:11 PM,2,3

7/9/2015 02:15:11 PM,2,3

7/10/2015 01:15:11 PM,2,3

];

RESULT:

FIRST (1)

NOCONCATENATE

LOAD * RESIDENT INPUT

WHERE DW_LOAD_DATE >= Daystart(Today(1)-1) and DW_LOAD_DATE <= Dayend(Today(1)-1)

ORDER BY DW_LOAD_DATE DESC;

DROP TABLE INPUT;

[Gysbert_Wassenaar]

Perhaps like this:

Temp:

LOAD Max(DW_LOAD_DATE) AS MAX

FROM ...source...

WHERE DW_LOAD_DATE < DayStart(Today());

LET vMax = peek('MAX', 'Temp');

DROP TABLE Temp;

Result:

LOAD * FROM ...source...

WHERE DW_LOAD_DATE = $(vMax);

[jagan]

Hi,

Try this script

Data:

LOAD

Date(Floor(Date#(DW_LOAD_DATE, 'M/D/YYYY hh:mm:ss TT'))) AS Date,

TimeStamp(Timestamp#(DW_LOAD_DATE, 'M/D/YYYY hh:mm:ss TT')) AS DW_LOAD_DATE,

NUM, COUNTER

INLINE [

DW_LOAD_DATE,  NUM,COUNTER

7/8/2015 11:10:20 AM,1,1

7/8/2015 12:05:11 AM,1,1

7/9/2015 01:15:11 PM,2,3

7/9/2015 02:15:11 PM,2,3

7/10/2015 01:15:11 PM,2,3];

INNER JOIN(Data)

LOAD

Date,

Timestamp(Max(DW_LOAD_DATE), 'M/D/YYYY hh:mm:ss TT') AS DW_LOAD_DATE

RESIDENT Data

WHERE Date = Date(Today() - 1)

Group By Date;

Regards,

Jagan.