Check In at 7.00PM and Check Out at 7.00AM

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Check In at 7.00PM and Check Out at 7.00AM

Author

Topic

indr4w
Starting Member

27 Posts

Posted - 2013-05-28 : 04:34:52

Hallo

How to show in 1 row on the grid for Work In and Work Out Over 24Hour in sql

Example :

USERID NAME CHECKIN CHECKOUT
007 TOM 28/05/2013 19:00:00 29/05/2013 07:00:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 05:06:42

you need to have two fields in your table of type datetime for storing CHECKIN and CHECKOUT and then a simple insert would be enough.
For displaying just do SELECT * FROM table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-10 : 03:24:37

I mean like this Mr. Visakh
Example:

I have Table checkinout

userid name checktime
007 raju 28/05/2013 19:00:00 ---> in
007 raju 29/05/2013 07:00:00 ---> out

and I want to display the grid to be "
USERID NAME CHECKIN CHECKOUT
007 raju 28/05/2013 19:00:00 29/05/2013 07:00:00

thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-10 : 03:38:03

quote:
Originally posted by indr4w

I mean like this Mr. Visakh
Example:

I have Table checkinout

userid name checktime
007 raju 28/05/2013 19:00:00 ---> in
007 raju 29/05/2013 07:00:00 ---> out

and I want to display the grid to be "
USERID NAME CHECKIN CHECKOUT
007 raju 28/05/2013 19:00:00 29/05/2013 07:00:00

thanks

How to find the IN, OUT date values.... Is there any specific time ranges to represent IN/OUT?

If you have only two rows per userid, then you can use as follows:
SELECT userid, name, MIN(checktime) CheckIn, MAX(checktime) CheckOut
FROM YourTable
GROUP BY userid, name

--
Chandu

indr4w
Starting Member

27 Posts

Posted - 2013-06-10 : 04:51:38

Hi see the date (I have different date)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-10 : 08:12:41

Assuming there's a type field to indicate IN,OUT


SELECT userid,name,checktime,MinTime
FROM Table
OUTER APPLY (SELECT MIN(checktime) AS MinTime
             FROM Table
             WHERE userid = t.userid
             AND name = t.name
             AND checktime > t.checktime
             AND type = 'OUT'
           )t1
WHERE t.type='IN'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-13 : 03:08:56

Hallo Mr. Visakh

There are a few tables :

GO

CREATE TABLE [dbo].[CHECKINOUT] (
[USERID] [int] NOT NULL ,
[CHECKTIME] [datetime] NOT NULL ,
[CHECKTYPE] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

INSERT INTO CHECKINOUT VALUES
(256, '29/04/2013 19:03:30', 'I'),
(256, '30/04/2013 06:34:42', 'O'),
(205, '03/04/2013 21:56:13', 'I'),
(205, '04/04/2013 06:01:15', 'O');

GO

CREATE TABLE [dbo].[USERINFO] (
[USERID] [int] IDENTITY (1, 1) NOT NULL ,
[Badgenumber] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO USERINFO VALUES
(256, '1061052', 'TAJUDIN'),
(205, '1061010', 'RAJU');

GO

CREATE TABLE [dbo].[SchClass] (
[schClassid] [int] IDENTITY (1, 1) NOT NULL ,
[schName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO SchClass VALUES
(4, 'NIGHT SHIFT', '23:00:00', '07:00:00'),
(6, 'LONG SHIFT', '19:00:00', '07:00:00');

GO

CREATE TABLE [dbo].[UserUsedSClasses] (
[UserId] [int] NOT NULL,
[SchId] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO UserUsedSClasses VALUES
(256, 6),
(205, 4);

I want the output like this :

USERID NAME SCHNAME DATEIN TIMEIN DATEOUT TIMEOUT
256 TAJUDIN LONG SHIFT 29/04/2013 19:03:30 30/04/2013 06:34:42
205 RAJU NIGHT SHIFT 03/04/2013 21:56:13 04/04/2013 06:01:15

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 03:41:07

[code]
SELECT ui.[USERID],ui.[Name],sc.schName,
CONVERT(varchar(10),cin.CHECKTIME,103) AS DATEIN,
CONVERT(varchar(8),cin.CHECKTIME,108) AS TIMEIN ,
CONVERT(varchar(10),cout.CHECKTIME,103) AS DATEOUT,
CONVERT(varchar(8),cout.CHECKTIME,108) AS TIMEOUT
FROM USERINFO ui
INNER JOIN SchClass sc
ON sc.UserId = ui.USERID
INNER JOIN CHECKINOUT cin
ON cin.USERID = ui.USERID
AND cin.CHECKTYPE = 'I'
INNER JOIN CHECKINOUT cout
ON cout.USERID = ui.USERID
AND cout.CHECKTYPE = 'O'
AND cout.CHECKTIME BETWEEN cin.CHECKTIME AND DATEADD(dd,DATEDIFF(dd,0,cin.CHECKTIME),sc.EndTime)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-13 : 04:24:49

Sorry,

how about table UserUsedSClasses

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 04:34:57

ah...missed that


SELECT ui.[USERID],ui.[Name],sc.schName,
CONVERT(varchar(10),cin.CHECKTIME,103) AS DATEIN,
CONVERT(varchar(8),cin.CHECKTIME,108) AS TIMEIN ,
CONVERT(varchar(10),cout.CHECKTIME,103) AS DATEOUT,
CONVERT(varchar(8),cout.CHECKTIME,108) AS TIMEOUT
FROM USERINFO ui
INNER JOIN UserUsedSClasses uus
ON uus.UserId = ui.USERID
INNER JOIN SchClass sc
ON sc.schClassid = uus.SchId
INNER JOIN CHECKINOUT cin
ON cin.USERID = ui.USERID
AND cin.CHECKTYPE = 'I'
INNER JOIN CHECKINOUT cout
ON cout.USERID = ui.USERID
AND cout.CHECKTYPE = 'O'
AND cout.CHECKTIME BETWEEN cin.CHECKTIME AND DATEADD(dd,DATEDIFF(dd,0,cin.CHECKTIME),sc.EndTime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-13 : 05:30:10

Sorry, nothing haven

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 05:30:50

what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-13 : 05:34:15

Execute is ok but file cannot display.
Please to check the table step by step

Sorry Mr. Visaks i am newbie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 06:17:18

it should be this i guess


SELECT ui.[USERID],ui.[Name],sc.schName,
CONVERT(varchar(10),cin.CHECKTIME,103) AS DATEIN,
CONVERT(varchar(8),cin.CHECKTIME,108) AS TIMEIN ,
CONVERT(varchar(10),cout.CHECKTIME,103) AS DATEOUT,
CONVERT(varchar(8),cout.CHECKTIME,108) AS TIMEOUT
FROM USERINFO ui
INNER JOIN UserUsedSClasses uus
ON uus.UserId = ui.USERID
INNER JOIN SchClass sc
ON sc.schClassid = uus.SchId
INNER JOIN CHECKINOUT cin
ON cin.USERID = ui.USERID
AND cin.CHECKTYPE = 'I'
INNER JOIN CHECKINOUT cout
ON cout.USERID = ui.USERID
AND cout.CHECKTYPE = 'O'
AND cout.CHECKTIME BETWEEN cin.CHECKTIME AND DATEADD(dd,DATEDIFF(dd,0,cout.CHECKTIME),sc.StartTime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-13 : 06:38:14

So I changed to be like this :

AND cout.CHECKTIME BETWEEN cin.CHECKTIME AND DATEADD(dd,DATEDIFF(dd,sc.StartTime,cin.CHECKTIME),0)

the Results is :
256 TAJUDIN LONG SHIFT 29/04/2013 19:03:30 30/04/2013 06:34:42
205 RAJU NIGHT SHIFT 03/04/2013 21:56:13 04/04/2013 06:01:15

as expected

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-13 : 06:54:36

Nope it should be the way i gave as out time can be anywhere until next day shift start

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

indr4w
Starting Member

27 Posts

Posted - 2013-06-28 : 03:09:53

Hi Mr. Visakh

sorry to disturb again
I want to ask again, what if from the above table, I want to display the results like this:

USERID NAME SCHNAME CHECKTIME AS CHECKIN CHECKTIME AS CHECKOUT
156 INDRA NON SHIFT 29/04/2013 07:00:30 30/04/2013 18:30:02
256 TAJUDIN LONG SHIFT 29/04/2013 19:03:30 30/04/2013 06:34:42
205 RAJU NIGHT SHIFT 03/04/2013 21:56:13 04/04/2013 06:01:15

date and time are not in separate.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-28 : 03:39:55

do you mean you want them in same field in output?


SELECT ui.[USERID],ui.[Name],sc.schName,
cin.CHECKTIME AS DATEIN,
cout.CHECKTIME AS DATEOUT
FROM USERINFO ui
INNER JOIN UserUsedSClasses uus
ON uus.UserId = ui.USERID
INNER JOIN SchClass sc
ON sc.schClassid = uus.SchId
INNER JOIN CHECKINOUT cin
ON cin.USERID = ui.USERID
AND cin.CHECKTYPE = 'I'
INNER JOIN CHECKINOUT cout
ON cout.USERID = ui.USERID
AND cout.CHECKTYPE = 'O'
AND cout.CHECKTIME BETWEEN cin.CHECKTIME AND DATEADD(dd,DATEDIFF(dd,0,cout.CHECKTIME),sc.StartTime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources