SQL View for Daily Payment Summary

| 08/05/2011 | 2 Comments

Hi all

Based on a request from the community, I have decided to post a script for identifying all the payments made on a specific date from a checkbook and the details of what was voided for the specific date.

This query helps a great deal in reconciling the funds that were used for processing the payments.

SELECT A.CHEKBKID ,
        A.PSTGDATE ,
        ISNULL((
                SELECT SUM(B.DOCAMNT)
                    FROM dbo.PM30200 B
                    WHERE B.DOCTYPE = 6
                        AND B.CHEKBKID = A.CHEKBKID
                        AND B.PSTGDATE = A.PSTGDATE
               ), 0) AS CHECKAMOUNT ,
        ISNULL((
                SELECT SUM(B.DOCAMNT)
                    FROM dbo.PM30200 B
                    WHERE B.DOCTYPE = 6
                        AND B.CHEKBKID = A.CHEKBKID
                        AND B.PSTGDATE = A.PSTGDATE
                        AND B.VOIDED = 1
               ), 0) AS VOIDEDCHECKS
    FROM dbo.PM30200 A
    WHERE A.DOCTYPE = 6
        AND A.PSTGDATE <> '1900-01-01'
    GROUP BY A.CHEKBKID ,
        A.PSTGDATE

Hope this helps the community…

Until next post!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Tags: , , ,

Category: Accounts Payables, Dynamics, Great Plains, SQL Server, SQL Server 2005

About the Author ()