Как получить данные из SQL Server, как требуется ниже?

У меня есть такая таблица:

  CustName    Country    RecordedTime
 ---------------------------------------------
  Alex        Australia  2018-Jun-01 08:00 AM
  Alex        China      2018-Jun-01 10:00 AM
  Alex        Japan      2018-Jun-01 11:00 AM
  John        Australia  2018-Jun-01 08:00 AM
  John        China      2018-Jun-02 08:00 AM
  Bob         Australia  2018-Jun-02 09:00 AM
  Bob         Brazil     2018-Jun-03 09:50 AM

Если запись является новой в системе, она должна отображать «ДОБАВИТЬ» и «НОВАЯ» в полях «Аудит» и «История» (два дополнительных поля в наборе результатов) на указанную дату.

Если запись была отредактирована дважды в этот день, она должна отображать две записи с «ДОБАВИТЬ» и «ИЗМЕНИТЬ» в полях «Аудит» и «ДО» и «ТЕКУЩИЙ» в полях состояния истории соответственно для данной даты.

Например, так должен выглядеть мой результат;

Когда я передаю дату ввода как 2018-Jun-01, вывод должен быть следующим:

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Alex        Australia  2018-Jun-01 08:00 AM   ADD      NEW
   Alex        China      2018-Jun-01 10:00 AM   CHANGE   BEFORE
   Alex        Japan      2018-Jun-01 11:00 AM   CHANGE   CURRENT
   John        Australia  2018-Jun-01 08:00 AM   ADD      NEW

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

   CustName    Country    RecordedTime           Audit    History
  -----------------------------------------------------------------
   John        China      2018-Jun-02 08:00 AM   CHANGE   CURRENT
   Bob         Australia  2018-Jun-02 09:00 AM   ADD      NEW

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

   CustName    Country    RecordedTime           Audit    History
  ----------------------------------------------------------------
   Bob         Brazil     2018-Jun-03 09:50 AM   CHANGE   CURRENT

Я пробовал много способов, но все же мне не хватает некоторых сценариев для достижения этой цели. Может кто-нибудь пролить свет на это?


sql sql-server tsql sql-server-2008 ssms-2014
person Mar1009    schedule 25.06.2018    source источник
comment
Спасибо всем за ценные предложения, но я прошу внести в него изменения, см. on-below-example" title="как получить данные с сервера sql на основе приведенного ниже примера"> stackoverflow.com/questions/51039577/   -  person Mar1009    schedule 26.06.2018

Ответы (3)


arrow_upward
1
arrow_downward

Один из способов сделать это — использовать cte, как показано ниже, где у нас есть функция row_number() для отслеживания последовательности в обоих направлениях.

См. демонстрацию

; with cte 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
    )
, cte2 as
(
select *, audit='New', History='Change' from cte where rn=1
    union 
select *, audit='Change', History='Current' from cte where rn2=1 and rn<>1
    union
select *, audit='Change', History='before' from cte where rn>1 and rn2<>1
)

select 
    CustName,
    Country,
    RecordedTime,
    audit,
    History
from cte2
order by  CustName,RecordedTime
person DhruvJoshi    schedule 25.06.2018
comment
Я сделал комментарий по этому поводу. Пожалуйста, посмотрите. Спасибо - person Mar1009; 26.06.2018

arrow_upward
2
arrow_downward

Я бы просто использовал case выражений.

select t.*,
       (case when seqnum = 1 then 'ADD' else 'CHANGE' end) as audit,
       (case when seqnum = 1 then 'NEW'
             when seqnum_day = 1 then 'CURRENT'
             else 'BEFORE'
        end) as history
from (select t.*,
             row_number() over (partition by custname order by recordedtime) as seqnum,
             row_number() over (partition by custname, cast(recordedtime as date) order by recordedtime desc) as seqnum_day
      from t
     ) t;

sqlfiddle:http://sqlfiddle.com/#!18/43c08/27

person Gordon Linoff    schedule 25.06.2018
comment
спасибо @Gordon, не могли бы вы посоветовать и это? ="как получить данные с сервера sql на основе приведенного ниже примера"> stackoverflow.com/questions/51039577/ - person Mar1009; 26.06.2018

arrow_upward
1
arrow_downward

Можешь попробовать.

CASE WHEN и RANK с функциями Windows

;WITH CTE (CustName,Country,RecordedTime,rn) AS(
  SELECT *,RANK() OVER(PARTITION BY CustName ORDER BY RecordedTime) rn
  FROM T
)
SELECT t.*,
       (CASE WHEN rn = 1 then 'ADD' ELSE 'CHANGE' END) 'Audit',
       (CASE 
          WHEN rn = 1  then 'NEW'
          WHEN t2.mRn = rn then 'CURRENT' 
       ELSE 'BEFORE' END) 'History'
FROM CTE t LEFT JOIN  (
  SELECT MAX(rn) mRn,CustName  FROM CTE GROUP BY CustName
) t2 on t2.mRn = t.rn and t2.CustName = t.CustName
WHERE CONVERT(char(10), RecordedTime,126) = '2018-06-02'

sqlfiddle:http://sqlfiddle.com/#!18/43c08/26

person D-Shih    schedule 25.06.2018
comment
спасибо @Shih, не могли бы вы дать совет и по этому поводу ="как получить данные с сервера sql на основе приведенного ниже примера"> stackoverflow.com/questions/51039577/ - person Mar1009; 26.06.2018