CODEWITHSUNDEEP
t-sqlnullrecords
Lee Hopkins
Lee Hopkins

Reputation: 135

Return row(s) when no values are found in the query

I have a table MyTable that is populated by BizTalk from two outside agencies (AGC1 and AGC2) every day. I have recently run into a problem where AGC2 has been sending a blank file, so no records get updated.

I need to run a query like below, that would return

AGC1   01/28/2016
AGC2   NULL

But since I have no records for AGC2 I only get

AGC1   01/28/2016

How can I get AGC2 NULL as in the first resultset?

SELECT Agency, MAX(CONVERT(nvarchar(30), DATEENTERED, 101)), Case when MAX(CONVERT(nvarchar(30), DATEENTERED, 101)) = CONVERT(nvarchar(30), GETDATE(), 101) THEN 'True' ELSE 'False' end
FROM DBO.MYTABLE 
GROUP BY AGENCY

Upvotes: 1

Views: 38

Answers (2)

TT.
TT.

Reputation: 16137

A RIGHT JOIN with a table containing all agencies should do the trick. This version uses a derived table with VALUES:

SELECT 
    ag.Agency, 
    MAX(CONVERT(nvarchar(30), mt.DATEENTERED, 101)), 
    Case when MAX(CONVERT(nvarchar(30), mt.DATEENTERED, 101)) = CONVERT(nvarchar(30), GETDATE(), 101) THEN 'True' ELSE 'False' end
FROM 
    DBO.MYTABLE AS mt
    RIGHT JOIN ( VALUES('AGC1'),('AGC2') ) AS ag(Agency) ON
         ag.Agency=mt.Agency
GROUP BY
    ag.AGENCY

Upvotes: 0

P. Jairaj
P. Jairaj

Reputation: 1033

Have another table containing:

NameTable

Name
-----
AGC1
AGC2

Then use this query:

Select n.Name, max(m.dateentered)
from MyTable m right join NameTable n
on m.Agency = n.Name
group by n.Name

Upvotes: 1

Related Questions