tsql proc logic help

Question

I am weak in SQL and need some help working through some logic with my proc.

Three pieces: store procedure, table1, table2

Table 1 stores most recent data for specific IDs

Customer_id status_dte  status_cde    app_dte
001         2010-04-19  Y             2010-04-19

Table 2 stores history of data for specific customer IDs: For example:

Log_id  customer_Id status_dte  status_cde
01     001         2010-04-20   N
02      001         2010-04-19   Y      
03      001         2010-04-19   N
04      001         2010-04-19   Y

The stored proecure currently throws an error if the status date from table1 is < than app_date in table1.

If @status_dte < app_date
    Error

Note: @status_dte is a variable stored as the status_dte from table1

However, I want it to throw an error when the EARLIEST status_dte from table 2 with a status_cde of 'Y' is less than the app_dte column in table 1.

Keep in mind that this earliest date is not stored anywhere, the history of data changes per customer. Another customer might have the following history.

Log_id  customer_Id status_dte  status_cde
01     002         2010-04-20  N
02      002         2010-04-18  N       
03      002         2010-04-19  Y
04      002         2010-04-19  Y

Any ideas on how I can approach this?

Answer 1

You can test in one go per customer to find where the earliest date is less than the appdate using this construct

IF EXISTS (SELECT *
    FROM
      mytable M
      JOIN
      HistoryTable H ON M.customer_Id = H.customer_Id
    WHERE
      H.status_cde = 'Y'
    GROUP BY
      H.customer_Id, M.app_dte
    HAVING
     MIN(H.status_dte) < M.app_dte)
  ...error...

Answer 2

If instead of a single customer, you wanted a list of customers with their earliest status date prior to the app_date, you could do something like:

;With 
    CustomerStatusDates As
    (
        Select T2.customer_id, Min(T2.status_dte) As MinDate
        From Table2 As T2
        Where status_cte = 'Y'
        Group By T2.customer_id
    )
Select ....
From Table1 As T1
    Join CustomerStatusDates As T2
        On T2.Customer_Id = T1.Customer_Id
Where T2.MinDate < T1.app_dte