本人的原创代码（SQL及T_SQL）

/*三明电力临时卡处理SQL代码,临时卡为专门一类卡，消费时五折优惠，月消费有限额，采取策略是“钱多存，消费少报”*/
SELECT dbo.T_Station.StatName AS 工作站, dbo.T_Cashiers.Name AS 出纳员,
      lll.OpDt AS 日期, lll.InFare AS 存款额, lll.InCount AS 存款次数, lll.OutFare AS 取款额,
      lll.OutCount AS 取款次数, ISNULL(lll.suminfare, 0) / 2 AS 让利额, ISNULL(lll.countinfare,
      0) AS 让利次数, (lll.InFare - lll.OutFare) - ISNULL(lll.suminfare, 0)
      / 2 AS 存取款应缴金额
FROM (SELECT dbo.T_CashDay.StatID, dbo.T_CashDay.OpDt, dbo.T_CashDay.CashID,
              dbo.T_CashDay.InFare, dbo.T_CashDay.InCount, dbo.T_CashDay.OutFare,
              dbo.T_CashDay.OutCount, ll.suminfare, ll.countinfare
        FROM dbo.T_CashDay LEFT OUTER JOIN /*采用左外联接查询*/
                  (SELECT cash.StatID, cash.CashID, dbo.T_CashDay.OpDt,
                       dbo.T_CashDay.InFare, dbo.T_CashDay.InCount,
                       dbo.T_CashDay.OutFare, dbo.T_CashDay.OutCount, cash.suminfare,
                       countinfare
                 FROM (SELECT Cashrec.StatID, Cashrec.CashID, Cashrec.dt,
                               SUM(Cashrec.InFare) AS SumInfare, COUNT(*)
                               AS CountInfare
                         FROM (SELECT StatID, CashID, RTRIM(CAST(YEAR(CashDt)
                                       AS char)) + '-' + RTRIM(CAST(MONTH(CashDt) AS char))
                                       + '-' + RTRIM(CAST(DAY(CashDt) AS char)) AS dt /*存款日期*/, InFare,
                                       OutFare, CustomerID
                                 FROM dbo.T_CashRec
                                 WHERE outfare = 0) Cashrec /*存款具体时间转换为日期的视图*/ INNER JOIN
                               dbo.T_Customers ON
                               Cashrec.CustomerID = dbo.T_Customers.CustomerID
                         WHERE (dbo.T_Customers.CardType = 1 /*临时卡类型*/ )
                         GROUP BY Cashrec.StatID, Cashrec.CashID, Cashrec.dt)
                       cash  /*以StatID,CashID,dt作为分组字段，统计出临时卡的存款总额及存款次数的视图*/ INNER JOIN
                       dbo.T_CashDay ON cash.StatID = dbo.T_CashDay.StatID AND
                       cash.CashID = dbo.T_CashDay.CashID AND
                       cash.dt = dbo.T_CashDay.OpDt) ll /*cash视图与T_CashDay表进行联接查询的视图*/ ON
              dbo.T_CashDay.OpDt = ll.OpDt AND dbo.T_CashDay.CashID = ll.CashID AND
              ll.StatID = dbo.T_CashDay.StatID
        WHERE (dbo.T_CashDay.OpDt BETWEEN '2004-7-1' AND '2004-7-1'/*临时卡存款的时间范围*/)) lll INNER JOIN
      dbo.T_Cashiers ON lll.CashID = dbo.T_Cashiers.CashID AND
      lll.StatID = dbo.T_Cashiers.StatID INNER JOIN
      dbo.T_Station ON lll.StatID = dbo.T_Station.StatID

SELECT 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 窗机名称, yf.SumDt AS 日期,
      dbo.T_Meal.MealName AS 餐别, yf.IncomeFare AS 营业额,
      yf.IncomeCount AS 营业次数, ISNULL(yf.SumOpfare, 0) / 2 AS 优惠额,
      ISNULL(yf.CountOpfare, 0) AS 优惠次数, yf.MngFare AS 管理费,
      yf.CorrectFare AS 纠错额, yf.IncomeFare - ISNULL(yf.SumOpfare, 0)
      / 2 + yf.MngFare - yf.CorrectFare AS 实际收入
FROM (SELECT dbo.T_IncomeRec.StatID, dbo.T_IncomeRec.MealID,
              dbo.T_IncomeRec.Port, dbo.T_IncomeRec.Term, dbo.T_IncomeRec.SumDt,
              dbo.T_IncomeRec.IncomeFare, dbo.T_IncomeRec.IncomeCount,
              s_c_opf.SumOpfare, s_c_opf.CountOpfare, dbo.T_IncomeRec.MngFare,
              dbo.T_IncomeRec.CorrectFare
        FROM dbo.T_IncomeRec LEFT OUTER JOIN
                  (SELECT ConsumeRec.StatID, ConsumeRec.Port, ConsumeRec.Term,
                       ConsumeRec.MealID, 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(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_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.Aclearcase/" target="_blank" >ccount, 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_SubsidyPutOut.CustomerID AND
                dbo.T_Subsidymonthplan.[Month] = dbo.T_SubsidyPutOut.[Month]
if (exists(select * FROM T_Subsidymonthplan
    WHERE (CustomerID=@id and [Month] = @month)))
begin
   DELETE FROM T_Subsidymonthplan
   WHERE (CustomerID=@id and [Month] = @month)   
end
else
  begin
    print '没有相关客户的本月补助冲突！或本月补助冲突已经清理完毕！'
    break
  end
end

/*清除冲突补助月份的T_SQL代码结束*/

set @month='2004-9-1' /*补助的月份,根据实际定*/
set @cardtype=4       /*卡的类别，根据实际定*/  
select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidypre where cardtype=@cardtype and month=@month /*统计当月补助计划人数及金额，以t_subsidypre帐本为准*/
if exists(select * from t_subsidymonth where plancount=@k and plansubsidy=@subsidyFare and month=@month　and cardtype=@cardtype)   /*判断当月补助计划数及金额是否正确*/
 begin
   select @k1=count(*),@subsidyFare1=sum(subsidy) from t_subsidymonthplan where   cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/
   if @subsidyFare1 is null
    begin
      set @subsidyFare1=0
    end
   set @k2=@k-@k1
   set @subsidyFare2=@subsidyFare-@subsidyFare1
   update t_subsidymonth set putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype    and    month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/
   print '更改当月的补助信息完成!'
 end
else
 begin
  print '计划总数不一致!'
 end

select @k=count(*),@subsidyFare=sum(subsidy) from t_subsidynotputout where cardtype=@cardtype and month<@month /*统计前期补助未发人数及金额*/
select @k1=planprecount,@subsidyFare1=planpre from t_subsidymonth where cardtype=@cardtype and month=@month /*统计当月前期补助计划人数及金额*/
set @k2=@k1-@k
set @subsidyFare2=@subsidyFare1-@subsidyFare
if @subsidyFare2 is null
    begin
      set @subsidyFare2=0
    end
update t_subsidymonth set putoutprecount=@k2,putoutpre=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月前期已发人数及金额=当月前期补助计划人数及金额-前期补助未发人数及金额*/
print '更改当月的前期补助信息完成!'
-------------------------------------------------------------------------------------------------
           /*清除管理费的触发器及生成t_mngfarelog表的脚本*/
create trigger tr_mngfarelog on t_incomerec for insert,update
as
if exists(select * from t_incomerec where mngfare>0)
   begin
     declare @statid tinyint,@mealid tinyint,@port tinyint,@term tinyint,
        @sumdt datetime,@incomefare money,@mngfare money,@avginc money,
        @incomecount int
     select @statid=statid,@mealid=mealid,@port=port,@term=term,
     @sumdt=sumdt, @incomefare=incomefare,@incomecount=incomecount,
     @mngfare=mngfare from t_incomerec where mngfare>0
     update t_incomerec set mngfare=0 where statid=@statid and mealid=@mealid and
            port=@port and term=@term and sumdt=@sumdt
     set @avginc=@incomefare/@incomecount
     if @avginc>5/*平均消费值，根据实际定*/
       begin
         update t_incomerec set incomefare=4.5*incomecount where
         statid=@statid and mealid=@mealid and port=@port and term=@term and sumdt=@sumdt
       end  
     insert into t_mngfarelog values(@statid,@mealid,@port,@term,
                 @sumdt,@incomefare,@incomecount,@mngfare)  
   end
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t_mngfarelog]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1) /*生成t_mngfarelog表的脚本*/
drop table [dbo].[t_mngfarelog]
GO

CREATE TABLE [dbo].[t_mngfarelog] (
    [statid] [tinyint] NOT NULL ,
    [mealid] [tinyint] NOT NULL ,
    [port] [tinyint] NOT NULL ,
    [term] [tinyint] NOT NULL ,
    [sumdt] [datetime] NOT NULL ,
    [incomefare] [money] NOT NULL ,
    [incomecount] [int] NOT NULL ,
    [mngfare] [money] NULL
) ON [PRIMARY]
GO