tsql proc logic help
- by bacis09
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?
