query to get details from a table

Question

2.50/5 (2 votes)

See more:

hi all..

i have table like:

HTML

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

HTML

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:

SQL

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

tulasiram3975

Updated 26-Sep-12 7:38am

Sandip.Nascar

v6

Add a Solution

3 solutions

Solution 1

The first issue I see in your query is this

SQL

...
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

SQL

AND TIME(a.datetime)>='9:00:00' 
AND TIME(a.datetime)<='18:00:00'

Posted 26-Sep-12 5:04am

fjdiewornncalwe

Updated 26-Sep-12 5:19am

v3

Comments

tulasiram3975 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

Solution 3

See if this link[^] helps.

Posted 26-Sep-12 5:14am

I.explore.code

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Santhosh Kumar Jayaraman · Accepted Answer · 2012-09-26T05:05:00

Solution 2

SQL

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

Updated solution:

SQL

;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:

XML

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

SQL

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')

)

Posted 26-Sep-12 5:05am

Santhosh Kumar Jayaraman

Updated 26-Sep-12 19:14pm

v6

Comments

tulasiram3975 26-Sep-12 11:13am

Yes you are absolutely right but there is no DATEPART function sir...

Santhosh Kumar Jayaraman 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 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 Jayaraman 26-Sep-12 11:23am

welcome..

tulasiram3975 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 Jayaraman 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 9-Oct-12 4:04am

thank you...
working great