Kristian Francischini

Olá, me ajudaram em outro lugar, então venho aqui deixar a solução para que se alguém mais precisar tenha aqui para ler. Obrigado. SELECT MUser.MUser_USER_NUMBER, MUser.MUser_USER_NAME, CASE WHEN Mpassport.Mpassport_EXPIRE_DATE < '2021/02/16' AND Mpassport.Mpassport_EXPIRE_DATE > '1900/01/01' THEN Mpassport.Mpassport_EXPIRE_DATE END As Mpassport_EXPIRE_DATE, MCar.MCar_PLATE, CASE WHEN MCar.MCar_SHAKEN_EXPIRE_DATE < '2021/02/16' AND MCar.MCar_SHAKEN_EXPIRE_DATE > '1900/01/01' THEN MCar.MCar_SHAKEN_EXPIRE_DATE END As MCar_SHAKEN_EXPIRE_DATE, CASE WHEN MCar.MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16' AND MCar.MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01' THEN MCar.MCar_JIBAISEKI_EXPIRE_DATE END As MCar_JIBAISEKI_EXPIRE_DATE, CASE WHEN MCar.MCar_INSURANCE_EXPIRE_DATE < '2021/02/16' AND MCar.MCar_INSURANCE_EXPIRE_DATE > '1900/01/01' THEN MCar.MCar_INSURANCE_EXPIRE_DATE END As MCar_INSURANCE_EXPIRE_DATE FROM MUser LEFT JOIN MPassport On MPassport.Mpassport_USER_NUMBER = MUser.MUser_USER_NUMBER AND MPassport.Mpassport_EXPIRE_DATE < '2021/02/16' AND MPassport.Mpassport_EXPIRE_DATE > '1900/01/01' LEFT JOIN MDrive On MDrive.MDrive_USER_NUMBER = MUser_USER_NUMBER AND MDrive.MDrive_EXPIRE_DATE < '2021/02/16' AND MDrive.MDrive_EXPIRE_DATE > '1900/01/01' LEFT JOIN MCar On MCar.MCar_USER_NUMBER = MUser_USER_NUMBER AND ( (MCar_SHAKEN_EXPIRE_DATE < '2021/02/16' AND MCar_SHAKEN_EXPIRE_DATE > '1900/01/01') OR (MCar_JIBAISEKI_EXPIRE_DATE < '2021/02/16' AND MCar_JIBAISEKI_EXPIRE_DATE > '1900/01/01') OR (MCar_INSURANCE_EXPIRE_DATE < '2021/02/16' AND MCar_INSURANCE_EXPIRE_DATE > '1900/01/01') ) WHERE MPassport_EXPIRE_DATE IS NOT NULL OR MDrive_EXPIRE_DATE IS NOT NULL OR MCar_SHAKEN_EXPIRE_DATE IS NOT NULL OR MCar_JIBAISEKI_EXPIRE_DATE IS NOT NULL OR MCar_INSURANCE_EXPIRE_DATE IS NOT NULL

Olá, estou batendo a cabeça aqui, não sei onde estou errando, ainda estou aprendendo então qualquer ajuda será bem vinda. Tabela MUser | MUser_USER_NUMBER | MUser_USER_NAME | --------------------------------------- | 1 | User1 | Tabela MPassport | MPassport_USER_NUMBER| MPassport_NUMBER | MPassport_EXPIRE_DATE | ---------------------------------------------------------------------- | 1 | 12345678 | 2021-01-28 | Tabela MCar | 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 | Tabela MDrive | MDrive_USER_NUMBER| MDrive_NUMBER | MDrive_EXPIRE_DATE | ---------------------------------------------------------------------- | 1 | 12345678 | 2022-10-28 | Query usada SELECT MUser.MUser_USER_NUMBER As MUser_USER_NUMBER ,MUser.MUser_USER_NAME AS MUser_USER_NAME ,(SELECT ISNULL(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(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(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(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(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' Resultado esperado | 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 | Resultado recebido | 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 | Alguma alma caridosa pode ajudar um novato que está perdido? Essa query na verdade ainda tem mais coisa, mas se conseguir funcionar isso o resto seria somente adicionar as outras tabelas. De início muito obrigado pela paciencia de ler tudo isso. -------------- 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' ) O sistema será o seguinte: ※ apenas uma parte do sistema Um usuário cadastra os dados de expiração dos documentos MDrive_EXPIRE_DATE = Carteira de motorista MCar_SHAKEN_EXPIRE_DATE = Documento do carro MCar_JIBAISEKI_EXPIRE_DATE = Seguro obrigatório do carro MCar_INSURANCE_EXPIRE_DATE = Seguro contra terceiros do carro Fora isso tem outros documentos, apenas coloquei esse para não ficar longo. Na parte da carteira de motorista seria apenas uma, mas na parte do carro, o usuário poderia ter mais de um. O que eu estou tentando fazer seria para que ao logar no sistema ativasse esse código que olharia os documentos que irão vencer entre hoje e uma data determinada para o documento, no exemplo aí eu coloquei mais ou menos 3 meses para frente. Na parte do carro, pode acontecer de um dos dados não serem imputados, pois seria controle do escritório, e o funcionário ainda não entregou os documentos, por isso coloquei a clausula >1900-01-01, pois o default da data no DB seria essa. Para que o sistema ignorasse esses carros que estão sem os dados corretos. A data <2021/02/16 (3 meses a partir da data de login no sistema) seria o máximo que procuraria, para avisar o funcionário que o documento venceria em tal data, para ter tempo da pessoa renovar. Coloquei o código online para quem puder ajudar. SQL Fiddle