Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 Tables
Card:
CARD_ID
CLEANUP_DT
SETUP_DT
SELECTED_CARD:
CASE_CONFIRMATION_ID
CARD_ID
A Case Confirmation_ID can have more then one Card associated with it. I however only want the Cleanup or the Setup time that is higher. Both of witch are DATETIME variables that show up as 1/1/1901 12:15:00 AM for 15 Minutes or 1/1/1901 12:30:00 AM for 30 Minute setup/cleanup times.
Case Confirmation ID | Card ID | SETUP DT | CLEANUP DT |
---|---|---|---|
135986 | 236587 | 1/1/1901 12:15:00 AM | 1/1/1901 12:15:00 AM |
135986 | 236526 | 1/1/1901 12:30:00 AM | 1/1/1901 12:15:00 AM |
189763 | 135498 | 1/1/1901 12:15:00 AM | 1/1/1901 12:15:00 AM |
169873 | 165798 | 1/1/1901 12:15:00 AM | 1/1/1901 12:15:00 AM |
For case 135986 it would just pick out CARD_ID 236526 and ignore 236587.
SELECTED_CARD:
CASE_CONFIRMATION_ID,
CARD_ID
From
SELECTED_CARD;
Left Join (SELECTED_CARD)
Card:
CARD_ID,
CLEANUP_DT
SETUP_DT
From
Card;
Inner Join(SELECTED_CARD)
Load
CASE_CONFIRMATION_ID,
Max(SETUP_DT) as SETUP_DT ,
Resident
SELECTED_CARD
Group By
CASE_CONFIRMATION_ID;