三明电业局M1卡餐饮计费系统部门月消费总计、月开户统计、卡余额统计SQL脚本
use accdb go if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[cyz_sum_month]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) drop procedure [dbo].[cyz_sum_month] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE cyz_sum_month ( @dpcode varchar(7), @month datetime )AS select sum(营业额) as 营业额,sum(营业次数) as 营业次数 from (SELECT T_Station.StatName AS 工作站, T_Eatery.STName AS 营业区, T_Group.GrpName AS 营业组, T_Terms.Port AS 端口, T_Terms.TermName AS 窗机, heixin.dt AS 月份, T_Meal.MealName AS 餐别, heixin.SumOpfare AS 营业额, heixin.CountOpfare AS 营业次数 FROM T_Station INNER JOIN T_Eatery ON T_Station.StatID = T_Eatery.StatID INNER JOIN T_Group ON T_Eatery.StatID = T_Group.StatID AND T_Eatery.STID = T_Group.STID INNER JOIN T_Terms ON T_Group.StatID = T_Terms.StatID AND T_Group.STID = T_Terms.STID AND T_Group.GrpID = T_Terms.GrpID INNER JOIN (SELECT consumerec.StatID, consumerec.Port, consumerec.Term, consumerec.MealID, consumerec.dt, SUM(consumerec.OpFare) AS SumOpfare, COUNT(*) AS CountOpfare FROM (SELECT StatID, Port, Term, CustomerID, RTRIM(CAST(YEAR(dbo.T_ConsumeRec.OpDt) AS char)) + ’-’ + RTRIM(CAST(MONTH(dbo.T_ConsumeRec.OpDt) AS char)) + ’-’ + rtrim(day(0)) AS dt, CollectDt, MealID, OpFare, MngFare, OddFare FROM dbo.T_ConsumeRec) consumerec INNER JOIN T_Customers ON consumerec.CustomerID = T_Customers.CustomerID WHERE (T_Customers.Account LIKE @dpcode + ’%’)/*参数*/ AND (consumerec.dt = @month)/*参数*/ GROUP BY consumerec.StatID, consumerec.Port, consumerec.Term, consumerec.MealID, consumerec.dt) heixin ON T_Terms.Port = heixin.Port AND T_Terms.Term = heixin.Term AND T_Station.StatID = heixin.StatID INNER JOIN T_Meal ON heixin.MealID = T_Meal.MealID) l GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /**/ if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[cyz_sum_month_kh]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1) drop procedure [dbo].[cyz_sum_month_kh] GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO CREATE PROCEDURE cyz_sum_month_kh ( @dpcode varchar(7), @month datetime )AS SELECT ISNULL(T_Department.DpName1, ’’) + ISNULL(T_Department.DpName2, ’’) + ISNULL(T_Department.DpName3, ’’) AS 部门, bmkhrc.部门开户人次 FROM (SELECT Account, COUNT(*) AS 部门开户人次 FROM (SELECT * FROM (SELECT Account, RTRIM(CAST(YEAR(OpenDt) AS char)) + ’-’ + RTRIM(CAST(MONTH(OpenDt) AS char)) + ’-’ + RTRIM(DAY(0)) AS kh_month FROM T_Customers) AS kh WHERE kh_month =@month and account like @dpcode+’%’) kh_month GROUP BY Account) bmkhrc INNER JOIN T_Department ON SUBSTRING(bmkhrc.Account, 1, 2) = T_Department.DpCode1 AND SUBSTRING(bmkhrc.Account, 3, 2) = T_Department.DpCode2 AND SUBSTRING(bmkhrc.Account, 5, 3) = T_Department.DpCode3 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO /**/ if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[bm_k_sum]’) and OBJECTPROPERTY(id, N’IsView’) = 1) drop view [dbo].[bm_k_sum] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.bm_k_sum AS SELECT ISNULL(dbo.T_Department.DpName1, ’’) + ISNULL(dbo.T_Department.DpName2, ’’) + ISNULL(dbo.T_Department.DpName3, ’’) AS 部门, oddfare.s_oddfare AS 卡余额总额, oddfare.cnt AS 卡数 FROM (SELECT Account, SUM(OddFare) AS s_oddfare, COUNT(*) AS cnt FROM T_Customers GROUP BY Account) oddfare INNER JOIN dbo.T_Department ON SUBSTRING(oddfare.Account, 1, 2) = dbo.T_Department.DpCode1 AND SUBSTRING(oddfare.Account, 3, 2) = dbo.T_Department.DpCode2 AND SUBSTRING(oddfare.Account, 5, 3) = dbo.T_Department.DpCode3 GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO grant exec on cyz_sum_month to capec go grant exec on cyz_sum_month_kh to capec go grant select on bm_k_sum to capec go if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[cyz_department]’) and OBJECTPROPERTY(id, N’IsView’) = 1) drop view [dbo].[cyz_department] GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE VIEW dbo.cyz_department AS SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, isnull(DpName1,’’)+isnull(DpName2,’’)+isnull( DpName3,’’) as dpname FROM dbo.T_Department GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO grant select on cyz_department to capec go
