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
 General SQL Server Forums
 New to SQL Server Programming
 Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/03/2013 :  01:32:33  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/03/2013 :  01:50:02  Show Profile  Reply with Quote


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  

Edited by - MuMu88 on 06/03/2013 01:50:36
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

immad
Posting Yak Master

Pakistan
219 Posts

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


Edited by - immad on 06/03/2013 03:12:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

immad
Posting Yak Master

Pakistan
219 Posts

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

Edited by - immad on 06/03/2013 04:13:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/03/2013 :  06:47:18  Show Profile  Reply with Quote


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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/03/2013 :  07:18:02  Show Profile  Reply with Quote
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

Edited by - immad on 06/03/2013 07:26:09
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

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

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 06/03/2013 :  07:36:51  Show Profile  Reply with Quote
Its ok sir

Thank You For Your Help

immad uddin ahmed
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

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

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.12 seconds. Powered By: Snitz Forums 2000