27,579
社区成员
发帖
与我相关
我的任务
分享
餐卡存款表:h_din_pre_money
工号 存款 存款时间 备注 编号(自增列)
emp_id pre_money pre_date remark id
010001 100.00 2011-07-11 10:15:38.000 1
010001 10.00 2011-07-11 11:44:52.517 2
010001 100.00 2011-07-12 10:09:26.063 NULL 4
101781 100.00 2011-07-28 11:19:24.327 NULL 5
餐卡退款表:h_din_tk_money 离职时,需要做退款处理
工号 退款 退款时间 备注 编号(自增列)
emp_id tk_money tk_date remark id
010001 110.00 2011-07-11 11:47:49.690 NULL 1
select
'收入时间' = convert(char(10),h_din_pre_money.pre_date,120) ,
'摘要' = convert(varchar(8000),h_din_pre_money.remark),
'收入' = sum(case when h_din_pre_money.pre_money>= 0 then h_din_pre_money.pre_money else 0 end) ,
'支出' =SUM (case when h_din_tk_money.tk_money >=0 then h_din_tk_money.tk_money else 0 end),
'余额' = sum(case when h_din_pre_money.pre_money>= 0 then h_din_pre_money.pre_money else 0 end)-SUM (case when h_din_tk_money.tk_money >=0 then h_din_tk_money.tk_money else 0 end)
from h_din_pre_money left outer join h_din_tk_money
on
h_din_pre_money.emp_id is not null
and h_din_pre_money.emp_id <>''
and h_din_tk_money.emp_id is not null
and h_din_tk_money.emp_id <>''
and h_din_pre_money.emp_id = h_din_tk_money.emp_id
group by convert(char(10),h_din_pre_money.pre_date,120),
convert(varchar(8000),h_din_pre_money.remark)
select
'收入时间' = convert(char(10),h_din_pre_money.pre_date,120) ,
'摘要' = convert(varchar(8000),h_din_pre_money.remark),
'收入' = sum(case when h_din_pre_money.pre_money>= 0 then h_din_pre_money.pre_money else 0 end) ,
'支出' =h_din_tk_money.tk_money,
'余额' = sum(case when h_din_pre_money.pre_money>= 0 then h_din_pre_money.pre_money else 0 end)
-h_din_tk_money.tk_money
from h_din_pre_money left outer join h_din_tk_money
on
h_din_pre_money.emp_id is not null
and h_din_pre_money.emp_id <>''
and h_din_tk_money.emp_id is not null
and h_din_tk_money.emp_id <>''
and h_din_pre_money.emp_id = h_din_tk_money.emp_id
where h_din_tk_money.tk_money > 0
group by convert(char(10),h_din_pre_money.pre_date,120),
convert(varchar(8000),h_din_pre_money.remark),
h_din_tk_money.tk_money
select
'收入时间' = convert(char(10),h_din_pre_money.pre_date,120) ,
'摘要' = convert(varchar(8000),h_din_pre_money.remark),
'收入' = sum(case when h_din_pre_money.pre_money>= 0 then h_din_pre_money.pre_money else 0 end) ,
'支出' =SUM (case when h_din_tk_money.tk_money >=0 then h_din_tk_money.tk_money else 0 end),
'余额' = sum(case when h_din_pre_money.pre_money>= 0 then h_din_pre_money.pre_money else 0 end)-SUM (case when h_din_tk_money.tk_money >=0 then h_din_tk_money.tk_money else 0 end)
from h_din_pre_money left outer join h_din_tk_money
on h_din_pre_money.emp_id = h_din_tk_money.emp_id
where h_din_pre_money.emp_id is not null
and h_din_pre_money.emp_id <>''
and h_din_tk_money.emp_id is not null
and h_din_tk_money.emp_id <>''
group by convert(char(10),h_din_pre_money.pre_date,120),
convert(varchar(8000),h_din_pre_money.remark)