Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# ASP.NET MySQL
hi all..
 
i have table like:
uid	empid	datetime	     
1	9003	2012-09-26 09:20:00	
3	9000	2012-09-26 09:20:00
4	9004	2012-09-26 09:20:00
5	9002	2012-09-26 09:20:00
7	9001	2012-09-26 09:20:00
11	9004	2012-09-26 08:59:00
12	9002	2012-09-26 08:59:00
14	9001	2012-09-26 08:59:00
here i need to get details like who get datetime after 9:00 but he did't get datetime befor 9 so the result should be
uid	empid	datetime	     
1	9003	2012-09-26 09:20:00	
3	9000	2012-09-26 09:20:00
i have written this query actually i need to bind three tables and get the result one emp names and another table for getting point of time to get results so the query made like:
SELECT concat(s.firstname,' ',s.midname,' ',s.lastname) as employee,
       id,
       s.shiftname, 
       DATE_FORMAT(DATE(`datetime`),'%d/%b/%y') as logdate,
       DATE_FORMAT(MIN(`datetime`),'%r') as logtime,
       starttime  
  FROM `attlog` a,
       staffdetails s,
       shifts f 
 WHERE s.id = a.empid 
   AND f.shiftname = s.shiftname 
   AND DATE_FORMAT(DATE(a.datetime),'%m/%d/%Y') Between '09/26/2012' AND '09/26/2012' 
   AND TIME(a.datetime)<'9:00:00' 
   AND TIME(a.datetime)<'18:00:00' 
 GROUP BY id, datetime 
 ORDER BY cast(s.id as signed), a.datetime
this one shows me results of all emps where datetime after 9
but we need to prevent result set by a condition like who are get datetime before 9 AM
please any one help me
thanks in advancce..
Posted 26-Sep-12 4:55am
Edited 26-Sep-12 7:38am
v6
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

The first issue I see in your query is this
...
AND TIME(a.datetime)<'9:00:00'
AND TIME(a.datetime)<'18:00:00'
Basically with your query you are returning all times between midnight and 18:00 on the given day.
What I believe you need is this
AND TIME(a.datetime)>='9:00:00' 
AND TIME(a.datetime)<='18:00:00'
  Permalink  
v3
Comments
tulasiram3975 at 26-Sep-12 11:06am
   
no sir actually what i need is display the results after 9 but not befor 9 so that i tried in this way
select * from tm where DATEPART(hh, [savedate])>=9 and empid not in (select empid from tm where DATEPART(hh, [savedate])<9) AND DATE_FORMAT(DATE(datetime),'%d/%m/%y') BETWEEN '25/09/2012' AND '26/09/2012'
but it is not showing results of 25/09/2012 empids
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

select * from tm where DATEPART(hh, [savedate])>=9 and empid not in (select empid from tm where DATEPART(hh, [savedate])<9)
 

Updated solution:
;With Cte
as
(select t1.* from tm t1
inner join tm t2 on t1.empid=t2.empid and convert(char(10), t1.[savedate], 111)=convert(char(10), t2.[savedate], 111)
where  DATEPART(hh, t1.[savedate])>=9 and DATEPART(hh, t2.[savedate])<9)
select * from tm where tm.empid not in (select empid from Cte where convert(char(10), tm.[savedate], 111)=convert(char(10), cte.[savedate], 111))
 
output:
uid empid   savedate
1   9003    2012-09-26 09:20:00.000
3   9000    2012-09-26 09:20:00.000
 
my table and insert query
 
create table tm
(uid int,
empid int,
savedate datetime)
insert into tm values (1,	9003,	'2012-09-26 09:20:00')	
insert into tm values (3,	9000,	'2012-09-26 09:20:00')
insert into tm values (4,	9004,	'2012-09-26 09:20:00')
insert into tm values (5,	9002,	'2012-09-26 09:20:00')
insert into tm values (7,	9001,	'2012-09-26 09:20:00')
insert into tm values (11,	9004,	'2012-09-26 08:59:00')
insert into tm values (12,	9002,	'2012-09-26 08:59:00')
insert into tm values (14,	9001,	'2012-09-26 08:59:00')
 
)
  Permalink  
v6
Comments
tulasiram3975 at 26-Sep-12 11:13am
   
Yes you are absolutely right but there is no DATEPART function sir...
Santhosh Kumar J at 26-Sep-12 11:15am
   
yes thats to get only hour from the datetime. SInce its for everydate, we need to code it in generic way to extract only hour from the datetime field. so i used datetime to check entries made after 9 but not before 9
tulasiram3975 at 26-Sep-12 11:22am
   
ok sir thank you i got some idea am checking like TIME(datetime) <'09:00:00'
in this way..
Santhosh Kumar J at 26-Sep-12 11:23am
   
welcome..
tulasiram3975 at 26-Sep-12 11:39am
   
but i have a problem when it have more dates like 09/25/2012 and 09/26/2012 it is not showing rest of empids let my table be like
uid empid datetime
1 9003 2012-09-26 09:20:00
3 9000 2012-09-26 09:20:00
4 9004 2012-09-26 09:20:00
5 9002 2012-09-26 09:20:00
7 9001 2012-09-26 09:20:00
18 9004 2012-09-25 09:15:00
17 9000 2012-09-25 09:15:00
11 9004 2012-09-26 08:59:00
12 9002 2012-09-26 08:59:00
15 9003 2012-09-25 09:15:00
14 9001 2012-09-26 08:59:00
19 9002 2012-09-25 09:15:00
21 9001 2012-09-25 09:15:00
am trying like:
select * from attlog where TIME(datetime )>='09:00:00' and empid not in (select empid from attlog where TIME( datetime )<'09:00:00') AND DATE_FORMAT(DATE( datetime ) , '%m/%d/%Y' )
BETWEEN '09/25/2012'
AND '09/26/2012'
now it is show like:
uid empid datetime
1 9003 2012-09-26 09:20:00
3 9000 2012-09-26 09:20:00
17 9000 2012-09-25 09:15:00
15 9003 2012-09-25 09:15:00
but it has to show like
uid empid datetime
1 9003 2012-09-26 09:20:00
3 9000 2012-09-26 09:20:00
18 9004 2012-09-25 09:15:00
17 9000 2012-09-25 09:15:00
15 9003 2012-09-25 09:15:00
19 9002 2012-09-25 09:15:00
21 9001 2012-09-25 09:15:00
22 9000 2012-09-25 09:15:00
23 9003 2012-09-25 09:15:00
like it should show..
Santhosh Kumar J at 27-Sep-12 1:14am
   
try my updated solution now.
;With Cte
as
(select t1.* from tm t1
inner join tm t2 on t1.empid=t2.empid and convert(char(10), t1.[savedate], 111)=convert(char(10), t2.[savedate], 111)
where DATEPART(hh, t1.[savedate])>=9 and DATEPART(hh, t2.[savedate])<9)
select * from tm where tm.empid not in (select empid from Cte where convert(char(10), tm.[savedate], 111)=convert(char(10), cte.[savedate], 111))
tulasiram3975 at 9-Oct-12 4:04am
   
thank you...
working great
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

See if this link[^] helps.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 504
1 Gihan Liyanage 298
2 RyanDev 250
3 Sergey Alexandrovich Kryukov 223
4 Richard Deeming 195
0 Sergey Alexandrovich Kryukov 8,906
1 OriginalGriff 7,771
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 27 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100