CODEWITHSUNDEEP
sql-serverdatemax
DeepDiver
DeepDiver

Reputation: 139

Find latest date

Example Table Cust

 CustID   | CustNameTx
 ---------+------
 1        | Paul
 2        | Bob
 3        | Chuck

Example Table Invoices

 Date       | InvCustID
 -----------+------
 19/02/2017 | 2
 19/02/2017 | 1
 19/02/2018 | 2
 19/02/2017 | 3

Example Results

CustID | CustNameTx | Expired
 ------+------------+---------
1      |   Bob      |    19/02/2017
3      |   Chuck    |    19/02/2017

I am attempting to write the SQL code required to generate results as above. In the first table, I have customer records. The second table holds customer invoices with an expiry date. I am attempting to write a code that finds customers whose membership expired 30 days ago and have not renewed yet. Thefore, the code needs to skip those customers who have renewed membership. I am working on Microsoft SQL.

This is what I succeeded to write so far.

SELECT CuFirstNameTx, CuStoreNoTx_N 
FROM T_CuCust 
INNER JOIN T_IIInvoiceItem ON IICustID=CuCustID
WHERE DATEDIFF (d, IIShipDate_N,GetDate()) = 31

SELECT MAX(IIShipDate_N), IICustID 
FROM T_IIInvoiceItem 
GROUP BY IICustID

They both work separately, however, I don't manage to "merge" the 2 lines of code in a single line to generate results above.

Upvotes: 0

Views: 51

Answers (4)

DeepDiver
DeepDiver

Reputation: 139

Based on the recommendation from Shuvankar, I managed to write the code I need

SELECT IICustID, CuFirstNameTx, MAX(IIShipDate_N) AS Expired 
FROM T_IIInvoiceItem
INNER JOIN T_CuCust ON CuCustID=IICustID
GROUP BY IICustID, CuFirstNameTx
HAVING DATEDIFF(d, MAX(IIShipDate_N),GetDate())=30

Many thanks for help

Upvotes: 0

DeepDiver
DeepDiver

Reputation: 139

Thank you, I followed your suggestion and wrote the following

SELECT CuCustID
       , CuFirstNameTx
       , MAX(IIShipDate_N) AS Expired
    FROM T_IIInvoiceItem AS I
    JOIN T_CuCust AS C ON I.T_IIInvoiceItem = C.CuCustID
GROUP BY CuCustID
       , CuFirstNameTx
 HAVING  MAX(IIShipDate_N) < DATEADD(DAY, -30, GETDATE())

however, it returns an error

Invalid column name 'T_IIInvoiceItem'.

That is actually the name of the table and it is correct.

Upvotes: 0

Shuvankar Saha
Shuvankar Saha

Reputation: 60

A very simple way to handle this -

  SELECT [CustID]
       , [CustNameTx]
       , MAX([Date]) AS [Expired]
    FROM [Invoices] AS I
    JOIN [Cust] AS C ON I.[InvCustID] = C.[CustID]
GROUP BY [CustID]
       , [CustNameTx]
 HAVING  MAX([Date]) < DATEADD(DAY, -30, GETDATE())

Upvotes: 1

James Z
James Z

Reputation: 12317

You're probably looking for something like this:

select * from
Cust c
cross apply (
    Select top 1 * 
    from Invoices i 
    where c.CustID = i.InvCustID and 
    i.[Date] < getdate() and i.[Date] > dateadd(day, -30, getdate()) i1
 where
     not exists (select 1 from Invoices i2 where c.CustID = i.InvCustID and i.[Date] > getdate())

Upvotes: 0

Related Questions