i am trying to get result from two tables, one table have data and another one have time. my table look like this.
ShiftName:
ShiftName StartTime EndTime
Morning 9:00 6:00
After 12:00 9:00
EmpShift:
EmpNo StartDate EndDate ShiftName
for example ,
100 01-Jun-2015 10-Jun-2015 Morning
100 11-Jun-2015 25-Jun-2015 After
in my case empid 100 have two shift in same month. but when i am executing my query its check only first one. i mean it return only morning for all the date.
please check my query.
Select EmpShift.EmpID,
EmpShift.ShiftName,
EmpShift.StartDate,
EmpShift.EndDate,
Convert(varchar(10), CHECKINOUT.CheckTime, 121) As WorkDate,
convert(char(5), Min(CHECKINOUT.CheckTime), 108) As CheckTime,
CHECKINOUT.CheckType,
CHECKINOUT.CheckTime
From EmpShift
Inner Join CHECKINOUT On EmpShift.EmpID=CHECKINOUT.USERID
Where ((EmpShift.EmpID=100) AND
((EmpShift.StartDate Between '2015-06-01' AND '2015-06-30') AND
(EmpShift.EndDate Between '2015-06-01' AND '2015-06-30')))
Group By EmpShift.EmpID, Convert(varchar(10),
CHECKINOUT.CheckTime, 121),convert(char(5), CHECKINOUT.CheckTime, 108),
EmpShift.EmpID,
EmpShift.ShiftName,
EmpShift.StartDate,
EmpShift.EndDate,
CHECKINOUT.CheckType,
CHECKINOUT.CheckTime
please help me to find the solution.