2 Вопрос: SQL-запрос для группировки значения начального сальдо

вопрос создан в Thu, May 2, 2019 12:00 AM

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

У меня есть две таблицы данных, которые я объединил, чтобы создать список транзакций, которые принадлежат определенной учетной записи. Эту часть я выполнил и работает (возможно, не самое эффективное кодирование, но насколько мне известно). Я могу опубликовать код, если требуется. Результаты выглядят так: столбец Баланс рассчитывается в запросе SQL, а не в какой-либо таблице: введите описание изображения здесь

Это замечательно, но я хочу включить строку «Начальный баланс» в начале, которая будет суммировать все записи в этом аккаунте до определенной даты, а остаток будет включать в себя начальную сумму. Я хотел бы, чтобы это выглядело так: введите описание изображения здесь

Скорректированный код, который я использую:

SELECT
 Result.TransID,
 Result.TransDate,
 Result.Reference,
 Result.Description,
 Result.Amount,
 SUM(Result.Amount) OVER(PARTITION BY Result.ledgerRef ORDER BY 
 Result.TransID) AS 'Balance'

FROM    
(
    (SELECT 
        -1 TransID,
        NULL AS 'TransDate',
        NULL AS 'Reference',
        OB.Description,
        SUM(OB.Amount) AS 'Amount',
        SUM(OB.Amount) AS 'Balance',
        OB.LedgerRef

    FROM        
        (
            (SELECT
                -1 AS 'ID',
                MAX(T.dtm_TransDate) AS 'TransDate',
                ' ' AS 'Reference',
                'Opening Balance' AS 'Description',
                    SUM(CASE
                            WHEN LT.txt_LedgerTypeRef = 'REV' then -TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'EXP' then TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'ASS' then TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'LIA' then -TD.dbl_TransDataAmount
                            WHEN LT.txt_LedgerTypeRef = 'EQU' then -TD.dbl_TransDataAmount
                            ELSE TD.dbl_TransDataAmount
                        END) AS 'Amount',
                        LT.txt_LedgerTypeRef AS 'LedgerRef'


            FROM dbo.tbl_TransData AS TD

                JOIN dbo.tbl_Trans AS T ON T.int_Trans_ID = TD.int_TransID
                JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
                JOIN dbo.tbl_Account AS A ON A.int_Account_ID = TD.int_AccountID
                JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
                JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID  

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate < '2019-01-01'
                AND TD.int_AccountID = 2167

            GROUP BY

                LT.txt_LedgerTypeRef,
                TD.dbl_TransDataAmount
            )

            UNION

            (SELECT
                -1 AS 'ID',
                MAX(T.dtm_TransDate) AS 'TransDate',
                ' ' AS 'Reference',
                'Opening Balance' AS 'Description',
                SUM(CASE
                        WHEN LT.txt_LedgerTypeRef = 'REV' THEN -T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'EXP' THEN T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'ASS' THEN T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'LIA' THEN -T.dbl_TransAmount
                        WHEN LT.txt_LedgerTypeRef = 'EQU' THEN -T.dbl_TransAmount
                        ELSE T.dbl_TransAmount
                    END) AS 'Amount',
                    LT.txt_LedgerTypeRef AS 'LedgerRef'


            FROM dbo.tbl_Trans AS T

            JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
            JOIN dbo.tbl_Account AS A ON A.int_Account_ID = T.int_AccountIDBank
            JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
            JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate < '2019-01-01'
                AND T.int_AccountIDBank = 2167

            GROUP BY
                LT.txt_LedgerTypeRef,
                T.dbl_TransAmount
            )
        ) AS OB

    GROUP BY
        OB.Description,
        OB.LedgerRef
    )

    UNION

    (SELECT 
        TR.TransID,
        TR.TransDate,
        TR.Reference,
        TR.Description,
        TR.Amount,
        SUM(TR.amount) OVER(PARTITION BY TR.ledgerRef ORDER BY TR.TransID) AS 'Balance',
        TR.LedgerRef

    FROM    
        (
            (SELECT
                T.int_Trans_ID AS 'TransID',
                T.dtm_TransDate AS 'TransDate',
                concat(TT.txt_TransTypeCode, T.dbl_TransRef) AS 'Reference',
                TD.txt_TransDataDescription AS 'Description',
                    CASE
                        WHEN LT.txt_LedgerTypeRef = 'REV' then -TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'EXP' then TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'ASS' then TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'LIA' then -TD.dbl_TransDataAmount
                        WHEN LT.txt_LedgerTypeRef = 'EQU' then -TD.dbl_TransDataAmount
                        ELSE TD.dbl_TransDataAmount
                    END AS 'Amount',
                    LT.txt_LedgerTypeRef AS 'LedgerRef'

            FROM dbo.tbl_TransData AS TD

                JOIN dbo.tbl_Trans AS T ON T.int_Trans_ID = TD.int_TransID
                JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
                JOIN dbo.tbl_Account AS A ON A.int_Account_ID = TD.int_AccountID
                JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
                JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID  

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate >= '2019-01-01'
                AND TD.int_AccountID = 2167
            )
            UNION
            (SELECT
                T.int_Trans_ID AS 'TransID',
                T.dtm_TransDate AS 'TransDate',
                concat(TT.txt_TransTypeCode, T.dbl_TransRef) AS 'Reference',
                T.txt_TransDescription AS 'Description',
                CASE
                    WHEN LT.txt_LedgerTypeRef = 'REV' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EXP' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'ASS' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'LIA' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EQU' THEN -T.dbl_TransAmount
                    ELSE T.dbl_TransAmount
                END AS 'Amount',
                LT.txt_LedgerTypeRef AS 'LedgerRef'

            FROM dbo.tbl_Trans AS T

            JOIN dbo.tbl_TransType AS TT ON TT.int_TransType_ID = T.int_TransTypeID
            JOIN dbo.tbl_Account AS A ON A.int_Account_ID = T.int_AccountIDBank
            JOIN dbo.tbl_Ledger AS L ON L.int_Ledger_ID = A.int_LedgerID
            JOIN dbo.tbl_LedgerType AS LT ON LT.int_LedgerType_ID = L.int_LedgerTypeID

            WHERE
                T.int_BusinessID = 1
                AND T.bit_Archive <> 1
                AND T.bit_Disabled <> 1
                AND T.bit_TransDelete <> 1
                AND T.dtm_TransDate >= '2019-01-01'
                AND T.int_AccountIDBank = 2167
            )

        ) AS TR
    )
) AS Result

Кажется, все это работает нормально, когда мне пришлось объединить 2 таблицы для начального баланса и транзакций, поэтому там было много объединений.

Вот как выглядят результаты:

 введите описание изображения здесь

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

Еще раз, любой совет будет высоко ценится.

    
1
  1. Какие у вас базы данных?
    2019-05-02 15: 02: 24Z
  2. сначала необходимо предоставить код, а мы можем просто добавить к нему что-нибудь.
    2019-05-02 15: 03: 04Z
  3. Очень жаль, я исправлю, чтобы включить код
    2019-05-02 15: 09: 59Z
  4. А как узнать, что такое начальный баланс?
    2019-05-02 15: 10: 49Z
  5. Начальный баланс будет рассчитываться на сумму этого счета до даты начала
    2019-05-02 15: 14: 40Z
2 ответа                              2                         

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

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

Я бы добавил еще одну настройку запроса примерно так (и опять же, просто руководство, подготовка полных данных и все).

select
      … original fields and sum(…)( over/order by)
   from
      ( InnerQuery1
        UNION
        InnerQuery2 )

и измените на

select
      … original fields and sum(…)( over/order by)
   from
      ( InnerQuery1
           where TransDate > YourCutoffDate
        UNION
        InnerQuery2
           where TransDate > YourCutoffDate
        UNION
        ( select
                -- place-holder field to match the union requirements               
                -1 int_Trans_ID,
                MAX( ExistingDataDate ) AS 'TransDate',
                ' ' as 'Reference',
                'Balance Forward' as 'Description',
                SUM( CASE
                    WHEN LT.txt_LedgerTypeRef = 'REV' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EXP' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'ASS' THEN T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'LIA' THEN -T.dbl_TransAmount
                    WHEN LT.txt_LedgerTypeRef = 'EQU' THEN -T.dbl_TransAmount
                    ELSE T.dbl_TransAmount
                END ) AS 'Amount',
                LT.txt_LedgerTypeRef AS 'LedgerRef'
            from
                . . .  
            where
                TransDate <= YourCutoffDate                   
            group by
                ?LedgerReg -- unsure per your data sources
        )
     )

В этом случае вы всегда можете выполнить новый запрос баланса вперед по своей собственной заслуге, чтобы убедиться, что синтаксис и агрегация имеют смысл, который вы намерены получить. Используя предложение WHERE, чтобы ограничить число самых популярных запросов только теми, ПОСЛЕ какой-либо конечной даты, они будут их собственными. Этот новый запрос будет предварительно агрегирован в одну строку (для каждой бухгалтерской книги) для всех транзакций до обрезания, оставляя только 1 запись (для каждой бухгалтерской книги). Использование MAX () в дату транзакции обеспечит, что она перемещается в верхнюю по порядку дате и должна быть первой, таким образом, начиная с вашего баланса вперед.

Надеюсь, это имеет смысл, так как остальная часть вашего запроса уже работает. Здесь вы, по сути, просто предварительно запрашиваете все записи ВКЛ или ДО даты окончания и добавляете их в список. Поэтому сначала создайте этот запрос самостоятельно, затем измените существующий, чтобы включить его в качестве дополнительного объединения.

Для окончательного удаления суммы начального баланса. У вас есть мошенничествоТроллированное значение идентификатора = -1. Используйте случай /когда удалить. В своем запросе OUTERMOST измените

Ревизия для очистки строки суммы начального баланса ...

Result.Amount,
SUM(Result.Amount) OVER(PARTITION BY Result.ledgerRef ORDER BY 

до

case when int_trans_id < 0 then 0 else Result.Amount end Amount,
SUM(Result.Amount) OVER(PARTITION BY Result.ledgerRef ORDER BY 
    
1
2019-05-03 10: 57: 55Z
  1. Спасибо. Я думаю, что это имеет смысл. Мне нужно будет посмотреть завтра более подробно и провести некоторое тестирование. Дадим вам знать, как я получу один
    2019-05-02 19: 41: 25Z
  2. Еще раз спасибо. Сегодня утром я попробовал, и все работает хорошо, только один глюк, который я отредактировал в своем первоначальном посте и включил весь код.
    2019-05-03 09: 11: 52Z
  3. @ jAshton, см. мой пересмотренный ответ внизу для case /when в строке начального баланса.
    2019-05-03 10: 58: 17Z
  4. Большое спасибо, это исправлено. Немного изменен на CASE WHEN Result.TransID < 0 THEN NULL ELSE Result.Amount END Amount, SUM (Result.Amount) OVER (PARTITION BY Result.ledgerRef ORDER BY Result.TransID) AS 'Balance' Извините за форматирование, я новичок в этом
    2019-05-03 11: 10: 03Z

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

SELECT SUM(T.[Value]) AS OPENING_BALANCE --Sum (addition of) all values in col [Value]
FROM 
(SELECT [Id]
      ,[Title]
      ,[Units]
      ,[Value]
  FROM [DBNAME].[dbo].[Data]) T
    
0
2019-05-02 15: 08: 27Z
  1. Я попробую, но не уверен, как ссылаться на него только один раз, затем список транзакций
    2019-05-02 15: 49: 49Z
источник размещен Вот