Android Question hi guys, i need very smart brains to find a solution for my problem...

[ilan]

hi

i need some help

i cannot find a good way without lots of loops to do my code

i have a table view in my table view i have maybe 200 entries (work shifts)

the first column is a DATE
the second is a TIME (shift entry)
the third entries is a time (shift exit)

1. first thing to search is: i would like to know if there are same DATES in my table for a special month

2. all those same DATES entries should be checked if the time has a conflict.

example:

want to search all entries for september:

if found 8 entries in my tableview:

10/09/2013
11/09/2013
11/09/2013
11/09/2013
15/09/2013
18/09/2013
18/09/2013
21/09/2013

so the dates 11/09/2013 is 3 times in my table, date 18/09/2013 two times all other only one

now i want to check for all 3 entries of 11/09/2013 if there is a conflict between the times

example:

date time in time out
1. 11/09/2013, 08:00, 11:00
2. 11/09/2013, 12:00, 14:00
3. 11/09/2013, 16:00, 21:00

in this example there is no conflict between the time

another example:

1. 18/09/2013, 08:00, 16:00
2. 18/09/2013, 10:00, 17:00

here is a conflict because its cannot be that i worked between 8:00 - 16:00 and also between 10:00 to 17:00 in the same day

if the second entry would be:

1. 18/09/2013, 08:00, 16:00
2. 18/09/2013, 16:30, 18:00

then there would be conflict because i worked a shift from 8:00 to 16:00 and had 30min break and work another shift from 16:30 to 18:00

how could i check this parameters?
i know its not easy, would be great if someone could give me a solution dont need a code only solution how to check it

thank you very much

 

[barx]

Is this info stored in a database?

If so the first step would be to get just the rows that have more than 1 entry. so you SELECT statement would be something like

SELECT * FROM table GROUP BY Date HAVING COUNT(Date) > 1

For step 2 (detecting conflict times)

Without getting too complicated, do for each row in the above statement do another query on the database selecting rows with just that date and ordered by start time. Then in your code do a loop checking if the start time of the next row is < the previous row finish time. If so then a conflict.

Non of this is tested, it's just an idea to get you started

 

[mc73]

Another way would be to clone the original shifts' table, then join and perform the desired comparisons.
Example:

drop table  if exists tempshifts;
create table tempshifts as (select * from shifts);
select * from shifts join tempshifts on tempshifts.shiftdate=shifts.shiftdate where (shifts.shiftentry>tempshifts.shiftentry and shifts.shiftentry<tempshifts.shiftexit) or (shifts.shiftexit>tempshifts.shiftentry and shifts.shiftexit<tempshifts.shiftexit)

 

[ilan]

Another way would be to clone the original shifts' table, then join and perform the desired comparisons.
Example:

sorry i didnot understood your example

 

[ilan]

i solve the problem in a nother way

when i add a new item or edit one i do the checking, in this way i am saving a few loops

    'check if same date exist to do new sorting
   
    Dim starttimechk As String
    Dim endtimechk As String
    Dim starttimechk2 As String
    Dim endtimechk2 As String

    nvlb19.Text = "0"
   
    For i = 0 To Table1.Size - 1
   
      Table1.SetValue(19,i,"0")
     
          If Table1.GetValue(4,i) = lb4.Text AND Table1.GetValue(18,i) > selecteddateday Then
              tabsort = True
              starttimechk = Table1.GetValue(1,i)
              starttimechk = (starttimechk.SubString2(0,2) * 60) + starttimechk.SubString2(3,5)
             
              starttimechk2 = Table1.GetValue(1,selecteddateday)
              starttimechk2 = (starttimechk2.SubString2(0,2) * 60) + starttimechk2.SubString2(3,5)
             
              endtimechk =  Table1.GetValue(2,selecteddateday)
              endtimechk = (endtimechk.SubString2(0,2) * 60) + endtimechk.SubString2(3,5)
             
              endtimechk2 =  Table1.GetValue(2,i)
              endtimechk2 = (endtimechk2.SubString2(0,2) * 60) + endtimechk2.SubString2(3,5)

              If endtimechk <> "--:--" AND endtimechk2 <> "--:--" Then
                  If endtimechk < starttimechk2 Then
'                    If starttimechk  <> endtimechk Then
                      Table1.SetValue(19,i,"1")
                      nvlb19.Text = "1"
'                      End If
                  Else
                      If endtimechk  > starttimechk Then
                        Table1.SetValue(19,i,"1")
                        nvlb19.Text = "1"
                      End If
                  End If
            End If     
             
            Else If  Table1.GetValue(4,i) = lb4.Text AND Table1.GetValue(18,i) < selecteddateday Then 
           
                tabsort = True
                  starttimechk = Table1.GetValue(1,selecteddateday)
                  starttimechk = (starttimechk.SubString2(0,2) * 60) + starttimechk.SubString2(3,5)
                 
                  starttimechk2 = Table1.GetValue(1,i)
                  starttimechk2 = (starttimechk2.SubString2(0,2) * 60) + starttimechk2.SubString2(3,5)
                 
                  endtimechk =  Table1.GetValue(2,i)
                  endtimechk = (endtimechk.SubString2(0,2) * 60) + endtimechk.SubString2(3,5)
                 
                  endtimechk2 =  Table1.GetValue(2,selecteddateday)
                  endtimechk2 = (endtimechk2.SubString2(0,2) * 60) + endtimechk2.SubString2(3,5)

                  If endtimechk2 <> "--:--" AND endtimechk <> "--:--" Then
                      If endtimechk < starttimechk2  Then
'                        If  endtimechk <> starttimechk Then
                          Table1.SetValue(19,i,"1")
                          nvlb19.Text = "1"
'                        End If
                      Else
                          If endtimechk  > starttimechk Then
                          Table1.SetValue(19,i,"1")
                          nvlb19.Text = "1"
                          End If
                      End If
                  End If
          End If
    Next
   
    'end checking