Как добиться такого вывода в SQL server

---

CREATE TABLE Test
    (CustName varchar(20), Country varchar(20), RecordedTime datetime, CurrNo tinyint);

INSERT INTO Test
    (CustName, Country, RecordedTime, CurrNo)
VALUES
    ('Alex', 'Australia', '2018-06-01 08:00:00', 1),
    ('Alex', 'China', '2018-06-01 10:00:00', 2),
    ('Alex', 'India', '2018-06-01 10:05:00', 3),
    ('Alex', 'Japan', '2018-06-01 11:00:00', 4),
    ('John', 'Australia', '2018-06-01 08:00:00', 1),
    ('John', 'China', '2018-06-02 08:00:00', 2),
    ('Bob', 'Australia', '2018-06-02 09:00:00', 1),
    ('Bob', 'Brazil', '2018-06-03 09:50:00', 2),
    ('Bob', 'Africa', '2018-06-03 11:50:00', 3),
    ('Bob', 'India', '2018-06-03 11:55:00', 4),
    ('Tim', 'Brazil', '2018-06-10 00:00:00', 2),
    ('Tim', 'Cuba', '2018-06-11 00:00:00', 3),
    ('Tim', 'India', '2018-06-11 00:05:00', 4),
    ('Jerry', 'Cuba', '2018-06-12 00:00:00', 4),
    ('Jerry', 'Brazil', '2018-06-12 00:05:00', 5),
    ('Jerry', 'India', '2018-06-12 00:10:00', 7),
    ('Jerry', 'USA', '2018-06-12 00:15:00', 9),
    ('Maulik', 'Aus', '2018-06-12 00:00:00',3),
    ('Maulik', 'Eng', '2018-06-13 00:00:00',4),
    ('Maulik', 'USA', '2018-06-14 00:00:00',5),
    ('Maulik', 'Ind', '2018-06-14 00:00:00',6);

Мне нужен вывод, который должен охватывать все нижеприведенные сценарии.

Существует эмпирическое правило того, как должны присутствовать значения для полей "аудит" и "история" ;

Записи должны иметь Audit = "ADD"или "CHANGE" & History = "NEW","BEFORE" или "CURRENT" только для оригинальных Accts (что означает, что записи в таблице определенно начинаются с CurrNo = 1)

Записи не должны иметь Audit = "ADD" & History = "NEW" Для перенесенных Accts (что означает, что записи в таблице не начинаются с CurrNo = 1, они могут начинаться с 2 или 3 или любых восходящих чисел ) для этого типа счетов аудит должен иметь "CHANGE", а поля истории должны иметь "BEFORE"или "CURRENT"

Цитата:

Цитата:

Scenario1: если задана дата ввода как 2018-Jun-01, то вывод должен быть таким, как показано ниже (т. е. когда запись добавляется и редактируется несколько раз в один и тот же день)

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Alex Australia 2018-Jun-01 08:00 AM добавить новое
Alex Australia 2018-Jun-01 08:00 AM CHANGE BEFORE
Alex Japan 2018-Jun-01 11:00 AM изменение тока
John Australia 2018-Jun-01 08:00 AM добавить новое

Цитата:

Scenario2: если задана дата ввода как 2018-Jun-02, то вывод должен быть таким, как показано ниже (т. е. когда запись уже присутствует в предыдущие дни и та же запись редактируется сегодня, а любая новая запись присутствует сегодня)

CustName Страна RecordedTime История Аудита
-----------------------------------------------------------------
John Australia 2018-Jun-01 08:00 AM CHANGE BEFORE
John China 2018-Jun-02 08:00 AM изменение тока
Bob Australia 2018-Jun-02 09:00 AM добавить новое
Scenario3: если задана дата ввода как 2018-Jun-03, то выходные данные должны быть такими, как показано ниже (т. е. когда запись редактируется несколько раз в один и тот же день, то она должна содержать последнюю запись для последней предыдущей даты, а затем последнюю запись для текущей данной даты)

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Bob Australia 2018-Jun-02 09:00 AM CHANGE BEFORE
Bob India 2018-Jun-03 12:55 AM изменение тока
Scenario4: если задана дата ввода как 2018-Jun-10, то вывод должен быть таким, как показано ниже

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Tim Brazil 2018-Jun-10 12:00 AM изменение тока
Scenario5: если задана дата ввода как 2018-Jun-11, то выходные данные должны быть такими, как показано ниже (т. е. аналогично сценарию 2)

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Tim Brazil 2018-Jun-10 12:00 AM CHANGE BEFORE
Tim India 2018-Jun-11 12:05 AM изменение тока
Scenario6: если задана дата ввода как 2018-Jun-12, то выходные данные должны быть такими, как показано ниже (т. е. аналогично сценарию 3)

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Jerry Cuba 2018-Jun-12 12:00 AM CHANGE BEFORE
Джерри США 2018-Jun-12 12:15 AM изменение тока
Maulik Aus 2018-Jun-12 00:00 AM изменение тока
Если задана дата ввода как 2018-Jun-13, то вывод должен быть таким, как показано ниже

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Maulik Aus 2018-Jun-12 00:00 AM CHANGE BEFORE
Maulik Eng 2018-Jun-13 00:00 AM изменение тока
Если задана дата ввода как 2018-Jun-14, то вывод должен быть таким, как показано ниже

CustName Страна RecordedTime История Аудита
----------------------------------------------------------------
Maulik Eng 2018-Jun-13 00:00 AM CHANGE BEFORE
Maulik Ind 2018-Jun-14 00:00 AM изменение тока

Цитата:

Любые эксперты SQL могут изменить этот запрос, чтобы удовлетворить все сценарии? Очень ценю и благодарю.

Что я уже пробовал:

And below is current code I'm using (Which satisfies Scenario 2 and 3, but not satisfies rest of them);

declare @d date='2018-Jun-03'

; with Indexer as 
(
    select 
        *, 
        rn= row_number() over(partition by CustName order by RecordedTime),
        rn2=row_number() over(partition by CustName order by RecordedTime desc)
    from records
)
,GetValidCustomerRecords as
(
    select 
        CustName,
        Country,
        RecordedTime,
        Audit   = case when cast(RecordedTime as date)=@d and rn=1 then 'add' else 'change' end,
        History = case 
                    when cast(RecordedTime as date)=@d and rn=1 
                    then 'new' 
                    when cast(RecordedTime as date)<@d and rn=1 
                    then 'before'
                    else 'current' end
    from Indexer i 
    where CustName in
    (
    select 
        distinct CustName 
    from records
    where cast(RecordedTime as date)=@d
    ) 
    and (rn=1 or rn2=1) and cast(RecordedTime as date)<=@d
)

select * from GetValidCustomerRecords
order by CustName, RecordedTime</blockquote>

---