SQL: создание корзин устаревания на основе последней даты платежа для финансовой отчетности

Мне нужно создать отчет о старении кредитных остатков на уровне клиента.

Примечания:

  1. Срок действия зависит от даты последней оплаты покупателем.

  2. У клиента может быть несколько учетных записей, и иногда возникают ошибки, когда платеж применяется не к тому счету. Например, клиент с балансом 15 долларов на счете совершает платеж в размере 15 долларов. Этот платеж в размере 15 долларов может быть применен к неправильной учетной записи, в результате чего у клиента останется -15 долларов на одном счете и 15 долларов на другом. Этого клиента нужно исключить из отчета.

SQL для получения клиентов с кредитным балансом:

SELECT 
  ACCOUNT.CUST_ID
, sum(ACCOUNT.BALANCE)

FROM ACCOUNT

GROUP BY ACCOUNT.CUST_ID

HAVING sum(ACCOUNT.BALANCE) < 0

SQL для получения последней даты платежа:

SELECT

  TRANSACTIONS.CUST_ID
, MAX(TRANSACTIONS.POST_DATE)

FROM TRANSACTIONS

WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'

GROUP BY TRANSACTIONS.CUST_ID

Мне нужно создать столбцы для ведер выдержки, например:

«0–30» СУММА КРЕДИТНОГО БАЛАНСА

'0–30' КРЕДИТНЫЙ БАЛАНС СЧЕТЧИК КЛИЕНТОВ

'31 - 60'...

Я собирался использовать операторы CASE, используя функцию DATEDIFF между max (TRANSACTIONS.POST_DATE) и «вчера» - DATEADD (dd, -1, getdate ()) для создания сегментов.

Однако не было бы намного эффективнее сделать это с использованием переменных или хранимой процедуры, чтобы разделить клиентов на основе последней даты платежа перед выполнением подсчета суммы и подсчета корзины?

Есть идеи, как это сделать точно и эффективно? До сих пор я отбирал клиентов с кредитным балансом вместе с датой последнего платежа с помощью простого запроса, а затем сам создавал сегменты устаревания с помощью Excel.


sql tsql sql-server-2008 accounting financial
person texas_mike81    schedule 12.07.2012    source источник

Ответы (3)


arrow_upward
3
arrow_downward

Ваш подход - правильный подход. Я не уверен, почему вы думаете, что выполнение чего-либо в хранимой процедуре будет более эффективным, чем выполнение этого в запросе.

Я думаю, вам нужен запрос вроде:

select (case when t.DaysAgo between 0 and 30 then '0-30'
             when t.DaysAgo between 31 and 60 then '31-60'
             else 'OLD'
        end),
       <whatever you want here>
from (SELECT ACCOUNT.CUST_ID, sum(ACCOUNT.BALANCE) as balance
      FROM ACCOUNT
      GROUP BY ACCOUNT.CUST_ID
     ) a left outer join
     (SELECT TRANSACTIONS.CUST_ID, MAX(TRANSACTIONS.POST_DATE) as LastPaymentDate,
             datediff(d, X(TRANSACTIONS.POST_DATE), getdate()) as DaysAgo, 
      FROM TRANSACTIONS
      WHERE TRANSACTIONS.TX_TYPE = 'PAYMENT'
      GROUP BY TRANSACTIONS.CUST_ID
     ) t
     on a.cust_id = t.cust_id 
group by  (case when t.DaysAgo between 0 and 30 then '0-30'
                when t.DaysAgo between 31 and 60 then '31-60'
                else 'OLD'
           end)

В общем, вы должны позволить механизму SQL оптимизировать запрос. Обычно (но определенно не всегда!) Он дает хорошую работу, позволяя вам думать о том, чего вы хотите достичь.

У вас будет дополнительная логика, чтобы исключить определенных клиентов и ввести другие поля.

person Gordon Linoff    schedule 12.07.2012
comment
@Gordon Я публикую этот вопрос stackoverflow.com/questions/11592880/sql-aging-report, который похож на этого ответа, но моя проблема в том, что я хочу сделать это, но только с 1 таблицей. Надеюсь, ты сможешь мне помочь. Спасибо - person VAAA; 21.07.2012

arrow_upward
1
arrow_downward

Мне нужно пояснение по одному поводу. Если у клиента может быть более одной учетной записи, и если платеж может быть проведен на неправильную учетную запись, как вы с этим справитесь?

Это означает, что если у клиента более одной учетной записи, как вы определяете, что платеж проводится не на тот счет. Означает ли это, что даже если у определенного клиента более одной учетной записи, у клиента все равно будет один и тот же CustID для разных учетных записей?

Поскольку, если CustID отличается для каждой учетной записи, я искренне верю, что нет никакого способа справиться с этим должным образом, кроме как использовать старый способ в Excel, который вы сейчас используете.

Спасибо

person Ragno Croft    schedule 12.07.2012
comment
У клиента один и тот же идентификатор клиента для нескольких учетных записей. Когда платежи применяются не к тому счету, они почти всегда применяются к одному из других счетов клиента. Таким образом, суммирование суммы всех счетов клиентов гарантирует, что будут выбраны только клиенты с истинно отрицательным балансом. - person texas_mike81; 12.07.2012

arrow_upward
0
arrow_downward

Интересно! .. Я разработал приложение ломбарда, в котором мы состариваем пешки на основании последней даты выплаты процентов или, в случае отсутствия процентной ставки, pawn_date. Иногда клиенты задерживают выплату процентов на 2 месяца, но платят проценты только за 1 месяц, чтобы избежать потери залога, если они не уплачивают проценты или не выкупают залог в течение 90 дней или более. Таким образом, мы делаем (сегодня - last_pymt_date), чтобы отобразить количество дней, прошедших с момента последнего int pymt или даты пешки.

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

person Frank R.    schedule 14.07.2012