ok may be i stick to 9 hours employee report

🙂 this is better for me let senior developer of my office decided what to do

ok sir thanks for your suggestion

Sir your query is working fine

thank you half of my problem is solve but

problem is

when i search 9 hours shift employee data its give accurate result.

but when i search...

sir first

it gives me this error

Conversion failed when converting the varchar value 'A' to data type int.

second i want all day d1,d1.....

becouse i am making montlhy attendance report

sir I MADE SOME CHANGES MAY IT WILL HELP YOU CHANGES APPER IN BOLD AND FOR NORMALIZATION I TRY BUT RIGHT NOW THIS IS THE STRUCTURE

this is my shift...

sir i am giving you my tables structure kindly help me out from this problem

this is my shift table structure

CREATE TABLE SHIFT

(

CompanyID int,

BranchID int,

ShiftID int,

ShiftName varchar(50),

LongName varchar(50),

SType varchar(50),

TimeIn datetime,

TimeOutdatetime,

LTime datetime,

HDTime...

ok then i make a eid in shift table then connect with query

thank you for your sugestion

Sir You are right but in our factory we have 2500 employee.its very difficult to insert a eid in shift table.

please tell me the solution thank you

may be its my join problem becouse when i didnt join shift table query working fine but when i join shift table its give me multiple data

any suggestion related to...

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...

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 :...

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 ) + '...

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

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,...

Thank You Sir For Helping me.

these query are very accurate for me this is what i want.

Thank You Again