need to get the count of records based on sysdate-lastactivitydate>4 hours

[sasqliksense]

Hi All,

i have records in my qvd as follows

id                     activitydate

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

1                    12-jun-2016 14:20:30

2                    05-sep-2017 10:00:03

3                    05-sep-2017 11:00:03

4                    04-sep-2017 10:00:03

5                    03-sep-2017 10:00:03

6                    03-sep-2017 10:00:03

7                    02-sep-2017 10:00:03

Now i need meassure which gives the count of records based on condition (sysdate-activitydate> 4 hours).

i tried to create like this but didn't work..

count({<[interval(activitydate-now(),'HH')={'>4'}]>}  id).

please help any one on this scenario

[its_anandrjs · Accepted solution]

Try this

Count( if( Interval( activitydate-now(),'hh:mm' ) > 4  ,id))

[sasqliksense]

Thanks Anand.

If i want to add one more condition in count.like i need to add status=2 and sysdate-activytydate>4hours.

How can i use?

[sasqliksense]

Thanks Anand,

I can add multiple conditions..

[Anonymous]

Count( if(status=2 and ((Interval( activitydate-now(),'hh:mm' )) > 4  ,id))

if you want to count distinct records, use

Count (Distinct( if(status=2 and ((Interval( activitydate-now(),'hh:mm' )) > 4  ,id)))

[its_anandrjs]

Yes sure this way

Count( if( Interval( activitydate-now(),'hh:mm' ) > 4  and status=2 ,id))

Or

Count( if( status=2 and Interval( activitydate-now(),'hh:mm' ) > 4  ,id))