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
[Access]sql随机抽取记录 [Access]ASP&SQL让select查询结果随机排序的实现方法 [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读：中文化与GB18030标准 [办公软件]linux指令大全
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
