Compare datetime of two different tables and find not between columns
sql compare dates without time
compare datetime in sql
how to compare two dates in where clause in sql
sql compare dates greater than
sql compare dates with time
compare datetime with current datetime in sql
sql datetime comparison in where clause
I have employee shift table, and then attendance table. I would like to know the shifts which the employee has not attended.
Below is the query to create sample tables.
DECLARE @InsideOutsideTable TABLE ( FromTime DATETIME, ToTime DATETIME ) DECLARE @ShiftTable TABLE ( FromDateTime DATETIME, ToDateTime DATETIME ) INSERT INTO @ShiftTable VALUES ('2018-05-02 07:30:00.000','2018-05-02 12:30:00.000') INSERT INTO @ShiftTable VALUES ('2018-05-02 01:30:00.000','2018-05-02 16:30:00.000') INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 07:24:00.000','2018-05-02 11:47:00.000') INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 18:07:00.000','2018-05-02 18:32:00.000')
Below is my query to bring the shifts which employee not attended.
SELECT S.* FROM @ShiftTable S CROSS APPLY @InsideOutsideTable T WHERE S.FromDateTime NOT BETWEEN T.FromTime AND T.ToTime AND S.ToDateTime NOT BETWEEN T.FromTime AND T.ToTime
But it is returning every row. Can you help me achieve my requirement?
FromDateTime ToDateTime 2018-05-02 01:30:00.000 2018-05-02 16:30:00.000
A big thanks in advance.
you may use this
DECLARE @InsideOutsideTable TABLE ( FromTime DATETIME, ToTime DATETIME ) DECLARE @ShiftTable TABLE ( ID Int, FromDateTime DATETIME, ToDateTime DATETIME ) INSERT INTO @ShiftTable VALUES (1,'2018-05-02 07:30:00.000','2018-05-02 12:30:00.000') INSERT INTO @ShiftTable VALUES (2,'2018-05-02 13:30:00.000','2018-05-02 16:30:00.000') INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 07:24:00.000','2018-05-02 11:47:00.000') INSERT INTO @InsideOutsideTable VALUES ('2018-05-02 18:07:00.000','2018-05-02 18:32:00.000') SELECT S.* FROM @ShiftTable S WHERE ID NOT IN ( SELECT S.ID FROM @ShiftTable S INNER JOIN @InsideOutsideTable T ON ( ( T.FromTime <= S.ToDateTime AND T.FromTime >= S.FromDateTime ) OR ( T.ToTime <= S.ToDateTime AND T.ToTime >= S.FromDateTime ) OR ( T.FromTime <= S.FromDateTime AND T.ToTime >= S.ToDateTime ) ) AND T.FromTime <= S.ToDateTime )
take care that you defined 1:30 not 13:30
sql, Compare datetime of two different tables and find not between columns Below is my query to bring the shifts which employee not attended. Create your own query to find unmatched records. One the Create tab, in the Queries group, click Query Design. In the Show Table dialog box, double-click the table that has unmatched records, and then double-click the table that has related records. Close the Show Table dialog box.
Use this query:
SELECT * FROM (SELECT S.*, (SELECT count(*) FROM @InsideOutsideTable T WHERE T.FromTime BETWEEN S.FromDateTime AND S.ToDateTime OR T.ToTime BETWEEN S.FromDateTime AND S.ToDateTime) AS cnt FROM @ShiftTable S) AS tbl WHERE cnt = 0
Compare two tables and find dates present in one and missing in , As far as you have not provided an expected result I've used this: emp_id | 01/01/2017 | 02/01/2017 | 03/01/2017 | 04/01/2017 | 05/01/2017 Comparing two tables (on the same database) to find matching column names or missing columns which also shows the following information – data types, values null or empty columns.
I think you are looking for full overlaps between between the two tables.
If so,the simplest approach is a
LEFT JOIN or
SELECT S.* FROM @ShiftTable S LEFT JOIN @InsideOutsideTable T ON S.FromDateTime <= T.FromTime AND S.ToDateTime >= T.ToTime WHERE T.ToTime IS NULL;
Here is a rextester.
Solved: Comparing two dates from different tables, Solved: Hello, Very beginner with PowerBI, i'm trying to compare two dates to error : a single value for column 'start time' in table incident-sla cannot be determined. If you are trying to use the max or min value from the many side to get the Comparison of two tables in Excel for finding matches in columns. We have two tables of orders copied into one worksheet. You need to compare the data of the two tables in Excel and check which positions are in the first table but not in the second one.
How to Compare Date in SQL Server Query? Finding , Finding All Rows Between Two Dates It may or may not, it entirely depends on upon data in your table. So if you have any row which has the same date but different time then this query will not work. them don't even know that how SQL Server will compare their condition with the data in that column. Compare Two Table using JOIN. This is the easiest but user has to do some additional work to get the correct result. In this approach you can join the two tables on the primary key of the two tables and use case statement to check whether particular column is matching between two tables.
Ways to compare and find differences for SQL Server tables and data, Let's say that we have two similar tables in different databases and we values of specific columns that are not common between two tables. From DAX via table A, I'd like to say if the date of Column B is to Column A or fell between the date of Column A and Column B , 1, else 0. I can't seem to find the right function/formula to allow via DAX for any DAX queries to "see" column b from a DAX query within table A. Hopefully that makes sense.
Compare two tables in Access and find only matching data, When you want to compare two Access tables and find matching data, you can If there is not already an existing relationship between the tables on the fields that Access provides several ways to add these sample tables to a database. Clear the check box in the Show row of the Major column in the query design grid. Finding non-matching values between two tables in Power BI Power GI. Calculate Difference This Year vs Last Year in Power BI with DAX Excel How To Compare Two Tables in a Second
- Aren't you missing an Employee key from your sample data?
- I do have in main tables. Since this question is only for a logic, I have given here only datetime stamps.
- This as well brings all rows
- those two shifts are interacted !!
- First shift 07:30 to 12:30 and there is a check-in happened in between this timings. Employee came in at 07:24 and went out at 11:47. He attended this shift. Second shift 01:30 PM to 04:30 PM. There is no check-in happened in between this shift. I would like to return that
- i've updated my answer , take care that you defined 1:30 not 13:30
- I see your updated answer but it doesn't bring any output. :-(
- This query brings first shift every the employee attends. First shift 07:30 to 12:30 and there is a check-in happened in between this timings. There is no check-in happened in between 01:30 PM and 04:30PM, second shift timing. I would like to return that.
- I thought attend means all duration of check-in must occurred in duration of shift but you are right every overlap of check-in and shift time means attend, so i changed AND to OR in where condition of query that bring all attend, it must work