Value at max and minimum date of every month

[harleen_singh]

Hello

  i have a fields like this

OBject no ,  Date,  Value

I just want fetch "Value" at maximum and minimum date of every month in the back end

what script should i write?

thanks

lavi

[#2 · Accepted solution]

On the basis of max date

**********************************

load  OBject no,

        Date(max(Date_new))  as Date1,

     FirstsortedValue(Value,-Date_new)  as  Value_new

group by OBject no;

load    OBject no,

           Date as Date_new,

          Value

From table1;

*******************************************

On the basis of Min date

------------------------------------

load  OBject no,

        Date(min(Date_min))  as Date2,

     FirstsortedValue(Value,-Date_min)  as  Value_new2

group by OBject no;

load    OBject no,

           Date as Date_min,

          Value

From table1;

[harleen_singh]

Vishwaranjan,

                    your answer is correct except this thing. You should not use - sign  for minimum date results

load  OBject no,

        Date(min(Date_min))  as Date2,

     FirstsortedValue(Value,Date_min)  as  Value_new2

group by OBject no;

thanks

Lavi

[er_mohit]

see the attached file\

hope it helps

[#5]

yes this is my mistake, sorry.

[harleen_singh]

Vishwaranjan,

                     What if i have more than 1 value at maximum date?

for example if i have data like this

Object no,      Date,           Value

a,           07-04-2013,           7

    f,           07-04-2013,           8

    g,           07-04-2013,           9

     i,           08-04-2013,             7

    j,           08-04-2013,              8

    k,           08-04-2013,               9

in this case i want fetch all values(7,8,9) at maximum date 08 04 2013. So the result would be the last three records in above example.

how would i achieve this?