Return A Null Value 2.0

Question

First, thanks for the help. My previous post was answered, and I marked it accordingly. I have a very similar problem. The follow query works:

select B.LastName, Count(Distinct FileNumber) as UnderReview
from tblCsCases AS A RIGHT OUTER JOIN tblCtAttorney AS B
ON A.ProsecutingAttorney = B.BarCode
where A.EnterDate >= '01/01/2017' and A.EnterDate <= '12/31/2017 23:59:59.999' and
B.PAFlag = 'Y' and
A.Status = 'REVIEW'
Group by B.LastName

However, it is not returning Null values for attorneys who do not fit the above criteria.  This below query that this forum just help me with and it works:

select B.LastName, Count(Distinct A.FileNumber) as UnderReview
from tblCsCases AS A RIGHT OUTER JOIN tblCtAttorney AS B
ON A.ProsecutingAttorney = B.BarCode
where A.EnterDate >= '01/01/2017' and A.EnterDate <= '12/31/2017 23:59:59.999' and
B.PAFlag = 'Y' and
A.Status = 'REVIEW'
Group by B.LastName

Any idea on why the first query is not return null values?

---

Kurt

Answer 1

Try ,

select B.LastName, Count(Distinct A.FileNumber) as UnderReview 
from  tblCtAttorney AS B  LEFT JOIN tblCsCases AS A
ON A.ProsecutingAttorney = B.BarCode
and A.EnterDate >= '01/01/2017' and A.EnterDate <= '12/31/2017 23:59:59.999' and A.Status = 'REVIEW'
where 
B.PAFlag = 'Y' 
Group by B.LastName

You need not create another thread. 

Even though LEFT JOIN is mentioned , few column conditions from table A are mentioned in WHERE clause and it would filter rows from B as well. Try above query and let forum know

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Try:

SELECT
    B.LastName,
    COUNT(DISTINCT A.FileNumber) AS UnderReview
FROM
    tblCsCases AS A
    RIGHT OUTER JOIN
    tblCtAttorney AS B
    ON A.ProsecutingAttorney = B.BarCode
    A.EnterDate >= '20170101'
    AND A.EnterDate < '20180101'
    AND A.Status = 'REVIEW'
WHERE
    B.PAFlag = 'Y'
GROUP BY
    B.LastName;

---

AMB

Some guidelines for posting questions...

AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

Answer 3

I had a very nice blog post explaining this problem on BeyondRelational website. Unfortunately, this website doesn't exist anymore and I didn't have a chance to re-write that blog as TechNet article yet (that it's on my ToDo list).

If you're using RIGHT or LEFT JOIN, then you need to put conditions into the JOIN condition. BTW, for left to right type of the people (e.g. not Hebrew or Arabic or few other languages) it is much easier to understand LEFT JOIN rather than right join. 

So, I'd re-write your query this way:

select B.LastName, Count(Distinct A.FileNumber) as UnderReview 
from tblCtAttorney AS B LEFT JOIN dbo.tblCsCases ON
ON A.ProsecutingAttorney = B.BarCode
AND A.EnterDate >= '20170101' and A.EnterDate < '20180101'
AND A.Status = 'REVIEW'
WHERE B.PAFlag = 'Y' 

Group by B.LastName

Also it's a very bad idea to use tbl prefix for table names. 

Please also note how I re-wrote your date conditions. Aaron Bertrand has a nice blog on this topic

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 4

Your suggestion worked.  I am now trying to join another table, and I get the following error when I run the below query:

Msg 4104, Level 16, State 1, Line 3

The multi-part identifier "A.FileNumber" could not be bound.

SELECT B.LastName, Count(Distinct A.FileNumber) AS ChargingDocuments
FROM tblCsEvents AS A RIGHT OUTER JOIN tblCtAttorney AS B
INNER JOIN tblCsCases C on C.FileNumber = A.FileNumber
    ON A.ProsecutingAttorney = B.BarCode
    AND A.EventCode IN ('CSMISINF','CSFELCOM','FLINFAIC','FLINFS','FLINFW','FLCMPAIC','FLCMATT','FLCMPCON','FLFCMPS','FLFCMPW','FLFUGCMP','GJIS','GJISAIC','RFFILE','TFLINFS')
    AND C.EnterDate >= '01/01/2017' AND C.EnterDate <= '12/31/2017 23:59:59.999'
WHERE B.PAFlag = 'Y'
Group by B.LastName
Order by B.LastName

---

Kurt

Answer 5

I no longer get the error, but I no longer get the Null values for the attorneys.  This query works:

SELECT
    B.LastName, Count(Distinct A.FileNumber) AS ChargingDocuments
FROM tblCsEvents AS A RIGHT OUTER JOIN tblCtAttorney AS B
ON A.ProsecutingAttorney = B.BarCode
    AND A.EventCode IN ('CSMISINF','CSFELCOM','FLINFAIC','FLINFS','FLINFW','FLCMPAIC','FLCMATT','FLCMPCON','FLFCMPS','FLFCMPW','FLFUGCMP','GJIS','GJISAIC','RFFILE','TFLINFS')
    AND A.EventDate >= '01/01/2017' AND A.EventDate <= '12/31/2017 23:59:59.999'
WHERE B.PAFlag = 'Y'
Group by B.LastName
Order by B.LastName

However, when I add the new table, I no longer get the Null values in the following query:

SELECT
    B.LastName, Count(Distinct A.FileNumber) AS ChargingDocuments
FROM tblCsEvents AS A RIGHT OUTER JOIN tblCtAttorney AS B
ON A.ProsecutingAttorney = B.BarCode
INNER JOIN tblCsCases C on C.FileNumber = A.FileNumber
    AND A.EventCode IN ('CSMISINF','CSFELCOM','FLINFAIC','FLINFS','FLINFW','FLCMPAIC','FLCMATT','FLCMPCON','FLFCMPS','FLFCMPW','FLFUGCMP','GJIS','GJISAIC','RFFILE','TFLINFS')
    AND C.EnterDate >= '01/01/2017' AND C.EnterDate <= '12/31/2017 23:59:59.999'
WHERE B.PAFlag = 'Y'
Group by B.LastName
Order by B.LastName

I assume I am using the wrong join on the second join.  Any thoughts on how to fix it?

---

Kurt

Answer 6

Ok, I got it to work:

SELECT
    B.LastName, Count(Distinct C.FileNumber) AS ChargingDocuments
FROM tblCsEvents AS A RIGHT OUTER JOIN tblCtAttorney AS B
ON A.ProsecutingAttorney = B.BarCode
left Outer JOIN tblCsCases C on C.FileNumber = A.FileNumber
    AND A.EventCode IN ('CSMISINF','CSFELCOM','FLINFAIC','FLINFS','FLINFW','FLCMPAIC','FLCMATT','FLCMPCON','FLFCMPS','FLFCMPW','FLFUGCMP','GJIS','GJISAIC','RFFILE','TFLINFS')
    AND C.EnterDate >= '01/01/2017' AND C.EnterDate <= '12/31/2017 23:59:59.999'
WHERE B.PAFlag = 'Y'
Group by B.LastName
Order by B.LastName

If I ever meet the guy who created join statements, I am going to punch him in the face!

---

Kurt

Answer 7

Ok, I got it to work:

SELECT
    B.LastName, Count(Distinct C.FileNumber) AS ChargingDocuments
FROM tblCsEvents AS A RIGHT OUTER JOIN tblCtAttorney AS B
ON A.ProsecutingAttorney = B.BarCode
left Outer JOIN tblCsCases C on C.FileNumber = A.FileNumber
    AND A.EventCode IN ('CSMISINF','CSFELCOM','FLINFAIC','FLINFS','FLINFW','FLCMPAIC','FLCMATT','FLCMPCON','FLFCMPS','FLFCMPW','FLFUGCMP','GJIS','GJISAIC','RFFILE','TFLINFS')
    AND C.EnterDate >= '01/01/2017' AND C.EnterDate <= '12/31/2017 23:59:59.999'
WHERE B.PAFlag = 'Y'
Group by B.LastName
Order by B.LastName

If I ever meet the guy who created join statements, I am going to punch him in the face!

---

Kurt

You really should pay attention to what Naomi N posted about dates - read the article and make sure you understand it.

Formatting your code in a more understandable way will also help you (and anyone else who needs to dig into your code) to figure out what it's doing, as will sticking with LEFT joins where possible.