Auto Calculate Total DAYS for leave, help please! 1

[RaffiqEddy]

Dear MySQL & PHP Expert...

I want to auto calculate DAYS for leave taken based on date key-in by user:

For example:

Start date: 15-04-2004 -- (Friday)
End date: 18-04-2004 -- (Monday)
Days: 3 -- (calculate automatically)

From the date given above, system will automatically:-
1) Count days as 3 -- assuming working day is 15, 16 and 18 only.
2) Sunday will not be count as working day.

Is this possible with MySQL(3.32) + PHP ??

TIA,

Regards.

 

[TonyGroves]

If it doesn't involve database access, then it's hardly a MySQL issue. It would execute faster using PHP. However, if you really wanted to do it in SQL, you could use:
[tt]
SELECT
@from:='2004-04-15' fromdate,
@to:='2004-04-18' todate,
@fromday:=TO_DAYS(@from),
@today:=TO_DAYS(@to),
@fromweekday:=DAYOFWEEK(@from),
@totaldays:=@today-@fromday+1,
@weeks:=CAST(@totaldays/7 AS UNSIGNED),
@sun:=
(
@fromweekday=1
OR @fromweekday+(@today-@fromday+1)%7>8
),
@totaldays-@weeks-@sun workingdays
[/tt]
which could be condensed to:
[tt]
SELECT
(@today:=TO_DAYS(@to:='2004-04-18'))
-(@fromday:=TO_DAYS(@from:='2004-04-15'))
+1
-CAST((@today-@fromday+1)/7 AS UNSIGNED)
-
(
DAYOFWEEK(@from)=1
OR DAYOFWEEK(@from)+(@today-@fromday+1)%7>8
) workingdays
[/tt]
This seems to work in MySQL 4.1, but I don't know about 3.23.

 

[RaffiqEddy]

Hi TonyGroves,

Thank you very much, as u suggested, I think I should use PHP code instead.

BTW, FYI, the code produced an error when used with MySQL 3.23.

An additional knowledge to me!

Regards.

Ps: A star for you too..