نحوه محاسبه فيلد قيمت ميانگين

[mgh64120]

با عرض سلام خدمت دوستان


جدولي دارم با فيلدهاي "مقدار"، "قيمت واحد" كه در صورت مثبت بودن عدد موجود در فيلد "مقدار" ركورد از نوع "خريد" و در صورت منفي بودن آن، ركورد از نوع "فروش" به حساب ميايد.




Meqdar Fi
-------- -------
20 50
-5 70
25 60
-10 75


كه مقدار موجودي كالا پس از هر خريد يا فروش رو با استفاده از SubQuery بصورت زير بدست آوردم :



Select Meqdar, Fi, (Select Sum(Meqdar) from Kala Where pk <= a.pk) As Mande
From Kala a






Meqdar Fi MojooDi
-------- ----- ----------
20 50 20
-5 70 15
25 60 40
-10 75 30


حالا ميخوام در كنار "موجودي"، "قيمت ميانگين" رو بدست بيارم. واضحه كه پس از هر فروش اين مقدار(قيمت ميانگين) تغييري نداره و فقط بعد از هر خريد جديدي كه از اين كالا انجام ميشه قيمت ميانگين تغيير خواهد كرد.



Meqdar Fi MojooDi Fi_Avg
-------- --- --------- ---------
20 50 20 50
-5 70 15 50
25 60 40 56.25
-10 75 30 56.25


قابل به ذكر كه من از SqlServer2000 استفاده ميكنم.
اميدوارم كه توضيحاتم قابل فهم باشه. ممنون ميشم اگه راهنماييم كنيد.

[محمد سلیم آبادی]

در مورد "قیمت میانگین" توضیحات واضح نبود. یعنی شما می خواهین میانگین فیلد Fee تمام سطرهای قبلی سطر مورد نظر که مقدار مثبت (خرید) دارند را بدست آورید؟



Set NoCount On;
Declare @Sample Table
(
PK int identity(1,1),
Meqdar int,
fee int
)

Insert Into @Sample Values (20, 50)
Insert Into @Sample Values (-5, 70)
Insert Into @Sample Values (25, 60)
Insert Into @Sample Values (-10,75)

Select Meqdar
, fee
, (Select Sum(Meqdar) from @Sample Where pk <= S.pk) As Mande
, (Select Avg(fee) from @Sample Where pk <= S.pk And Meqdar>0) As Fee_Avg
From @Sample S



خروجی:



Meqdar fee Mande Fee_Avg
----------- ----------- ----------- -----------
20 50 20 50
-5 70 15 50
25 60 40 55
-10 75 30 55

[mgh64120]

دوست عزيز ممنون از توجهتان
منظور من از فيلد "ميانگين" رو همراه با مثال توضيح ميدم:




Meqdar Fi MojooDi Fi_Avg
-------- --- --------- ---------
20 50 20 50
-5 70 15 50
25 60 40 56.25
-10 75 30 56.25



در اولين ركورد يه خريد از اين كالا انجام شده به مقدار 20 عدد و "قيمت ميانگين" برابر با همان فيلد"قيمت واحد" يعني 50 تومان است.
در دومين ركورد يه فروش صورت گرفته كه از مقدار موجودي ما (20)كه در فيلد "موجودي" مشخص شده، كم شده و 15 تا باقيمانده است. و همانطور كه قبلا گفتم پس از فروش "قيمت ميانگين" تغييري ندارد.
حال موجودي و قيمت ميانگين برابر است با : موجودي = 15 قيمت ميانگين = 50
حالا در سومين ركورد مقدار 25 عدد از اين كالا با قيمت جديد يعني 60 تومان خريداري شده كه مقادير جديد به شرح زير محاسبه ميشود
موجودي : 15+25=40
قيمت ميانگين فيلد جديد :

750 = 50*15
1500=60*25
-------------------
56.25=40/2250=(25+15)/(1500+750)

يعني ما تعداد 40 عدد از اين كالا با قيمت ميانگين 56.25 رو داريم
و "مقدار فروش شده" در ركورد دوم از موجودي كم شده و ديگر در محاسبه قيمت ميانگين در ركورد سوم به حساب نمي ايد


اميدوارم تونسته باشم واضح تر توضيح داده باشم
اگه باز هم مشكلي داشت بگين تا توضيح بيشتر توضيح بدم
ممنون

[محمد سلیم آبادی]

از Trigger زیر استفاده کنید.
اول خواستم با کوئری این کار را انجام بدهم ولی کار کمی مشکل شد. ترجیحا از DDL کمک گرفتم.



Set NoCount On;
Create Table Test
(
PK int primary key,
Meqdar int,
fee int,
Mojoodi int,
Fee_Avg real
)
GO

Create Trigger Instead_Of_Insert
On Test
Instead Of Insert
AS
Begin
Declare @pk int,
@meqdar int
Select @pk=pk
, @meqdar=meqdar
From Inserted
If Not Exists (Select * From Test)
Insert Into Test
Select pk
, meqdar
, fee
, meqdar
, fee
From Inserted
Else If @meqdar<0
Insert Into Test
Select pk
, meqdar
, fee
, (select Mojoodi From Test Where pk=@pk-1)+meqdar
, (select Fee_Avg From Test Where pk=@pk-1) From Inserted
Else If @meqdar>0
Insert Into Test
Select pk
, meqdar
, fee
, (select Mojoodi From Test Where pk=@pk-1)+meqdar
, (@meqdar*(select fee from inserted)+(select fee_avg*mojoodi From test where pk=@pk-1))
/((select Mojoodi From Test Where pk=@pk-1)+meqdar)
From Inserted
End

Insert Into test (pk, meqdar, fee) Values (1, 20, 50)
Insert Into test (pk, meqdar, fee) Values (2, -5, 70)
Insert Into test (pk, meqdar, fee) Values (3, 25, 60)
Insert Into test (pk, meqdar, fee) Values (4, -10,75)


select * from test

/*
PK Meqdar fee Mojoodi Fee_Avg
----------- ----------- ----------- ----------- -------------
1 20 50 20 50
2 -5 70 15 50
3 25 60 40 56.25
4 -10 75 30 56.25

*/

[mgh64120]

سلام جناب msalim
من كدي رو كه زحمت كشيديد گذاشتيد رو تست كردم (با ورژن 2000) ولي نميدونم چرا جواب نداد.
بعد از اجراي دستور Select هيچ ركوردي نمايش داده نميشه.
يعني در حقيقيت دستور Insert يا Trigger هيچ ركوردي رو به جدول اضافه نميكنه!
با توجه به زحمتي كه كشيديد و من رو شرمنده كرديد اين سئوال رو دارم كه آيا طراحي كوئري براي اين سئوال غيرممكنه؟
چون استفاده از Trigger در اينجا يه كمي كار رو سخت ميكنه و بايستي براي جدولم جهت ويرايش و حدف ركوردها نيز تريگرهاي مربوطه رو هم بنويسم.
و يه سئوال ديگه اينكه ايا ميشه در يك دستور Select يا همان كوئري از دو SubQuery استفاده كرد.

[محمد سلیم آبادی]

بین Trigger و دستورهای Insert از یک GO استفاده کنید. در واقع در همان دسته (Batch) نمی توانیم از تریگری که ایجاد کردیم استفاده کنیم.

غیر ممکن نیست، شاید نیاز باشه کمی صورت مساله تغییر کنه. بعضی مواقع شیوهای Procedural (رویه ای) خیلی ساده تر از روش های Set-based است.


ايا ميشه در يك دستور Select يا همان كوئري از دو SubQuery استفاده كرد
بله

[FSarab]

create table ##Kala
(
pk int identity(1,1),
Meqdar numeric,
Fi numeric,
LastFi numeric,
Mande numeric,
Fi_Avg numeric(18, 2)
)

---------------------------------------------
insert into ##Kala(Meqdar, Fi) values(20, 50)
insert into ##Kala(Meqdar, Fi) values(-5, 70)
insert into ##Kala(Meqdar, Fi) values(25, 60)
insert into ##Kala(Meqdar, Fi) values(-10, 75)
insert into ##Kala(Meqdar, Fi) values(-2, 80)
insert into ##Kala(Meqdar, Fi) values(-1, 85)
insert into ##Kala(Meqdar, Fi) values(10, 65)
insert into ##Kala(Meqdar, Fi) values(14, 72)
insert into ##Kala(Meqdar, Fi) values(-2, 90)
---------------------------------------------

alter table ##Kala add primary key clustered(pk)
---------------------------------------------

---------------------------------------------
declare @Mande numeric
declare @Fi_Avg numeric(18, 2)
declare @LastFi numeric
declare @LastMeqdar numeric

---------------------------------------------
set @LastFi = 0
update ##Kala
set @LastFi = LastFi = case when (Meqdar > 0) then Fi else @LastFi end
---------------------------------------------

update A
set LastFi = (select LastFi from ##Kala where (pk = A.pk - 1))
from ##Kala A
where (pk > 1) and (Meqdar > 0)

---------------------------------------------
set @LastMeqdar = 0
set @Mande = 0
set @Fi_Avg = 0.00

update ##Kala
set
@Mande = Mande = @Mande + Meqdar,
@Fi_Avg = Fi_Avg = case when (Meqdar > 0) then (((@Mande - Meqdar) * LastFi) + (Meqdar * Fi)) / @Mande else @Fi_Avg end
---------------------------------------------

--select Meqdar, Fi, Mande, Fi_Avg from ##Kala
select * from ##Kala

drop table ##Kala

[محمد سلیم آبادی]

FSarab لطفا نتیجه ی Query پست 7 را قرار دهین (با کمک Result to text)

نمی دونم چرا نتیجه ی Query شما با Query من (با کمک Trigger) متفاوت است

[محمد سلیم آبادی]

یک Trigger از نوع After تعریف کنید که بعد از Insert و Update و Delete اجرا شده و جدول شما را Update کند. برای Update کردن جدول از کد که در ادامه قرار داده ام استفاده کنید.


set nocount on
---==========================================
declare @Kala table
(
id int identity(1, 1),
Meqdar int,
Fi int,
Mande int,
Fi_Avg numeric(4, 2)
)
---==========================================
insert into @Kala(Meqdar, Fi) values(20, 50)
insert into @Kala(Meqdar, Fi) values(-5, 70)
insert into @Kala(Meqdar, Fi) values(25, 60)
insert into @Kala(Meqdar, Fi) values(-10, 75)
insert into @Kala(Meqdar, Fi) values(-2, 80)
insert into @Kala(Meqdar, Fi) values(-1, 85)
insert into @Kala(Meqdar, Fi) values(10, 65)
insert into @Kala(Meqdar, Fi) values(14, 72)
insert into @Kala(Meqdar, Fi) values(-2, 90)
---==========================================
declare @Mande int,
@Fi_avg numeric(4, 2)
---==========================================
set @Fi_avg = (select top 1 fi from @Kala)
set @Mande = 0
update @Kala
set Fi_avg = @Fi_avg,
@fi_avg = case when meqdar < 0 or id = 1 then @Fi_avg
else (((meqdar)*(fi))+(@Mande*@Fi_avg))/(@Mande+meqdar) end,
@mande = mande = @mande + meqdar
---==========================================
select cast(Meqdar as CHAR(5)) as meqdar
, cast(Fi as CHAR(5)) as fi
, cast(Mande as CHAR(5)) as mande
, cast(Fi_Avg as CHAR(5)) as fi_avg
from @Kala
---==========================================
/*
meqdar fi mande fi_avg
------ ----- ----- ------
20 50 20 50.00
-5 70 15 50.00
25 60 40 56.25
-10 75 30 56.25
-2 80 28 56.25
-1 85 27 56.25
10 65 37 58.61
14 72 51 62.29
-2 90 49 62.29
*/
---==========================================

[مهدی نان شکری]

با سلام
می بخشید من یک سوال دارم:
شما برای محاسبه چطور متوجه می شوید که باید 15 * 50 شود?
آیا قانون خاصی برای این عمل هست؟
رکورد 5 اگه عدد 2 بود چطور محاسبه می شد؟
یعنی عدد -10 رو از 15 تای مانده از 50 تومانی محاسبه می کنید یا از 25 تای 60 تومانی؟
شما برنامه فروش(میانگین موزون) رو دارید پیاده سازی می کنید؟

[FSarab]

create table #Kala
(
pk int identity(1,1),
Meqdar int,
Fi int,
Mande int,
Fi_Avg real
)

---------------------------------------------
insert into #Kala(Meqdar, Fi) values(20, 50)
insert into #Kala(Meqdar, Fi) values(-5, 70)
insert into #Kala(Meqdar, Fi) values(25, 60)
insert into #Kala(Meqdar, Fi) values(-10, 75)
insert into #Kala(Meqdar, Fi) values(-2, 80)
insert into #Kala(Meqdar, Fi) values(-1, 85)
insert into #Kala(Meqdar, Fi) values(10, 65)
insert into #Kala(Meqdar, Fi) values(14, 72)
insert into #Kala(Meqdar, Fi) values(-2, 90)
---------------------------------------------

alter table #Kala add primary key clustered(pk)
---------------------------------------------

---------------------------------------------
declare @Mande int
declare @Fi_Avg real
set @Mande = 0
set @Fi_Avg = 0.00

update #Kala
set
@Mande = Mande = @Mande + Meqdar,
@Fi_Avg = Fi_Avg = case when (Meqdar > 0) then (((@Mande - Meqdar) * @Fi_Avg) + (Meqdar * Fi)) / @Mande else @Fi_Avg end
---------------------------------------------

select * from #Kala

drop table #Kala

[mgh64120]

با سلام
می بخشید من یک سوال دارم:
شما برای محاسبه چطور متوجه می شوید که باید 15 * 50 شود?
آیا قانون خاصی برای این عمل هست؟
رکورد 5 اگه عدد 2 بود چطور محاسبه می شد؟
یعنی عدد -10 رو از 15 تای مانده از 50 تومانی محاسبه می کنید یا از 25 تای 60 تومانی؟
شما برنامه فروش(میانگین موزون) رو دارید پیاده سازی می کنید؟

دوست عزيز NanShekari
جواب سؤال 1 شما:



Meqdar Fi MojooDi Fi_Avg
-------- --- --------- ---------
20 50 20 50
-5 70 15 50
25 60 40 56.25
-10 75 30 56.25


دوست عزيز لطفا به جدول بالا دقت كنيد و ببينيد با توضيحات من جور مياد يا نه؟
شما در رديف اول مقدار 20 عدد از يك كالا خريداري كرده ايد به قيمت خريد 50 تومان كه قاعدتاً مقدار موجودي كالاي شما برابر 20 و في ميانگين برابر 50خواهد بود.
در سطر دوم شما مقدار 5 تا از اين كالا را در في 70 تومان فروخته ايد. خوب حالا مقدار باقيمانده كالا براي شما مقدار 15 تا و به همان قيمت 50 تومان است چون شما خريد ديگري انجام نداده ايد.
در سطر سوم شما يه خريد انجام ميدهيد به مقدار 25 عدد و به في متفاوت با خريد قبلي يعني به في خريد 60. خوب حالا اگر خواسته باشيد در اين لحظه بدانيد چه مقدار كالا داريد و براي هر عدد از اين كالا چه مبلغي پرداخته ايد چطوري محاسبه ميكنيد؟
تا جايي من ميدونم بايد ميانگين بگيريم.
پس در اينجا مجموع حاصلضرب مقدار هر كالا در قيمت خريد را بر مقدار كل موجودي كالا تقسيم ميكنيم تا نتيجه كه ميانگين قيمت است حاصل شود:

سطر دوم : 15*50 = 750
سطر سوم : 25 * 60=1500
مجموع براير : 2250
تقسيم بر موجودي كالا : 40
قيمت ميانگين برابر : 56.25

دقت كنيد همانطور كه قبلا توضيح داده بودم مقدار منفي به معناي فروش است و فيلد قيمت در سطر فروش كالا قيمت فروش را نگه ميدارد و قيمت فروش در قيمت ميانگين تاثيري ندارد.
و جواب سؤال ديگر :

مقدار 10- نه از 15 تاي مانده در قيمت 50 تومان كسر ميشود و نه از 25 تاي 60 توماني!
بلكه از مقدار موجودي(40) و قيمت ميانگين(56.25) ماقبل رديف 10- كسر ميشود.