how to join sql tables in my desired way

Question

I have 2 sql tables named mtblAttendance and mtblLeave_Data.

I need to get the all dates from mtblLeave_Data when User was on leave depending on absent in mtblAttendance.

In my mtblAttendance for every leave there is a row, but if a user on leave for a period so there is no unique row, there are just two columns Leave_From and Leave_To (or it may be a single entry where Leave_From= Leave_To).

For getting the absent dates of user I wrote the query

USE [ILeave]

ALTER procedure [dbo].[Attendance_Report]
@Date1 datetime,
@Date2 datetime,
@User_Id nvarchar(50)
as begin


SELECT distinct
a.Sno,
a.[Login_Date],
a.[Week_Day],
a.[In_Time],
a.[Out_Time],
a.Attendance_Status,
a.Half_Full,
a.Leave_Type,

(convert(varchar(max),floor (abs(cast(datediff(mi, a.Out_Time, a.In_Time) AS int) / 60)))+ '.'+ convert(varchar(max),(abs(cast(datediff(mi, a.Out_Time, a.In_Time) AS int) % 60))))  as Hrs


, l.[Sno] 
, l.[Leave_ID] 
, l.[User_Id]
, l.[Dept_To]
, l.[Leave_Type] 
, l.[Total_Leave_HR] 
, l.[Leave_From] 
, l.[Leave_To] 
, l.[Leave_Half_Full]
, l.[Comments]
, l.[Leave_Status]
FROM 
[mtblAttendance] a
LEFT JOIN [mtbl_Leave_Data] l 
    ON a.[Login_Date] BETWEEN l.[Leave_From] AND  l.[Leave_To]
    AND l.[User_Id] = a.[User_Id]  where a.Login_Date between @Date1 and @Date2 and a.User_Id=@User_Id order by Login_Date  
end

enter image description here

I think I have an idea what you mean, but I'm not sure. An example with some real data would be nice. It makes your question much more clear. Do I understand correctly that your problem mainly is that you want records as a result with dates that are part of a period? So you can't really select the days but you need a way to dynamically generate the days of the periods? — SonOfGrey, Jan 17 '14 at 09:57
If your problem is what I think it is, you should be able to join with a table or function that contains all dates in a period. [Here](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) are some good examples of how to do that. The simplest begin: create a table with a date column that contains dates for lots of years. You can join with it whenever you want. — SonOfGrey, Jan 17 '14 at 10:07

Mike Sherrill 'Cat Recall' · Answer 1 · 2014-01-20T10:52:37.823

0

Join expressions aren't limited to using the equals sign. Use "between" in the join expression. Something along these (untested) lines should work.

select distinct A.Login_Date  
from mtblAttendance A
inner join mtbl_Leave_Data L 
    on A.User_id = L.User_id
   and A.Login_date between L.Leave_From and L.Leave_To
where A.User_Id = 'sasi' AND A.Attendance_Status='A'

Depending on what you're trying to do, you might need to change the inner join to a left outer join. A left outer join will preserve all login dates from mtblAttendance, regardless of whether they satisfy the join condition. (Those rows will be filtered by the WHERE clause, of course.)

edited Jan 20 '14 at 10:52

answered Jan 17 '14 at 10:18

Mike Sherrill 'Cat Recall'

91,602
17
122
185

it gives repeated rows. – Gaurav Jan 20 '14 at 06:31
That's because you have either multiple rows in mtbl_Leave_Data that satisfy the join condition for some values of mtblAttendance.Login_Date, or you have multiple rows in mtblAttendance that have the same value for login date. Using `SELECT DISTINCT` will eliminate them. Paste CREATE TABLE statements and INSERT statements into your question for more details. – Mike Sherrill 'Cat Recall' Jan 20 '14 at 10:58

talegna · Answer 2 · 2014-01-20T10:16:43.587

0

The following query should return the leave record assigned

SELECT 
    a.[Login_Date]
    , l.[Sno] 
    , l.[Leave_ID] 
    , l.[User_Id]
    , l.[Dept_To]
    , l.[Leave_Type] 
    , l.[Total_Leave_HR] 
    , l.[Leave_From] 
    , l.[Leave_To] 
    , l.[Leave_Half_Full]
    , l.[Comments]
    , l.[Leave_Status]
FROM 
    [mtblAttendance] a
    LEFT JOIN [mtbl_Leave_Data] l ON a.[Login_Date] BETWEEN l.[Leave_From] AND  l.[Leave_To]
WHERE 
    a.User_Id = 'sasi' 
    AND a.Attendance_Status='A'

I put it into a fiddle, but with no data so all I can say is that the query parses.

As someone has previously stated, it is common to have tables with dates in, whereby queries requiring every date in a 2 year period can quickly be assessed.

Updated SQL:

SELECT DISTINCT
    a.[Login_Date]
    , l.[Sno] 
    , l.[Leave_ID] 
    , l.[User_Id]
    , l.[Dept_To]
    , l.[Leave_Type] 
    , l.[Total_Leave_HR] 
    , l.[Leave_From] 
    , l.[Leave_To] 
    , l.[Leave_Half_Full]
    , l.[Comments]
    , l.[Leave_Status]
FROM 
    [mtblAttendance] a
    LEFT JOIN [mtbl_Leave_Data] l 
        ON a.[Login_Date] BETWEEN l.[Leave_From] AND  l.[Leave_To]
        AND l.[userId] = a.[user_id] -- Ensure only attendance/leave for the same user being linked
WHERE 
    a.User_Id = 'sasi' 
    AND a.Attendance_Status='A'

edited Jan 20 '14 at 10:16

answered Jan 17 '14 at 10:20

talegna

2,407
2
19
22

it also gives repeated rows. – Gaurav Jan 20 '14 at 06:32
If they're fully repeated, you can do a `SELECT DISTINCT` how about you update the fiddle I created to include some data so that it's easier for us to see if we're helping. Are there duplicate or conflicting records for the 'sasi' user in the attendance or leave tables? (i.e. is there 2 leave entries for a single date?) I did notice that my query wasn't limiting the user id of the leave table so it is possible that this is causing the problem as if another user has leave on a date which overlaps a 'sasi' attendance there will be multiple lines. – talegna Jan 20 '14 at 09:32
see my updated quesry. It gives repeted records,, which I marked with red pen, see the image attatched. – Gaurav Jan 20 '14 at 09:59
Unfortunately there's not really enough information in that screenshot, some "real data" would be helpful as the interface is obviously stripping out various columns which it doesn't require, and this means the two rows "appear" identical when actually there might be differences in any of the non-displayed columns. – talegna Jan 20 '14 at 12:44

how to join sql tables in my desired way

2 Answers2