مهم : (سرعت) ایجاد گزارش دفترکل با یک Query

[shsoft]

با سلام
همان طور که می دونید سرعت عمل در نمایش گزارش ها بسیار مهمه.

حالا مشکل رو با یک مثال توضیح می دم:
- دو جدول داریم که شامل دریافت و پرداخت می شود.
- در هر جدول 10,000رکورد برای هر کد حساب وجود دارد که در مجموع 200 کد حساب دارم

Table1= 10,000 * 200=>2,000,000 Records
Table2= 10,000 * 200=>2,000,000 Records


خوب حال اگه بخواهیم یک Query بنویسیم که در کمترین زمان ممکن گزارش رو تهیه کنه باید چکار کنیم
لطفا نظرات خودتون رو بنویسید
:flower:

[AminSobati]

دوست عزیزم،
شما میبایست اول دقیقا Query مورد نیاز رو بنویسین، بعد متناسب با اون Indexها ساخته بشن.

[shsoft]

اگه میشه در مورد این بخش از کار صحبت کنیم

ما یک جدول داریم که یه صورت زیر حاوی اطلاعات حساب است که حاصل یک Query می باشد
Treport.id
Treport.date
treport.debtor
treport.credito
debtor شامل بدهکار و creditor شامل بستانکار است
حالا چگونه می شود با یک query مقادیر ستون مانده را بدست آورد

[AminSobati]

بدهکار منظورتون حاصل تفریق Debtor و Creditor هستش؟ یا...

[shsoft]

debtor شامل بدهکار - یعنی شامل مقداری است که دارای ارزش منفی می باشد
creditor شامل بستانکار است - یعنی شامل مقدار مثبت است.

در واقع این دو فیلد همان ستون های دفترکل هستند

[AminSobati]

ببخشید که من سوالم رو بد مطرح کردم. منظورم تعریف ستون مانده بود
اگر میبایست تفریق بشن که به این صورت عمل کنین:
SELECT Creditor-Debtor FROM MyTable

[shsoft]

آقا ببخشید وقت شما رو خیلی گرفتیم
گزارشی رو که من می خوام دقیقا گزارش دفترکل است که در زیر چند خط از اون رو مثال می زنم

ردیف / تاریخ / شرح / بدهکار / بستانکار / مانده

1 /25/2/84 / بابت خرید کالا / 10000 / 0 / 10000-
2 /25/2/84 / پرداخت نقدی / 0 / 10000 / 0
3 /26/2/84 / بابت اعتبار حساب / 0 / 200000 / 200000+

خوب همون طور که می دونید ستون مانده نمایان گر وضعیت حساب تا آن سطر است
مثلا در سطر اول -10000 بدهکار شده است پس مانده برابر -10000 است
در سطر دوم 10000 بستانکار شده است خوب حالا مقدار مانده سطر دوم برابر مقدار مانده سطر اول می شود با مقدار بستانکار سطر دوم

دستوری که شما نوشتین برای گام اول کاملا درسته و در مثال بالا دارای خروجی زیر است:

-10000
10000
200000
خوب حالا اگر بشه دستوری نوشت که هر سط رو با سطر قبلیش جمع کنه مشکل حله
یعنی منظورم همان سطر های ستون مانده است:
ردیف
1 0+-10000=-10000
2 -10000+10000=0
3 0 + 200000=200000
همان طور که مشاهده می کنید به روش بالا مقدار ستون مانده بدست آمد
آقا شرمنده که طولانی شد :oops:

[AminSobati]

پس برای مثال، اول من تابع رو میسازم:

create function dbo.Calc(@Radif int, @Bedehkar money, @Bestankar money)
returns money
as
begin
declare @PrevRecord as int
declare @RetVal money
select @PrevRecord=max(radif) from dbo.test where radif<@radif
if @PrevRecord is null
begin
set @RetVal=@Bestankar-@Bedehkar
end
else
begin
declare @PrevMandeh money
select @PrevMandeh=Mandeh from dbo.test where radif=@PrevRecord
set @RetVal=@Bestankar-@Bedehkar+@PrevMandeh
end
return @RetVal
end

بعد جدول رو (باز برای مثال):

create table test(
Radif int,
Bedehkar money,
Bestankar money,
Mandeh as dbo.Calc(Radif, Bedehkar, Bestankar))


insert test values(1, 1000,0)
insert test values(2, 0,1000)
insert test values(3, 0, 2000)

و برای بهینه کردن سرعت تابع، این ایندکس رو نیاز داریم:

create index ix1 on test(radif)
حالا نتیجه:

select * from test
فقط در مورد فیلد Radif دقت کنید که ممکنه ردیف رو شما به شکل دیگه ای کنترل کرده باشین. در صورت نیاز، تابع رو ویرایش کنین تا با ساختار جدول شما مطابقت داشته باشه.
موفق باشید

[shsoft]

با سلام
از اینکه این همه وقت گذاشتین متشکرم :flower:

[bahman.net]

سلام بر همگی
این تابع درخط 7 وبه شرط where radif<@radif
و علامت(; )خطا میده کسی میتونه بگه خطاش چیه؟ یا علت اون چیه
خیلی ممنون می شم

[bahman.net]

کسی جواب ما رو نمی خواد بده؟؟؟؟؟؟؟!!!!!

[AminSobati]

به جای < علامت کوچکتر قرار بدین. اینجا علامت کوچکتر تبدیل به کدهای HTML شده!

[bahman.net]

جناب ثباتی خیلی ازتون ممنونم

[حمیدرضاصادقیان]

سلام.ببخشید این تاپیک خیلی قدیمی رو بالا اوردم.اخه با اجرای اون روی تعداد رکوردهای بالای 40 یا 50 ردیف پیغام خطا میدهد که بیشترین مقدار برای nesting level 32 می باشد.

[AminSobati]

دقیقا چه کاری انجام میدین که این پیغام رو میده؟

[حمیدرضاصادقیان]

تمام مراحلی که خودتون گفتین رو انجام بدین فقط به جای 3 رکورد 50 رکورد ثبت کنین با این مشکل مواجه میشین

[AminSobati]

من همون 3 رکورد رو به تعداد دفعات بیشتر Insert کردم (نزدیک به 200 رکورد) و بعد Select زدم به کمک تابع Calc ولی مشکلی نداشتم. مطمئن هستین که تابع Calc رو در جای دیگه Call نکردین؟ چون مشکل Nest شدن تا 32 مرحله که برای توابع، Trigger یا SP ها رخ میده فقط در صورت تو در تو شدن پیش میاد

[حمیدرضاصادقیان]

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

[حمیدرضاصادقیان]

استاد من اول که تعداد زیادی ردیف وارد کردم در هر دو ستون بد و بس عدد وارد کردم. فکر کردم شاید ایراد از این باشه. شروع کردم از اول در جدول رکورد اضافه کردم تا 35 ردیف درست انجام شد.
دقیقا به 36 که رسید خطا داد.

[حمیدرضاصادقیان]

استاد ثباتی بررسی نکردین شرایط رو؟؟ اگر امکان داره از دیتابیس خودتون یا از اون جدول یک backup قرار بدین تا شاید من اشتباه میکنم

[AminSobati]

حمید جان حق با شماست، اشکال کار رو پیدا کردم. این Query بدون تابع رو شما هم تست کن، امیدوارم جواب بده:


select radif,Bedehkar, Bestankar,
(select sum(-1*Bedehkar+Bestankar) from test t2 where radif<=test.radif)
from test

[linux]

اگه میشه در مورد این بخش از کار صحبت کنیم

ما یک جدول داریم که یه صورت زیر حاوی اطلاعات حساب است که حاصل یک Query می باشد
Treport.id
Treport.date
treport.debtor
treport.credito
debtor شامل بدهکار و creditor شامل بستانکار است
حالا چگونه می شود با یک query مقادیر ستون مانده را بدست آورد
قبلا عین همین سوال را مطرح کردم جوابی را که گرفتم و الان هم خوب کار می کند
می توانید سوال و جواب ها را اینجا ببنید
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79764

[حمیدرضاصادقیان]

استاد ممنون این جواب داد.حالا اون -1 چیست که شما ضرب کردین؟

[AminSobati]

استاد ممنون این جواب داد.حالا اون -1 چیست که شما ضرب کردین؟
برای اینکه فیلد بدهکار باعث بشه از مجموع کل تفریق انجام بده. البته اگر Bestankar و Bedehkar تفریق بشن همون نتیجه رو باید بده

[AminSobati]

قبلا عین همین سوال را مطرح کردم جوابی را که گرفتم و الان هم خوب کار می کند
می توانید سوال و جواب ها را اینجا ببنید
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79764

هر دو یک روش هستن!

[حمیدرضاصادقیان]

ببخشید استاد من نتونستم این روش رو روی جدول خودم پیاده سازی کنم. یک راهنمایی میخواستم.
ببینید ساختار جدول من به این صورته که من در جدول سند ترکیب ردیف و شماره سند رو کلید اصلی گرفتم زیرا برای هر سند حداقل دو ردیف وجود داره که شماره سندش تکرار میشه به همین خاطر این کارو کردم.
بعد یک فیلد دارم که اگر 0 بود یعنی بستانکار و اگر 1 بود یعنی بدهکار.
و یک ستون مبلغ دارم. یعنی برای بدهکار و بستانکار یک ستون مجزا قرار ندادم.
حالا با این وضعیت چیکار باید بکنم.؟

[AminSobati]

لطفا Script و نمونه Data پست کنین

[حمیدرضاصادقیان]

استاد اینم نمونه دیتابیسش.

[AminSobati]

فکر میکنم Case راه حل خوبی باشه. اگر type مثلا بدهکار بود، فیلد مربوطه منفی بشه و در جمع شرکت کنه، اگر بستانکار بود که روال عادی

[حمیدرضاصادقیان]

اخه استاد من از case استفاده کردم ولی وقتی فیلدهاشو در جمع شرکت میدهم میگه این فیلدها وجود نداره.

[AminSobati]

لابد از Alias فیلدها توی محاسبات استفاده کردین!

[linux]

هر دو یک روش هستن!
بله هر دو یکی هستند گفتم لینک مطلب تو سایت باشه چون آنجا کاملا از اول تا اخر با توضیحات کامل مساله حل شده

[حمیدرضاصادقیان]

استاد میشه یک نمونه مثال بزنید من که دیگه هنگ کردم و اینجوری شمارو اذیت میکنم.

[علامت سوال]

پس برای مثال، اول من تابع رو میسازم:

create function dbo.Calc(@Radif int, @Bedehkar money, @Bestankar money)
returns money
as
begin
declare @PrevRecord as int
declare @RetVal money
select @PrevRecord=max(radif) from dbo.test where radif&lt;@radif
if @PrevRecord is null
begin
set @RetVal=@Bestankar-@Bedehkar
end
else
begin
declare @PrevMandeh money
select @PrevMandeh=Mandeh from dbo.test where radif=@PrevRecord
set @RetVal=@Bestankar-@Bedehkar+@PrevMandeh
end
return @RetVal
end

بعد جدول رو (باز برای مثال):

create table test(
Radif int,
Bedehkar money,
Bestankar money,
Mandeh as dbo.Calc(Radif, Bedehkar, Bestankar))


insert test values(1, 1000,0)
insert test values(2, 0,1000)
insert test values(3, 0, 2000)

و برای بهینه کردن سرعت تابع، این ایندکس رو نیاز داریم:

create index ix1 on test(radif)
حالا نتیجه:

select * from test
فقط در مورد فیلد Radif دقت کنید که ممکنه ردیف رو شما به شکل دیگه ای کنترل کرده باشین. در صورت نیاز، تابع رو ویرایش کنین تا با ساختار جدول شما مطابقت داشته باشه.
موفق باشید
آقای امین ثباتی راه حل بسیار جالبه در صورتی که فیلد ردیف رو نداشته باشیم و بخایم بجای اون از فیلد AutoNumber استفاده کنیم یا فیلدی از تنوع Uniqueidentifier تکلیف چی میشه؟
با تشکر قبلی از استاد گرامی

[danial82]

من موجودی همه مشتریان و باهم تو یه جدول میریزم و با فیلد member_id از هم جداشون میکنم یعنی موجودی هرکی رو بدست میارم حالا با این تفاسیر تابع چه تغییری میکنه و اینکه الان جدولم پر از داده است

[farzad1]

با سلام خدمت همه
من هم مشکل شما رو دارم حال اگر جدول من ستون ديگري به نام کد معين داشت (Code) که چندين حساب توي همين جدول باشند چطوري مي تونم باقيمانده اونها رو از هم جداگانه حساب کنم. تازه سال مالي رو هم حساب كنيد كه فيلد Date هم داشته باشه.
با تشکر