p; 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(CAST(DAY(dbo.T_ConsumeRec.OpDt) AS char)) AS dt, CollectDt, MealID, OpFare, MngFare, OddFare FROM dbo.T_ConsumeRec) AS consumerec INNER JOIN dbo.T_Customers ON ConsumeRec.CustomerID = dbo.T_Customers.CustomerID WHERE (dbo.T_Customers.CardType = 1) GROUP BY ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term, ConsumeRec.MealID, ConsumeRec.dt) s_c_opf ON s_c_opf.StatID = dbo.T_IncomeRec.StatID AND s_c_opf.Port = dbo.T_IncomeRec.Port AND s_c_opf.Term = dbo.T_IncomeRec.Term AND s_c_opf.MealID = dbo.T_IncomeRec.MealID AND dbo.T_IncomeRec.SumDt = s_c_opf.dt WHERE (dbo.T_IncomeRec.SumDt BETWEEN ''''2004-7-6'''' AND ''''2004-7-6'''')) yf INNER JOIN dbo.T_Eatery ON yf.StatID = dbo.T_Eatery.StatID INNER JOIN dbo.T_Group ON yf.StatID = dbo.T_Group.StatID AND dbo.T_Eatery.STID = dbo.T_Group.STID INNER JOIN dbo.T_Station ON yf.StatID = dbo.T_Station.StatID INNER JOIN dbo.T_Terms ON dbo.T_Eatery.StatID = dbo.T_Terms.StatID AND dbo.T_Group.STID = dbo.T_Terms.STID AND dbo.T_Group.GrpID = dbo.T_Terms.GrpID AND yf.Port = dbo.T_Terms.Port AND yf.Term = dbo.T_Terms.Term INNER JOIN dbo.T_Meal ON yf.MealID = dbo.T_Meal.MealID------------------------------------------------------------------------------------- /*列出未注册卡SQL代码*/ select t_consumerec.customerid as 未注册客户号,t_consumerec.oddfare as 未注册卡余额,t_consumerec.opdt as 未注册卡消费时间 from t_consumerec where t_consumerec.customerid<>all(select customerid from t_customers) and t_consumerec.opdt between ''''2004-9-12 00:00:00'''' and ''''2004-9-15 23:59:59'''' ----------------------------------------------------------------------- /*门禁系统后台数据库IDCARD中T_customers表的触发器*/ create trigger delterms on t_customers for update as declare @id int,@k1 int,@k2 int select @k1=count(*) from t_customers where Inserted select @k2=count(*) from t_customers where Deleted if @k1=@k2 and @k1>0 begin select @id=t_customers.customerid from t_customers,t_terms where t_customers.customerid=t_terms.customerid and t_customers.cardno<>t_terms.cardno delete from t_terms where customerid=@id end ----------------------------------------------------------------------- /*门禁系统的卡号纠正T_SQL代码*/ declare @id int, @no int while 1=1 begin select @id=t_customers.customerid,@no=t_customers.cardno from t_customers,t_terms where t_customers.customerid=t_terms.customerid and t_customers.cardno<>t_terms.cardno order by t_customers.customerid asc if exists(select t_customers.customerid from t_customers,t_terms where t_customers.customerid=t_terms.customerid and t_customers.cardno<>t_terms.cardno ) begin update t_terms set cardno=@no where customerid=@id end else begin print ''''更新完毕!'''' break end end ----------------------------------------------------------------------- /*清除冲突补助月份的T_SQL代码*/ declare @id int declare @month datetime while 1=1 begin SELECT @id=T_SubsidyNotPutOut.CustomerID ,@month=dbo.T_SubsidyNotPutOut.[Month] FROM dbo.T_SubsidyNotPutOut INNER JOIN dbo.T_SubsidyPutOut ON dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month] if (exists(select * FROM T_SubsidyNotPutOut WHERE (CustomerID=@id and [Month] = @month))) begin DELETE FROM T_SubsidyNotPutOut WHERE (CustomerID=@id and [Month] = @month) continue end else begin print ''''没有相关客户的相关补助发放冲突月份!或补助冲突月份已经清理完毕!'''' break end end ----------------------------------------------------------------------- /*前期补助无法发放名单SQL代码*/ SELECT dbo.T_Customers.Name AS 姓名, dbo.T_SubsidyNotPutOut.[Month] AS 月份 FROM dbo.T_SubsidyNotPutOut INNER JOIN dbo.T_SubsidyPutOut ON dbo.T_SubsidyNotPutOut.CustomerID = dbo.T_SubsidyPutOut.CustomerID AND dbo.T_SubsidyNotPutOut.[Month] = dbo.T_SubsidyPutOut.[Month] INNER JOIN dbo.T_Customers ON dbo.T_SubsidyNotPutOut.CustomerID = dbo.T 上一页 [1] [2] [3] [4] 下一页 [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|