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] 下一页 |
