holiday - date logic/question

[Szabi Zsoldos]

Hi guys,

I'm working on a holiday scheduling app and I was wondering that if a person wants his holiday for example 10 days.

id  uid data1       data2       ore tip_concediu    ts
7   244 2013-01-25  2013-02-05  3   2               2013-01-25 16:08:35

How would it be possible that I could track the holiday for each month ?

January - 6 days
February - 5 days

In this case, January has 31 days, I would like to group the days into months, in January I should have 6 days and the remaining in February, is this possible from a query?

Thanks in advance
Szabi.

[adam_k]

On the top of my head (and with MS SQL in mind) it is possible, but a) it won't be nice and b) I don't know how much use it'll be to anybody.

If you create a set of days from beginning until end of period, you can count(*) group by month :

select month(date),count(*) 
from (
    select DATEADD(dd, num , @start_date ) as date 
(select a.id + b.id as num from 
(select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) a full join 
(select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90 ) b )
) 
where date < @end_date

Please note a couple things on the above query:
1) I'm using a series of numbers from 0 to 99 to calculate the dates in the period. So this query is limited to 99 days between start date and end date. If you need more days you need to add a "table" like a and b with values 0 to 900.
2) I prefer calculating the dates over using a calendar table, as this method is maintenance free and will work for any given period - if SQL supports the date.
3) I haven't tested it, so it might contain errors.

[Szabi Zsoldos]

Hi adam_k, thank you for your feedback, but sincerly, I cannot figure it out where to add my table name :)

[adam_k]

I've checked the above and it did contain errors, so below is the corrected script (but I haven't tested this one either):

select month(date),count(*) 
from (
    select DATEADD(dd, num , @start_date ) as date from 
(select a.id + b.id as num from 
(select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0) a cross join 
(select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 union select 60 union select 70 union select 80 union select 90 ) b ) c
) d
where date < @end_date
group by month(date)

What you do with it is:

select month(date),count(*) 
from (
    select case when DATEADD(dd, num , data1 ) <= data2 
    then DATEADD(dd, num , data1 )
    else null end as date
    from tablename 
cross join 
(select a.id + b.id as num from (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 
union select 7 union select 8 union select 9 union select 0) a 
cross join (select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 
union select 60 union select 70 union select 80 union select 90 ) b 
    where case when DATEADD(dd, num , data1 ) <= data2 
    then DATEADD(dd, num , data1 )
    else null end is not null
) c 
group by month(date)

What this is supposed to do is: get date from data1, add to that 99 days and compare it to data2. If the comparison shows the new date to be smaller than or equal to data2 it will return the date if it's not then it will return NULL, which will be removed from the where clause. Finally the outer select will count the dates that where created and group them by month.

First time I saw this technique I didn't quite get it, but I started running each select individually and then it made sense.
Please note that I type these scripts in the reply box and I haven't tested them, so probably I've missed something or have done a mistake, but the logic should be there.

[Szabi Zsoldos]

Hi Adam,

Thank you for the repost, and I've tried to correct some of the errors but I'm still having difficulties in solving this problem :)

Below is the database

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

-- Dumping structure for table contact_concedii
DROP TABLE IF EXISTS `contact_concedii`;
CREATE TABLE IF NOT EXISTS `contact_concedii` (
  `id` int(10) NOT NULL auto_increment,
  `uid` int(10) NOT NULL,
  `data1` date default NULL,
  `data2` date default NULL,
  `ore` int(11) NOT NULL,
  `tip_concediu` enum('1','2','3') default NULL COMMENT '1 - Concediu de odihna, 2 - Concediu medical, 3 - Concediu fara plat',
  `ts` timestamp NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

-- Dumping data for table contact_concedii: 4 rows
/*!40000 ALTER TABLE `contact_concedii` DISABLE KEYS */;
INSERT INTO `contact_concedii` (`id`, `uid`, `data1`, `data2`, `ore`, `tip_concediu`, `ts`) VALUES
    (1, 244, '2013-01-28', '2013-01-28', 5, '1', '2013-01-28 16:18:29'),
    (2, 244, '2013-01-29', '2013-01-29', 8, '1', '2013-01-28 16:18:47'),
    (3, 244, '2013-01-30', '2013-02-21', 16, '1', '2013-01-28 16:19:10'),
/*!40000 ALTER TABLE `contact_concedii` ENABLE KEYS */;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

And I have this problem with the WHERE clause, it says that Unknown column 'data1' in 'where clause' but it makes no sens, i'm not using it's alias...

This is my corrected query

select month(date),count(*) 
from (
    select case when DATE_ADD(data1, INTERVAL num day) <= data2 
    then DATE_ADD(data1, INTERVAL num day)
    else null end as date
    from contact_concedii
cross join 
(select a.id + b.id as num from (select 1 as id union select 2 union select 3 union select 4 union select 5 union select 6 
union select 7 union select 8 union select 9 union select 0) as a 
cross join (select 0 as id union select 10 union select 20 union select 30 union select 40 union select 50 
union select 60 union select 70 union select 80 union select 90 ) as b 
    where case when DATE_ADD(data1, INTERVAL num day) <= data2 
    then DATE_ADD(data1, INTERVAL num day)
    else null end is not null
) as x ) as c 
group by month(date)

[adam_k]

The problem is ) as x. It needs to go before the where and just after b:
I've tested this in sqlfiddle.com:

SELECT month(date),count(*)
FROM (
    SELECT CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END AS date
    FROM contact_concedii
CROSS JOIN
(SELECT a.id + b.id AS num FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS a
CROSS JOIN (SELECT 0 AS id UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) AS b ) AS x
    WHERE CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END IS NOT NULL
) AS c
GROUP BY month(date)

The results are:

| MONTH(DATE) | COUNT(*) |
--------------------------
|           1 |        4 |
|           2 |       21 |

So I'm guessing you are really after something like:

| ID | MONTH(DATE) | COUNT(*) |
-------------------------------
|  1 |           1 |        1 |
|  2 |           1 |        1 |
|  3 |           1 |        2 |
|  3 |           2 |       21 |

For this you'll need this:

SELECT id, month(date),count(*)
FROM (
    SELECT id, CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END AS date
    FROM contact_concedii
CROSS JOIN
(SELECT a.id + b.id AS num FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 0) AS a
CROSS JOIN (SELECT 0 AS id UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40 UNION SELECT 50
UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90 ) AS b ) AS x
    WHERE CASE WHEN DATE_ADD(data1, INTERVAL num DAY) <= data2
    THEN DATE_ADD(data1, INTERVAL num DAY)
    ELSE NULL END IS NOT NULL
) AS c
GROUP BY id, month(date)

[Szabi Zsoldos]

This aproach is fantastic, never tought of this in this particular way! Amazing!
I've grouped them by month and uid now.

LE:
And it wont be the case of bigger then 99 days :)

You've been a heck of a help !! :) Thank you!