tsql proc logic help

Posted by bacis09 on Stack Overflow See other posts from Stack Overflow or by bacis09
Published on 2010-04-19T14:35:31Z Indexed on 2010/04/19 15:03 UTC
Read the original article Hit count: 307

Filed under:
|
|

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?

© Stack Overflow or respective owner

Related posts about tsql

Related posts about sql