_Customers.CustomerID
--------------------------------------------------------------------------------
/*显示人员消费情况及联系SQL代码*/
SELECT dbo.T_Customers.Name AS 姓名, dbo.T_ConsumeRec.OpDt AS 消费时间,
dbo.T_ConsumeRec.OpFare AS 消费额, dbo.T_ConsumeRec.OddFare AS 余额,
ISNULL(dbo.T_Department.DpName1, '''''''') + ISNULL(dbo.T_Department.DpName2, '''''''')
+ ISNULL(dbo.T_Department.DpName3, '''''''') AS 部门,
dbo.T_Station.StatName AS 工作站, dbo.T_Eatery.STName AS 食堂,
dbo.T_Group.GrpName AS 食堂组, dbo.T_Terms.Port AS 端口号,
dbo.T_Terms.TermName AS 窗机名称
FROM dbo.T_ConsumeRec INNER JOIN
dbo.T_Customers INNER JOIN
dbo.T_Department ON SUBSTRING(dbo.T_Customers.Account, 1, 2)
= dbo.T_Department.DpCode1 AND SUBSTRING(dbo.T_Customers.Account, 3, 2)
= dbo.T_Department.DpCode2 AND SUBSTRING(dbo.T_Customers.Account, 5, 3)
= dbo.T_Department.DpCode3 ON
dbo.T_ConsumeRec.CustomerID = dbo.T_Customers.CustomerID INNER JOIN
dbo.T_Eatery ON dbo.T_ConsumeRec.StatID = dbo.T_Eatery.StatID INNER JOIN
dbo.T_Group ON dbo.T_ConsumeRec.StatID = dbo.T_Group.StatID AND
dbo.T_Eatery.STID = dbo.T_Group.STID INNER JOIN
dbo.T_Station ON dbo.T_ConsumeRec.StatID = dbo.T_Station.StatID INNER JOIN
dbo.T_Terms ON dbo.T_Eatery.StatID = dbo.T_Terms.StatID AND
dbo.T_Eatery.STID = dbo.T_Terms.STID AND
dbo.T_Group.GrpID = dbo.T_Terms.GrpID AND
dbo.T_ConsumeRec.Port = dbo.T_Terms.Port AND
dbo.T_ConsumeRec.Term = dbo.T_Terms.Term
----------------------------------------------------------------------------------------
/*列出存取款及联系SQL代码*/
SELECT dbo.T_Customers.Name AS 姓名, ISNULL(dbo.T_Department.DpName1, '''''''') + ISNULL(dbo.T_Department.DpName2, '''''''')
+ ISNULL(dbo.T_Department.DpName3, '''''''') AS 部门,
dbo.T_CashRec.CashDt AS 存取款时间, dbo.T_CashRec.InFare AS 存款额,
dbo.T_CashRec.OutFare AS 取款额, dbo.T_CashRec.OddFare AS 余额,
dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,
dbo.T_CashRec.Port AS 出纳机端口, dbo.T_CashRec.Term AS 出纳机机器号
FROM dbo.T_Station INNER JOIN
dbo.T_Cashiers ON dbo.T_Station.StatID = dbo.T_Cashiers.StatID INNER JOIN
dbo.T_Customers INNER JOIN
dbo.T_Department ON SUBSTRING(dbo.T_Customers.Account, 1, 2)
= dbo.T_Department.DpCode1 AND SUBSTRING(dbo.T_Customers.Account, 3, 2)
= dbo.T_Department.DpCode2 AND SUBSTRING(dbo.T_Customers.Account, 5, 3)
= dbo.T_Department.DpCode3 INNER JOIN
dbo.T_CashRec ON dbo.T_Customers.CustomerID = dbo.T_CashRec.CustomerID ON
dbo.T_Cashiers.CashID = dbo.T_CashRec.CashID AND
dbo.T_Station.StatID = dbo.T_CashRec.StatID
-----------------------------------------------------------------------------------------
/*列出incomerec帐本中与现有窗机比较后不存在的窗机*/
SELECT Port, Term
FROM T_IncomeRec
WHERE (Term <> ALL
(SELECT term
FROM t_terms
WHERE statid = 2)) AND (StatID = 2)
----------------------------------------------------------------------------------------
/*公安专发放补助前处理的SQL代码(当月补助形成后)*/
if exists(select customerid from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0 group by customerid having count(*)>1)
begin /*判断5、6类卡是否存在一人两条前期记录即存在去年没领补助的教师*/
select month ,customerid as 去年没领补助的教师的客户号,cardtype,subsidy from t_subsidynotputout where customerid
=any(select customerid from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0 group by customerid having count(*)>1)
end
else
begin
--go
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy)
select * from t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0
/*公安专在当月补助形成后（5、6类卡为教师卡，一年只领一次补助），发补助前：向表monthplan插入符合在表notputout中5、6类卡subsidy为0的记录，
注意是否存在一人两条前期记录(一般不会出现这种况，除非去年没领补助)，否则在monthplan表中后面的更新时间会出错！！*/
--go
delete t_subsidynotputout where (cardtype=5 or cardtype=6) and subsidy=0
/*删除notputout被复制的记录*/
--go
update t_subsidymonthplan set month=''''2004-12-1'''' where (cardtype=5 or cardtype=6) and subsidy=0
/*更改表monthplan中的month日期，月份根据实际定*/
--go
update t_customers set subsidydt=''''2004-12-1'''' where (cardtype=5 or cardtype=6) and cursubsidyfare>0
/*更改t_customers表中的补助时间，注意与t_subsidymonthplan表中的month日期值保持一致！！*/
end
go
select sum(cursubsidyfare) as 客户帐本的补助总额 from t_customers
go
select sum(subsidy) as 前期补助总额 from t_subsidynotputout
go
select sum(subsidy) as 当月补助总额 from t_subsidymonthplan
/*查询客户帐本的补助总额是否等于前期补助总额与当月补助总额之和！*/
--------------------------------------------------------------------------------------------------
/*师大补助月报表信息纠正T_SQL代码*/
declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money /*清除冲突补助月份的T_SQL代码开始*/
declare @id int
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)
end
else
begin
print ''''没有相关客户的相关补助发放冲突月份！或补助冲突月份已经清理完毕！''''
break
end
end /**/
while 1=1
begin
SELECT @id=T_Subsidymonthplan.CustomerID ,@month=dbo.T_Subsidymonthplan.[Month]
FROM dbo.T_Subsidymonthplan INNER JOIN
dbo.T_SubsidyPutOut ON
dbo.T_Subsidymonthplan.CustomerID = dbo.T_Su 上一页 [1] [2] [3] [4] 下一页 |
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 