Select and compare two Datetime columns from different table without having any relation
Another possible approach, which will find duplicate dates between two tables and possible duplicate dates in each table:
WITH cte (date_time) AS (
SELECT date_time1
FROM DateTimeTable1
UNION ALL
SELECT date_time2
FROM DateTimeTable2
)
SELECT
date_time,
CASE
WHEN COUNT(*) > 1 THEN 'True'
ELSE 'False'
END AS is_match
FROM cte
GROUP BY date_time
ORDER BY date_time
How to compare date fields between two tables and get the less or equal date from the second table
While I'm not sure if this will scale well (if you have more than 100k rows) this will bring back the results which you want.
Theoretically, the correct way for you to do this, in a fashion which will scale well, would be to have a view where you utilize RANK() and join both of these tables together, though this was the quick and easy way. Please try this and let me know if it meets your requirements.
For your edification, I have left both of the dates in there for you to be able to compare them.
SELECT
A.ID
,A.FECHA OLDDATE
,B.FECHA CORRECTDATE
FROM #TMPA A
LEFT OUTER JOIN #TMPB B ON 1=1
WHERE 1=1
AND B.FECHA = (
SELECT MAX(FECHA)
FROM #TMPB
WHERE FECHA <= A.FECHA)
How can I Compare two tables without relationship (1 table with 40k records and the other one with 7k records)?
You don't make it easy, but here is a working solution. In the future try to use this type of code in your question so we can focus on the query and solution. I have edited some of your dates to make the example work.
EDIT: New Code
declare @cal table (
calID int not null
, date_ date not null
, isWeekday bit not null
, isHoliday bit not null
, year_ int not null
);
insert into @cal (calID, date_, isWeekday, isHoliday, year_)
select 1, '1-Jan-2010', 1, 1, 2010 union all
select 2, '2-Jan-2010', 0, 0, 2010 union all
select 3, '3-Jan-2010', 0, 0, 2010 union all
select 4, '4-Jan-2010', 1, 0, 2010 union all
select 5, '5-Jan-2010', 1, 0, 2010 union all
select 6, '6-Jan-2010', 1, 0, 2010 union all
select 7, '7-Jan-2010', 1, 0, 2010 union all
select 8, '8-Jan-2010', 1, 0, 2010 union all
select 9, '9-Jan-2010', 0, 0, 2010 union all
select 10, '10-Jan-2010', 0, 0, 2010 union all
select 11, '11-Jan-2010', 1, 0, 2010 union all
select 12, '12-Jan-2010', 1, 0, 2010 union all
select 13, '13-Jan-2010', 1, 0, 2010 union all
select 14, '14-Jan-2010', 1, 0, 2010 union all
select 15, '15-Jan-2010', 1, 0, 2010 union all
select 16, '16-Jan-2010', 0, 0, 2010 union all
select 17, '17-Jan-2010', 0, 0, 2010 union all
select 18, '18-Jan-2010', 1, 1, 2010 union all
select 19, '19-Jan-2010', 1, 0, 2010 union all
select 20, '20-Jan-2010', 1, 0, 2010 union all
select 21, '21-Jan-2010', 1, 0, 2010 union all
select 22, '22-Jan-2010', 1, 0, 2010 union all
select 23, '23-Jan-2010', 0, 0, 2010 union all
select 24, '24-Jan-2010', 0, 0, 2010 union all
select 25, '25-Jan-2010', 1, 0, 2010 union all
select 26, '26-Jan-2010', 1, 0, 2010;
declare @date table(
dateID int identity(1,1) not null
, date2 date null
, date3 date null
, date4 date null
, date5 date null
);
insert into @date (date2, date3, date4, date5)
select '6/20/2009', NULL, NULL, '7/19/2009' union all
select '1/2/2010', NULL, NULL, '1/19/2010' union all
select '1/4/2010', NULL, NULL, '1/15/2010' union all
select '1/2/2010', NULL, NULL, '1/22/2010' union all
select '9/17/2009', NULL, NULL, '10/26/2009' union all
select '6/4/2009', NULL, NULL, '6/24/2009';
;with cte as (
select dateid
, b.date_
from @date
cross apply (
Select Top (DateDiff(DAY,date2,IsNull(date5,date2))+1) DateAdd(DAY, -1+Row_Number() Over (Order By 1/0),date2) date_
from master..spt_values n1
) b
)
select distinct b.dateID
, c.date2
, c.date5
, count(*) over(order by b.dateid) cnt
from @cal a
join cte b
on a.date_ = b.date_
join @date c
on b.dateid = c.dateid
where isWeekday = 1
and isHoliday = 0
you could change out the from master..spt_values n1
for something like this:
;with E00(n) as (select 1 union all select 1)
, E02(n) as (select 1 from E00 a, E00 b)
, E04(n) as (select 1 from E02 a, E02 b)
, E08(n) as (select 1 from E04 a, E04 b)
, E16(n) as (select 1 from E08 a, E08 b)
, E32(n) as (select 1 from E16 a, E16 b)
, cteTally(d) as (select row_number() over (order by n) from E32)
, cte as (
select dateid
, b.date_
from @date
cross apply (
select top (datediff(day,date2,isnull(date5,date2))+1) dateadd(day, -1+row_number() over(order by 1/0),date2) date_
from cteTally
) b
)
select distinct b.dateID
, c.date2
, c.date5
, count(*) over(order by b.dateid) cnt
from @cal a
join cte b
on a.date_ = b.date_
join @date c
on b.dateid = c.dateid
where isWeekday = 1
and isHoliday = 0
Comparing two DATETIME columns in SQL
The solution is simpler to convert the columns in Date
it depends if both columns are datetime or one is date
SELECT Table1.Id,Table1.Startdate
FROM Table1
INNER JOIN Table2 ON Table1.Id = Table2.Id
where (cast(Table1.Startdate as date))=(cast(Table2.AdmitDateTime as date))
SELECT Table1.Id,Table1.Startdate
FROM Table1
INNER JOIN Table2 ON Table1.Id = Table2.Id
where (cast(Table1.Startdate as date))=Table2.AdmitDate
Example
How to compare two column from different table and differentiate in mysql?
use left and right join
for output 1
select t2.roll from table1 t1
right
join table2 t2 on t1.roll=t2.roll
where t1.roll is null
for output 2
select t1.roll from table1 t1
left
join table2 t2 on t1.roll=t2.roll
where t2.roll is null
Related Topics
Calculate Total Working Hour of Employee in SQL With Only 1 Column
Sql - How to Sum/Aggregate Certain Rows in a Table
How to Get Depatment Wise Max Salary as Well as Name of Employee Having It
Select Every Employee That Has a Higher Salary Than the Average of His Department
Better Techniques for Trimming Leading Zeros in SQL Server
How to Include Results of SQL Count If Count=0
Multiple Insert With Same Column (Ms Access)
Selecting First and Last Values in a Group
Sql Merge Two Rows With Same Id But Different Column Values (Oracle)
How to Get the Numeric Part from a String Using T-Sql
Getting Two Counts and Then Dividing Them
Using a Select Statement for Columns With Spaces in It
How to Replace Single-Quote With Double-Quote in SQL Query - Oracle 10G
Mysql Workbench Closes Unexpectedly
If a Query Return Empty Row I Need to Get Its as Zero Value,How Can Check It in SQL
Sql Query to Get Number of Times a Field Repeats for Another Specific Field