Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How show datetime select only in Sql Server? Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 9:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:44 AM
Points: 14, Visits: 46
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. :)
Post #1378299
Posted Monday, October 29, 2012 9:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 3,476, Visits: 7,517
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1378309
Posted Monday, October 29, 2012 11:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:06 AM
Points: 83, 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



Post #1378568
Posted Tuesday, October 30, 2012 7:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 29, 2013 9:44 AM
Points: 14, Visits: 46
Thanks you so much! Luis Cazares and SSC Rookie.It's work two solution.
Post #1378772
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse