SQL, Анализ данных

Как найти дату предшествующую отчетной?

Время прочтения: 4 мин.

На просторах сети интернет достаточно примеров по поиску значения даты, предшествующей отчетной. Но я не встретил примеров, которые бы помогли определить дату, в частности дату проведения операции, которая может быть меньше на дни, месяцы, годы…

В рамках поставленной задачи необходимо было провести выборку остатков по счетам, на отчетную дату. Возьмем за дату отчета 01.02.2021 и один счет, на примере которого проведем все необходимые действия:

Проводим выборку всех имеющихся операций до отчетной даты:

SELECT
        [Дата операции]
	,[Время операции]
	,[Сумма операции]
	,[Баланс после операции]
       ,[Порядковый номер операции по счету]
 FROM [Транзакции по картам]
  where  
	  [Номер счета] = '40817************74'
	  and [Дата операции] < '2021-02-01'

До отчетной даны у нас получилось 115 операций:

Для того, чтобы нам выбрать последнюю операцию с датой, предшествующей отчетной, можно использовать несколько функций, применимых к нашей выборке:

  1. Datediff – функция используется для вычисления разности двух дат;
  2. Оконная функция Over () в сочетании агрегатной функцией MAX (), используемой для поиска максимального значения определенного столбца или выражения.

Итак, рассмотрим действие функции Datediff, с помощью которой мы найдем разницу в днях между отчетной датой и датой проведенной операции. Чем меньше значение, тем больше такая операция нам подходит:

SELECT
       [Дата операции]
       ,datediff (day,[Дата операции],'2021-02-01') As [Разница в днях]
	,[Время операции]
	,[Сумма операции]
	,[Баланс после операции]
       ,[Порядковый номер операции по счету]
 FROM [Транзакции по картам]
  where  
	  [Номер счета] = '40817************74'
	  and [Дата операции] < '2021-02-01'

Мы видим, что наименьшая разница у нас отражена за 30.01.2021. Однако возникает проблема с определением необходимой операции, т.к. за данное число было проведено две операции, и соответственно указано два остатка.

Соответственно использовать только функцию datediff мы не можем.

И к нам на помощь при ходит оконная функция Over () в сочетании агрегатной функцией MAX (), которые мы применим к двум столбцам [Время операции] и [Порядковый номер операции по счету], позволяющим придать точности нашему запросу:

SELECT
[Дата операции]
,datediff (day,[Дата операции],'2021-02-01') As [Разница в днях]
,[Время операции]
,max([Время операции]) over (partition by [Номер счета]) As [Время операции_max]
,[Сумма операции]
,[Баланс после операции]
,[Порядковый номер операции по счету]
,max([Порядковый номер операции по счету]) over (partition by [Номер счета]) 
As [Номер операции_max]
 
FROM [Транзакции по картам]
  where  
	  [Номер счета] = '40817************74'
	  and [Дата операции] < '2021-02-01'

Для вывода искомого значения наш запрос обернем в подзапрос с применением дополнительных условий:

select s.*

from
(SELECT
[Дата операции]
,datediff (day,[Дата операции],'2021-02-01') As [Разница в днях]
,[Время операции]
,max([Время операции]) over (partition by [Номер счета]) As [Время операции_max]
,[Сумма операции]
,[Баланс после операции]
,[Порядковый номер операции по счету]
,max([Порядковый номер операции по счету]) over (partition by [Номер счета]) 
As [Номер операции_max]
 
FROM [Транзакции по картам]
  where  
	  [Номер счета] = '40817************74'
	  and [Дата операции] < '2021-02-01'
 
	 ) S

	 where S.[Номер операции_max] = S.[Порядковый номер операции по счету]
	 and   S.[Время операции_max] = S.[Время операции]

Вот так данные команды помогут нам в определении баланса по транзакционным операциям, проведенным до отчетной даты.

Советуем почитать