مقدار به دست اومده از يه سلكت تقسيم بر نتيجه يه سلكت ديگه

[bftarane]

سلام.
اول اين رو بگم كه خيلي به عنواني كه گذاشتم تكيه نكنيد چون بهتر از اين چيزي به نظرم نرسيد
لطفاً فرض كنيد جدولي داريم كه يكي از فيلدهاش Bill هست و مي خواهيم گزارشي به شكل زير از اين جدول داشته باشيم
كه اون Bill مجموع همه bill ها بر اساس نام واحد در يك سال مشخص هست مثلاً 1386

نام واحد Billpercent Bill
واحد1 12 3.73
واحد 2 14 4.036
واحد 3 60 18.69
واحد 4 235 73.20
مجموع 321
و Billpercent به روش زير به دست مياد
Bill ضربدر 100 ميشه و تقسيم بر مجموع ميشه مثلاً
12 * 100 =1200
1200/321=3.76
حالا من مي خوام اين شكلي كه در بالا مي بينيد از طريق يه sp به دست بيارم.
...........................................
خوب توضيحات بالا يه مثال براي اينكه قضيه رو ساده توضيح بدم بود
حالا در عمل
من يه sp به شكل زير دارم
ALTER PROCEDURE [dbo].[sptblRequestInnerBuyYearlyShow] (@pYear as smallint) AS
declare @pYearOld as smallint
set @pYearOld = @pYear - 1
Execute
(
'SELECT IstgRef.dbo.tbrFolder.folName AS rieSuppliantName,
SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearly,
SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBillPercent ELSE 0 END) AS rieBuyYearlyPercent,
SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearlyOld,
SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBillPercent ELSE 0 END) AS rieBuyYearlyPercentOld
FROM dbo.tblRequestInnerBuyMonth LEFT OUTER JOIN
IstgRef.dbo.tbrFolder ON dbo.tblRequestInnerBuyMonth.rieSuppliant = IstgRef.dbo.tbrFolder.folId
Where rieYear in ( ' + @pYearOld + ' , ' + @pYear + ' )
GROUP BY IstgRef.dbo.tbrFolder.folName '
)
كه نتيجش ميشه گزارش زير
104172

الآن اون درصد ها اشتباهه يعني هر درصد همونطور كه در بالا گفتم بايد از ضرب فيلد مبلغ خريد متناظرش در 100 تقسيم بر جمع كل به دست بياد
لطفاً راهنمايي كنيد چطور مي تونم اين كار رو انجام بدم.

[mo.esmp]

حالا چرا سمت دیتابیس این محاسبات رو انجام میدین چرا سمت کد این کار رو انجام نمیدین ؟ قاعدتن سمت کد هم دست شما بیشتر بازه و هم نگهداریش آسونتر.

[bftarane]

ببينيد اين كدها در يه سيستم آماده هست و من يا توسط كريستال ريپورت يا توسط sp بايد اين كار رو انجام بدم. شما در هر مورد بتونيد راهنمايي كنيد ممنون ميشم.

[bftarane]

سلام.
آخرين چيزي كه فعلاً بهش رسيدم اينه

ALTER PROCEDURE [dbo].[sptblRequestInnerBuyYearlyShow] (@pYear as smallint) AS
declare @pYearOld as smallint
set @pYearOld = @pYear - 1

execute(
'SELECT SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS total,
SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS totalold
FROM dbo.tblRequestInnerBuyMonth

SELECT IstgRef.dbo.tbrFolder.folName AS rieSuppliantName,
SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearly,
SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearlyOld
FROM dbo.tblRequestInnerBuyMonth LEFT OUTER JOIN
IstgRef.dbo.tbrFolder ON dbo.tblRequestInnerBuyMonth.rieSuppliant = IstgRef.dbo.tbrFolder.folId
Where rieYear in ( ' + @pYearOld + ' , ' + @pYear + ' )
GROUP BY IstgRef.dbo.tbrFolder.folName'
)
نتيجه اجراش هم اينه
104185
يعني الان جمع كل رو دارم
Bill رو هم كه دارم حالا بايد بتونم اون عمليات رياضي رو انجام بدم.
حالا مسئله اينه چطور در سلكت دوم به total سلكت قبلي دسترسي داشته باشم؟
و همچنين وقتي اين كد رو به سلكت دوم اضافه مي كنم

((rieBuyYearly*100)/total) as rieBuyYearlyPercent,
با اروز زير مواجه ميشم
Msg 207, Level 16, State 1, Line 8
Invalid column name 'total'.
Msg 8120, Level 16, State 1, Line 8
Column 'dbo.tblRequestInnerBuyMonth.rieBuyYearly' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ولي وقتي اون كدي كه گفتم رو به شكل زير مي نويسم ارور قبلي برطرف ميشه و حالا خطايي مبني بر نشناختن total ميده
((SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END))*100)/total,
يعني اين ارور
Msg 207, Level 16, State 1, Line 8
Invalid column name 'total'.

(1 row(s) affected)
من دارم روش كار مي كنم لطفاً شما هم راهنمايي كنيد چون خيلي برام مهمه اين رو امروز حتماً انجام بدم.

[N_D]

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


Drop Table #T
Create table #T( UnitName varchar(100),Bill int)
Insert into #T values( 'Unit1',12)
Insert into #T values( 'Unit2',14)
Insert into #T values( 'Unit3',60)
Insert into #T values( 'Unit4',235)

Select UnitName,(Bill*100.0)/ Sum(Bill) Over( Order by (select NULL))
From #T

[bftarane]

سلام. من با 2012 كار مي كنم
اين شكل كامل نشون مي ده چي مي خوام
http://barnamenevis.org/attachment.php?attachmentid=104181&d=1368520101
ببينيد دو تا جدولن در يكي bill ها به همراه سال و ماه خريد و واحد خريدار ذخيره ميشن و در يه جدول ديگه واحدها (مثلاً حفاري) ذخيره شدن.
حالا مسئله اينجاست كه من مي خوام جمع كل bill ها بر اساس يك سال رو به دست بيارم كه به دست هم آوردم كه اين ميشه جمع كل (يعني اينجا خريدهاي همه واحدها با هم جمع ميشن) كه اسمش رو گذاشتم total. يعني اون total بدون GroupBy به دست مي ياد.
همچنين bill ها بر اساس سال و واحد خريدار هم به دست اومدن كه اسمش رو گذاشتم rieBuyYearly
حالا من بايد اين rieBuyYearly رو در صد ضرب كنم تقسيم بر Total كنم تا درصد به دست بياد
ممنون.

[N_D]

ببین با استفاده از این الگو میتونی کارتو راه بندازی. ابتدا مطمئن شو که compatiblity level دیتا بیس شما روی 110 تنظیم شده باشه..



Drop Table #T
Create table #T( YR int,UnitName varchar(100),Bill int)
Insert into #T values( 91,'Unit1',12)
Insert into #T values( 91,'Unit2',14)
Insert into #T values( 91,'Unit3',60)
Insert into #T values( 91,'Unit4',235)
Insert into #T values( 92,'Unit1',10)
Insert into #T values( 92,'Unit2',11)
Insert into #T values( 92,'Unit3',12)
Insert into #T values( 92,'Unit4',13)

--Select UnitName,YR,(Bill*100.0)/ Sum(Bill) Over(partition by YR Order by (select NULL)) as F1 From #T

declare @Year int, @S varchar(500)
SET @Year = 92
SET @S =
'
SELECT *
FROM
(SELECT UnitName,YR,(Bill*100.0)/ Sum(Bill) Over(partition by YR Order by (select NULL)) as F1 FROM #T) AS SourceTable
PIVOT
( MAX(F1) FOR YR IN (['+Cast( (@Year-1) as varchar(10) )+'], ['+Cast( (@Year) as varchar(10) )+'])) AS PivotTable;
';
EXEC( @S );



یه جوری دیتا هات رو به فرمتی که تو جدول T# نوشتم در بیار .. یعنی کلا 3 ستون اصلیت باید شامل سال و bill, واحد مورد نظر باشه..
همین کد رو به شکل آزمایشی تاجرا کن و توشو تغییر بده .. بجای T# کوئری و یا جداول join شده خودتو قرار بده

[Mahmoud.Afrad]

چرا از داینامیک کوئری استفاده کردید؟

اسکریپت جدول و داده های آزمایشی رو قرار میدادید تا بتونیم تست کنیم.

به هر حال کوئری زیر رو امتحان کنید:

ALTER PROCEDURE [dbo].[sptblRequestInnerBuyYearlyShow]
(
@pYear as smallint
)
AS
BEGIN
declare @pYearOld as smallint
declare @Total as decimal
declare @TotalOld as decimal

set @pYearOld = @pYear - 1
set @Total = (SELECT SUM(CASE rieYear WHEN @pYear THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) FROM dbo.tblRequestInnerBuyMonth);
set @TotalOld = (SELECT SUM(CASE rieYear WHEN @pYearOld THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) FROM dbo.tblRequestInnerBuyMonth);

with t_cte
as
(
SELECT IstgRef.dbo.tbrFolder.folName AS rieSuppliantName,
SUM(CASE rieYear WHEN @pYear THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearly,
SUM(CASE rieYear WHEN @pYearOld THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearlyOld,
FROM dbo.tblRequestInnerBuyMonth
LEFT OUTER JOIN
IstgRef.dbo.tbrFolder ON dbo.tblRequestInnerBuyMonth.rieSuppliant = IstgRef.dbo.tbrFolder.folId
WHERE rieYear in ( @pYearOld , @pYear )
GROUP BY IstgRef.dbo.tbrFolder.folName
)
select * ,
(rieBuyYearly / @Total * 100) as Percentage ,
(rieBuyYearlyOld / @TotalOld * 100) as PercentageOld
FROM t_cte
END
GO

[bftarane]

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

[bftarane]

سلام.
یه فایل ضمیمه کردم شامل یه دیتابیس تستی که خودم درست کردم. SQLQuery44
SQLQuery1 همون کوئری هست که شما نوشتید آقای Afrad که من با دیتابیسی که خودم ساختم برای تست تطبیق دادم.
و یه کوئری هم که خودم نوشتم ولی الان مشکل اینه که چیزی نشون داده نمیشه اصلاً
ولی من انتظار دارم این نتایج رو ببینم
104201


چرا از داینامیک کوئری استفاده کردید؟


sp از قبل نوشته شده بود که حالا من باید این تغییراتی که گفتم روش انجام بدم.
ممنون.

[bftarane]

بچه ها به صورت زیر درست شد الآن درست کار می کنه
ولی وقتی مجموع 0 باشه خطای Divide by zero error encountered. زیر رو میده که اگه با جستجو به نتیجه نرسم در یه تاپیک دیگه می پرسم. از همه ممنون.
execute(

'DECLARE @t as float
DECLARE @tOld as float




SELECT @t =SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) ,
@tOld= SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END)
FROM dbo.tblRequestInnerBuyMonth


SELECT IstgRef.dbo.tbrFolder.folName AS rieSuppliantName,
SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearly,

((SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END))*100)/@t,


SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END) AS rieBuyYearlyOld,
((SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END))*100)/@tOld

FROM dbo.tblRequestInnerBuyMonth LEFT OUTER JOIN
IstgRef.dbo.tbrFolder ON dbo.tblRequestInnerBuyMonth.rieSuppliant = IstgRef.dbo.tbrFolder.folId
Where rieYear in (' + @pYearOld + ' , ' + @pYear + ')
GROUP BY IstgRef.dbo.tbrFolder.folName'



)

[tooraj_azizi_1035]

جایی که داری تقسیم رو انجام می دی شرط "مخرج <>0 "رو قرار بده تا این حالت پیش نیاد.

[bftarane]

فکر کنم یه همچین چیزی باید بنویسم درسته؟

if @t<>0
((SUM(CASE rieYear WHEN ' + @pYear + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END))*100)/@t,

درسته؟ آخه تو sql تا حالا از if استفاده نکردم.
به صورت زیر نوشتم ارور میده

((SUM(CASE rieYear WHEN ' + @pYearOld + ' THEN dbo.tblRequestInnerBuyMonth.rieBill ELSE 0 END))*100)/@tOld<>0

[bftarane]

به صورت زیر ارورش برطرف شد

NULLIF(@tOld,0)
ولی الآن اون ستون رو همش رو null نشون میده راهی داره به جاش صفر نشون بده؟

[hossein_h62]

به صورت زیر ارورش برطرف شد

NULLIF(@tOld,0)
ولی الآن اون ستون رو همش رو null نشون میده راهی داره به جاش صفر نشون بده؟
ISNULL(NULLIF(@tOld,0),0)

[Mahmoud.Afrad]

ISNULL(NULLIF(@tOld,0),0)
صفر را به null و بعد null را به صفر تبدیل کرده اید؟! همون اول صفر را داریم نیازی به این کار نیست.

با همون case چک کنید اگر صفر بود صفر و اگر صفر نبود تقسیم انجام بشه. لازمه اش هم اینه که مجموع ها رو اول محاسبه کنید. (در اینجا نیازی نیست کوئری را به صورت داینامیک ایجاد کنید و بعد execute کنید)

ALTER PROCEDURE [dbo].[sptblRequestInnerBuyYearlyShow]
(
@pYear AS smallint
)
AS
BEGIN

DECLARE @pYearOld as smallint
DECLARE @Total as decimal
DECLARE @TotalOld as decimal

SET @pYearOld = @pYear - 1
SET @Total = (SELECT ISNULL(SUM(tblinnerBuy.Bill) , 0) FROM tblinnerBuy WHERE tblinnerBuy.YEAR = @pYear);
SET @TotalOld = (SELECT ISNULL(SUM(tblinnerBuy.Bill) , 0) FROM tblinnerBuy WHERE tblinnerBuy.YEAR = @pYearOld);

WITH t_cte
AS
(
SELECT TBLUNIT.unit AS rieSuppliantName,
SUM(CASE Year WHEN @pYear THEN tblinnerBuy.Bill ELSE 0 END) AS rieBuyYearly,
SUM(CASE Year WHEN @pYearOld THEN tblinnerBuy.Bill ELSE 0 END) AS rieBuyYearlyOld
FROM tblinnerBuy
LEFT OUTER JOIN TBLUNIT
ON tblinnerBuy.unitId = TBLUNIT.unitId
WHERE Year in ( @pYearOld , @pYear )
GROUP BY TBLUNIT.unit
)
SELECT * ,
(CASE @total WHEN 0 THEN 0 ELSE (t_cte.rieBuyYearly / @Total * 100) END) AS Percentage ,
(CASE @totalOLD WHEN 0 THEN 0 ELSE (rieBuyYearlyOld / @TotalOld * 100) END) AS PercentageOld
FROM t_cte
End