Hello! There is a table IdStudenta/IdSpravki/Nachalo/Konec/Primechanie . How can I calculate the number of days with the student's certificates, knowing the beginning and end of the certificate, while excluding weekends, or even better, for example, create a table "holidays" and enter data like 8.03.2012 = March 8, etc. and that these dates were excluded too. In general, I want to get an answer like:

 idstudent/idspravki/Count 1/1/ 20

(for example, 1st student on 1st help 20 days pass).

And the option is more complicated: If there is an intersection of certificates, for example, one certificate 1.1.2012-20.1.2012 - note-Bronchitis, and another 12.1.2012-28.1.2012 note - participation in the conference and must be displayed in another form, for example

 idstudenta/idspravki/date/primechanie 1/ 1/ 1.1.2012/Бронхит 1/ 1/ 2.1.2012/Бронхит ..... 1/ 1/ 12.1.2012/Бронхит 1/ 2/ 12.1.2012/УЧАСТИЕ В КОНФЕРЕНЦИИ ...

How to make a request to get such an answer?

  • @Roman Rakzin, According to the rules of the forum, questions should not be limited to solving or completing study assignments for students. Please clarify what you have done yourself and what did not work out. - Arseniy

1 answer 1

  1. Query is simple; see the datediff help (which sql you didn’t specify, look for an analogue, if it is not mysql), then deduct the number of holidays for the period from the difference. see example SQL Fiddle
  2. Here it is more difficult, more precisely in any way. To get such a result with a pure query, you will have to create a table with days, for each day, by appointment, this is a bad idea. Better to do it in code. cycle. You receive all references for an interval, run on dates and check whether it is suitable or not. well there it is simple, your logic, you will understand.