--------------------------------------------------------------------------------
--
--   Copyright (c) 2021 Software Gems Pty Ltd
--   Derek Asirvadem  07 Oct 21
--
--------------------------------------------------------------------------------
-- Code snippet for:
--     Account_Month_V
-- for generic use.
--   (In general, a View contains derived columns, and it should remain generic, 
--   not specific to a single usage). 
--
-- In this specific usage, a typical Month End procedure in a Double Entry
-- Accounting system, the View produces all months, the particular month will be
-- selected by the caller.
--
-- This is example code, not expected to execute as is, you need to make it site-
-- & DDL-specific.  Likewise details re your specific stored proc and methods.
--------------------------------------------------------------------------------
-- Related: 
--   StackOverflow Answer (full discussion):
--     https://stackoverflow.com/a/59465148/484814
--   Data Model & Transaction examples
--     https://www.softwaregems.com.au/Documents/Student_Resolutions/Alex/Alex%20Account%20TA.pdf
--------------------------------------------------------------------------------
-- Compliance:
--   - Using SQL-92
--   - on Sybase ASE 15.7 (same as MS SQL)
--   - Although the correlated subquery is standard SQL, not all “sql” platforms
--     provide it.  For Orable & freeware, convert the subquery into a Derived Table.
--------------------------------------------------------------------------------

CREATE VIEW Account_Month_V
    AS 
SELECT  ASS.AccountNo, 
        OpeningDate = Date,                                     -- 1st of applicable month
        OpeningBalance = ClosingBalance,
        TotalCredit = ( 
            SELECT SUM( Amount ) 
                FROM AccountTransaction   ATT
                WHERE ATT.AccountNo = ASS.AccountNo 
                    AND XactTypeCode_Ext IN ( "AC", "Dp" ) 
                    AND DateTime >= ASS.Date                    -- >= 1st day of applicable month
                    AND DateTime <  DATEADD( MM, 1, ASS.Date )  -- <  1st day of next month
                ), 
        TotalDebit = ( 
            SELECT SUM( Amount ) 
                FROM AccountTransaction  ATT
                WHERE ATT.AccountNo = ASS.AccountNo 
                    AND XactTypeCode_Ext NOT IN ( "AC", "Dp" ) 
                    AND DateTime >= ASS.Date
                    AND DateTime <  DATEADD( MM, 1, ASS.Date ) 
                ),
        ClosingBalance = ClosingBalance +                       -- sum PREVIOUS month
            <TotalCredit> -   -- subquery above 
            <TotalDebit>      -- subquery above 
    FROM AccountStatement  ASS
