5

I have assignment on my hand of building an Attendance system which I am doing all right but I am stuck at one place. When counting the leaves, if a person takes a leave on Friday and then take leave again on Monday, then the in between Saturday and Sunday should count as leaves too.

I have been able to extract only the Fridays and Mondays from my table by following query:

SELECT * FROM  `main` WHERE (DAYOFWEEK( DATE ) =2 OR DAYOFWEEK( DATE ) =6 ) 
AND emp_no =4 AND STATUS ='leave' ORDER BY DATE ASC

but I don't know how to select the Friday and the next Monday only so I know that the person was on leave on Friday as well as Monday.

Any help would be appreciated.

awatan
  • 1,182
  • 15
  • 33

1 Answers1

5

This will give you all "leaves" which span a Friday and the following Monday:

SELECT *
FROM   main fri JOIN main mon
    ON fri.DAYOFWEEK(DATE)=6
   AND mon.DATE = fri.DATE + INTERVAL 3 DAY
   AND fri.emp_no = mon.emp_no
WHERE  fri.STATUS='leave' AND mon.STATUS='leave'
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • but It only gives the records that are Fridays. – awatan Oct 15 '12 at 11:18
  • 1
    @JewelThief: It shouldn't do... however the column names collide, so your client may not be able to access the Monday columns. You can alias them e.g. with `SELECT fri.emp_no, fri.DATE AS friday, mon.DATE AS monday FROM ...` – eggyal Oct 15 '12 at 11:21
  • Thanks @eggyal. You really solved my problem. You query is correct. I was doing a mistake in running it. – awatan Oct 15 '12 at 11:30