|
|
 |
专题栏目 |
 |
|
相关文章 |
|
|
|
|
|
 |
三明电业局M1卡餐饮计费系统SQL脚本 |
热 |
|
| 三明电业局M1卡餐饮计费系统SQL脚本 |
|
作者：cyz1980 文章来源：不详 点击数： 更新时间：2007-11-14 13:07:07  |
|
|
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
|
|
| 文章录入：mintao 责任编辑：mintao |
|
|
上一篇文章： 福建公安专科学校贫困生补助处理T-SQl代码
下一篇文章： hdsi2.0 sql注入部分抓包分析语句 |
|
|
| 【字体：小 大】【发表评论】【加入收藏】【告诉好友】【打印此文】【关闭窗口】 |
|
 注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] |
| 网友评论：（只显示最新10条。评论内容只代表网友观点，与本站立场无关！） |
| |
|
|
|
|
