Select and Compare Two Datetime Columns from Different Table Without Having Any Relation

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