JwezKgSU

1drop table if exists support;
2
3create table support
4(
5support_id int unsigned not null auto_increment primary key,
6subject varchar(255) not null,
7created_date datetime not null
8);
9
10insert into support (subject, created_date) values 
11('help me i am a noob', date_sub(now(), interval 3 day)),
12('i need support', date_sub(now(), interval 2 day)),
13('server crashed', date_sub(now(), interval 1 day)),
14('can''t access cpanel', now());
15
16
17drop table if exists support_reply;
18
19create table support_reply
20(
21reply_id int unsigned not null auto_increment primary key,
22support_id int unsigned not null,
23emp_id int unsigned not null,
24created_date datetime not null
25);
26
27insert into support_reply (support_id, emp_id, created_date) values
28(1,1,date_sub(now(), interval 3 day)),
29(1,2,date_sub(now(), interval 2 day)),
30(1,3,date_sub(now(), interval 1 day)),
31(1,4, now()),
32(2,4,now()),
33(2,1,now()),
34(3,2,date_sub(now(), interval 2 day)),
35(3,3,date_sub(now(), interval 1 day));
36
37select * from support;
38select * from support_reply;
39
40select
41 s.*,
42 sr.*,
43 min_sr.under_24_hrs
44from
45 support s
46inner join support_reply sr on s.support_id = sr.support_id
47inner join
48(
49select
50 min(reply_id) as reply_id,
51 support_id,
52 case 
53  when datediff(now(), created_date) <= 1 then 1 else 0 
54 end as under_24_hrs
55from
56 support_reply
57group by
58 support_id
59) min_sr on sr.reply_id = min_sr.reply_id;
60
61
62
63support_id	subject			created_date		reply_id	support_id1	emp_id	created_date1		under_24_hrs
64==========	=======			============		========	===========	======	=============		============
651		help me i am a noob	30/10/2009 13:50:00	1		1		1	30/10/2009 13:50:00	0
662		i need support		31/10/2009 13:50:00	5		2		4	02/11/2009 13:50:00	1		<--- if 1 it was under 24 hrs
673		server crashed		01/11/2009 13:50:00	7		3		2	31/10/2009 13:50:00	0