数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
三明电业局M1卡餐饮计费系统部门月消费总计、月开户统计、卡余额统计SQL脚本

作者:未知 来源:月光软件站 加入时间:2005-6-5 月光软件站

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 [email protected] 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


相关文章

相关软件




月光软件源码下载编程文档电脑教程网站优化网址导航网络文学游戏天地生活休闲写作范文安妮宝贝站内搜索
电脑技术编程开发网络专区谈天说地情感世界游戏元素分类游戏热门游戏体育运动手机专区业余爱好影视沙龙
音乐天地数码广场教育园地科学大观古今纵横谈股论金人文艺术医学保健动漫图酷二手专区地方风情各行各业

月光软件站·版权所有