0
Hello, I’m banging my head here, I don’t know where I’m going wrong, I’m still learning so any help will be welcome.
Tabela Muser
| MUser_USER_NUMBER | MUser_USER_NAME |
---------------------------------------
| 1 | User1 |
Mpassport table
| MPassport_USER_NUMBER| MPassport_NUMBER | MPassport_EXPIRE_DATE |
----------------------------------------------------------------------
| 1 | 12345678 | 2021-01-28 |
Mcar table
| MCar_USER_NUMBER | MCar_PLATE | MCar_SHAKEN_EXPIRE_DATE | MCar_JIBAISEKI_EXPIRE_DATE | MCar_INSURANCE_EXPIRE_DATE |
-----------------------------------------------------------------------------------------------------------------------
| 1 | Plate 1 | 2020-12-07 | 2020-12-08 | 2021-07-01 |
| 1 | Plate 2 | 2021-03-01 | 2021-03-02 | 2021-01-01 |
Mdrive table
| MDrive_USER_NUMBER| MDrive_NUMBER | MDrive_EXPIRE_DATE |
----------------------------------------------------------------------
| 1 | 12345678 | 2022-10-28 |
Query used
SELECT MUser.MUser_USER_NUMBER As MUser_USER_NUMBER
,MUser.MUser_USER_NAME AS MUser_USER_NAME
,(SELECT ISNULL(MIN(Mpassport.Mpassport_EXPIRE_DATE), NULL)
FROM MPassPort
WHERE MPassport.Mpassport_EXPIRE_DATE < '2021/02/16'
AND MPassport.Mpassport_EXPIRE_DATE > '1900/01/01'
AND MPassport.Mpassport_USER_NUMBER = MUser_USER_NUMBER ) As Mpassport_EXPIRE_DATE
,(SELECT ISNULL(MIN(MDrive.MDrive_EXPIRE_DATE), NULL)
FROM MDrive
WHERE MDrive.MDrive_EXPIRE_DATE < '2021/02/16'
AND MDrive.MDrive_EXPIRE_DATE > '1900/01/01'
AND MDrive.MDrive_USER_NUMBER = MUser_USER_NUMBER ) As MDrive_EXPIRE_DATE
,MCar.MCar_PLATE AS MCar_PLATE
,(SELECT ISNULL(MIN(MCar.MCar_SHAKEN_EXPIRE_DATE), NULL)
FROM MCar
WHERE MCar.MCar_SHAKEN_EXPIRE_DATE < '2021/02/16'
AND MCar.MCar_SHAKEN_EXPIRE_DATE > '1900/01/01'
AND MCar.MCar_USER_NUMBER = MUser_USER_NUMBER ) As MCar_SHAKEN_EXPIRE_DATE
,(SELECT ISNULL(MIN(MCar.MCar_JIBAISEKI_EXPIRE_DATE), NULL)
FROM MCar
WHERE MCar.MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16'
AND MCar.MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01'
AND MCar.MCar_USER_NUMBER = MUser_USER_NUMBER ) As MCar_JIBAISEKI_EXPIRE_DATE
,(SELECT ISNULL(MIN(MCar.MCar_INSURANCE_EXPIRE_DATE), NULL)
FROM MCar
WHERE MCar.MCar_INSURANCE_EXPIRE_DATE < '2021/02/16'
AND MCar.MCar_INSURANCE_EXPIRE_DATE > '1900/01/01'
AND MCar.MCar_USER_NUMBER = MUser_USER_NUMBER ) As MCar_INSURANCE_EXPIRE_DATE
FROM (((MUser
LEFT JOIN MPassport On MUser.MUser_USER_NUMBER = MPassport.Mpassport_USER_NUMBER)
LEFT JOIN MDrive On MUser_USER_NUMBER = MDrive.MDrive_USER_NUMBER)
LEFT JOIN MCar On MUser_USER_NUMBER = MCar.MCar_USER_NUMBER)
WHERE (SELECT Mpassport_EXPIRE_DATE
FROM MPassPort
WHERE Mpassport_EXPIRE_DATE < '2021/02/16'
AND Mpassport_EXPIRE_DATE > '1900/01/01'
AND Mpassport_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'
OR (SELECT MDrive_EXPIRE_DATE
FROM MDrive
WHERE MDrive_EXPIRE_DATE < '2021/02/16'
AND MDrive_EXPIRE_DATE > '1900/01/01'
AND MDrive_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'
OR (SELECT MCar_SHAKEN_EXPIRE_DATE
FROM MCar
WHERE MCar_SHAKEN_EXPIRE_DATE < '2021/02/16'
AND MCar_SHAKEN_EXPIRE_DATE > '1900/01/01'
AND MCar_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'
OR (SELECT MCar_JIBAISEKI_EXPIRE_DATE
FROM MCar
WHERE MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16'
AND MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01'
AND MCar_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'
OR (SELECT MCar_INSURANCE_EXPIRE_DATE
FROM MCar
WHERE MCar_INSURANCE_EXPIRE_DATE < '2021/02/16'
AND MCar_INSURANCE_EXPIRE_DATE > '1900/01/01'
AND MCar_USER_NUMBER = MUser_USER_NUMBER) < '2021/02/16'
Expected result
| MCar_USER_NUMBER | MUser_USER_NAME | MPassport_EXPIRE_DATE | MDrive_EXPIRE_DATE | MCar_PLATE | MCar_SHAKEN_EXPIRE_DATE | MCar_JIBAISEKI_EXPIRE_DATE | MCar_INSURANCE_EXPIRE_DATE |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | User1 | 2021-01-28 | | Plate 1 | 2020-12-07 | 2020-12-08 | |
| 1 | User1 | 2021-01-28 | | Plate 2 | | | 2021-01-01 |
Result received
| MCar_USER_NUMBER | MUser_USER_NAME | MPassport_EXPIRE_DATE | MDrive_EXPIRE_DATE | MCar_PLATE | MCar_SHAKEN_EXPIRE_DATE | MCar_JIBAISEKI_EXPIRE_DATE | MCar_INSURANCE_EXPIRE_DATE |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | User1 | 2021-01-28 | | Plate 1 | 2020-12-07 | 2020-12-08 | 2021-01-01 |
| 1 | User1 | 2021-01-28 | | Plate 2 | 2020-12-07 | 2020-12-08 | 2021-01-01 |
Some charitable soul can help a newcomer who’s lost?
This query actually still has more stuff, but if it can work that the rest would just add the other tables.
At first thank you very much for the patience to read all this.
Edited
DB
CREATE DATABASE [Teste]
GO
USE Teste
GO
CREATE TABLE MUser (
MUser_ID int IDENTITY(1,1) NOT NULL,
MUser_USER_NUMBER nvarchar(10) NOT NULL,
MUser_USER_NAME nvarchar(50) NOT NULL,
CONSTRAINT PK_MUser_USER_NUMBER PRIMARY KEY (MUser_USER_NUMBER),
);
INSERT MUser (
MUser_USER_NUMBER
,MUser_USER_NAME
)
VALUES (
N'1'
,N'User1'
)
CREATE TABLE MCar (
MCar_ID int IDENTITY(1,1) NOT NULL,
MCar_USER_NUMBER nvarchar(10) NOT NULL,
MCar_PLATE nvarchar(50) NOT NULL,
MCar_SHAKEN_EXPIRE_DATE date NULL,
MCar_JIBAISEKI_EXPIRE_DATE date NULL,
MCar_INSURANCE_EXPIRE_DATE date NULL,
CONSTRAINT PK_MCar_PLATE PRIMARY KEY (MCar_PLATE),
CONSTRAINT FK_MCar_USER_NUMBER FOREIGN KEY (MCar_USER_NUMBER) REFERENCES MUser(MUser_USER_NUMBER)
);
INSERT MCar (
MCar_USER_NUMBER
,MCar_PLATE
,MCar_SHAKEN_EXPIRE_DATE
,MCar_JIBAISEKI_EXPIRE_DATE
,MCar_INSURANCE_EXPIRE_DATE
)
VALUES (
N'1'
,N'Plate1'
,N'2020-12-07'
,N'2020-12-08'
,N'2021-07-01'
)
INSERT MCar (
MCar_USER_NUMBER
,MCar_PLATE
,MCar_SHAKEN_EXPIRE_DATE
,MCar_JIBAISEKI_EXPIRE_DATE
,MCar_INSURANCE_EXPIRE_DATE
)
VALUES (
N'1'
,N'Plate2'
,N'2021-03-01'
,N'2021-03-02'
,N'2021-01-01'
)
CREATE TABLE Mpassport (
Mpassport_ID int IDENTITY(1,1) NOT NULL,
Mpassport_USER_NUMBER nvarchar(10) NOT NULL,
Mpassport_NUMBER nvarchar(50) NULL,
Mpassport_EXPIRE_DATE date NULL,
CONSTRAINT PK_Mpassport_USER_NUMBER PRIMARY KEY (Mpassport_USER_NUMBER),
CONSTRAINT FK_Mpassport_USER_NUMBER FOREIGN KEY (Mpassport_USER_NUMBER) REFERENCES MUser(MUser_USER_NUMBER)
);
INSERT Mpassport (
Mpassport_USER_NUMBER
,Mpassport_NUMBER
,Mpassport_EXPIRE_DATE
)
VALUES (
N'1'
,N'12345678'
,N'2021-01-28'
)
CREATE TABLE MDrive (
MDrive_ID int IDENTITY(1,1) NOT NULL,
MDrive_USER_NUMBER nvarchar(10) NOT NULL,
MDrive_NUMBER nvarchar(50) NULL,
MDrive_EXPIRE_DATE date NULL,
CONSTRAINT PK_MDrive_USER_NUMBER PRIMARY KEY (MDrive_USER_NUMBER),
CONSTRAINT FK_MDrive_USER_NUMBER FOREIGN KEY (MDrive_USER_NUMBER) REFERENCES MUser(MUser_USER_NUMBER)
);
INSERT MDrive (
MDrive_USER_NUMBER
,MDrive_NUMBER
,MDrive_EXPIRE_DATE
)
VALUES (
N'1'
,N'12345678'
,N'2022-10-28'
)
Edited
The system will be as follows: only a part of the system
A user registers the expiration data of documents
Mdrive_expire_date = Driver’s license
Mcar_shaken_expire_date = Car document
Mcar_jibaiseki_expire_date = Compulsory car insurance
Mcar_insurance_expire_date = Third-party car insurance
Other than that there are other documents, I just put this so it doesn’t get long.
On the part of the driver’s license would be only one, but on the part of the car, the user could have more than one.
What I am trying to do would be so that when logging into the system activated this code that would look at the documents that will win between today and a certain date for the document, in the example there I put about 3 months forward.
In the car part, it can happen that one of the data is not imputed, because it would be control of the office, and the employee has not yet delivered the documents, so I put the clause >1900-01-01, because the default date in DB would be this. So the system would ignore those cars that are without the correct data.
The date <2021/02/16 (3 months from the login date on the system) would be the maximum that would look, to warn the employee that the document would expire on such date, to have time to renew the person.
Important [Dit] the question and explain objectively and punctually the difficulty found, accompanied by a [mcve] problem and attempt to solve. To better enjoy the site, understand and avoid closures worth understanding What is the Stack Overflow and read the Stack Overflow Survival Guide (summarized) in Portuguese. It is important to note that each community has its specific criteria (even if the theme is similar), so it is important to know the documentation of each one.
– Bacco
As the conversation went on, it was moved to the chat. Who wants to participate, guide or add something, just click on the link provided.
– Bacco