表sta_information (员工基本信息表)字段有:sta_id 员工ID,sta_name 员工姓名
---------------------------------------------------------------------------------
表att_dutyinfo (出勤情况表) 字段有:sta_ddate 员工上班时间,sta_DID 员工编号
,att_am_late 上午迟到分钟数,att_pm_late 下午迟到分钟数,att_am_early 上午迟到分钟数,
att_pm_early 下午迟到分钟数,att_daddwork 超时加班分钟数
---------------------------------------------------------------------------------
表att_unattinfo(非正常出勤信息表) 字段有:att_usid 员工ID,att_udate 日期,
att_amtype 上午出去情况(旷工,带请假,非带薪假)
att_pmtyep 下午出勤情况(旷工,带薪假,非带薪假)
-----------------------------------
现在要根据上面3个表的数据,统计出出勤信息,把信息插入到下面表(att_porttotal)
备注:当上午分钟数>0或者下午分钟数>0,则那天上午显示迟到或者早退
当超时加班分钟数>0 则那天上下午都显示加班。
优先考虑非正常出勤情况。出勤天数是除掉非正常出勤外的出勤天数,出勤率等于当月天数除以出勤天数
===================================
现在有另外一个表 att_porttotal (出勤统计表)
字段有 att_ptid 出勤统计Id,att_ptdid 员工ID,att_ampm 上午或下午(0上午,1下午),
att_ptm1 1号,att_ptm2 2号,...,att_ptm31 31号,att_ptday 出勤天数,att_ptattdance 出勤率显示的数据最后是这样的效果 (自己手写的数据不一定合实际统计)
att_ptid att_ptdid att_ampm att_ptm1 att_ptm2 att_ptm3.......att_ptm31,att_ptday,att_ptattdance
1 H001 0 迟到 正常 带薪假 迟到 14 1.0
2 H001 1 早退 正常 非带薪假 正常 10 0.8
3 H002 0 迟到 迟到 正常 迟到 12 0.9
4 H002 1 早退 迟到 正常 迟到 10 0.8
---------------------------------------------------------------------------------
表att_dutyinfo (出勤情况表) 字段有:sta_ddate 员工上班时间,sta_DID 员工编号
,att_am_late 上午迟到分钟数,att_pm_late 下午迟到分钟数,att_am_early 上午迟到分钟数,
att_pm_early 下午迟到分钟数,att_daddwork 超时加班分钟数
---------------------------------------------------------------------------------
表att_unattinfo(非正常出勤信息表) 字段有:att_usid 员工ID,att_udate 日期,
att_amtype 上午出去情况(旷工,带请假,非带薪假)
att_pmtyep 下午出勤情况(旷工,带薪假,非带薪假)
-----------------------------------
现在要根据上面3个表的数据,统计出出勤信息,把信息插入到下面表(att_porttotal)
备注:当上午分钟数>0或者下午分钟数>0,则那天上午显示迟到或者早退
当超时加班分钟数>0 则那天上下午都显示加班。
优先考虑非正常出勤情况。出勤天数是除掉非正常出勤外的出勤天数,出勤率等于当月天数除以出勤天数
===================================
现在有另外一个表 att_porttotal (出勤统计表)
字段有 att_ptid 出勤统计Id,att_ptdid 员工ID,att_ampm 上午或下午(0上午,1下午),
att_ptm1 1号,att_ptm2 2号,...,att_ptm31 31号,att_ptday 出勤天数,att_ptattdance 出勤率显示的数据最后是这样的效果 (自己手写的数据不一定合实际统计)
att_ptid att_ptdid att_ampm att_ptm1 att_ptm2 att_ptm3.......att_ptm31,att_ptday,att_ptattdance
1 H001 0 迟到 正常 带薪假 迟到 14 1.0
2 H001 1 早退 正常 非带薪假 正常 10 0.8
3 H002 0 迟到 迟到 正常 迟到 12 0.9
4 H002 1 早退 迟到 正常 迟到 10 0.8
解决方案 »
- 后面两句vb语句,用delphi怎么写 Dim word As Aspose.Word.Word = New Aspose.Word.Word(); Document doc = word.Open("MyDocument.doc
- TImageList中放入21张图片,最后5张为什么不能显示?
- TListView的多选问题!
- 招 ‘合作者’ 开发软件!欢迎在校学生加入!----》》》
- 动态table field 案例(50分)
- 为什么我将EXECL表的数导入数据库时类型不对。
- 很急!帮我侃侃!
- 关于TRichEdit控件的查找方法议题?
- 帮看看:ADOConnection1.ConnectionString:='Remote Provider={SQL Server};'....我想连不同域的另一台MS SQL SERVER 2000.
- 使用 RainXP 和 XPMenu 制作出精美的 XP 式样窗体
- 求救关于DeleteFile函数问题???
- 能否获取dbgrid的总行数,如果能,该如何获取?
再不行,你就写程序一条条去分析
access中 用iif都是条件选择
写就写完,每次一句一句,几个提示的。
贴出来的就是试了好久不会的嘛。
给提示这些都还知道的,但结果就是弄不出来或者不完整
你要是把整个sql贴出来。我就万分感激你啊
员工基本信息表(sta_information)
sta_id sta_name
H001 apple
H002 steven
------------------------
出勤情况表(att_dutyinfo)
sta_ddate sta_DID att_am_late att_pm_late att_am_early att_pm_early att_daddwork
2009-01-01 H001 5 0 0 2 0
2009-01-02 H001 1 3 6 0 0
2009-01-02 H002 0 2 0 0 30
-------------------------
非正常出勤信息表(att_unattinfo)
att_usid att_udate att_amtype att_pmtype
H001 2009-01-01 旷工 带薪假
H001 2009-01-03 旷工
H002 2009-01-04 非带薪假
--------------------------
出勤统计表 att_porttotal
显示的数据最后是这样的效果 (自己手写的数据不一定合上面的数据)
att_ptid att_ptdid att_ampm att_ptm1 att_ptm2 att_ptm3.......att_ptm31,att_ptday,att_ptattdance
1 H001 0 迟到 正常 带薪假 迟到 14 1.0
2 H001 1 早退 正常 非带薪假 正常 10 0.8
3 H002 0 迟到 迟到 正常 迟到 12 0.9
4 H002 1 早退 迟到 正常 迟到 10 0.8
declare @sta_information table(sta_id varchar(10), sta_name varchar(32)) --员工基本信息表
insert into @sta_information values('H001','apple')
insert into @sta_information values('H002','steven')
select * from @sta_information
------------------------
--出勤情况表
declare @att_dutyinfo table(sta_ddate datetime, sta_DID varchar(10),att_am_late int, att_pm_late int,att_am_early int,att_pm_early int,att_daddwork int)
insert into @att_dutyinfo values ('2009-01-01','H001',5,0,0,2,0)
insert into @att_dutyinfo values ('2009-01-02','H001',1,3,6,0,0)
insert into @att_dutyinfo values ('2009-01-02','H002',0,2,0,0,30)select * from @att_dutyinfo
-------------------------
--非正常出勤信息表(att_unattinfo)declare @att_unattinfo table(att_usid varchar(10), att_udate datetime, att_amtype nvarchar(36),att_pmtype nvarchar(36))
insert into @att_unattinfo values('H001','2009-01-01','旷工','带薪假')
insert into @att_unattinfo values('H001','2009-01-03', '','旷工')
insert into @att_unattinfo values('H002','2009-01-04', '非带薪休假','')
--------------------------select * from @att_unattinfo-- 出勤统计表 att_porttotal
-- 显示的数据最后是这样的效果 (自己手写的数据不一定合上面的数据)
-- att_ptid att_ptdid att_ampm att_ptm1 att_ptm2 att_ptm3.......att_ptm31,att_ptday,att_ptattdance
-- 1 H001 0 迟到 正常 带薪假 迟到 14 1.0
-- 2 H001 1 早退 正常 非带薪假 正常 10 0.8
-- 3 H002 0 迟到 迟到 正常 迟到 12 0.9
-- 4 H002 1 早退 迟到 正常 迟到 10 0.8 select att_usid, sta_name,
case datepart(d,att_udate) when 1 then att_pmtype else '正常' end att_ptm1,
case datepart(d,att_udate) when 2 then att_pmtype else '正常' end att_ptm2,
case datepart(d,att_udate) when 3 then att_pmtype else '正常' end att_ptm3,
case datepart(d,att_udate) when 4 then att_pmtype else '正常' end att_ptm4,
case datepart(d,att_udate) when 5 then att_pmtype else '正常' end att_ptm5,
case datepart(d,att_udate) when 6 then att_pmtype else '正常' end att_ptm6,
case datepart(d,att_udate) when 7 then att_pmtype else '正常' end att_ptm7,
case datepart(d,att_udate) when 8 then att_pmtype else '正常' end att_ptm8,
case datepart(d,att_udate) when 9 then att_pmtype else '正常' end att_ptm9,
case datepart(d,att_udate) when 10 then att_pmtype else '正常' end att_ptm10,
case datepart(d,att_udate) when 11 then att_pmtype else '正常' end att_ptm11,
case datepart(d,att_udate) when 12 then att_pmtype else '正常' end att_ptm12,
case datepart(d,att_udate) when 13 then att_pmtype else '正常' end att_ptm13,
case datepart(d,att_udate) when 14 then att_pmtype else '正常' end att_ptm14,
case datepart(d,att_udate) when 15 then att_pmtype else '正常' end att_ptm15,
case datepart(d,att_udate) when 16 then att_pmtype else '正常' end att_ptm16,
case datepart(d,att_udate) when 17 then att_pmtype else '正常' end att_ptm17,
case datepart(d,att_udate) when 18 then att_pmtype else '正常' end att_ptm18,
case datepart(d,att_udate) when 19 then att_pmtype else '正常' end att_ptm19,
case datepart(d,att_udate) when 20 then att_pmtype else '正常' end att_ptm20,
case datepart(d,att_udate) when 21 then att_pmtype else '正常' end att_ptm21,
case datepart(d,att_udate) when 22 then att_pmtype else '正常' end att_ptm22,
case datepart(d,att_udate) when 23 then att_pmtype else '正常' end att_ptm23,
case datepart(d,att_udate) when 24 then att_pmtype else '正常' end att_ptm24,
case datepart(d,att_udate) when 25 then att_pmtype else '正常' end att_ptm25,
case datepart(d,att_udate) when 26 then att_pmtype else '正常' end att_ptm26,
case datepart(d,att_udate) when 27 then att_pmtype else '正常' end att_ptm27,
case datepart(d,att_udate) when 28 then att_pmtype else '正常' end att_ptm28,
case datepart(d,att_udate) when 29 then att_pmtype else '正常' end att_ptm29,
case datepart(d,att_udate) when 30 then att_pmtype else '正常' end att_ptm30
from @att_unattinfo a left join @sta_information b on b.sta_id = a.att_usid
有一個不能識別的函數datepart
你可能要換別的函數了。
可以了。但只符合一般的要求select att_usid, sta_name,
case EXTRACT(day from att_udate) when 1 then att_pmtype else '正常' end att_ptm1,
case EXTRACT(day from att_udate) when 2 then att_pmtype else '正常' end att_ptm2,
case EXTRACT(day from att_udate) when 3 then att_pmtype else '正常' end att_ptm3,
case EXTRACT(day from att_udate) when 4 then att_pmtype else '正常' end att_ptm4,
case EXTRACT(day from att_udate) when 5 then att_pmtype else '正常' end att_ptm5,
case EXTRACT(day from att_udate) when 6 then att_pmtype else '正常' end att_ptm6,
case EXTRACT(day from att_udate) when 7 then att_pmtype else '正常' end att_ptm7,
case EXTRACT(day from att_udate) when 8 then att_pmtype else '正常' end att_ptm8,
case EXTRACT(day from att_udate) when 9 then att_pmtype else '正常' end att_ptm9,
case EXTRACT(day from att_udate) when 10 then att_pmtype else '正常' end att_ptm10,
case EXTRACT(day from att_udate) when 11 then att_pmtype else '正常' end att_ptm11,
case EXTRACT(day from att_udate) when 12 then att_pmtype else '正常' end att_ptm12,
case EXTRACT(day from att_udate) when 13 then att_pmtype else '正常' end att_ptm13,
case EXTRACT(day from att_udate) when 14 then att_pmtype else '正常' end att_ptm14,
case EXTRACT(day from att_udate) when 15 then att_pmtype else '正常' end att_ptm15,
case EXTRACT(day from att_udate) when 16 then att_pmtype else '正常' end att_ptm16,
case EXTRACT(day from att_udate) when 17 then att_pmtype else '正常' end att_ptm17,
case EXTRACT(day from att_udate) when 18 then att_pmtype else '正常' end att_ptm18,
case EXTRACT(day from att_udate) when 19 then att_pmtype else '正常' end att_ptm19,
case EXTRACT(day from att_udate) when 20 then att_pmtype else '正常' end att_ptm20,
case EXTRACT(day from att_udate) when 21 then att_pmtype else '正常' end att_ptm21,
case EXTRACT(day from att_udate) when 22 then att_pmtype else '正常' end att_ptm22,
case EXTRACT(day from att_udate) when 23 then att_pmtype else '正常' end att_ptm23,
case EXTRACT(day from att_udate) when 24 then att_pmtype else '正常' end att_ptm24,
case EXTRACT(day from att_udate) when 25 then att_pmtype else '正常' end att_ptm25,
case EXTRACT(day from att_udate) when 26 then att_pmtype else '正常' end att_ptm26,
case EXTRACT(day from att_udate) when 27 then att_pmtype else '正常' end att_ptm27,
case EXTRACT(day from att_udate) when 28 then att_pmtype else '正常' end att_ptm28,
case EXTRACT(day from att_udate) when 29 then att_pmtype else '正常' end att_ptm29,
case EXTRACT(day from att_udate) when 30 then att_pmtype else '正常' end att_ptm30
from att_unattinfo a left join sta_information b on b.sta_id = a.att_usid
我把你上面的代碼改成這樣可以了。
但還沒完全符合條件。非正常出勤信息的表可以顯示出來
但出勤信息的表,遲到早退,加班都沒能顯示出來。
好像都顯示正常了。
呵呵
case datepart(d,att_udate) when 1 then att_pmtype else '正常' end att_ptm1,
case datepart(d,att_udate) when 2 then att_pmtype else '正常' end att_ptm2,
case datepart(d,att_udate) when 3 then att_pmtype else '正常' end att_ptm3,
case datepart(d,att_udate) when 4 then att_pmtype else '正常' end att_ptm4,
case datepart(d,att_udate) when 5 then att_pmtype else '正常' end att_ptm5,
case datepart(d,att_udate) when 6 then att_pmtype else '正常' end att_ptm6,
case datepart(d,att_udate) when 7 then att_pmtype else '正常' end att_ptm7,
case datepart(d,att_udate) when 8 then att_pmtype else '正常' end att_ptm8,
case datepart(d,att_udate) when 9 then att_pmtype else '正常' end att_ptm9,
case datepart(d,att_udate) when 10 then att_pmtype else '正常' end att_ptm10,
case datepart(d,att_udate) when 11 then att_pmtype else '正常' end att_ptm11,
case datepart(d,att_udate) when 12 then att_pmtype else '正常' end att_ptm12,
case datepart(d,att_udate) when 13 then att_pmtype else '正常' end att_ptm13,
case datepart(d,att_udate) when 14 then att_pmtype else '正常' end att_ptm14,
case datepart(d,att_udate) when 15 then att_pmtype else '正常' end att_ptm15,
case datepart(d,att_udate) when 16 then att_pmtype else '正常' end att_ptm16,
case datepart(d,att_udate) when 17 then att_pmtype else '正常' end att_ptm17,
case datepart(d,att_udate) when 18 then att_pmtype else '正常' end att_ptm18,
case datepart(d,att_udate) when 19 then att_pmtype else '正常' end att_ptm19,
case datepart(d,att_udate) when 20 then att_pmtype else '正常' end att_ptm20,
case datepart(d,att_udate) when 21 then att_pmtype else '正常' end att_ptm21,
case datepart(d,att_udate) when 22 then att_pmtype else '正常' end att_ptm22,
case datepart(d,att_udate) when 23 then att_pmtype else '正常' end att_ptm23,
case datepart(d,att_udate) when 24 then att_pmtype else '正常' end att_ptm24,
case datepart(d,att_udate) when 25 then att_pmtype else '正常' end att_ptm25,
case datepart(d,att_udate) when 26 then att_pmtype else '正常' end att_ptm26,
case datepart(d,att_udate) when 27 then att_pmtype else '正常' end att_ptm27,
case datepart(d,att_udate) when 28 then att_pmtype else '正常' end att_ptm28,
case datepart(d,att_udate) when 29 then att_pmtype else '正常' end att_ptm29,
case datepart(d,att_udate) when 30 then att_pmtype else '正常' end att_ptm30
from @att_unattinfo a left join @sta_information b on b.sta_id = a.att_usid
where att_ampm = 0
union
select att_usid, sta_name,
case datepart(d,att_udate) when 1 then att_pmtype else '正常' end att_ptm1,
case datepart(d,att_udate) when 2 then att_pmtype else '正常' end att_ptm2,
case datepart(d,att_udate) when 3 then att_pmtype else '正常' end att_ptm3,
case datepart(d,att_udate) when 4 then att_pmtype else '正常' end att_ptm4,
case datepart(d,att_udate) when 5 then att_pmtype else '正常' end att_ptm5,
case datepart(d,att_udate) when 6 then att_pmtype else '正常' end att_ptm6,
case datepart(d,att_udate) when 7 then att_pmtype else '正常' end att_ptm7,
case datepart(d,att_udate) when 8 then att_pmtype else '正常' end att_ptm8,
case datepart(d,att_udate) when 9 then att_pmtype else '正常' end att_ptm9,
case datepart(d,att_udate) when 10 then att_pmtype else '正常' end att_ptm10,
case datepart(d,att_udate) when 11 then att_pmtype else '正常' end att_ptm11,
case datepart(d,att_udate) when 12 then att_pmtype else '正常' end att_ptm12,
case datepart(d,att_udate) when 13 then att_pmtype else '正常' end att_ptm13,
case datepart(d,att_udate) when 14 then att_pmtype else '正常' end att_ptm14,
case datepart(d,att_udate) when 15 then att_pmtype else '正常' end att_ptm15,
case datepart(d,att_udate) when 16 then att_pmtype else '正常' end att_ptm16,
case datepart(d,att_udate) when 17 then att_pmtype else '正常' end att_ptm17,
case datepart(d,att_udate) when 18 then att_pmtype else '正常' end att_ptm18,
case datepart(d,att_udate) when 19 then att_pmtype else '正常' end att_ptm19,
case datepart(d,att_udate) when 20 then att_pmtype else '正常' end att_ptm20,
case datepart(d,att_udate) when 21 then att_pmtype else '正常' end att_ptm21,
case datepart(d,att_udate) when 22 then att_pmtype else '正常' end att_ptm22,
case datepart(d,att_udate) when 23 then att_pmtype else '正常' end att_ptm23,
case datepart(d,att_udate) when 24 then att_pmtype else '正常' end att_ptm24,
case datepart(d,att_udate) when 25 then att_pmtype else '正常' end att_ptm25,
case datepart(d,att_udate) when 26 then att_pmtype else '正常' end att_ptm26,
case datepart(d,att_udate) when 27 then att_pmtype else '正常' end att_ptm27,
case datepart(d,att_udate) when 28 then att_pmtype else '正常' end att_ptm28,
case datepart(d,att_udate) when 29 then att_pmtype else '正常' end att_ptm29,
case datepart(d,att_udate) when 30 then att_pmtype else '正常' end att_ptm30
from @att_unattinfo a left join @sta_information b on b.sta_id = a.att_usid
where att_ampm = 1sqlserver 的语法,你自行更改为firebird,手头没有。