How to check multi dates from multiple tables

Question

0.00/5 (No votes)

See more:

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.

SQL

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.

Posted 20-Jun-15 4:41am

tastini

Updated 20-Jun-15 6:00am

CPallini

v3

Add a Solution

Comments

Tomas Takac 20-Jun-15 15:07pm

You didn't show the content of the CHECKINOUT table. My guess is there are no records in that one for 11 Jun or after hence the inner join filters the second shift out.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

tastini · Answer 1 · 2015-06-20T20:13:00

I got the solution from another forum. it was nothing but need to make link between the shift table and checkinout table.

Select  EmpShift.EmpID,
            EmpShift.ShiftName,
            EmpShift.StartDate,
            EmpShift.EndDate,
            CHECKINOUT.CheckType,
            CHECKINOUT.CheckTime
    From    EmpShift
            Inner Join CHECKINOUT On EmpShift.EmpID=CHECKINOUT.USERID 
            and CHECKINOUT.CheckTime >= EmpShift.StartDate 
            and CHECKINOUT.CheckTime < EmpShift.EndDate
    Where  (--(EmpShift.EmpID=84) AND
     ((EmpShift.StartDate < '2015-06-30') AND
     (EmpShift.EndDate > '2015-06-01')))