CODEWITHSUNDEEP
sqlsql-servermax
user1811852
user1811852

Reputation: 75

SQL - query to report MAX DATE results or NULL results

I have an SQL database with 3 tables:

  1. Customer record table, holding master data (each row is unique)
  2. Customer notes, each note data/time stamped (there could be many notes per customer, or none at all)

  3. Product table, showing which customer has bought which product

    Table: tbl_Customer_Records
    CustomerID---- Company Name-----Company Segment----- Promo Code

    Table: tbl_Customer_Notes
    CustomerID---- Note-----Created Date

    Table: tbl_Customer_Products
    CustomerID---- Product Category

What I want is to pull a list of customer records which includes the latest note only, so there are no duplicate lines if multiple notes exist. But I also want my report to include customer records if no notes exist at all. I've achieved the first part of this with a SELECT MAX function, and that works well, the problem is when I add the OR = NULL clause in the final line of code below. This doesn't work, and I can't figure out a solution.

Any suggestions will be greatly appreciated!

SELECT        

[tbl_Customer_Records].[CustomerID], 
[tbl_Customer_Records].[Company Name], 
[tbl_Customer_Records].[Company Segment], 
[tbl_Customer_Records].[Promo Code], 
[tbl_Customer_Notes].[Note],
[tbl_Customer_Products].[Product Category]

FROM            

tbl_Customer_Records
LEFT OUTER JOIN tbl_Customer_Notes
ON tbl_Customer_Records.CustomerID = tbl_Customer_Notes.CustomerID 
LEFT OUTER JOIN tbl_Customer_Products
ON tbl_Customer_Records.CustomerID = tbl_Customer_Products.CustomerID

WHERE  
[Product Category] in ('Nuts','Bolts','Screws','Spanners') 

AND 

[Created Date] in (SELECT MAX ([Created Date]) FROM tbl.Customer_Notes GROUP BY [CustomerID]) 

OR tbl_Customer_Note.Note is null

Upvotes: 2

Views: 1872

Answers (4)

roman
roman

Reputation: 117400

There're a few tricks to do this kind of query (row_number or join with grouped data), but I think most cleanest one in your case is to use outer apply:

select
   cr.[CustomerID], 
   cr.[Company Name], 
   cr.[Company Segment], 
   cr.[Promo Code], 
   cn.[Note],
   cp.[Product Category]
from tbl_Customer_Records as cr
    left outer join tbl_Customer_Products as cp on cp.CustomerID = cr.CustomerID
    outer apply (
        select top 1
            t.[Note]
        from tbl_Customer_Notes as t
        where t.[CustomerID] = cr.[CustomerID]
        order by t.[Created_Date] desc
    ) as cn
where
    cp.[Product Category] in ('Nuts','Bolts','Screws','Spanners')

Changed all clumsy table name.column name to alias.column name, I think it's much more readable this way.

Or:

select
    cr.[CustomerID], 
    cr.[Company Name], 
    cr.[Company Segment], 
    cr.[Promo Code], 
    cn.[Note],
    cp.[Product Category]
from tbl_Customer_Records as cr
    left outer join tbl_Customer_Products as cp on cp.CustomerID = cr.CustomerID
    left outer join tbl_Customer_Notes as cn on
        cn.CustomerID = cr.CustomerID and
        cn.[Created_Date] = (select max(t.[Created_Date]) from tbl_Customer_Notes as t where t.CustomerID = cr.CustomerID)
where
    cp.[Product Category] in ('Nuts','Bolts','Screws','Spanners')

Upvotes: 1

Justin
Justin

Reputation: 9724

Should work, tried with NULL values:

SELECT a.[CustomerID], 
       a.[Company Name], 
       a.[Company Segment], 
       a.[Promo Code], 
       a.[Note],
       a.[Product Category]
FROM (
SELECT        
cr.[CustomerID], 
cr.[Company Name], 
cr.[Company Segment], 
cr.[Promo Code], 
cn.[Note],
cp.[Product Category],
ROW_NUMBER() OVER(PARTITION BY cr.[CustomerID] ORDER BY cn.[Created Date] DESC) as rnk
FROM tbl_Customer_Records cr
LEFT JOIN tbl_Customer_Notes cn
ON cr.CustomerID = cn.CustomerID 
LEFT JOIN tbl_Customer_Products cp
ON cr.CustomerID = cp.CustomerID
WHERE cp.[Product Category] in ('Nuts','Bolts','Screws','Spanners') )a
WHERE a.rnk = 1

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10274

You can add your filter condition in ON predicate to preserve rows from left table and fetch only required matching rows from right table, from first LEFT OUTER JOIN operator. Following query should work: 

SELECT        
CR.[CustomerID], 
CR.[Company_Name], 
CR.[Company_Segment], 
CR.[Promo_Code], 
CN.[Note],
CP.[Product_Category]
FROM            
tbl_Customer_Records CR
LEFT OUTER JOIN tbl_Customer_Notes CN
ON CR.CustomerID = CN.CustomerID AND CN.[Created_Date] in (SELECT MAX ([Created_Date]) 
                                                           FROM tbl_Customer_Notes 
                                                           WHERE CR.CustomerID =  tbl_Customer_Notes.CustomerID 
                                                           GROUP BY [CustomerID]) 
LEFT OUTER JOIN tbl_Customer_Products CP
ON CR.CustomerID = CP.CustomerID
WHERE  
[Product_Category] in ('Nuts','Bolts','Screws','Spanners')

Upvotes: 1

Pankaj Sharma
Pankaj Sharma

Reputation: 1853

try to use CustomerID not in (select CustomerID from tbl_Customer_Note)

Upvotes: 0

Related Questions