جمع مبلغ فروش به تفکیک ماه

[kamran_14]

سلام
خسته نباشید
من میخوام کوئری بنویسم که جمع مبلغ فروش فروشگاه رو به تفکیک ماه و براساس سال نمایش دهد.
در این کوئری تمامی ماهها در عنوان ستونها نشان داده میشود و داده ها هم جمع مبلغ فروش مربوط به هر ماه را نشان میدهد (گزارش گیری سالانه)
در جدول tblfactoreforush تاریخ فروش و شماره فاکتور فروش ذخیره میشود
در جدول tblAglamefactoreForush مبلغ فروش و شماره فاکتور فروش مربوطه ذخیره میشود
که فیلد شماره فاکتور در هر دو جدول فوق برابر است
آیا من میتونم این کد رو کوتاهتر کنم بدون اینکه خروجی تغیر کند
من خروجی رو هم فرستادم


select substring(tblsal.tarikh,1,4) as sal,
isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='01' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as farvardin,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='02' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as ordebehesht,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='03' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as khordad,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='04' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as tir,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='05' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as mordada,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='06' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as shahrivar,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='07' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as mehr,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='08' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as aban,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='09' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as azar,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='10' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as dey,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='11' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as bahman,

isnull((select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
from tblAglamefactoreForush
inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID
where SUBSTRING(tblfactoreforush.tarikh,6,2)='12' and SUBSTRING(tblfactoreforush.tarikh,1,4)=substring(t blsal.tarikh,1,4)),0) as esfand

--(select sum(tblAglamefactoreForush.tedad*tblAglamefactoreF orush.gemateforush)
--from tblAglamefactoreForush
--inner join tblfactoreforush on tblAglamefactoreForush.factoreforushID=tblfactoref orush.factoreforushID) as sal
from tblfactoreforush tblsal
group by substring(tblsal.tarikh,1,4)

[reza_ali202000]

پیشنهاد بنده اینه که حتما از دستور while (حلقه) استفاده کنید.
چرا که:
1.کد نوشته شده رو تقریبا یک دوازدهم میکنه
2. اطمینان دارید که اگر حتی اشتباه هم حساب میشه برای تمام ماه ها و سالها به یک صورت حساب میشه.(اینجوری ممکنه مال فروردین رو درست کنید مال تیر یادتون بره)
3. مدیریت کد نویسیتون هم ساده تر میشه.

[Mahmoud.Afrad]

اول ستونهای مورد نیاز رو به همراه join سلکت کن تا نیاز به join های مجزا و این همه فراخوانی SUBSTRING نباشه. با در نظر گرفتن اینکه با استفاده از case میتونی چک کنی اگر برج مورد نظر هستی مبلغ رو به تابع sum بفرستی به صورت زیر میتونی انجام بدی

WITH cte
AS
( select SUBSTRING(tarikh , 1 , 4) as yearC,
SUBSTRING(tarikh , 6 , 2) as monthC,
mablagh
from tblfactoreforush
join tblAglamefactoreForush
on tblfactoreforush.id = tblAglamefactoreForush.factorID
)
select cte.yearC as 'year',
SUM(CASE WHEN cte.monthC=01 THEN cte.mablagh END) as 'farvardin' ,
SUM(CASE WHEN cte.monthC=02 THEN cte.mablagh END) as 'ordibehesht' ,
SUM(CASE WHEN cte.monthC=03 THEN cte.mablagh END) as 'khordad' ,
SUM(CASE WHEN cte.monthC=04 THEN cte.mablagh END) as 'tir' ,
SUM(CASE WHEN cte.monthC=05 THEN cte.mablagh END) as 'mordad' ,
SUM(CASE WHEN cte.monthC=06 THEN cte.mablagh END) as 'shahrivar' ,
SUM(CASE WHEN cte.monthC=07 THEN cte.mablagh END) as 'mehr' ,
SUM(CASE WHEN cte.monthC=08 THEN cte.mablagh END) as 'aban' ,
SUM(CASE WHEN cte.monthC=09 THEN cte.mablagh END) as 'azar' ,
SUM(CASE WHEN cte.monthC=10 THEN cte.mablagh END) as 'dey' ,
SUM(CASE WHEN cte.monthC=11 THEN cte.mablagh END) as 'bahman' ,
SUM(CASE WHEN cte.monthC=12 THEN cte.mablagh END) as 'esfand'
from cte
group by cte.yearC
--with rollup

کد بالا رو به شکل زیر هم میتونی بنویسی

select yearC as 'year',
SUM(CASE WHEN monthC=01 THEN mablagh END) as 'farvardin' ,
SUM(CASE WHEN monthC=02 THEN mablagh END) as 'ordibehesht' ,
SUM(CASE WHEN monthC=03 THEN mablagh END) as 'khordad' ,
SUM(CASE WHEN monthC=04 THEN mablagh END) as 'tir' ,
SUM(CASE WHEN monthC=05 THEN mablagh END) as 'mordad' ,
SUM(CASE WHEN monthC=06 THEN mablagh END) as 'shahrivar' ,
SUM(CASE WHEN monthC=07 THEN mablagh END) as 'mehr' ,
SUM(CASE WHEN monthC=08 THEN mablagh END) as 'aban' ,
SUM(CASE WHEN monthC=09 THEN mablagh END) as 'azar' ,
SUM(CASE WHEN monthC=10 THEN mablagh END) as 'dey' ,
SUM(CASE WHEN monthC=11 THEN mablagh END) as 'bahman' ,
SUM(CASE WHEN monthC=12 THEN mablagh END) as 'esfand'
from
(select SUBSTRING(tarikh , 1 , 4) as yearC,
SUBSTRING(tarikh , 6 , 2) as monthC,
mablagh
from tblfactoreforush
join tblAglamefactoreForush
on tblfactoreforush.id = tblAglamefactoreForush.factorID
) as ttt
group by yearC
--with rollup