ایجاد گزارش بدهکار بستانکار مانده حساب بالانس حساب با SQL Server

[ali]

می خواهیم تابعی در SQL Server ایجاد نماییم که توسط آن بتوانیم تراز حساب اشخاص را به صورت بدهکار بستانکار و مانده بدست آوریم ، به این صورت که اطلاعات مربوط به حساب یک نفر را از یک جدول گرفته و مانده حساب ها ، بدهکار و بستانکار را محاسبه کند. در این روش هر عدد با عدد قبلی خود جمع می شود و در روبرای عدد جاری مقدار مانده نوشته می شود. به مثال زیر دقت فرمائید:

بدهکار بستانکار مانده
=========================
1000 0 1000
0 200- 800
450 0 1250

ابتدا یک جدول با مشخصات زیر ایجاد می کنیم.که اطلاعات مربوط به حساب افراد را نگه داری می کند.

کد:

CREATE TABLE [dbo].[tblAcount]
(
    [RowID] [int] PRIMARY key  IDENTITY(1,1) NOT NULL,
    [AcountStatus] [int] NOT NULL,
    [Name] [nvarchar](20) NOT NULL
)

فیلد RowID به صورت خودکار مقدار دهی می شود و کلید جدول است.
فیلد AcountStatus مقدار حساب را نشان می دهد.
فیلد Name نشان دهنده نام فرد دارای حساب است.
اطلاعات را به صورت زیر وارد جدول می کنیم.

کد:

INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( 100, 'ali')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( -520, 'amin')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( 30, 'mohammad')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( -90, 'ali')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( 160, 'ali')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( 95, 'amin')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( 700, 'reza')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( 50, 'mohammad')
INSERT INTO dbo.tblAcount ( AcountStatus, Name )VALUES  ( -180, 'reza')

تعریف تابع
عملکرد تابع به این صورت است که نام صاحب حساب را می گیرد و مقادیر حساب به همراه مانده آن را محاسبه می کند.

کد:

CREATE function [dbo].[UDFAcount2](@name nvarchar(20))
    returns @tblAcount table(
                    RowID int not null ,
                    debit decimal not null,
                    credit decimal not null,
                    remain decimal not null)
    as
    begin
            
        insert into @tblAcount
            select RowID,  
                  Isnull((select AcountStatus  from tblAcount
                               where  RowID=ta.RowID and AcountStatus>=0),0),
Isnull((select AcountStatus from tblAcount
            where  RowID=ta.RowID and AcountStatus<0),0),

            (select sum(AcountStatus) from tblAcount
                          where RowID<=ta.RowID and Name=@name)
              
                  from tblAcount ta where name=@name

        return
      end

با استفاده از این تابع، اطلاعات را از جدول tblAcount دریافت کرده و در یک جدول جدید با مشخصات زیر وارد کنیم تا مقدار باقیمانده حساب را محاسبه کند.با این شرط که اگر مقدار AcountStatus مثبت باشد، مقدار آن در ستون debit قرار گرفته و مقدار credit صفر شود. اگر مقدار AcountStatus منفی باشد مقدار آن را در ستون credit قرار داده و مقدار فیلد debit صفر می شود.

کد:

@tblAcount table(
        RowID int not null ,
        debit decimal not null,
        credit decimal not null,
                            remain decimal not null)

قطعه کد زیر بررسی می کند که آیا مقدار AcountStatus مثبت است یا خیر؟ در صورتی که مقدار آن مثبت باشد مقدار AcountStatus را در ستون debit قرار می دهد و در صورت منفی بودن مقدار صفر را قرار می دهد.

کد:

Isnull((select AcountStatus  from tblAcount
      where  RowID=ta.RowID and AcountStatus>=0),0)

قطعه کد زیر بررسی می کند که آیا مقدار AcountStatus منفی است یا خیر؟ در صورتی که مقدار آن منفی باشد مقدار AcountStatus را در ستون credit قرار می دهد و در صورت مثبت بودن مقدار صفر را قرار می دهد.

کد:

Isnull((select AcountStatus from tblAcount
       where  RowID=ta.RowID and AcountStatus<0),0)

این قسمت نیز مقدار مانده را محاسبه می کند.برای محاسبه مانده برای هر ردیف باید مقدار حساب فعلی یک نفر را با حساب های قبلی او جمع کرد.

کد:

(select sum(AcountStatus) from tblAcount
                          where RowID<=ta.RowID and Name=@name)

اجرای تابع
تابع را برای محاسبه مانده حساب فردی به نام ali اجرا می کنیم.

کد:

SELECT * FROM dbo.UDFAcount2('ali')

نتیجه به صورت زیر است.

(ارسال شده توسط آقای مهندس امین امیرخانی کارشناس ارشد نرم افزار)