SQL Server SQL Query That Returns Rows when a date does not fall exactly on an anniversary which could be a number of years in the future

7xllpg7q 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(110)

I have two tables we'll call them TableA and TableB .

CREATE TABLE TableA 
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    DateOne Date Not NULL,
);

CREATE TABLE TableB 
(
    Id int IDENTITY(1,1) PRIMARY KEY,
    TableAId int NOT NULL,
    DateTwo Date Not NULL,
    FOREIGN KEY (TableAId) References TableA(Id)
);

I insert the following data.

INSERT INTO TableA (DateOne)
VALUES ('2020-01-02');

INSERT INTO TableB (TableAId, DateTwo)
VALUES (1,'2021-01-01'),
       (1,'2022-02-02'),
       (1,'2023-01-01');

I want to write a query that returns all of the Id's of B where the date is exactly one or more calendar year minus one day from A's date one.

Pseudo code:

SELECT A.Id, b.Id,  
FROM A 
LEFT JOIN B ON b.AId = A.Id
WHERE (B.DateTwo is anniversary of A.DateOne minus one day.)

The query ran against this data should return the first and last values from the B table as they are equal to exactly one year (minus a day). The middle value should not appear as it is not equal to a an anniversary minus one day.

sql-server

来源：https://stackoverflow.com/questions/76739611/sql-query-that-returns-rows-when-a-date-does-not-fall-exactly-on-an-anniversary

1条答案

按热度按时间

qco9c6ql1#

This is easier in a standards-compliant database , and Transact SQL is not a standards-compliant database. The following suggestion smells approximately like that of @Charlieface with a sign change and runs in TSQL :

create table TableA (
    ID int primary key,
    DateOne date not null
);

create table TableB (
    ID int primary key,
    TableAID int not null references TableA(ID),
    DateTwo date not null
);

insert into TableA(ID, DateOne) values
(1, '2021-01-02');

insert into TableB(ID, TableAId, DateTwo) values
(1, 1, '2021-01-01'),
(2, 1, '2022-02-02'),
(3, 1, '2023-01-01');

select TableA.ID as TableAID,
       TableB.ID as TableBID
from TableA
left join TableB on (
  TableB.TableAID = TableA.ID
  and datepart(month, DateOne) = datepart(month, dateadd(day, 1, DateTwo))
  and datepart(day,   DateOne) = datepart(day,   dateadd(day, 1, DateTwo))
);

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server SQL Query That Returns Rows when a date does not fall exactly on an anniversary which could be a number of years in the future

1条答案

相关问题

热门标签

最新问答