报表SQL语句个人整理.doc

报表SQL语句整理 select dbo.fun_money2str(SUM(PurchaseAmount)) PurchaseAmount, dbo.fun_money2str(SUM(DistributeAmount)) DistributeAmount, dbo.fun_money2str(SUM(WarehouseAmount)) WarehouseAmount, dbo.fun_money2str(SUM(PayedAmount)) PayedAmount, dbo.fun_money2str(SUM(WithdrawAmount)) WithdrawAmount,count(Distinct DistributorID) DistributorCount from dbo.REP_DistributorAreaMedtype dam join funGetSubZoneByUID(CGZX,0) SZ ON dam.ZoneID = SZ.ZoneID join funGetSubMedtype(YPLY, 1) gsm on dam.MedicineTypeId = gsm.MedicineTypeId where 1=1 AND OrderYear = 2012 select OrderMonth,HM.MedicineTypeID,HM.MedicineTypeName, dbo.fun_money2str(SUM(PurchaseAmount)) PurchaseAmount, dbo.fun_money2str(SUM(DistributeAmount)) DistributeAmount, dbo.fun_money2str(SUM(WithdrawAmount)) WithdrawAmount from REP_DistributorAreaMedtype HM join funGetSubZoneByUID(CGZX,0) SZ ON HM.ZoneID = SZ.ZoneID join funGetSubMedtype(YPLY, 1) gsm on HM.MedicineTypeId = gsm.MedicineTypeId where 1=1 AND OrderYear = 2012 GROUP BY OrderMonth,HM.MedicineTypeID,HM.MedicineTypeName order by OrderMonth,HM.MedicineTypeName select SUBSTRING(CONVERT(VARCHAR(12), OrderMonth), 0, 5) + 年 + CONVERT(VARCHAR(2), ( ( OrderMonth % 100 - 1 ) / 3 + 1 )) + 季度 AS Quarters, dam.MedicineTypeID,dam.MedicineTypeName, dbo.fun_money2str(SUM(PurchaseAmount)) PurchaseAmount, dbo.fun_money2str(SUM(DistributeAmount)) DistributeAmount, dbo.fun_money2str(SUM(PayedAmount)) PayedAmount, dbo.fun_money2str(SUM(WithdrawAmount)) WithdrawAmount from REP_DistributorAreaMedtype dam join funGetSubZoneByUID(CGZX,1) SZ ON dam.ZoneID = SZ.ZoneID join funGetSubMedtype(YPLY, 1) gsm on dam.MedicineTypeId = gsm.MedicineTypeId where 1=1 AND OrderYear = 2012 GROUP BY SUBSTRING(CONVERT(VARCHAR(12), OrderMonth), 0, 5) + 年 + CONVERT(VARCHAR(2), ( ( OrderMonth % 100 - 1 ) / 3 + 1 )) + 季度,dam.MedicineTypeID,dam.MedicineTypeName order by Quarters,dam.MedicineTypeName select OrderCount,PurchaseCount,PurchaseAmount,DistributeAmount,WarehouseCount,WarehouseAmou