PDA

View Full Version : کوئری جمع مبلغ فروش یک فروشگاه



kamran_14
جمعه 13 آذر 1394, 18:37 عصر
سلام
این کوئری جمع مبلغ فروش یه فروشگاه رو محاسبه میکنه
این کد درست عمل میکنه اما برای اینکه در خروجی رکوردهای تکراری نده من دستور groupBy substring(tblsal.tarikh,1,4) را در انتها اضافه میکنم اما با اضافه کردن این دستور خطا میده
من هم در خروجی نوشتم groupBy substring(tblsal.tarikh,1,4) و هم در جلوی دستور groupby نوشتم و از tblsal.tarikh در select داخلی به عنوان متغیر استفاده کردم فکر میکنم به همین خاطر خطا میده چون substring(tblsal.tarikh,1,4) فقط 4 کاراکتر اول tblsal.tarikh رو میده در حالی که من در جدول داخلی tblsal.tarikh رو استفاده کردم اما اگه به این دلیل باشه پس چرا وقتی groupBy substring(tblsal.tarikh,1,4) نمینویسم خطا نمیده؟

select substring(tblsal.tarikh as tarikh),
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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),0) as mordad,
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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),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 tblfactoreforush.tarikh=tblsal.tarikh),0) as esfand
from tblfactoreforush tblsal
where tblsal.tarikh>=@beginTarikh and tblsal.tarikh<=@endTarikh