SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Check In at 7.00PM and Check Out at 7.00AM
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

indr4w
Starting Member

Indonesia
27 Posts

Posted - 05/28/2013 :  04:34:52  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 05/28/2013 :  05:06:42  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/10/2013 :  03:24:37  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 06/10/2013 :  03:38:03  Show Profile  Reply with Quote
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

Edited by - bandi on 06/10/2013 03:43:33
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/10/2013 :  04:51:38  Show Profile  Reply with Quote
Hi see the date (I have different date)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/10/2013 :  08:12:41  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/13/2013 :  03:08:56  Show Profile  Reply with Quote
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



Edited by - indr4w on 06/13/2013 05:31:24
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/13/2013 :  03:41:07  Show Profile  Reply with Quote

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)


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

Edited by - visakh16 on 06/13/2013 03:49:22
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/13/2013 :  04:24:49  Show Profile  Reply with Quote
Sorry,

how about table UserUsedSClasses
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/13/2013 :  04:34:57  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/13/2013 :  05:30:10  Show Profile  Reply with Quote
Sorry, nothing haven
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/13/2013 :  05:30:50  Show Profile  Reply with Quote
what does that mean?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/13/2013 :  05:34:15  Show Profile  Reply with Quote
Execute is ok but file cannot display.
Please to check the table step by step

Sorry Mr. Visaks i am newbie

Edited by - indr4w on 06/13/2013 05:43:25
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/13/2013 :  06:17:18  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/13/2013 :  06:38:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/13/2013 :  06:54:36  Show Profile  Reply with Quote
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
Go to Top of Page

indr4w
Starting Member

Indonesia
27 Posts

Posted - 06/28/2013 :  03:09:53  Show Profile  Reply with Quote
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

Edited by - indr4w on 06/28/2013 03:10:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/28/2013 :  03:39:55  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000