SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How show datetime select only in Sql Server?


How show datetime select only in Sql Server?

Author
Message
ppc493
ppc493
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 47
I need show datetime when I select datetime only. Because I try to run SQL but show all datetime.

Table Emp
EmpNo | fullName
00001 | Midna
00002 | Klog
00003 | Porla
00004 | Seka
00005 | Mila

Table tFile
EmpNo | cDate | cTime
00001 | 2012-10-29 00:00:00.000 | 2012-10-29 07:52:00.000
00001 | 2012-10-29 00:00:00.000 | 2012-10-29 19:00:00.000
00002 | 2012-10-29 00:00:00.000 | 2012-10-29 07:40:00.000
00002 | 2012-10-29 00:00:00.000 | 2012-10-29 19:32:00.000
00005 | 2012-10-29 00:00:00.000 | 2012-10-29 07:58:00.000
00005 | 2012-10-29 00:00:00.000 | 2012-10-29 18:35:00.000

This code

SELECT em.EmpNo as 'EmpNo', 
case when tf.cDate <> null then tf.cDate else coalesce(tf.cDate, '2012-10-29') end as 'cDate',
Min(tf.cTime) as 'timeIn', Max(tf.cTime) as 'timeOut'
FROM tFile tf Full Outer join Emp em On tf.EmpNo = em.EmpNo
Group By em.EmpNo,tf.cDate
Order By 'EmpNo'



returns this result:

EmpNo | cDate | timeIn | timeOut
-------------------------------------------------------------------------------------
00001 | 2012-10-21 00:00:00.000 | 2012-10-21 07:22:00.000 | 2012-10-21 17:35:00.000
00001 | 2012-10-24 00:00:00.000 | 2012-10-24 07:30:00.000 | 2012-10-24 19:00:00.000
00001 | 2012-10-29 00:00:00.000 | 2012-10-29 07:52:00.000 | 2012-10-29 19:00:00.000
00002 | 2012-10-25 00:00:00.000 | 2012-10-25 07:58:00.000 | 2012-10-25 18:35:00.000
00002 | 2012-10-22 00:00:00.000 | 2012-10-22 08:04:00.000 | 2012-10-22 17:55:00.000
00002 | 2012-10-24 00:00:00.000 | 2012-10-24 08:00:00.000 | 2012-10-24 18:45:00.000
00002 | 2012-10-29 00:00:00.000 | 2012-10-29 07:40:00.000 | 2012-10-29 19:32:00.000
00003 | 2012-10-29 00:00:00.000 | NULL | NULL
00004 | 2012-10-29 00:00:00.000 | NULL | NULL
00005 | 2012-10-28 00:00:00.000 | 2012-10-28 07:30:00.000 | 2012-10-28 19:20:00.000
00005 | 2012-10-27 00:00:00.000 | 2012-10-27 07:38:00.000 | 2012-10-27 19:30:00.000
00005 | 2012-10-29 00:00:00.000 | 2012-10-29 07:58:00.000 | 2012-10-29 18:35:00.000

But I need this result:

*I select date ex. 2012-10-29 then I need to show all rows with 2012-10-29 only (or other that datetime).
But some Empno don't have data in 2012-10-29 it's set NULL.
I don't fix select datetime is 2012-10-29.but it's exam only.

EmpNo | cDate | timeIn | timeOut
---------------------------------------------------------------------------------------
00001 | 2012-10-29 00:00:00.000 | 2012-10-29 07:52:00.000 | 2012-10-29 19:00:00.000
00002 | 2012-10-29 00:00:00.000 | 2012-10-29 07:40:00.000 | 2012-10-29 19:32:00.000
00003 | 2012-10-29 00:00:00.000 | NULL | NULL
00004 | 2012-10-29 00:00:00.000 | NULL | NULL
00005 | 2012-10-29 00:00:00.000 | 2012-10-29 07:58:00.000 | 2012-10-29 18:35:00.000

Thanks for your time. Smile
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16724 Visits: 19112
You have some problems in your query, I'll explain in the code


SELECT em.EmpNo as 'EmpNo',
--You should not compare NULLs with = or <> because it will always return unknown and never true
-- To compare NULLs use IS NULL or IS NOT NULL
--case when tf.cDate <> null then tf.cDate else coalesce(tf.cDate, '2012-10-29') end as 'cDate',
--In this case, you don't need the CASE, a simple COALESCE or ISNULL will work
--I prefere ISNULL where there are only 2 possible values
ISNULL(tf.cDate, '2012-10-29') as 'cDate',
Min(tf.cTime) as 'timeIn', Max(tf.cTime) as 'timeOut'
FROM tFile tf RIGHT OUTER JOIN Emp em On tf.EmpNo = em.EmpNo AND tf.cDate = '20121029'
--You don't have anything to filter your data. In this case, the filter will be in the JOIN to avoid an INNER JOIN
-- I turned your FULL JOIN into a RIGHT JOIN
Group By em.EmpNo,tf.cDate
Order By 'EmpNo'



By the way, the design of your table is not correct, you don't need separate columns for date and time since you have information of both in the cTime column. Try to correct that.;-)


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
BriPan
BriPan
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 296
Try this one

declare @emp table(EMPNo varchar(10),fullnam varchar(20))

declare @tfile table(EMPNo varchar(10),CDate datetime,Ctime datetime)

insert into @emp
select * from
(
Values
('0001','xyz1'),
('0002','xyz2'),
('0003','xyz3'),
('0004','xyz4'),
('0005','xyz5')
)a (no,name)


insert into @tfile
select * from
(
Values
('0001','10/29/2012','2012-10-29 07:52:00.000'),
('0001','10/29/2012','2012-10-29 19:00:00.000'),
('0002','10/29/2012','2012-10-29 07:40:00.000'),
('0002','10/29/2012','2012-10-29 19:32:00.000'),
('0005','10/29/2012','2012-10-29 07:58:00.000'),
('0005','10/29/2012','2012-10-29 18:35:00.000')
)a (no,cdate,ctime)

-----------------------------------------------------------------------------------------
select a.EMPNo,cdate,MIN(Ctime) AS timeIn ,MAX(Ctime) AS timeOut
from @emp a
LEFT join @tfile b on a.EMPNo=b.EMPNo AND CDate='10/29/2012'
GROUP BY a.EMPNo,cdate
ORDER BY A.EMPNo




ppc493
ppc493
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 47
Thanks you so much! Luis Cazares and SSC Rookie.It's work two solution. :-P
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search