Select only the most recent values

[#1]

I'm not sure how to select the most recent value for presentation.

I have a field called [ChangeDate] and a load script that looks like this.

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;

SQL SELECT ChangeKeyValue,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

All I want is the most recent [ChangeNewValue] if they read "Quarantined - DSC".

[Anonymous]

You could try.

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident YourTable

group by ITMSNumber

;

[#3]

can you write it exactly how it should go in there or at least elaborate a bit?

[Anonymous]

Essentially create a summary table that has just the max changed date you're looking for.

So after your code:

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;

SQL SELECT ChangeKeyValue,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

You would add:

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident Table6

group by ITMSNumber

;

This would give you a summary table with max changed date for each ITMSNumber. It would look something like this.

ITMSNumber	ChangeDateMax
1A	01/01/2016
2B	06/15/16
4C	12/12/16

[#5]

It's coming back with "ChangeDate" not found...

[Anonymous]

Sorry you would need to change your script to have it included like so :

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

ChangeDate,

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff')))) as DaysPassed;

SQL SELECT ChangeKeyValue,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

[#7]

Ok it worked. Now how can I put a count of all of the most recent "Quarantined - DSC" that I have into a text box?

[Anonymous]

=count(ChangeDateMax) is one way.

[#9]

That is way off. It says 3763 when there is only 37 Quarantined - DSC in the data.

[Anonymous]

I keep making erroneous assumptions.  I apologize.  You're pulling all ChangeNewValues into your summary table.

Add in the ChangeNewValue into your table6 and then filter to quarantined with summary table generation.

Left keep

Table6:

Load

ChangeKeyValue as ITMSNumber,

ChangeDate,

ChangeNewValue,

IF(ChangeNewValue='Quarantined - DSC', date(Today()) - Floor(date(TIMESTAMP(ChangeDate, 'YYYY-MM-DD hh.mm.ss.fff'))));

SQL SELECT ChangeKeyValue,

ChangeNewValue,

ChangeDate

FROM MPL.dbo."tblCHANGE";

SummaryTable:

Load

ITMSNumber,

max([ChangeDate] as ChangeDateMax

resident Table6

group by ITMSNumber

where ChangeNewValue='Quarantined - DSC'

;

then your formula for your text box would be =count(ChangeDateMax)