insert emplWalmartWorkTime
select a.Area,c.编号,c.姓名,c.作息类型,a.日期,a.类型,Null,Null,null,null,null,null,Null,
case when isnull(早班小时,0)>0 and isnull(e.早班请假,0)=0 then m上班一 else 0 end as m上班一,
case when isnull(早班小时,0)>0 and isnull(e.早班请假,0)=0 then m下班一 else 0 end as m下班一,
case when isnull(午班小时,0)>0 and isnull(e.午班请假,0)=0 then m上班二 else 0 end as m上班二,
case when isnull(午班小时,0)>0 and isnull(e.午班请假,0)=0 then m下班二 else 0 end as m下班二,
case when isnull(晚班小时,0)>0 and isnull(e.晚班请假,0)=0 then m上班三 else 0 end as m上班三,
case when isnull(晚班小时,0)>0 and isnull(e.晚班请假,0)=0 then m下班三 else 0 end as m下班三,
(case when 早班类型='正常' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='正常' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='正常' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t正常,
(case when 早班类型='正常加班' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='正常加班' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='正常加班' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t正常加班,
(case when 早班类型='休息日加班' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='休息日加班' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='休息日加班' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t休息日加班,
(case when 早班类型='假日加班' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='假日加班' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='假日加班' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t假日加班,
假日补贴小时
from (select * from emplwalmartDate where area=' & CurArea & ') a
left outer join
emplwalmartDateType b
on a.类型=b.类型
inner join
emplWalmartEmployee c
on a.日期>=isnull(c.入厂日期,'1900-1-1') and a.日期<=isnull(c.离职日期,'2100-1-1') and isnull(c.固定工资,0)=0
left outer join
emplWalmartWorkType d
on c.作息类型=d.作息类型
left outer join
(select * from emplWalmartLeave where area=' & CurArea & ') e
on a.日期=e.日期 and c.编号=e.编号
order by a.Area,c.编号,a.日期
select a.Area,c.编号,c.姓名,c.作息类型,a.日期,a.类型,Null,Null,null,null,null,null,Null,
case when isnull(早班小时,0)>0 and isnull(e.早班请假,0)=0 then m上班一 else 0 end as m上班一,
case when isnull(早班小时,0)>0 and isnull(e.早班请假,0)=0 then m下班一 else 0 end as m下班一,
case when isnull(午班小时,0)>0 and isnull(e.午班请假,0)=0 then m上班二 else 0 end as m上班二,
case when isnull(午班小时,0)>0 and isnull(e.午班请假,0)=0 then m下班二 else 0 end as m下班二,
case when isnull(晚班小时,0)>0 and isnull(e.晚班请假,0)=0 then m上班三 else 0 end as m上班三,
case when isnull(晚班小时,0)>0 and isnull(e.晚班请假,0)=0 then m下班三 else 0 end as m下班三,
(case when 早班类型='正常' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='正常' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='正常' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t正常,
(case when 早班类型='正常加班' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='正常加班' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='正常加班' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t正常加班,
(case when 早班类型='休息日加班' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='休息日加班' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='休息日加班' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t休息日加班,
(case when 早班类型='假日加班' and isnull(e.早班请假,0)=0 then isnull(早班小时,0) else 0 end )+(case when 午班类型='假日加班' and isnull(e.午班请假,0)=0 then isnull(午班小时,0) else 0 end )+(case when 晚班类型 ='假日加班' and isnull(e.晚班请假,0)=0 then isnull(晚班小时,0) else 0 end ) as t假日加班,
假日补贴小时
from (select * from emplwalmartDate where area=' & CurArea & ') a
left outer join
emplwalmartDateType b
on a.类型=b.类型
inner join
emplWalmartEmployee c
on a.日期>=isnull(c.入厂日期,'1900-1-1') and a.日期<=isnull(c.离职日期,'2100-1-1') and isnull(c.固定工资,0)=0
left outer join
emplWalmartWorkType d
on c.作息类型=d.作息类型
left outer join
(select * from emplWalmartLeave where area=' & CurArea & ') e
on a.日期=e.日期 and c.编号=e.编号
order by a.Area,c.编号,a.日期
IIf(条件,满足时的值,不满足时的值)
Access使用的是Jet-SQL,SQL Server使用的是T-SQL,两者用法上相差很大。
JET SQL 帮助(jet4 access2000)下载地址http://www.access911.net/index.asp?board=8&recordid=75FAB71E&tt=
这个语句中几个地方需要改:
1、case when 改用 iif()
2、Access中的多个表关联时,没增加一个表,就需要增加一对()。如:select
from ((A
inner join b on A.id=B.id)
inner join C on A.id=C.id)
IIf 函数
根据表达式的值,来返回两部分中的其中一个。语法IIf(expr, truepart, falsepart)IIf 函数的语法含有下面这些命名参数:部分 描述
expr 必要参数。用来判断真伪的表达式。
truepart 必要参数。如果 expr 为 True,则返回这部分的值或表达式。
falsepart 必要参数。如果 expr 为 False,则返回这部分的值或表达式。 说明由于 IIf 会计算 truepart 和 falsepart,虽然它只返回其中的一个。因此要注意到这个副作用。例如,如果 falsepart 产生一个被零除错误,那么程序就会发生错误,即使 expr 为 True。IIf 函数示例
本示例使用 IIf 函数来判断 CheckIt 过程之 TestMe 参数的值,如果参数值大于 1000 则传回“Large”;否则传回“小图标”。Function CheckIt (TestMe As Integer)
CheckIt = IIf(TestMe > 1000, "Large", "Small")
End Function
2、Access中的多个表关联时,没增加一个表,就需要增加一对()。如:select
from ((A
inner join b on A.id=B.id)
inner join C on A.id=C.id)3、isnull()用nz()或iif(isnull())替换。
分开写。Access中的多个表关联时,没增加一个表,就需要增加一对()。如:select
from ((A
inner join b on A.id=B.id)
inner join C on A.id=C.id)
from (((A
inner join b on A.id=B.id)
left join C as c1 on A.id=C1.id)
left join C as c2 on b.id=c2.id)
结贴