function
''把形如２００１０１格式化为２０００年０１月
Public Function FormatStrToDate(ByVal Str As String) As String
If Str.Trim.Length = 4 Then
Return Str & "年"
End If
If Str.Trim.Length = 6 Then
Dim nIAN As String
Dim YUE As String
nIAN = Str.Trim.Substring(0, 4)
YUE = Str.Trim.Substring(4, 2)
Return nIAN & "年" & YUE & "月"
End If
If Str.Trim.Length = 8 Then
Return Str.Trim.Substring(0, 4) & "年" & Str.Trim.Substring(4, 2) & "月" & Str.Trim.Substring(6, 2) & "日"
End If
Return Str
End Function
63358245 - FAN HUA YING -808
树型结构数据在关系数据库中用链式存储相关查询实现
select b.dptcode from base_dept b where b.dptcode_p = (SELECT a.dptcode
FROM base_dept a where a.dptname ='第二餐厅')
union
SELECT a.dptcode
FROM base_dept a where a.dptname ='第二餐厅' /*第二餐厅'的单位代码及其子单位的单位代码*/
select sum(r.opfare) sum_fare from rec_cust_acc r where (r.termid in (SELECT l.termid
FROM base_term l where l.dptcode in (select b.dptcode from base_dept b where b.dptcode_p = (SELECT a.dptcode
FROM base_dept a where a.dptname ='第二餐厅')
union
SELECT c.dptcode
FROM base_dept c where c.dptname ='第二餐厅' ) )) and (r.opdt between to_date('2005-09-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and to_date('2006-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')) /*利用第一段代码，通过明细表进行营业额的统计查询代码*/
福州某火锅城餐饮收费系统查询代码
SELECT consumption.consumptionid AS 消费单号,
consumption.checkindatetime AS 日始时间, consumption.begindatetime AS 开台时间,
consumption.enddatetime AS 结帐时间, consumption.number AS 用餐人数,
emp.empdes AS 开台员工, emp.empdes AS 收银员, site.sitedesc AS 房台
FROM consumption INNER JOIN
emp ON consumption.empid = emp.empid INNER JOIN
site ON consumption.siteid = site.siteid /*消费说明*/
SELECT consumptionid, menuid, SUM(menunumber) AS sum_menunumber
FROM (SELECT consumptionid, menuid, menunumber
FROM consumptionmenu
UNION ALL
SELECT consumptionid, menuid, menunumber
FROM consumption_addmenu) consume
GROUP BY consumptionid, menuid /*消费明细中间代码*/
SELECT group_consume.consumptionid AS 消费单号, group_consume.menuid,
isnull(group_consume.sum_menunumber - consumptioncancel.menunumber,group_consume.sum_menunumber) AS 数量,
menu.menudesc AS 名称, menu.price AS 原价, menu.discount AS 折扣
FROM (SELECT consumptionid, menuid, SUM(menunumber) AS sum_menunumber
FROM (SELECT consumptionid, menuid, menunumber
FROM consumptionmenu
UNION ALL
SELECT consumptionid, menuid, menunumber
FROM consumption_addmenu) consume
GROUP BY consumptionid, menuid) group_consume left outer JOIN
consumptioncancel ON
group_consume.consumptionid = consumptioncancel.consumptionid AND
group_consume.menuid = consumptioncancel.menuid INNER JOIN
menu ON group_consume.menuid = menu.menuid /*消费明细*/
SELECT 消费说明.房台, 消费说明.日始时间, 消费说明.消费单号, 消费说明.用餐人数,
消费说明.开台员工, 消费说明.开台时间, 消费说明.结帐时间, 消费说明.收银员,
消费明细.名称, 消费明细.数量, 消费明细.原价, 消费明细.折扣
FROM (SELECT group_consume.consumptionid AS 消费单号, group_consume.menuid,
isnull(group_consume.sum_menunumber - consumptioncancel.menunumber,group_consume.sum_menunumber) AS 数量,
menu.menudesc AS 名称, menu.price AS 原价, menu.discount AS 折扣
FROM (SELECT consumptionid, menuid, SUM(menunumber) AS sum_menunumber
FROM (SELECT consumptionid, menuid, menunumber
FROM consumptionmenu
UNION ALL
SELECT consumptionid, menuid, menunumber
FROM consumption_addmenu) consume
GROUP BY consumptionid, menuid) group_consume left outer JOIN
consumptioncancel ON
group_consume.consumptionid = consumptioncancel.consumptionid AND
group_consume.menuid = consumptioncancel.menuid INNER JOIN
menu ON group_consume.menuid = menu.menuid) 消费明细 INNER JOIN
(SELECT consumption.consumptionid AS 消费单号,
consumption.checkindatetime AS 日始时间,
consumption.begindatetime AS 开台时间,
consumption.enddatetime AS 结帐时间, consumption.number AS 用餐人数,
emp.empdes AS 开台员工, emp.empdes AS 收银员,
site.sitedesc AS 房台
FROM consumption INNER JOIN
emp ON consumption.empid = emp.empid INNER JOIN
site ON consumption.siteid = site.siteid) 消费说明 ON
消费明细.消费单号 = 消费说明.消费单号 /*消费综合查询*/
/*利用MS SQL实现异构数据库的分布式查询的t-sql代码*/
exec sp_addlinkedserver 'MS_SQL','','SQLOLEDB','172.18.9.20'
exec sp_addlinkedsrvlogin 'MS_SQL','false',null,'Sa','capec' /*创建一个sqlserver对sqlserver的数据库远程链接*/
exec sp_addlinkedserver 'ora', 'Oracle', 'MSDAORA', 'hj'
exec sp_addlinkedsrvlogin 'ora', false, 'sa', 'ccense', '******' /*创建一个sqlserver对Oracle的数据库远程链接*/
SELECT V_REC.CUSTOMERID,REC.OPDT,REC.COLLECTDT,REC.UPLOAD,rec.opcount rec_opcount,v_rec.opcount as v_rec_opcount
FROM ora..CCENSE.REC_CUST_ACC V_REC INNER JOIN
REC_CUST_ACC REC ON V_REC.CUSTOMERID = REC.CUSTOMERID AND
V_REC.OPCOUNT = REC.OPCOUNT
WHERE (REC.UPLOAD = 0) /*MS SQL本机与Oracle的分布式查询*/
SELECT V_REC.CUSTOMERID,REC.OPDT,REC.COLLECTDT,REC.UPLOAD,rec.opcount rec_opcount,v_rec.opcount as v_rec_opcount
FROM ora..CCENSE.REC_CUST_ACC V_REC INNER JOIN
MS_SQL.localcost.dbo.rec_cust_acc REC ON V_REC.CUSTOMERID = REC.CUSTOMERID AND
V_REC.OPCOUNT = REC.OPCOUNT
WHERE (REC.UPLOAD = 0) /*远程MS SQL与Oracle的分布式查询*/
树型结构数据的客户部门SQL显示语句(纯sql语句)
SELECT dpcode1 as dpcode,dpname1 as dpname
FROM BASE_CUSTDEPT where dpcode1>0 and dpcode2=0 and dpcode3=0 and dpcode4=0
union all
SELECT dpcode1+dpcode2 as dpcode,dpname1+dpname2 as dpname
FROM BASE_CUSTDEPT where dpcode1>0 and dpcode2>0 and dpcode3=0 and dpcode4=0
union all
SELECT dpcode1+dpcode2 +dpcode3 as dpcode,dpname1+dpname2+dpname3 as dpname
FROM BASE_CUSTDEPT where dpcode1>0 and dpcode2>0 and dpcode3>0 and dpcode4=0
union all
SELECT dpcode1+dpcode2 +dpcode3+dpcode4 as dpcode,dpname1+dpname2+dpname3+dpname4 as dpname
FROM BASE_CUSTDEPT where dpcode1>0 and dpcode2>0 and dpcode3>0 and dpcode4>0
获取oracle中某一用户某一表空间下的表、列注释说明的SQL查询语句
SELECT A.TABLE_NAME,A.COMMENTS,B.COLUMN_NAME,B.COMMENTS FROM USER_TAB_COMMENTS A,USER_COL_COMMENTS B WHERE A.TABLE_NAME=B.TABLE_NAME
针对gyz单位补助产生的各种问题的T-SQL处理代码
一、本地库的检查与核对代码
use accdblocal
go
SELECT count(*) as cur_cnt
FROM T_Customers
WHERE subsidydt=(SELECT TOP 1 subsidydt
FROM t_customers
ORDER BY subsidydt DESC) and subsidyout='T' /*本地库的T_Customers帐本当月补助已发人数统计*/
go
SELECT COUNT(*) AS cur_out_cnt
FROM T_SubsidyPutOut
WHERE ([Month] =
(SELECT TOP 1 subsidydt
FROM t_customers
ORDER BY subsidydt DESC))/*本地库的T_SubsidyPutOut帐本当月补助已发人数统计*/
go
update T_Customers set T_Customers.subsidyout='T' from T_SubsidyPutOut as out where
T_Customers.customerid=out.customerid and T_Customers.subsidydt=out.month and T_Customers.subsidyout='F' and subsidydt=(SELECT TOP 1 subsidydt
FROM t_customers
ORDER BY subsidydt DESC)/*以t_subsidyputout为准，对本地库的T_Customers帐本的subsidyout字段进行核对更正*/
go
UPDATE T_SubsidyPutOut
SET T_SubsidyPutOut.upload = 1
FROM accdb.dbo.T_SubsidyPutOut AS a_putout
WHERE a_putout.CustomerID = T_SubsidyPutOut.CustomerID AND
a_putout.OpCount = T_SubsidyPutOut.OpCount AND
T_SubsidyPutOut.[Month] = a_putout.[Month] AND T_SubsidyPutOut.upload = 0 /*修改T_SubsidyPutOut帐本已上传的记录，但upload标准位未变的标志*/
二、主库的检查与核对代码
use accdb
go
/*先把t_subsidyputout中针对customerid,month字段的唯一值的约束给取消*/
update T_Customers set T_Customers.subsidyout='T' from
AccdbLocal.dbo.T_Customers l_cust where
l_cust.CustomerID = T_Customers.CustomerID and T_Customers.subsidyout='F' and l_cust.subsidyout='T' /*根据本地库客户帐本的subsidyout的标志更改主库客户帐本的subsidyout的标志*/
go
DELETE FROM T_SubsidyMonthPlan
WHERE (CustomerID IN
(SELECT customerid
FROM t_customers
WHERE subsidyout = 'T'))
go
DELETE FROM T_Subsidynotputout
WHERE (CustomerID IN
(SELECT customerid
FROM t_customers
WHERE subsidyout = 'T')) /*删除客户帐本中subsidyout的标志位为T的客户号的补助未发记录*/
go
/*M1卡计费系统标准补助月末处理SQL代码*/
UPDATE t_subsidymonth /*当月补助处理*/
SET putoutcount = t_subsidymonth.plancount - 新当月未发汇总.cnt_sub,
putoutsubsidy = t_subsidymonth.plansubsidy - 新当月未发汇总.sum_subsidy
FROM (SELECT cardtype.cardtype, ISNULL(当月未发汇总.sum_subsidy, 0)
AS sum_subsidy, ISNULL(当月未发汇总.cnt_sub, 0) AS cnt_sub
FROM (SELECT cardtype
FROM t_subsidymonth
WHERE month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)) cardtype LEFT OUTER JOIN
(SELECT CardType, SUM(Subsidy) AS sum_subsidy, COUNT(*)
AS cnt_sub
FROM T_SubsidyMonthPlan
GROUP BY CardType) 当月未发汇总 ON
当月未发汇总.CardType = cardtype.cardtype) 新当月未发汇总
WHERE t_subsidymonth.cardtype = 新当月未发汇总.cardtype AND month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)
go
UPDATE t_subsidymonth /*当月前期补助处理*/
SET putoutprecount = t_subsidymonth.planprecount - 新当月未发汇总.cnt_sub,
putoutpre = t_subsidymonth.planpre - 新当月未发汇总.sum_subsidy
FROM (SELECT cardtype.cardtype, ISNULL(当月未发汇总.sum_subsidy, 0)
AS sum_subsidy, ISNULL(当月未发汇总.cnt_sub, 0) AS cnt_sub
FROM (SELECT cardtype
FROM t_subsidymonth
WHERE month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)) cardtype LEFT OUTER JOIN
(SELECT CardType, SUM(Subsidy) AS sum_subsidy, COUNT(*)
AS cnt_sub
FROM T_Subsidynotputout
GROUP BY CardType) 当月未发汇总 ON
当月未发汇总.CardType = cardtype.cardtype) 新当月未发汇总
WHERE t_subsidymonth.cardtype = 新当月未发汇总.cardtype AND month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)
go
某单位m1卡计费系统按某大类部门进行月度相关统计的t-sql代码(原创，修正稿)
declare @month datetime
set @month='2005-4-1'
select @month as 月份,dpname1 as 部门,isnull(开户人次,0) as 开户人次,isnull(开户后第一次存款额,0) as 开户后第一次存款额,isnull(消费额,0) as 消费额,
isnull(消费次数,0) as 消费次数,isnull(存取款额,0) as 存取款额,isnull(存取款次数,0) as 存取款次数,isnull(卡余额总额,0) as 卡余额总额
from (select distinct dpcode1,dpname1 from T_Department) Department left outer join (SELECT DpCode1, kh_month, COUNT(*) AS 开户人次, SUM(in_out_fare)
AS 开户后第一次存款额
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(CAST(MONTH(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(DAY(0)) AS kh_month, min_in_out_fare.in_out_fare
FROM T_Customers INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON
T_Customers.Account = dep.dpcode left outer JOIN
(SELECT min_opcount.CustomerID,
T_CashRec.InFare - T_CashRec.OutFare in_out_fare
FROM (SELECT CustomerID, MIN(OpCount) AS min_opcount
FROM T_CashRec
GROUP BY CustomerID) min_opcount INNER JOIN
T_CashRec ON
min_opcount.CustomerID = T_CashRec.CustomerID AND
min_opcount.min_opcount = T_CashRec.OpCount) min_in_out_fare ON
min_in_out_fare.CustomerID = T_Customers.CustomerID)
一级单位月开户明细
GROUP BY DpCode1, kh_month having kh_month=@month/*一级单位月开户汇总*/
) kh on kh.dpcode1=Department.dpcode1 left outer join (SELECT DpCode1, xf_month, SUM(OpFare) AS 消费额,count(*) as 消费次数
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(consumerec.OpDt) AS char))
+ '-' + RTRIM(CAST(MONTH(consumerec.OpDt) AS char)) + '-' + RTRIM(DAY(0))
AS xf_month, consumerec.OpFare
FROM T_ConsumeRec consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode)
一级单位月消费明细
GROUP BY DpCode1, xf_month having xf_month=@month /*一级单位月消费汇总*/
) xf on xf.dpcode1=Department.dpcode1 left outer join (SELECT DpCode1, cqk_month, SUM(inFare - outFare) AS 存取款额,count(*) as 存取款次数
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(consumerec.cashdt) AS char))
+ '-' + RTRIM(CAST(MONTH(consumerec.cashdt) AS char))
+ '-' + RTRIM(DAY(0)) AS cqk_month, consumerec.inFare,
consumerec.outFare
FROM T_CashRec consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode)
一级单位月存取款明细
GROUP BY DpCode1, cqk_month having cqk_month=@month/*一级单位月存取款汇总*/
) cq on cq.dpcode1=Department.dpcode1 left outer join (SELECT dep.DpCode1, sum(id_MaxO.OddFare) as 卡余额总额
FROM (SELECT id_m_maxC.customerid, id_c_o.OddFare
FROM (SELECT customerid, MAX(OpCount) AS max_opcount
FROM (SELECT CustomerID, OpCount, RTRIM(CAST(YEAR(Dt) AS char))
+ '-' + RTRIM(CAST(MONTH(Dt) AS char)) + '-' + RTRIM(DAY(0))
AS month
FROM (SELECT CustomerID, OpCount, OpDt AS dt
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, cashDt AS dt
FROM T_cashRec
UNION ALL
SELECT CustomerID, OpCount, putoutDt AS dt
FROM T_subsidyputout) id_c_d) id_c_m where month <= @month/*月份参数*/
GROUP BY customerid
) id_m_maxC INNER JOIN
(SELECT CustomerID, OpCount, OddFare
FROM (SELECT CustomerID, OpCount, OddFare
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, OddFare
FROM T_cashRec
UNION ALL
SELECT CustomerID, OpCount, OddFare
FROM T_subsidyputout) Lid_c_o) id_c_o ON
id_c_o.CustomerID = id_m_maxC.customerid AND
id_c_o.OpCount = id_m_maxC.max_opcount) id_MaxO INNER JOIN
T_Customers ON id_MaxO.customerid = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode/*一级单位在某月份的卡余额明细*/
group by dep.DpCode1 /*一级单位在某月份的卡余额汇总*/) kye on kye.dpcode1=Department.dpcode1
执行后的示例数据:
月份 部门 开户人次 开户后第一次存款额 消费额 消费次数 存取款额 存取款次数 卡余额总额
2005-4-1 职工卡 4 ￥2,400.00 ￥7,728.29 1054 ￥531,369.40 1112 ￥523,937.84
2005-4-1 职工卡2 0 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
2005-4-1 外单位人员 100 ￥620.00 ￥0.00 0 ￥620.00 4 ￥620.00
2005-4-1 挂帐卡 0 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
2005-4-1 现金卡 2 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
2005-4-1 折扣卡 56 ￥16,500.00 ￥984.40 152 ￥16,500.00 55 ￥15,515.60
2005-4-1 集团代办卡 0 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
某一M1卡计费系统标准补助月末处理SQL代码
UPDATE t_subsidymonth /*当月补助处理*/
SET putoutcount = t_subsidymonth.plancount - 新当月未发汇总.cnt_sub,
putoutsubsidy = t_subsidymonth.plansubsidy - 新当月未发汇总.sum_subsidy
FROM (SELECT cardtype.cardtype, ISNULL(当月未发汇总.sum_subsidy, 0)
AS sum_subsidy, ISNULL(当月未发汇总.cnt_sub, 0) AS cnt_sub
FROM (SELECT cardtype
FROM t_subsidymonth
WHERE month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)) cardtype LEFT OUTER JOIN
(SELECT CardType, SUM(Subsidy) AS sum_subsidy, COUNT(*)
AS cnt_sub
FROM T_SubsidyMonthPlan
GROUP BY CardType) 当月未发汇总 ON
当月未发汇总.CardType = cardtype.cardtype) 新当月未发汇总
WHERE t_subsidymonth.cardtype = 新当月未发汇总.cardtype AND month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)
go
UPDATE t_subsidymonth /*当月前期补助处理*/
SET putoutprecount = t_subsidymonth.planprecount - 新当月未发汇总.cnt_sub,
putoutpre = t_subsidymonth.planpre - 新当月未发汇总.sum_subsidy
FROM (SELECT cardtype.cardtype, ISNULL(当月未发汇总.sum_subsidy, 0)
AS sum_subsidy, ISNULL(当月未发汇总.cnt_sub, 0) AS cnt_sub
FROM (SELECT cardtype
FROM t_subsidymonth
WHERE month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)) cardtype LEFT OUTER JOIN
(SELECT CardType, SUM(Subsidy) AS sum_subsidy, COUNT(*)
AS cnt_sub
FROM T_Subsidynotputout
GROUP BY CardType) 当月未发汇总 ON
当月未发汇总.CardType = cardtype.cardtype) 新当月未发汇总
WHERE t_subsidymonth.cardtype = 新当月未发汇总.cardtype AND month =
(SELECT TOP 1 month
FROM t_subsidymonth
ORDER BY month DESC)
/* 某一单位贫困生补助处理T-SQl代码
需求分析与设计
＊贫困生补助：1、部份贫困生原已有学校给予的常规补助(称为A型卡)，但卡类别不一致（发补助时以卡类别为标准），且这些卡类中的卡中只有部分是贫困生卡。
2、现要求为A型卡的贫困生实际补助额为在原有常规补助额上增加贫困补助额。
3、增加没有常规补助的贫困生(称为B型卡)的补助记录(要非0类卡，否则天王老子都没办法，0类卡不能领补助),其实际补助额就为贫困补助额。
4、补助月报要有所体现（主要在当月计划补助金额上）。
＊前提条件：1、一张卡补助一个月只能领一次(补助以月为发放单位)。2、该单位只能提供含有姓名、部门、贫困补助额等列值的名单，可能含有同名同姓的但没有贫困补助的学生！
＊方案：1、采取相关的信息在当月补助形成后发放前修正，以保证在不动卡内信息的基础上实现常规补助和贫困补助的正常发放。
2、 新建两个基本表：CREATE TABLE [customerid] (
[customerid] [int] NOT NULL ,--客户号
[pk_subsidyfare] [money] NOT NULL ),--贫困补助额
CREATE TABLE [name] (
[name] [varchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,--姓名
[pk_subsidyfare] [money] NOT NULL ) --贫困补助额
3、提取该单位提供的贫困补助名单中的姓名、贫困补助额列值信息（Excel形式）导入到name表中去。在name表基础上新建一个含有customerid、name、部门、pk_subsidyfare列值信息的视图。
4、把含有customerid、name、部门、pk_subsidyfare列值的信息的视图导出到Excel中去，作为同名同姓学生筛选排除之用。
5、把以上筛选排除的结果中的customerid、pk_subsidyfare列值的信息导入到customerid表中去！
6、执行以下的代码（注意只能执行一次，否则实际补助额将会多出、补助月报当月计划补助金额会超支,确记！！）
*/
declare @pk_count/*作贫困生补助记录数统计之用*/ int,@pk_sum_subsidyfare/*作补助额统计之用*/ money,@customerid/*客户号*/ int,@pk_subsidyfare/*贫困补助额*/ money,@subsidyfare/*补助额*/ money,@k/*卡类*/ int,@month/*月份*/ datetime, @subsidyfare1/*补助额*/ money
select top 1 @month= month from t_subsidymonth order by month desc/*获取补助当月份值*/
declare pk_cursor cursor for select customerid,pk_subsidyfare from customerid/*定义贫困生游标，从customerid表查询获取*/
open pk_cursor
fetch next from pk_cursor into @customerid,@pk_subsidyfare
while (@@fetch_status=0)/*更改、处理t_customers、t_subsidymonthplan、t_subsidypre表相关明细记录的循环*/
begin
select @k=cardtype from t_customers where customerid=@customerid
select @subsidyfare=cursubsidyfare from t_customers where customerid=@customerid/*获取贫困生当月常规补助额*/
select @subsidyfare1=subsidy from t_subsidymonthplan where customerid=@customerid
set @subsidyfare=@subsidyfare+@pk_subsidyfare/*贫困生实际补助额=常规补助额+贫困补助额*/
set @subsidyfare1=isnull(@subsidyfare1,0)+@pk_subsidyfare
if not exists(select * from t_subsidymonthplan where customerid=@customerid)
begin/*B型卡处理代码段*/
update t_customers set cursubsidyfare=@subsidyfare,subsidydt=@month,subsidyout='F' where customerid=@customerid
insert into t_subsidymonthplan values(@month,@customerid,@k,@subsidyfare1)
insert into t_subsidypre values(@month,@customerid,@k,@subsidyfare1)
end
else
begin/*A型卡处理代码段*/
update t_customers set cursubsidyfare=@subsidyfare where customerid=@customerid/*更改T_customers表中贫困生记录的cursubsidyfare字段指*/
update t_subsidymonthplan set subsidy=@subsidyfare1 where customerid=@customerid/*更改T_subsidymonthplan表中贫困生记录的subsidy字段指*/
update t_subsidypre set subsidy=@subsidyfare1 where customerid=@customerid and month=@month/*更改T_subsidypre表中贫困生当月记录的subsidy字段指*/
end
fetch next from pk_cursor into @customerid,@pk_subsidyfare
end
close pk_cursor
deallocate pk_cursor
set @k=1/*卡类初设*/
while (@k<=15)/*处理t_subsidymonth表的相关汇总信息的循环*/
begin
if exists(select * from t_subsidymonth where cardtype=@k and month=@month)/*判断某一类卡在t_subsidymonthplan表是否有记录*/
begin/*处理A型卡或有补助要补发的某一类卡的代码段*/
select @pk_count=count(*),@pk_sum_subsidyfare=isnull(sum(subsidy),0) from t_subsidymonthplan where cardtype=@k
update t_subsidymonth set plancount=@pk_count,plansubsidy=@pk_sum_subsidyfare where month=@month and cardtype=@k
end
else
begin/*处理B型卡的代码段*/
if exists(select * from t_subsidymonthplan where cardtype=@k)
begin
select @pk_count=count(*),@pk_sum_subsidyfare=isnull(sum(subsidy),0) from t_subsidymonthplan where cardtype=@k
insert into t_subsidymonth values(@month,@k,@pk_count,@pk_sum_subsidyfare,0,0,0,0,0,0,0,0,0,0,0,getdate(),getdate(),0)
end
end
set @k=@k+1
end
/*月初*/
declare @month/*月份*/ datetime
select top 1 @month= month from t_subsidymonth order by month desc/*获取补助当月份值*/
update t_customers set t_customers.subsidydt=@month ,t_customers.cursubsidyfare=l.sum_subsidy ,t_customers.subsidyout='F' from ( select customerid,sum(subsidy) as sum_subsidy from
(select * from t_subsidynotputout union select * from t_subsidymonthplan) l1 group by customerid) as l
where t_customers.customerid=l.customerid
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy) select subsidydt,customerid,cardtype,0 as subsidyfare from t_customers where customerid in (select distinct customerid from t_subsidynotputout where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month))
-------------------------------------------------------------------
/*补助月报表信息纠正T-SQL代码(当月补助发放完后)*/
declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money
select top 1 @month=month from t_subsidymonth order by month desc /*获取当月补助的月份*/
delete from T_subsidymonthplan where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month)
delete from t_subsidynotputout where customerid in (select customerid from t_customers where subsidyout='T' and cardtype in (select cardtype from t_subsidymonth where month=@month))
declare k cursor for select cardtype from t_subsidymonth where month=@month
open k
fetch next from k into @cardtype
while (@@fetch_status=0)
begin /*以下为修正T_subsidymonth表信息代码，以未发算已发为原则*/
select @k=count(*),@subsidyFare=isnull(sum(subsidy),0) 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=isnull(sum(subsidy),0) from t_subsidymonthplan where cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/
set @k2=@k-@k1
set @subsidyFare2=@subsidyFare-@subsidyFare1
update t_subsidymonth set putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/
print str(@cardtype)+'类卡更改当月的补助信息完成!'
end
else
begin
print str(@cardtype)+'类卡计划总数不一致!'
end
select @k=count(*),@subsidyFare=isnull(sum(subsidy),0) 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 str(@cardtype)+'类卡更改当月的前期补助信息完成!'
fetch next from k into @cardtype
end
close k
deallocate k
某一单位m1卡计费系统几个月或一年只有一次补助的卡实现在窗机领取的优化T-SQL代码(原创)
/*月初*/
declare @month/*月份*/ datetime
select top 1 @month= month from t_subsidymonth order by month desc/*获取补助当月份值*/
update t_customers set t_customers.subsidydt=@month ,t_customers.cursubsidyfare=l.sum_subsidy ,t_customers.subsidyout='F' from ( select customerid,sum(subsidy) as sum_subsidy from t_subsidynotputout where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month) group by customerid) as l
where t_customers.customerid=l.customerid
insert into t_subsidymonthplan(month,customerid,cardtype,subsidy) select subsidydt,customerid,cardtype,0 as subsidyfare from t_customers where customerid in (select distinct customerid from t_subsidynotputout where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month))
-----------------------------------------------------------
/*月末*/
declare @k int, @k1 int,@k2 int,@month datetime,@subsidyFare money,@cardtype tinyint,@subsidyFare1 money,@subsidyFare2 money
select top 1 @month= month from t_subsidymonth order by month desc/*获取补助当月份值*/
delete from T_subsidymonthplan where cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month)
delete from t_subsidynotputout where customerid in (select customerid from t_customers where subsidyout='T' and cardtype in (select cardtype from t_subsidymonth where plancount=0 and month=@month))
declare k cursor for select cardtype from t_subsidymonth where month=@month
open k
fetch next from k into @cardtype
while (@@fetch_status=0)
begin /*以下为修正T_subsidymonth表信息代码，以未发算已发为原则*/
select @k=count(*),@subsidyFare=isnull(sum(subsidy),0) 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=isnull(sum(subsidy),0) from t_subsidymonthplan where cardtype=@cardtype and month=@month /*统计当月补助未发人数及金额*/
set @k2=@k-@k1
set @subsidyFare2=@subsidyFare-@subsidyFare1
update t_subsidymonth set putoutcount=@k2,putoutsubsidy=@subsidyFare2 where cardtype=@cardtype and month=@month /*当月已发人数及金额=当月补助计划人数及金额-当月补助未发人数及金额*/
print str(@cardtype)+'类卡更改当月的补助信息完成!'
end
else
begin
print str(@cardtype)+'类卡计划总数不一致!'
end
select @k=count(*),@subsidyFare=isnull(sum(subsidy),0) 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 str(@cardtype)+'类卡更改当月的前期补助信息完成!'
fetch next from k into @cardtype
end
close k
deallocate k
MS SQL Server树型结构数据显示的SQL语句(纯SQL语句，不用函数)
SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+dpname2+dpname3 as 部门名称 FROM T_Dpt where dpname1 is not null and dpname2 is not null and dpname3 is not null union all SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+dpname2 as 部门名称 FROM T_Dpt where dpname1 is not null and dpname2 is not null and dpname3 is null UNION ALL SELECT dpcode1+dpcode2+dpcode3 as 部门代码,dpname1 as 部门名称 FROM T_Dpt where dpname1 is not null and dpname2 is null and dpname3 is null;
运行结果：
部门代码 部门名称
01 外语系
0102 外语系日语专业
0102001 外语系日语专业0331班
0103 外语系商务英语
0103001 外语系商务英语0341班
0104 外语系教工
0103002 外语系商务英语0242班
附：
用到的基本表
CREATE TABLE [dbo].[T_Dpt] (
[DpCode1] [char] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[DpCode2] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[DpCode3] [char] (3) COLLATE Chinese_PRC_CI_AS NULL ,
[DpName1] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[DpName2] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[DpName3] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
示例数据:
DpCode1 DpCode2 DpCode3 DpName1 DpName2 DpName3
01 外语系 null null
01 02 外语系 日语专业 null
01 02 001 外语系 日语专业 0331班
01 03 外语系 商务英语 null
01 03 001 外语系 商务英语 0341班
01 04 外语系 教工 null
01 03 002 外语系 商务英语 0242班
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=459411
原创T-SQL代码
/*列出未注册卡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.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_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
-------------------------------------------------------------------------------------------------
/*某单位临时卡处理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
------------------------------------------------------------------------------------
/*显示某一星期几的营业报表*/
SELECT DATENAME(dw, T_IncomeRec.SumDt) AS 星期, T_IncomeRec.SumDt AS 日期,
T_Meal.MealName AS 餐别, T_IncomeRec.IncomeFare AS 营业额,
T_IncomeRec.IncomeCount AS 营业次数, T_Station.StatName AS 工作站,
T_Eatery.STName AS 食堂, T_Group.GrpName AS 工作组, T_Terms.Port AS 端口,
T_Terms.TermName AS 窗机名
FROM T_Station INNER JOIN
T_IncomeRec ON T_Station.StatID = T_IncomeRec.StatID INNER JOIN
T_Eatery INNER JOIN
T_Group ON T_Eatery.STID = T_Group.STID AND T_Eatery.StatID = T_Group.StatID ON
T_Station.StatID = T_Eatery.StatID INNER JOIN
T_Terms ON T_Group.GrpID = T_Terms.GrpID AND
T_Group.STID = T_Terms.STID AND T_IncomeRec.Port = T_Terms.Port AND
T_IncomeRec.Term = T_Terms.Term AND
T_Group.StatID = T_Terms.StatID INNER JOIN
T_Meal ON T_IncomeRec.MealID = T_Meal.MealID
WHERE (DATENAME(dw, T_IncomeRec.SumDt) = '星期一')
-------------------------------------------------------------------------------------
/*同名同姓筛选排查之用的视图*/
SELECT T_Customers.CustomerID, name.name, ISNULL(T_Department.DpName1, '')
+ ISNULL(T_Department.DpName2, '') + ISNULL(T_Department.DpName3, '') AS 部门,
name.pk_subsidyfare
FROM T_Customers INNER JOIN
T_Department ON SUBSTRING(T_Customers.Account, 1, 2)
= T_Department.DpCode1 AND SUBSTRING(T_Customers.Account, 3, 2)
= T_Department.DpCode2 AND SUBSTRING(T_Customers.Account, 5, 3)
= T_Department.DpCode3 INNER JOIN
name ON T_Customers.Name = name.name
ORDER BY name.name
