- 帖子
- 1
- 主題
- 1
- 精華
- 0
- 積分
- 2
- 點名
- 0
- 作業系統
- windows7
- 軟體版本
- office 2007
- 閱讀權限
- 10
- 性別
- 男
- 註冊時間
- 2016-1-25
- 最後登錄
- 2016-1-26
|
以下是我遇到的問題
請問 如果資料庫中的資料上下班時間是在同一排中
而我只能抓出一天 的上下班
請問
1.我要如何 用讓他用迴圈跑出我要的結果?
2.我加入下面的方法 都會跑出資料庫存在#F這些(我也試過insert into XXX ( ) Select )
WHILE(@VAR1<=31)
BEGIN
SET @day1 = DATEADD(day, 1, @day1)
SET @VAR1 =@VAR1 + 1
END
use demo_face
DECLARE @begintime2 as datetime
DECLARE @endtime2 as datetime
DECLARE @day as datetime
DECLARE @VAR1 int
set @day = '2016-01-23'
set @begintime2 = ' 00:00:00.000'
set @endtime2 = ' 23:59:00.000'
DROP TABLE #f2 ,#f3,#f4,#f5
Create table #f5 (UserNo char(50), 日期1 date,上班時間 time, 下班時間 time,workhours time,workhours1 int,workhours2 int)
SELECT [UserNo],[MatTime],[WorkTime] into #f2 FROM dbo.MF_Record where MatTime between @day+ @begintime2 and @day+@endtime2
select [UserNo],min(#f2.MatTime) as 上班時間,max(#f2.MatTime) as 下班時間,(max(#f2.MatTime)-min(#f2.MatTime)) as 工時 into #f3 from #f2 group by userno
select [UserNo],CONVERT(varchar(12),#f3.上班時間,102) as 日期1,CONVERT(varchar(8),#f3.上班時間,114) as 上班時間,CONVERT(varchar(8),#f3.下班時間,114) as 下班時間,CONVERT(varchar(8),#f3.工時,114) as 工時 into #f4 from #f3
INSERT INTO #f5(UserNo,日期1,上班時間,下班時間,workhours,workhours1,workhours2) select #f4.UserNo,CONVERT(varchar(12),#f4.日期1,102) as date, #f4.上班時間 as duty,#f4.下班時間 as offwork,#f4.工時 as workhours,datediff(HH,#f4.上班時間,#f4.下班時間) as workhours1,datediff(HH,#f4.上班時間,#f4.下班時間)-1 as workhours2 from #f4
select * from #f4 |
|
發表於 2016-1-26 16:13
| 
