Count of 3 Consecutive Days

[sjhussain]

Hello

Hope all is well with everyone.

We are working on requirement to find out 3 consecutive days for a non moving vehicle.

Our table is as follows:

Vehicle No., Date, Moving

111, 01/01/2015, Yes

112, 01/01/2015, No

113, 01/01/2015, Yes

111, 01/02/2015, Yes

112, 01/02/2015, No

113, 01/02/2015, Yes

111, 01/03/2015, Yes

112, 01/03/2015, No

113, 01/03/2015, Yes

Vehicle 112 did not travel on 1st, 2nd and 3rd so the count will be 1 in this case.  We disregard the other vehicles as they moved.

We have to keep in account the working days.  The working days from Sun, Mon, Tue, Wed, Thu.

So if a car did not move on Sun, Mon, Tue then it would be considered as Count 1

Also taking the weekends away - not moving in Wed, Thu, Sun would be considered as Count 1

Thanks.

[sunny_talwar]

You want this to be done at the script level or the front end of the application?

[Anonymous]

Dear Sunny,

We want to show the count report by the vehicle number to the customer.  So either load script or set analysis should be fine.  As long as we are able to show it.

We think script should be easier?

Thanks.

[sjhussain]

Dear Sunny,

We would like to show the result in form of table.  So as long as we get the correct result it can be either load script or set analysis.  Does not really matter.

But i think Load script would be much easier?

Thanks.

[sunny_talwar]

That make sense, but I think your sample dates have weekend dates in it as 2nd and 3rd January 2015 were Friday and Saturday. The reason I ask is because I want to make sure that I am reading it correctly

[sjhussain]

Dear Sunny,

Sorry for the sample data - it was just meant to be sample - I did not realize it was fri / sat.

Nevertheless I think you understood what we are looking for

Awaiting your response.

Thanks.

[sunny_talwar]

Tried to add some more data, but was not sure if this is how your data is;

Vehicle No., Date, Moving

111, 01/01/2015, Yes

112, 01/01/2015, No

113, 01/01/2015, Yes

111, 01/02/2015, Yes

112, 01/02/2015, No

113, 01/02/2015, Yes

111, 01/03/2015, Yes

112, 01/03/2015, No

113, 01/03/2015, Yes

112, 01/04/2015, No

112, 01/05/2015, No

112, 01/06/2015, No

So if the most recent three days, the vehicle hasn't moved then it is Not Moved for Moving Flag.

[maxgro]

PFA