Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 01:32:33

This is my query

select
Date,
[Time in],
[Time out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
CAST (ABS( 540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar ) as excesstime

from
trans
where employeecode=17090

this query give me that result:

Date--------------------------------------- 2013-01-01 00:00:00.000
Timein------------------------------------- 2013-01-01 09:14:00.000
Timeout------------------------------------ 2013-01-01 19:06:00.000
spendtime--------------------------------- 09:52:00
excessshorttime-------------------------- 0:52
excess/short-------------------------------- ?

i want to do that if its show excess time in excessshorttime column so its say excess in excess/short column
or if its show short time in excessshorttime column so its say short in excess/short column.

how do i implement in this query.

immad uddin ahmed

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-03 : 01:50:02

[CODE]

select
Date,
[Time in],
[Time out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
CAST (ABS( 540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )
CAST (ABS( 540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar ) as excesstime,
(CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= 540) THEN 'Excess' ELSE 'Short' END) AS ExcessShort
FROM trans where employee code = 17090
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 02:15:03

CASE WHEN SIGN(540 - DATEDIFF (MINUTE, [Time in], [Time out] )) >= 0 'excess' ELSE 'short' END

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 02:54:08

thanks your query is working

this is my shift table that i make

CREATE TABLE SHIFT
(
CompanyID int,
BranchID int,
ShiftID int,
ShiftName varchar(50),
LongName varchar(50),
SType varchar(50),
TimeIn datetime,
TimeOutdatetime,
LTime datetime,
HDTime datetime,
Night int,
TotalTime datetime,
)

and this is my data of shift Table

CompanyID-----------1
BranchID------------1
ShiftID---------------1
ShiftName-------------G
LongName------------------GENERAL SHIFT
Stype-------------------------------null
timein------------------------------1/23/2013 9:00:00 AM
Timeout---------------------------1/23/2013 6:00:00 PM
Ltime--------------------------------1/23/2013 9:16:00 AM
HdTime----------------------------1/23/2013 2:00:00 PM
Night---------------------------------0
TotalTime-------------------------6/3/2013 9:00:00 AM

i want TotalTime column Replace 540 in above query so if we inserts more shifts in this table.so query automatically calculate its spend time and excess short.TotalTime Column in shift table is adding timein and timeout of shift table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 03:50:59

do you've shift start and endtimes stored somewhere?. TimeIn and TimeOut will only have actually In,Out times right?

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 03:55:17

No sir .This is shift start and shift end time in shift table. actullay i write time in and time out but actullay its Shift start and Shift end timing.

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 04:18:24

then you could just replace 540 with DATEDIFF(minute,[shift start],[shift end])

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 06:10:31

Please use as suggested . its DATEDIFF and not DATEADD


select
distinct 
Date,
[Time in],
[Time out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
CAST (ABS (
dateadddatediff(minute,s.Timein,Timeout) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )
CAST (ABS (
dateadddatediff(minute,s.Timein,Timeout) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= 
dateadddatediff(minute,s.Timein,Timeout) THEN 'Excess' ELSE 'Short' END ExcessShort
--case when [time in] > 
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 06:47:18

Sir i use this function in query

select
distinct
Date,
[Time in],
[Time out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
CAST (ABS (
datediff(minute,s.Timein,Timeout) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )
CAST (ABS (
datediff(minute,s.Timein,Timeout) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >=
datediff(minute,s.Timein,Timeout) THEN 'Excess' ELSE 'Short' END ExcessShort
--case when [time in] >
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446

but it gives me multiple result

Date---------------2013-01-01 00:00:00.000
Timein--------------2013-01-01 09:29:00.000
Timeout-------------2013-01-01 18:47:00.000
Spendtime-----------09:18:00
excessTime-------------0 hours : 18 minutes
excesshsort------------Excess

it gives me 10 time same result

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 06:52:28

it may be because you've one to many relationship existing between tables. I cant suggest anything more as I dont know how data is existing in the tables. Can you post sample data from tables here for an employee (say 26446)

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 06:58:15

Post the requested data otherwise nobody can help you

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 07:18:02

ok this is my employee table
USE attendance
GO
/****** Object: Table [dbo].[Employee] Script Date: 06/03/2013 16:09:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[CID] [int] NULL,
[BID] [int] NULL,
[DID] [int] NULL,
[EID] [int] NULL,
[ENAME] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JoiningDate] [datetime] NULL,
[ConfirmationDate] [datetime] NULL,
[LeavingDate] [datetime] NULL,
[DOB] [datetime] NULL,
[DesignationID] [int] NULL,
[EMail] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImagePath] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BankAccntNo] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JobType] [int] NULL,
[ShiftType] [int] NULL,
[LateExcemption] [int] NULL,
[Grade] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AttExcempt] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

1 ---- cid mean company id
2 ------ BID mean branch id
212 ----------DID mean department id
26446 ----------EID mean employee id
SHAN NAZAR -----------Ename mean employee name
MUHAMMAD NAZAR AHMED -----------Fname mean father name
2012-11-01 00:00:00.000 -------------joindate
NULL -------------------Confirmdate
NULL ---------------------leavingdate
1989-02-28 00:00:00.000 ---------DOB mean date of birth
354--------------------Deisgnation
1--------------jobtype
1------------shifttype
0--------------late excemption
E2-----------------grade
0----------attexcempt

email,imagepath,address,bankaccount is null

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 07:26:58

Is it so difficult to go through link and post data in required format?

Unless you're going to do that I'm not planning to help you further

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

immad
Posting Yak Master

230 Posts

Posted - 2013-06-03 : 07:36:51

Its ok sir

Thank You For Your Help

immad uddin ahmed

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-03 : 17:10:02

In the query below you refer to the tables trans and shift. Can you describe their structure, content and the expected output?

As Visakh said it is difficult for us to help you without this information.

quote:
Originally posted by immad

Sir i use this function in query

select
distinct
Date,
[Time in],
[Time out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
CAST (ABS (
datediff(minute,s.Timein,Timeout) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )
CAST (ABS (
datediff(minute,s.Timein,Timeout) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >=
datediff(minute,s.Timein,Timeout) THEN 'Excess' ELSE 'Short' END ExcessShort
--case when [time in] >
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446

but it gives me multiple result

Date---------------2013-01-01 00:00:00.000
Timein--------------2013-01-01 09:29:00.000
Timeout-------------2013-01-01 18:47:00.000
Spendtime-----------09:18:00
excessTime-------------0 hours : 18 minutes
excesshsort------------Excess

it gives me 10 time same result

immad uddin ahmed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources