查詢出來的數據如何加總 |
|
wangwb_1027
一般會員 發表:6 回覆:13 積分:3 註冊:2006-07-06 發送簡訊給我 |
各位GG好,小弟從考勤系統中查詢出某人的出勤記錄,想讓它自動加總它的平時加班時間和假日加班、法寫加班時間。顯示在旁邊。不知道該如何寫。 代碼如下:
procedure TForm1.BitBtn1Click(Sender: TObject); var sql1:string; VAR BDAT,EDAT:TDATE; begin BDAT:=DATETIMEPICKER1.Date; EDAT:=DATETIMEPICKER2.Date; query1.SQL.Clear; query1.SQL.Add('select a.emp_no,b.emp_name,a.att_date,a.att_dpt,'); query1.SQL.Add('convert(varchar,a.in_time1,120) as in_time1, convert(varchar,a.out_time1,120) as out_time1,'); query1.SQL.Add('convert(varchar,a.in_time2,120) as in_time2, convert(varchar,a.out_time2,120) as out_time2,'); query1.SQL.Add('convert(varchar,a.in_time3,120) as in_time3, convert(varchar,a.out_time3,120) as out_time3,'); query1.SQL.Add('a.work_time as work_time, a.over_time as over_time , a.fdj_over_time as fdj_over_time, a.jr_over_time as jr_over_time'); query1.SQL.Add('from attrecord a, employee b'); query1.SQL.Add('Where a.emp_no = b.emp_no and'); query1.SQL.Add('a.att_date between :DB AND :DE'); query1.SQL.Add('and a.emp_no like :emp_no'); query1.SQL.Add('order by a.att_dpt,a.emp_no'); query1.ParamByName('DB').AsDate:=BDAT; query1.ParamByName('DE').AsDate:=EDAT; query1.ParamByName('emp_no').AsString:=edit1.Text; query1.Prepare; query1.Open; end; procedure TForm1.FormCreate(Sender: TObject); begin DB1.DriverName:='MSSQL'; DB1.Params.Clear; DB1.Params.Add('SERVER NAME=mis-02'); DB1.Params.Add('DATABASE NAME=pis2005'); DB1.Params.Add('USER NAME=sa'); DB1.DatabaseName:='MSQL'; DB1.LoginPrompt:=false; DB1.Connected:=TRUE; Query1.DatabaseName:='MSQL'; Query1.RequestLive:=False; DBGrid1.ReadOnly:=true; end; procedure TForm1.BitBtn2Click(Sender: TObject); var i:integer; xlsfile:string; elcapp,workbook:variant; begin savedialog1.Execute; xlsfile:=savedialog1.filename; elcapp:=Createoleobject('excel.application'); workbook:=createoleobject('excel.sheet'); workbook:=elcapp.workbooks.add; elcapp.cells(1,1):='工號'; elcapp.cells(1,2):='姓名'; elcapp.cells(1,3):='出勤日期'; elcapp.cells(1,4):='部門'; elcapp.cells(1,5):='上班1'; elcapp.cells(1,6):='下班1'; elcapp.cells(1,7):='上班2'; elcapp.cells(1,8):='下班2'; elcapp.cells(1,9):='上班3'; elcapp.cells(1,10):='下班3'; elcapp.cells(1,11):='正常上班時間'; elcapp.cells(1,12):='加班時間'; elcapp.cells(1,13):='法定加班'; elcapp.cells(1,14):='假日加班'; query1.First; for i:=0 to query1.recordcount-1 do begin elcapp.cells(i 2,1):=query1.fieldbyname('emp_no').asstring; elcapp.cells(i 2,2):=query1.fieldbyname('emp_name').asstring; elcapp.cells(i 2,3):=query1.fieldbyname('att_date').asstring; elcapp.cells(i 2,4):=query1.fieldbyname('att_dpt').asstring; elcapp.cells(i 2,5):=query1.fieldbyname('in_time1').asstring; elcapp.cells(i 2,6):=query1.fieldbyname('out_time1').asstring; elcapp.cells(i 2,7):=query1.fieldbyname('in_time2').asstring; elcapp.cells(i 2,8):=query1.fieldbyname('out_time2').asstring; elcapp.cells(i 2,9):=query1.fieldbyname('in_time3').asstring; elcapp.cells(i 2,10):=query1.fieldbyname('out_time3').asstring; elcapp.cells(i 2,11):=query1.fieldbyname('work_time').asstring; elcapp.cells(i 2,12):=query1.fieldbyname('over_time').asstring; elcapp.cells(i 2,13):=query1.fieldbyname('fdj_over_time').asstring; elcapp.cells(i 2,14):=query1.fieldbyname('jr_over_time').asstring; query1.Next; end; |
暗黑破壞神
版主 發表:9 回覆:2301 積分:1627 註冊:2004-10-04 發送簡訊給我 |
|
pcboy
版主 發表:177 回覆:1838 積分:1463 註冊:2004-01-13 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |