June 3, 2013 at 4:27 am
my table is like this for attendance
create table attendance
(
CID int,
BID int,
date datetime,
eid int,
timein datetime,
timeout datetime,
)
and my shift table is like this
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
shifttimein------------------------------1/23/2013 9:00:00 AM
shiftTimeout---------------------------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
my query is like this
select
distinct
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 : ' +-- Hours ( + ':' separator )
CAST (ABS (540 - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= 540 THEN 'Excess' ELSE 'Short' END ExcessShort
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446
i want to to add Totaltime column in replace of 540
becouse shift timing is not 9 hours some shift are 8 hours and some are 10 hours
please help me out
immad
June 3, 2013 at 4:51 am
replace the number 540 with the code below:datediff(minute, '0:00:00', convert(nvarchar(5), TotalTime, 114))The code will return the difference in minutes between midnight and the time in column TotalTime.
June 3, 2013 at 5:23 am
Sir i use your function and its give me multiple result
this is 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, '0:00:00', convert(nvarchar(5), s.TT, 114)) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )
CAST (ABS (datediff(minute, '0:00:00', convert(nvarchar(5), s.TT, 114)) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= datediff(minute, '0:00:00', convert(nvarchar(5), s.TT, 114)) THEN 'Excess' ELSE 'Short' END ExcessShort
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446
and its give me this 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---------------------NULL
excessshort-------------------Short
this gives me 4 time result with same date
and every time its give me different excesstime
1st row give me
NULL
2nd row give me
0 hours : 18 minutes (this one is correct)
3rd row give me
0 hours : 42 minutes
and 4th row give me
1 hours : 18 minutes
immad
June 3, 2013 at 5:39 am
In the original post you only state the vlues of a single row. From your last reply it looks like the value in the TotalTime (TT) column is quite different. from each row. Without more data we can't give specific conclusions.
It would be helpfull if you provide statements to create and fill some sample data. And specify your requested result for each data.
June 3, 2013 at 5:41 am
immaduddinahmed (6/3/2013)
Sir i use your function and its give me multiple result
Multiple results is not a result of the use of the function
There is some issue with your JOINS, may be they are not specific enough
Please check your JOINS and add some more filtering clauses to reduce the no of rows if you are getting duplicates
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 3, 2013 at 5:50 am
this is my attendance data
create table attendance
(
CID int,
BID int,
date datetime,
eid int,
timein datetime,
timeout datetime,
)
employee swap card his id timein come when he leaving his time out come
immad
June 3, 2013 at 5:55 am
Sorry, but this will not be enough
Please provide the DDL of both the tables involved, some sample data and the expected results based on your sample data
If you are not sure how to do this, the link in my signature will help you do it.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 3, 2013 at 5:55 am
Could you include the TotalTime column to your query and post the complete results of your SELECT statement?
June 3, 2013 at 5:58 am
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, '0:00:00', convert(nvarchar(5), s.TotalTime, 114)) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )
CAST (ABS (datediff(minute, '0:00:00', convert(nvarchar(5), s.TotalTime, 114)) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= datediff(minute, '0:00:00', convert(nvarchar(5), s.TotalTime, 114)) THEN 'Excess' ELSE 'Short' END ExcessShort
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446
its gives me this result same date but different excesstime and excessshort
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-------------------NULL
excessshort--------------------Short
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
excessshort--------------------Excess
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 : 42 minutes
excessshort--------------------Short
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-------------------1 hours : 18 minutes
excessshort--------------------Excess
immad
June 3, 2013 at 6:01 am
We'll need the OUTPUT of the select statement and not the statement itself. Execute the statement and post the excel-like output. It would even be even more helpfull if we have some data (values) that is in your tables, so we can test things in our own environment.
June 3, 2013 at 6:04 am
this output come
its gives me this result same date but different excesstime and excessshort
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-------------------NULL
excessshort--------------------Short
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
excessshort--------------------Excess
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 : 42 minutes
excessshort--------------------Short
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-------------------1 hours : 18 minutes
excessshort--------------------Excess
immad
June 3, 2013 at 6:13 am
Your output indicates each row has a different value in the TotalTime column. How many rows are in the SHIFT table with ID related to emplyee 26446?
please give us the output of below queries:
select * from TRANS where employeecode = 26446
select ID, TotalTime from SHIFT where BID = (select BID from TRANS where employeecode = 26446) and CID = (select CID from TRANS where employeecode = 26446)
P.S.: are you JOINing both tables on the correct fields? If your JOIN isn't correct, the result could give more rows then expected.
June 3, 2013 at 6:19 am
trans and attendace is same table
create table attendance
(
CID int, mean company id
BID int, mean branch id
date datetime,
eid int, mean employee id
timein datetime,
timeout datetime,
)
and we dont have a employeeid in shift table
this is a shift table
CREATE TABLE SHIFT
(
CompanyID int,
BranchID int,
ShiftID int,
ShiftName varchar(50),
LongName varchar(50),
SType varchar(50),
TimeIn datetime,
TimeOut datetime,
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
shifttimein------------------------------1/23/2013 9:00:00 AM
shiftTimeout---------------------------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
immad
June 3, 2013 at 6:36 am
I'm sorry, but it's getting very unclear at this moment. Please provide us with complete sample data of both tables instead of just a single row from one table. And looking at the results of your query I can't imagine there's only one row in the SHIFTS table.
June 3, 2013 at 6:37 am
immaduddinahmed (6/3/2013)
trans and attendace is same tablecreate table attendance
(
CID int, mean company id
BID int, mean branch id
date datetime,
eid int, mean employee id
timein datetime,
timeout datetime,
)
and we dont have a employeeid in shift table
this is a shift table
CREATE TABLE SHIFT
(
CompanyID int,
BranchID int,
ShiftID int,
ShiftName varchar(50),
LongName varchar(50),
SType varchar(50),
TimeIn datetime,
TimeOut datetime,
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
shifttimein------------------------------1/23/2013 9:00:00 AM
shiftTimeout---------------------------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
Does the query below work with the structure you have provided?
If this is not the query, provide the query that matches with your structure or vice-versa.
immaduddinahmed (6/3/2013)
selectdistinct
Date,
[Time in],
[Time out],
CONVERT(varchar(8),DATEADD(ss,DATEDIFF(ss,[Time in],[Time out]),0),108) AS SpendTime,
CAST (ABS (datediff(minute, '0:00:00', convert(nvarchar(5), s.TotalTime, 114)) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) / 60 as varchar ) + ' hours : ' +-- Hours ( + ':' separator )
CAST (ABS (datediff(minute, '0:00:00', convert(nvarchar(5), s.TotalTime, 114)) - DATEDIFF (MINUTE, [Time in], [Time out] ) ) % 60 as varchar )+ ' minutes' as excesstime,
CASE WHEN DATEDIFF(MINUTE, [Time in], [Time out]) >= datediff(minute, '0:00:00', convert(nvarchar(5), s.TotalTime, 114)) THEN 'Excess' ELSE 'Short' END ExcessShort
FROM trans t
Left outer join shift s on t.bid = s.bid and t.cid = s.cid
where employeecode = 26446
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply