Member Avatar for Ahmed_39

Hi all,

i hope that to find help and found how to do that

i have creat table in my database and i add days as bit and save the days that employee must work in week and i can get the count of work days at week for each employee

48c452f6f0d1ba712a4c5de8eb4a275f

i need that to get how many days must employee work in any month becouse i have many employee each one of them his work days in week not the same for all

some employee work (3 days in week) , other work (4 days in week), other work (6 days work) so i need to get how many days employss must work in any month (becouse some month has 30 days and other has 31 days) to set the monthly work target for each employee ?

  • 3 Contributors
  • 2 Replies
  • 222 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by ddanbe

Dani AI

Generated

A reliable way to solve this for every employee and any month is to expand the month into actual calendar dates, then match those dates against each employee’s weekly pattern (the bitmask you already have). This handles partial weeks automatically (as pointed out) and is straightforward to convert into either a single SQL query or a small Python routine (as suggested, use date/calendar functions). The key points: agree on a weekday-to-bit mapping (for example: Monday=bit0 … Sunday=bit6), generate every date in the month, test the weekday bit for each date, and count matches.

Example (MySQL 8+ using a recursive calendar CTE and a week-mask integer where Monday=0):

WITH RECURSIVE cal AS (
  SELECT DATE(:month_start) AS d
  UNION ALL
  SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM cal
   WHERE DATE_ADD(d, INTERVAL 1 DAY) <= LAST_DAY(:month_start)
)
SELECT e.employee_id,
       COUNT(*) AS workdays_in_month
FROM employees e
JOIN cal ON (e.week_mask & (1 << WEEKDAY(cal.d))) > 0
GROUP BY e.employee_id;

Or a compact Python routine using the same Monday=0 mapping:

import calendar
from datetime import date, timedelta

def count_workdays(mask, year, month):
    start = date(year, month, 1)
    end = date(year, month, calendar.monthrange(year, month)[1])
    cnt = 0
    d = start
    while d <= end:
        if (mask >> d.weekday()) & 1:
            cnt += 1
        d += timedelta(days=1)
    return cnt

Practical tips: 1) Keep the weekday-bit mapping consistent between DB and code and test with known cases (e.g., Mon/Wed/Fri -> mask = 1<<0 | 1<<2 | 1<<4 = 21). 2) Account separately for hire/termination dates, leaves, and company holidays by subtracting exception records after the base count. 3) For performance, maintain a permanent calendar table (indexed) or precompute monthly targets nightly rather than recalculating on every report. This approach covers the partial-week edge cases mentioned by and is easy to adapt to your existing bit-storage.

Recommended Answers

All 2 Replies

Member Avatar for Ancient Dragon

You also have to do that by the week because many months have two partial weeks. For example the first week in October 2013 has only 5 days instead of 7 and the last week also has only 5 days. So how will you count them if an employee works 6 days/week?

Which days may also be important. If the employee works Monday, Wed, and Friday, he will work only 2 days during the first week of Oct 2013, not 3 days. An employee who works Tue, Wed and Thursday will work all 3 days during the same week.

Edited by Ancient Dragon
Member Avatar for ddanbe

The DateTime structure should provide you with all the methods you need to accomplish this task.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.