use QtnDB1;
-- حذف جدول ان وجد
if OBJECT_ID('tempdb..#temp') is not null
begin
drop table #temp
PRINT 'Exist'
end
-- إنشاء الجدولمختصر عن دفتر اليومية
CREATE TABLE #temp
(
[DebitAccount] [decimal](18, 2) NOT NULL,
[CreditAccount] [decimal](18, 2) NOT NULL,
[BalanceAccount] [decimal](18, 2) NOT NULL,
[AccountID] [int] NOT NULL,
[AccountName] [nvarchar](50) NOT NULL,
[AccountStatement] [nvarchar](50) NOT NULL,
[AccountDate] [date] NOT NULL,
[AutoNumbering] [int] NOT NULL,
[AccountNum] [int] NOT NULL,
) ON [PRIMARY]
-- تفريغ الجدول
delete from #temp
-- هذا إستعلام إضافة سجل رصيد الزبون قبل البحث بين تاريخين
insert into #temp (DebitAccount,CreditAccount,BalanceAccount,AccountID,AccountName,AccountStatement,AccountDate,AutoNumbering,AccountNum)(Select Sum(DebitAccount)As DebitAccount,Sum(CreditAccount)As CreditAccount,Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount,0,N'رصيد سابق',N'رصيد سابق','2020-09-09',1,0 From DayBook Where (AccountNum IN(SELECT AccountNum FROM daybook AS DAY_BOOK_1
WHERE AccountDate < '2020-09-10') And AccountID != 1811)
group by AccountID,AccountName,AccountNum)
-- جلب عدد سجلات الجدول المؤقت
DECLARE @RecCount int
set @RecCount= (Select count(*) from #temp)
SELECT DebitAccount, CreditAccount, Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount, AccountID, AccountName, AccountStatement,
AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering FROM dbo.daybook WHERE (AccountID = 1811)
group by DebitAccount, CreditAccount,AccountID, AccountName, AccountStatement,AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering
-- جلب سجل الرصيد و معلوماته
select Sum(DebitAccount)As DebitAccount,Sum(CreditAccount)As CreditAccount,Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount,0 as AccountID,N'رصيد سابق'as AccountName,N'رصيد سابق'as AccountStatement,''as AccountMoneyName,'2020-09-09'as AccountDate,0 as AccountNum,1 as Accounting,0 as AutoNumbering from #temp
-- دمج النتيجتين الخاصتين بكشف الحساب
UNION ALL
SELECT DebitAccount, CreditAccount, Sum(DebitAccount)-Sum(CreditAccount)As BalanceAccount, AccountID, AccountName, AccountStatement,
AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering FROM daybook WHERE (AccountNum IN(SELECT AccountNum FROM daybook AS DAY_BOOK_1
WHERE (AccountID = 1811))) AND (AccountID != 1811) And AccountDate > '2020-09-10'
group by DebitAccount, CreditAccount,AccountID, AccountName, AccountStatement,AccountMoneyName, AccountDate, AccountNum, Accounting, AutoNumbering;