I need a select query, I am very confused how to set multiple conditions to get result output 4 columns.
select Act_unit, Active_status, Active_date, Inactive_date
left join between @GLNAME to @GLNAMESAU based on act_unit and max(R_Date) and Max(R_Time)
If act_units based on max(r_date) & max(r_time) is found in table two, i want to get the dates and populate as active_date & inactive_date based on status in the output
If act_unit is not found in table two, then need to use first table active_status I or A and populate hardcoded values in active_date and inactive date
if Active_status is 'I' in table 1
then inactivedate = to_date('12/31/9999','MM/DD/YYYY')
If Active_status is 'A' in table 1 then hardcode
Active_date = to_date('01/01/1900','MM/DD/YYYY')
declare @GLNAME table (ACT_UNIT char(10), ACTIVE_STATUS char(1)
select '34562', 'I' union all
select '32674', 'A' union all
select '33456', 'A' union all
select '99123', 'A' union all
select '92016', 'A'
declare @GLNAMESAU table (ACT_UNIT char(10), R_DATE date, R_TIME int, ACTIVE_STATUS char(1)
select '92016', '13-Aug-13',150434,'I' union all
select '92016', '13-Aug-13',151754,'A'
Thank you very much for helpful info.