need help in getting the equivalent month of week

[newphpcoder]

Hi..

 

I have 2 tables for week and calendar days.

 

first I have so_week

fields:

from_week

to_week

 

second is calendar_days

fields:

month_name

working_days

 

sample data:

from_week : 33

to_week: 36

 

month_name:

Jan

Feb

Mar

Apr

May

and so on

 

working_days:

23

22

25

25

23

 

now I need to get the working days based on from_week to week

 

for example from 33 to 36 the month is aug, sep, sep, sep i need to get the working days for that month.

 

any help is highly appreciated..

 

Thank you so much

[Barand]

You are making life hard for yourself holding working days by month and working with weeks that not align to those months.

 

If I were you I'd create a table of holiday dates eg

 

+------------------+-----------------------+

|  Date            |  Holiday            |

+------------------+-----------------------+

|  2012-01-01      |  New Year            |

|  2012-04-06      |  Good Friday        |

|  2012-04-09      |  Easter Monday      |

|  2012-05-07      |  Mayday Holiday      |

|  2012-05-08      |  Jubilee Holiday    |

|  2012-08-27      |  Late Summer Holiday |

|  2012-12-25      |  Christmas Day      |

|  2012-12-26      |  Boxing Day          |

+------------------+-----------------------+

 

Then

 

SELECT w.from_week, w.to_week,
  (w.to_week  - w.from_week + 1) * 5 -
  (SELECT COUNT(*) FROM holiday
   WHERE WEEK(hol_date) BETWEEN w.from_week AND w.to_week) 
   as workdays
FROM so_week w;

 

Which gives this with my dates

 

+-----------+---------+----------+

| from_week | to_week | workdays |

+-----------+---------+----------+

|        17 |      20 |      18 |

|        33 |      36 |      19 |

+-----------+---------+----------+