CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
StackTrace
StackTrace

Reputation: 9416

Get all parent rows that do not have a row for current date in child table?

SELECT 
    [dbo].[Mission].[MissionId]  
FROM 
    [dbo].[Mission] 
LEFT OUTER JOIN 
    [dbo].[Report] ON [dbo].[Mission].[MissionId] = [dbo].[Report].[MissionId] 
WHERE 
    [dbo].[Report].ReportDate IS NULL 
ORDER BY 
    [dbo].[Mission].[MissionId]

How can I change the above query such that it gives me all MissionId's from table [dbo].[Mission] that do not have a row in table [dbo].[Report] where [dbo].[Report].ReportDate is today?

MissionId is the primary key in table Mission and a foreign key in table Report. So I want to get all missions that do not have a row in table Report for the current date.

Upvotes: 0

Views: 62

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

I've introduced some aliases to make the query easier to read, and added the needed condition. I've also changed the WHERE clause, not sure if that's required:

SELECT m.[MissionId]  
FROM [dbo].[Mission] m LEFT OUTER JOIN [dbo].[Report] r
  ON m.[MissionId] = r.[MissionId] 
  AND r.ReportDate = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
WHERE r.MissionId IS NULL 
ORDER BY m.[MissionId]

This assumes that ReportDate contains dates with the time portions set to midnight. If that's not so, then a slightly more complex query is required:

SELECT m.[MissionId]  
FROM [dbo].[Mission] m 
WHERE NOT EXISTS(select * from dbo.Report r
                where r.MissionID = m.MissionID and
                   r.ReportDate >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and
                   r.ReportDate < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)
                )
ORDER BY m.[MissionId]

GETDATE() returns the current date and time. I'm using a couple of tricks with DATEADD and DATEDIFF to take that value and turn it into the current date at midnight, and (in the second query) tomorrow's date at midnight.

Second query as a fully runnable query:

declare @mission table (MissionID int not null);
insert into @mission (MissionID) select 1 union all select 2;
declare @report table (MissionID int not null,ReportDate datetime not null);
insert into @report (MissionID,ReportDate)
select 2,GETDATE() union all select 1,DATEADD(day,-1,GETDATE());

SELECT m.[MissionId]  
FROM @mission m 
WHERE NOT EXISTS(select * from @report r
                where r.MissionID = m.MissionID and
                   r.ReportDate >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and
                   r.ReportDate < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)
                )
ORDER BY m.[MissionId]

Result:

MissionId
-----------
1

Upvotes: 4

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

select
  m.MissionId
from Mission m 
left join Report r
  on  m.MissionId = r.MissionId
  and day(r.ReportDate) = day(getdate())
  and month(r.ReportDate) = month(getdate())
  and year(r.ReportDate) = year(getdate())
WHERE r.ReportDate is null
ORDER BY m.MissionId

Upvotes: 0

Related Questions