با سلام
3 جدول دارم dafater_tbl و tbl_kind و asli
tbl_dafater نام دفتر كه code_daftar است و zir_majmoeh كه كدي است ميگويد اين دفتر زير مجموعه كدام شهرستان است
tbl_kind نوع خدماني است كه اين دفتر در طول 1 ماه انجام داده code_kind_khadmat و motaref كه اين فيلد نشان مي دهد كه اين خدمت از نوع قديم است يا جديد
asli جدولي است كه ماه _ سال_كد دفتر _كد خدمت_ترافيك_3نوع مبلغ_2 نوع سهم_كل مبلغ
گزارشي كه ميخواهم بگيرم بايد همه دفاتر را نشان بدهد چه آمار داشته اند چه نداشته باشند
فيلد motaref مشكل ساز شده زماني كه دفتري از 2 نوع خدمت را انجام نداده زماني كه گروپ بر اساس دفتر ميشو د ان دفتر را نشان ميدهد اما آمارش را خير
codegهمان كد زير مجموعه است
ALTER PROCEDURE sp1
@azmah int,
@tamah int,
@azsal int,
@tasal int,
@codeg int
AS
declare @tedad int
select dt0.cod_dt0,dt0.zir_majmoeh,dt1. trf1,dt1.ba_tamr1,dt1.skol1,dt2.skol2,
dt1.ba_naghsh1,dt1.bi_tamr1,
(dt1.sdaftar1)+(dt2.sdaftar2) as sdaftar_k,
(dt1.spost1)+(dt2.spost2) as spost_k,
(dt1.skol1)+(dt2.skol2) as skol,
dt2. trf2,dt2.ba_tamr2,
dt2.ba_naghsh2,dt2.bi_tamr2
from
(select code_daftar as cod_dt0,zir_majmoeh from dbo.tbl_dafater where zir_majmoeh=@codeg) dt0
LEFT OUTER JOIN
(SELECT dbo.tbl_dafater.code_daftar as cod_dt1, SUM(dbo.asli.trafic) AS trf1, SUM(dbo.asli.mablagh_ba_tamr) AS ba_tamr1, SUM(dbo.asli.mablagh_ba_naghsh)
AS ba_naghsh1, SUM(dbo.asli.mablagh_bi_tamr) AS bi_tamr1, SUM(dbo.asli.sahm_daftar) AS sdaftar1, SUM(dbo.asli.sahm_post) AS spost1,
SUM(dbo.asli.kol_mablagh) AS skol1, dbo.tab_kind.motaref
FROM dbo.asli INNER JOIN
dbo.tab_kind ON dbo.asli.code_kind_khadmat = dbo.tab_kind.code_kind_khadmat INNER JOIN
dbo.tbl_dafater ON dbo.asli.code_daftar = dbo.tbl_dafater.code_daftar
WHERE (dbo.asli.mah BETWEEN @azmah AND @tamah ) AND (dbo.asli.sal BETWEEN @azsal AND @tasal ) AND
(dbo.tab_kind.motaref = 1) and (dbo.tbl_dafater.zir_majmoeh = @codeg )
GROUP BY dbo.tbl_dafater.code_daftar, dbo.tab_kind.motaref) dt1
on dt0.cod_dt0=dt1.cod_dt1
LEFT OUTER JOIN
(SELECT dbo.tbl_dafater.code_daftar as cod_dt2, SUM(dbo.asli.trafic) AS trf2, SUM(dbo.asli.mablagh_ba_tamr) AS ba_tamr2, SUM(dbo.asli.mablagh_ba_naghsh)
AS ba_naghsh2, SUM(dbo.asli.mablagh_bi_tamr) AS bi_tamr2, SUM(dbo.asli.sahm_daftar) AS sdaftar2, SUM(dbo.asli.sahm_post) AS spost2,
SUM(dbo.asli.kol_mablagh) AS skol2, dbo.tab_kind.motaref
FROM dbo.asli INNER JOIN
dbo.tab_kind ON dbo.asli.code_kind_khadmat = dbo.tab_kind.code_kind_khadmat INNER JOIN
dbo.tbl_dafater ON dbo.asli.code_daftar = dbo.tbl_dafater.code_daftar
WHERE (dbo.asli.mah BETWEEN @azmah AND @tamah) AND (dbo.asli.sal BETWEEN @azsal AND @tasal) AND
(dbo.tab_kind.motaref = 2) and (dbo.tbl_dafater.zir_majmoeh = @codeg )
GROUP BY dbo.tbl_dafater.code_daftar, dbo.tab_kind.motaref) dt2
on dt0.cod_dt0=dt2.cod_dt2
