June 3, 2013 at 6:47 am
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 joins. is my query join perfectly with shift table
or i should make more column in shift table ?
create table attendance
(
CID int, means company id
BID int, means branch id
date datetime,
eid int, mean employee id
timein datetime,
timeout datetime,
)
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,
)
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
i join shift trans(attendance) cid(company id) and bid(branch id) with shift cid(company id) and bid (branch id)
immad
June 3, 2013 at 6:55 am
I think you'll have to add "eid" to the shifts table as well.
The [shifts]-table should have the same column (or columns) that make a row in the [attendance]-table distinctive. If the "eid" column in the [attendance] table is distinctive, then this column is the only one needed in the [shifts] table and you could even remove the "bid" and "cid" column.
June 3, 2013 at 7:00 am
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
immad
June 3, 2013 at 7:08 am
immaduddinahmed (6/3/2013)
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
The basic question comes to this: How can you tell which employee (i.e. which row in attendance table) belongs to which row in the SHIFT table? When you have the answer to this question you also have the answer to your JOIN statement.
Perhaps you can join the other way around? Make a unique rowID in the [shifts] table and add this row to the [attendance] table in a one-to-many relationship.
June 3, 2013 at 8:09 am
ok then i make a eid in shift table then connect with query
thank you for your sugestion
immad
June 4, 2013 at 12:10 am
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 datetime,
Night int,
TotalTime datetime,
)
and this is my data of shift Table
CompanyID--1
BranchID--2
ShiftID--12
ShiftName--G
LongName--GENERAL SHIFT (09 HRS)
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
this is my trans table structure
CREATE TABLE TRANS
(
Date datetime,
Timein datetime,
Timeout datetime,
Spend nvarchar(50),
excessshort nvarchar(50)
)
data look like this.
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
excess/short
0 hours : 52 minutes
this is my Shift_employee table structure
CREATE TABLE SHIFT_EMPLOYEE
(
CID int,
BID int,
EID int,
month int,
year int,
D1 varchar(50),
D2 varchar(50),
D3 varchar(50),
D4 varchar(50),
D5 varchar(50),
D6 varchar(50),
D7 varchar(50),
D8 varchar(50),
D9 varchar(50),
D10 varchar(50),
D11 varchar(50),
D12 varchar(50),
D13 varchar(50),
D14 varchar(50),
D15 varchar(50),
D16 varchar(50),
D17 varchar(50),
D18 varchar(50),
D19 varchar(50),
D20 varchar(50),
D21 varchar(50),
D22 varchar(50),
D23 varchar(50),
D24 varchar(50),
D25 varchar(50),
D26 varchar(50),
D27 varchar(50),
D28 varchar(50),
D29 varchar(50),
D30 varchar(50),
D31 varchar(50)
)
THIS IS DATA OF SHIFT_EMPLOYEE TABLE
CID--1
BID--2
EID--17090
month--05
year--2013
D1--G
D2--G
D3--G
D4--G
D5--G
D6--G
D7--O
D8--G
D9--G
D10--G
D11--G
D12--G
D13--G
D14--O
D15--G
D16--G
D17--G
D18--G
D19--G
D20--G
D21--O
D22--G
D23--G
D24--G
D25--G
D26--G
D27--G
D28--O
D29--G
D30--G
D31--G
G MEANS GENERAL SHIFT AND I DESCRIBE THIS SHIFT DETAILS IN SHIFT TABLE
LIKE G SHIFT (TIME IN AND TIME OUT AND OTHERS DETAIL).
O MEAN OFF DAY AND I ALSO DEFINE THIS IN SHIFT TABLE AND D1,D2......... MEAN DAY 1 ,DAY 2 WHOLE MONTH SHIFT DEFINE FOR
EMPLOYEE IN THIS TABLE(SHIFT_EMPLOYEE).
IF U WANT MORE EXAMPLE I GIVE U THAT TOO
IN SHIFT TABLE I DEFINE ONE MORE SHIFT THAT SHIFT CALLED C2
CompanyID--1
BranchID--2
ShiftID--10
ShiftName--C2
LongName--NIGHT SHIFT (10 HRS)
Stype--null
timein--2013-01-23 22:00:00.000
Timeout--2013-01-23 08:00:00.000
Ltime--2013-01-23 22:16:00.000
HdTime--2013-01-23 23:53:00.000
Night--1
TotalTime--2013-06-03 10:00:00.000
NOW IF I DEFINE THIS SHIFT INTO SHIFT_EMPLOYEE TABLE AND TYPE THIS DATA
CID--1
BID--2
EID--26446
month--05
year--2013
D1--C2
D2--C2
D3--C2
D4--C2
D5--C2
D6--C2
D7--O
D8--C2
D9--C2
D10--C2
D11--C2
D12--C2
D13--C2
D14--O
D15--C2
D16--C2
D17--C2
D18--C2
D19--C2
D20--C2
D21--O
D22--C2
D23--C2
D24--C2
D25--C2
D26--C2
D27--C2
D28--O
D29--C2
D30--C2
D31--C2
I WANT A QUERY THAT IF I WANT TO SEE 9 HOURS SHIFT EMPLOYEE DATA IT 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
EXCESSSTIME----0 hours : 52 minutes
EXCESSSHORT----EXCESS
AND IF I WANT TO SEE 8 HOURS EMPLOYEE SHIFT DATA
THEN IT GIVE ME THAT RESULT
DATE---2013-01-01 00:00:00.000
TIMEIN----2013-01-01 07:53:00.000
TIMEOUT----2013-01-01 21:46:00.000
SPENDTIME---13:53:00
EXCESSSTIME----4 hours : 53 minutes
EXCESSSHORT----EXCESS
IN SHIFT_EMPLOYEE I AM NOT ALLOW TO CHANGE A TABLE STRUCTURE PLEASE SEE MY TABLES STRUCTURE AND DATA AND SUGGEST ME A QUERY THAT PERFECTLY FITS IN THIS STURCTURE
THANKS FOR THE HELP
immad
June 4, 2013 at 1:14 am
I have created all the DDL and DML statement in the correct format and put the code at the end of this post, so other readers can use this.
For now I have one question: how do you know which row of table [attendance] belongs to which row of table [Shift_employee]?
P.S.: If possible you should normilize the [Shift_employee] table. It will be more easy to create a query when each day has it's own row instead of all days in one row.
CREATE TABLE Shift_employee (
CID INT
, BID INT
, EID INT
, day INT
, month INT
, year INT
, Shift VARCHAR(50)
INSERT INTO Shift_employee
VALUES (
1
, 2
, 17090
, 01 -- day number, increasing by each insert
, 05
, 2013
, 'G')
INSERT INTO Shift_employee
VALUES (
1
, 2
, 17090
, 02 -- day number, increasing by each insert
, 05
, 2013
, 'G')
-- etcetera
Complete DDL and DML statement for the issue as started with this forum post:
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
,
)
CREATE TABLE attendance (
DATE DATETIME
, Timein DATETIME
, Timeout DATETIME
, Spend NVARCHAR(50)
, excessshort NVARCHAR(50)
)
CREATE TABLE Shift_employee (
CID INT
, BID INT
, EID INT
, month INT
, year INT
, D1 VARCHAR(50)
, D2 VARCHAR(50)
, D3 VARCHAR(50)
, D4 VARCHAR(50)
, D5 VARCHAR(50)
, D6 VARCHAR(50)
, D7 VARCHAR(50)
, D8 VARCHAR(50)
, D9 VARCHAR(50)
, D10 VARCHAR(50)
, D11 VARCHAR(50)
, D12 VARCHAR(50)
, D13 VARCHAR(50)
, D14 VARCHAR(50)
, D15 VARCHAR(50)
, D16 VARCHAR(50)
, D17 VARCHAR(50)
, D18 VARCHAR(50)
, D19 VARCHAR(50)
, D20 VARCHAR(50)
, D21 VARCHAR(50)
, D22 VARCHAR(50)
, D23 VARCHAR(50)
, D24 VARCHAR(50)
, D25 VARCHAR(50)
, D26 VARCHAR(50)
, D27 VARCHAR(50)
, D28 VARCHAR(50)
, D29 VARCHAR(50)
, D30 VARCHAR(50)
, D31 VARCHAR(50)
)
INSERT INTO Shift_employee
VALUES (
1
, 2
, 17090
, 05
, 2013
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'O'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'O'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'O'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'G'
, 'O'
, 'G'
, 'G'
, 'G'
)
--G MEANS GENERAL SHIFT AND I DESCRIBE THIS SHIFT DETAIL IN SHIFT TABLE
--LIKE G SHIFT TIME IN AND TIME OUT AND OTHERS DETAIL.
--O MEAN OFF DAY AND D1,D2......... MEAN DAY 1 ,DAY 2 WHOLE MONTH SHIFT DEFINE FOR EMPLOYEE IN THIS TABLE.IF U WANT MORE EXAMPLE I GIVE U THAT TOO
--MEANS IN SHIFT TABLE I DEFINE ONE MORE SHIFT THAT SHIFT CALLED C2
INSERT INTO Shift_employee
VALUES (
1
, 2
, 26446
, 05
, 2013
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'O'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'O'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'O'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'C2'
, 'O'
, 'C2'
, 'C2'
, 'C2'
)
INSERT INTO SHIFT
VALUES (
1
, 2
, 12
, 'G'
, 'GENERAL SHIFT (09 HRS)'
, NULL
, '1/23/2013 9:00:00 AM'
, '1/23/2013 6:00:00 PM'
, '1/23/2013 9:16:00 AM'
, '1/23/2013 2:00:00 PM'
, 0
, '6/3/2013 9:00:00 AM'
)
INSERT INTO SHIFT
VALUES (
1
, 2
, 10
, 'C2'
, 'NIGHT SHIFT (10 HRS)'
, NULL
, '2013-01-23 22:00:00.000'
, '2013-01-23 08:00:00.000'
, '2013-01-23 22:16:00.000'
, '2013-01-23 23:53:00.000'
, 1
, '2013-06-03 10:00:00.000'
)
INSERT INTO attendance
VALUES (
'2013-01-01 00:00:00.000'
, '2013-01-01 09:14:00.000'
, '2013-01-01 19:06:00.000'
, '09:52:00'
, '0 hours : 52 minutes'
)
June 4, 2013 at 1:20 am
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 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 datetime,
Night int,
TotalTime datetime,
)
and this is my data of shift Table
CompanyID--1
BranchID--2
ShiftID--12
ShiftName--G
LongName--GENERAL SHIFT (09 HRS)
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
this is my trans table structure
CREATE TABLE ATTEDNACE
(
eid INT,
Date datetime,
Timein datetime,
Timeout datetime,
Spend nvarchar(50),
excessshort nvarchar(50),
SHIFT VARCHAR(10)
)
data look like this.
eid
17090
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
SHIFT
G
excess/short
0 hours : 52 minutes
this is my Shift_employee table structure
CREATE TABLE SHIFT_EMPLOYEE
(
CID int,
BID int,
EID int,
month int,
year int,
D1 varchar(50),
D2 varchar(50),
D3 varchar(50),
D4 varchar(50),
D5 varchar(50),
D6 varchar(50),
D7 varchar(50),
D8 varchar(50),
D9 varchar(50),
D10 varchar(50),
D11 varchar(50),
D12 varchar(50),
D13 varchar(50),
D14 varchar(50),
D15 varchar(50),
D16 varchar(50),
D17 varchar(50),
D18 varchar(50),
D19 varchar(50),
D20 varchar(50),
D21 varchar(50),
D22 varchar(50),
D23 varchar(50),
D24 varchar(50),
D25 varchar(50),
D26 varchar(50),
D27 varchar(50),
D28 varchar(50),
D29 varchar(50),
D30 varchar(50),
D31 varchar(50)
)
THIS IS DATA OF SHIFT_EMPLOYEE TABLE
CID--1
BID--2
EID--17090
month--05
year--2013
D1--G
D2--G
D3--G
D4--G
D5--G
D6--G
D7--O
D8--G
D9--G
D10--G
D11--G
D12--G
D13--G
D14--O
D15--G
D16--G
D17--G
D18--G
D19--G
D20--G
D21--O
D22--G
D23--G
D24--G
D25--G
D26--G
D27--G
D28--O
D29--G
D30--G
D31--G
G MEANS GENERAL SHIFT AND I DESCRIBE THIS SHIFT DETAILS IN SHIFT TABLE
LIKE G SHIFT (TIME IN AND TIME OUT AND OTHERS DETAIL).
O MEAN OFF DAY AND I ALSO DEFINE THIS IN SHIFT TABLE AND D1,D2......... MEAN DAY 1 ,DAY 2 WHOLE MONTH SHIFT DEFINE FOR
EMPLOYEE IN THIS TABLE(SHIFT_EMPLOYEE).
IF U WANT MORE EXAMPLE I GIVE U THAT TOO
IN SHIFT TABLE I DEFINE ONE MORE SHIFT THAT SHIFT CALLED A1
CompanyID--1
BranchID--2
ShiftID--2
ShiftName--A1
LongName--'A1' SHIFT (8+4) HRS
Stype--null
timein--2013-01-23 08:00:00.000
Timeout--2013-01-23 16:00:00.000
Ltime--2013-01-23 08:16:00.000
HdTime--2013-01-23 09:30:00.000
Night--0
TotalTime--2013-06-03 10:00:00.000
NOW IF I DEFINE THIS SHIFT INTO SHIFT_EMPLOYEE TABLE AND TYPE THIS DATA
CID--1
BID--2
EID--26446
month--05
year--2013
D1--A1
D2--A1
D3--A1
D4--A1
D5--A1
D6--A1
D7--O
D8--A1
D9--A1
D10--A1
D11--A1
D12--A1
D13--A1
D14--O
D15--A1
D16--A1
D17--A1
D18--A1
D19--A1
D20--A1
D21--O
D22--A1
D23--A1
D24--A1
D25--A1
D26--A1
D27--A1
D28--O
D29--A1
D30--A1
D31--A1
I WANT A QUERY THAT IF I WANT TO SEE 9 HOURS SHIFT EMPLOYEE DATA IT 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
SHIFT--G
EXCESSSTIME----0 hours : 52 minutes
EXCESSSHORT----EXCESS
AND IF I WANT TO SEE 8 HOURS EMPLOYEE SHIFT DATA
THEN IT GIVE ME THAT RESULT
DATE---2013-01-01 00:00:00.000
TIMEIN----2013-01-01 07:59:00.000
TIMEOUT----2013-01-01 17:47:00.000
SPENDTIME---09:48:00
SHIFT--A1
EXCESSSTIME----1 hours : 48 minutes
EXCESSSHORT----Excess
IN SHIFT_EMPLOYEE I AM NOT ALLOW TO CHANGE A TABLE STRUCTURE PLEASE SEE MY TABLES STRUCTURE AND DATA AND SUGGEST ME A QUERY THAT PERFECTLY FITS IN THIS STURCTURE
THANKS FOR THE HELP
immad uddin ahmed
immad
June 4, 2013 at 2:00 am
With all the data you have provided, it looks like the statement below will get your required results. But the JOIN in the below query on table [Shift_employee] is just on the first day column "D1"!! This columnname must be changed to "D2", "D3", etc. to calculate the results for the other days. Or you should join the [SHIFT] table multiple time to each seperate day column (but that will become a unreadable query).
select
t.eid
, se.month
, se.year
, se.d1
, s.shiftname
, s.longname
--, s.timein as shift_timeIn
--, s.timeout as shift_timeOut
, t.timein as registered_timeIn
, t.timeout as registered_timeOut
, CONVERT(VARCHAR(8), DATEADD(ss, DATEDIFF(ss, t.[Timein], t.[Timeout]), 0), 108) AS SpendTime
, CAST(ABS(datediff(minute, '0:00:00', convert(NVARCHAR(5), s.TotalTime, 114)) - DATEDIFF(MINUTE, t.[Timein], t.[Timeout])) / 60 AS VARCHAR) + ' hours : ' + -- Hours ( + ':' separator )
CAST(ABS(datediff(minute, '0:00:00', convert(NVARCHAR(5), s.TotalTime, 114)) - DATEDIFF(MINUTE, t.[Timein], t.[Timeout])) % 60 AS VARCHAR) + ' minutes' AS excesstime
, CASE
WHEN DATEDIFF(MINUTE, t.[Timein], t.[Timeout]) >= datediff(minute, '0:00:00', convert(NVARCHAR(5), s.TotalTime, 114))
THEN 'Excess'
ELSE 'Short'
END ExcessShort
FROM trans t
inner JOIN Shift_employee se ON t.eid = se.eid
inner join shift s on s.companyid = se.cid
and s.branchid = se.bid
and s.shiftname = se.d1 -- change this column name to the desired day
June 4, 2013 at 2:08 am
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
immad
June 4, 2013 at 2:19 am
immaduddinahmed (6/4/2013)
...and this is my data of shift Table
CompanyID--1
BranchID--2
ShiftID--12
ShiftName--G
LongName--GENERAL SHIFT (09 HRS)
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
...
You are more likely to get effective help if folks can run code against some sample data. Please read this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D
It will describe how to post sample data on the forum. The data you have posted is difficult to read and time-consuming to convert into a usable format.
Are you designing the tables in this database or are they components of an existing system?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 4, 2013 at 2:32 am
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 8 hours shift employee data it gives me no excesstime
this is the result of your query in sql
date--2013-01-01 00:00:00.000
employeecode----24
month--2
year--2013
d1--A1
shift--A1
LONGNAME--'A1' SHIFT (8+4) HRS
REGISTERED_TIMEIN---2013-01-01 07:59:00.000
REGISTERED_TIMEOUT-------2013-01-01 17:47:00.000
SPENDTIME---------09:48:00
EXCESSTIME--------NULL
EXCESSHSORT------------SHORT
only this is a problem
immad
June 4, 2013 at 2:34 am
I can't reproduce this error with your sample data. Please check all the data in your tables to see if there is a mismatch somewhere.
And like I said before: you need to normalize the [Shift_employee] table. That's the only way to create an acceptable query. If that is not possible for you then you can use 31 queries (one for each day) and UNION ALL these together. But it will be unreadable and very difficult to maintain (I hope I will never ever see or maintain such a mess :cool:).
June 4, 2013 at 2:37 am
ok sir thanks for your suggestion
immad
June 4, 2013 at 2:44 am
I can't reproduce your NULL value with the sample data you provided.
A NULL value in the EXCESSTIME column will be generated if one (or more) of the parts in, CAST(ABS(datediff(minute, '0:00:00', convert(NVARCHAR(5), s.TotalTime, 114)) - DATEDIFF(MINUTE, t.[Timein], t.[Timeout])) / 60 AS VARCHAR) + ' hours : ' + -- Hours ( + ':' separator )
CAST(ABS(datediff(minute, '0:00:00', convert(NVARCHAR(5), s.TotalTime, 114)) - DATEDIFF(MINUTE, t.[Timein], t.[Timeout])) % 60 AS VARCHAR) + ' minutes' AS excesstime
generates a NULL value.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply