How to get the first value in a date range

Wed, 28 Aug 2013 19:51:22 GMT

Hi,

I have the following table:

ID date State Index

1 01/01/2011 3 1

1 01/01/2011 4 2

2 01/01/2011 1 1

2 02/01/2011 2 2

1 02/01/2011 2 3

1 02/01/2011 4 4

2 02/01/2011 4 3

3 02/01/2011 4 1

I want to count the IDs which receive state 4 for FIRST time within a date, eg for 02/01/2011 the Count would be 2:

ID date State

1 02/01/2011 4 <--- not first date in which ID 1 has state 4

2 02/01/2011 4

3 02/01/2011 4

Index is the order inwhich the id changes from state

I have something like this, which is not working as expected

Count

(

{$<

StateID={4},

ContactID={"=min({$<State={4} Index) = min({$<date=varCurrentDate ,State={4} Index)"}

)

Any help is appreciated, thanks!

Re: How to get the first value in a date range

markodonovan — Thu, 29 Aug 2013 11:09:57 GMT

Hi Paola,

Is this what you are after:

Thanks

Mark

Fri, 30 Aug 2013 21:22:31 GMT

Thanks Mark!