0
|----|------------|--------------|----------|
| id |  fromdate  |    todate    | priority |
|----|------------|--------------|----------|
| 1  | 2016-01-01 |  2016-01-30  |  1       |
| 2  | 2016-01-05 |  2016-01-20  |  2       |
| 3  | 2016-01-11 |  2016-01-12  |  3       |
|----|------------|--------------|----------|

This is a sample of input in my table "test". this table shows date assigned to particular user (only one user shown here).

I need to make a schedule from this as below.

|------|--------------|--------------|-----------|
|  id  |    fromdate  |     todate   |  priority |
|------|--------------|--------------|-----------|
| 1    |  2016-01-01  |  2016-01-05  |  1        |
| 2    |  2016-01-05  |  2016-01-11  |  2        |
| 3    |  2016-01-11  |  2016-01-12  |  3        |
| 4    |  2016-01-12  |  2016-01-20  |  2        |
| 5    |  2016-01-20  |  2016-01-30  |  1        |
|------|--------------|--------------|-----------|

Users work will be scheduled according to the priority and date. If on date 16-01-2016 he will be coming under priority 2.

so I want to get a list of all the possible date entries for the person with the priority.

Priority is when a user has 2 entries in same date the higher one should be used.

In the given example 1- 30 it is in priority 1 after that on 5th the priority is set to 2 and on 11-12 the priority is set to 3 so,after 12th the priority should be set back to 2 and on 20th the priority is back again to 1 in simple words. i need entries for an entire month...

Niyas Niya
  • 155
  • 10
  • mysql or sql-server? – Dylan Su Mar 28 '16 at 11:42
  • 1
    Also take a look at this answer of mine, it already do what you need: http://stackoverflow.com/a/34140783/460557 apart of the priority column – Jorge Campos Mar 28 '16 at 12:05
  • So you want to split up the date-ranges into single days and find out the `max(priority)` for each particular day, right? – PerlDuck Mar 28 '16 at 12:09
  • not for a date but for particular date range. – Niyas Niya Mar 28 '16 at 12:11
  • 1
    This is quite painful in MySQL. It is easier in other databases. – Gordon Linoff Mar 28 '16 at 12:21
  • Sorry didn't get it. How is the priority set to a date range and still get duplicated? – Jorge Campos Mar 28 '16 at 12:32
  • in the given example 1- 30 it is in priority 1 after that on 5th the priority is set to 2 and on 11-12 the priority is set to 3 so,after 12th the priority should be set back to 2 and on 20th the priority is back again to 1 in simple words. i need entries for an entire month – Niyas Niya Mar 28 '16 at 12:41
  • Yeah but it is the same for 05 on the result. It is 1- 05 1; 5- 30 1 – Jorge Campos Mar 28 '16 at 12:46
  • sorry...dint get that one. or leave the priority. can u write a query which can generate as the result as i'v given – Niyas Niya Mar 28 '16 at 12:48
  • I stick with Gordon here, it will be a lot painful to achieve that. I would add an answer but even with your explanation I can't see a logical solution to this 'set back' priority. I can't see a way to do that priority ordering based on your sample data. – Jorge Campos Mar 28 '16 at 12:49
  • okay.thnak you.@jorge campos,@gordon – Niyas Niya Mar 28 '16 at 12:51

0 Answers0