Click here to Skip to main content
Search within:



Next

query to get details from a table
Sign Up to vote bad
good
See more: C#ASP.NETMySQL
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:55
tulasiram39751.9K
Edited 26 Sep '12 - 7:38
Sandip.Nascar14.9K
v6
Add a Solution


3 solutions

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  
Posted 26 Sep '12 - 5:05
Santhosh Kumar Jayaraman23.2K
Edited 26 Sep '12 - 19:14
v6
Comments
tulasiram3975 - 26 Sep '12 - 11:13
Yes you are absolutely right but there is no DATEPART function sir...
Santhosh Kumar J - 26 Sep '12 - 11:15
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 - 26 Sep '12 - 11:22
ok sir thank you i got some idea am checking like TIME(datetime) <'09:00:00' in this way..
Santhosh Kumar J - 26 Sep '12 - 11:23
welcome..
tulasiram3975 - 26 Sep '12 - 11:39
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 - 27 Sep '12 - 1:14
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 - 9 Oct '12 - 4:04
thank you... working great
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  
Posted 26 Sep '12 - 5:04
asreighawleiybhghwlerisg109K
Edited 26 Sep '12 - 5:19
v3
Comments
tulasiram3975 - 26 Sep '12 - 11:06
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
See if this link[^] helps.
  Permalink  
Posted 26 Sep '12 - 5:14
Aman.A6.5K
Add a Solution

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

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 388
1 OriginalGriff 291
2 Mayur_Panchal 153
3 Mohammed Hameed 145
4 Dave Kreskowiak 125
0 Sergey Alexandrovich Kryukov 8,146
1 OriginalGriff 6,236
2 CPallini 3,482
3 Rohan Leuva 2,703
4 Maciej Los 2,234


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