none
1、计算工作时间和空闲时间。 RRS feed

  • 问题

  • 1、表

     
    ScanUser ScanTime
    10000-0006 2011-03-13 下午 10:15:00
    10000-0001 2011-03-13 下午 10:15:00
    10000-0006 2011-03-13 下午 10:15:00
    10000-0002 2011-03-13 下午 10:15:00
    10000-0006 2011-03-13 下午 10:16:00
    10000-0006 2011-03-13 下午 10:16:00
    10000-0006 2011-03-13 下午 10:16:00
    10000-0006 2011-03-13 下午 10:16:00
    10000-0006 2011-03-13 下午 10:16:00
    10000-0006 2011-03-13 下午 10:16:00
    10000-0006 2011-03-13 下午 10:18:00
    10000-0006 2011-03-14 下午 07:39:00
    10000-0006 2011-03-14 下午 07:39:00
    10000-0006 2011-03-14 下午 07:39:00
    10000-0006 2011-03-14 下午 07:39:00
    10000-0006 2011-03-14 下午 07:39:00
    10000-0006 2011-03-14 下午 07:39:00
    Scanner_Operation

    2、日期、工号、工作时间、空闲时间。

    一天定义:上一日12:00,下一日12:00。

    上班时间:08:30--17:30(白班),21:00--05:00(夜班),

    空闲条件:(下一条记录-上一天记录)>=5分钟。

    3、可以用SQL语句,也可以用MDX语句。




    • 已编辑 ma_jiang 2011年10月7日 1:44
    2011年9月27日 0:44

答案

  • select
    isnull(x1.日期1,y1.日期2) as 日期
    ,isnull(x1.工号1,y1.工号2) as 工号
    ,isnull(x1.工作时间,0) as 工作时间
    ,isnull(y1.空闲时间,0) as 空闲时间

    from
    (
    (
    select
    --一天定义:上一日12:00:00-下一日12:00:00。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, a.ScanTime) >= '12' THEN dateadd(d, 0, a.ScanTime) ELSE dateadd(d, - 1, a.ScanTime) END), 120), 10) ) as 日期1
    ,a.ScanUser  as 工号1
    ,MIN(convert(varchar,ScanTime,108))最小值1
    ,Max(convert(varchar,ScanTime,108))最大值1
    --工作时间=(最后一次刷卡时间-第一次刷卡时间)
    ,datediff(minute,MIN(convert(varchar,ScanTime,108)),max(convert(varchar,ScanTime,108))) as 工作时间
    from dbo.Scanner_Operation  a
    where
    --计算2011年x月x日-2011年x月x日,工作时间。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, a.ScanTime) >= '12' THEN dateadd(d, 0, a.ScanTime) ELSE dateadd(d, - 1, a.ScanTime) END), 120), 10)) between '2011-01-01' and '2011-01-30'
    --上班时间:08:00:00--18:00:00(白班)。
    and  (DATEPART(HH, ScanTime)>='8' and DATEPART(HH, ScanTime)<='18' )--白班
    group by CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, a.ScanTime) >= '12' THEN dateadd(d, 0, a.ScanTime) ELSE dateadd(d, - 1, a.ScanTime) END), 120), 10))
    ,a.ScanUser
    )x1
    full join
    (
    select
    isnull(b.日期,c.日期)  as 日期2,
    isnull(b.工号,c.工号)  as 工号2,
    sum(datediff(minute,b.时间,c.时间))as 空闲时间
    from
    (
    --1、上一条记录。
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    (
    select
    ROW_NUMBER() over(order by
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)
    ) as 序号
    --一天定义:上一日12:00:00-下一日12:00:00。
    ,CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) ) as 日期
    ,ScanUser  as 工号
    ,convert(varchar,ScanTime,108) as 时间
    from dbo.Scanner_Operation   
    where
    --计算2011年x月x日-2011年x月x日,工作时间。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10)) between '2011-01-01' and '2011-01-30'
    --上班时间:08:00:00--18:00:00(白班)。
    and  (DATEPART(HH, ScanTime)>='8' and DATEPART(HH, ScanTime)<='18' )--白班
    group by CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)

    )b
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    full join
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    --2、下一条记录。
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    (select

    ROW_NUMBER() over(order by
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)
    ) as 序号
    --一天定义:上一日12:00:00-下一日12:00:00。
    ,CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) ) as 日期
    ,ScanUser  as 工号
    ,convert(varchar,ScanTime,108) as 时间
    from dbo.Scanner_Operation   
    where
    --计算2011年x月x日-2011年x月x日,工作时间。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10)) between '2011-01-01' and '2011-01-30'
    --上班时间:08:00:00--18:00:00(白班)。
    and  (DATEPART(HH, ScanTime)>='8' and DATEPART(HH, ScanTime)<='18' )--白班
    group by CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)

    )c
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    on   b.序号=c.序号-1 and b.日期=c.日期  and b.工号=c.工号
    )
    --空闲条件:(下一条记录-上一条记录)>5分钟。
    where datediff(minute,b.时间,c.时间)>5
    group by isnull(b.日期,c.日期)
    ,isnull(b.工号,c.工号)

    )y1
    on x1.日期1=y1.日期2 and x1.工号1=y1.工号2
    )
    order by 1,2

    • 已编辑 ma_jiang 2011年10月10日 4:35
    • 已标记为答案 ma_jiang 2011年10月10日 4:35
    2011年10月7日 5:52

全部回复

  • 请问工作时间是怎么计算的呀?


    it's time to start living the life you are imagined.
    2011年9月30日 7:55
  • 你好:

    1、工作时间=(最后一次刷卡时间-第一次刷卡时间)

    --- 00:00

       |

       |----------上一日 12:00

       |            |

    ---24:00   |

                    |

    ------------ |下一日12:00

     

    a、第一次刷卡时间:从上一日12:00开始计算,

    b、最后一次刷卡时间:到下一日12:00计算结束。

    c、如果上白班:在上一日12:00---下一日12:00,8:30--9:30(白班),

    d、如果上夜班:在下一日12:00---上一日12:00,21:00--5:00(夜班)。


    人的聪明和自己的明智及道路的选择,往往在失败以后。

    • 已编辑 ma_jiang 2011年10月7日 1:26
    2011年9月30日 8:46
  • select
    isnull(x1.日期1,y1.日期2) as 日期
    ,isnull(x1.工号1,y1.工号2) as 工号
    ,isnull(x1.工作时间,0) as 工作时间
    ,isnull(y1.空闲时间,0) as 空闲时间

    from
    (
    (
    select
    --一天定义:上一日12:00:00-下一日12:00:00。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, a.ScanTime) >= '12' THEN dateadd(d, 0, a.ScanTime) ELSE dateadd(d, - 1, a.ScanTime) END), 120), 10) ) as 日期1
    ,a.ScanUser  as 工号1
    ,MIN(convert(varchar,ScanTime,108))最小值1
    ,Max(convert(varchar,ScanTime,108))最大值1
    --工作时间=(最后一次刷卡时间-第一次刷卡时间)
    ,datediff(minute,MIN(convert(varchar,ScanTime,108)),max(convert(varchar,ScanTime,108))) as 工作时间
    from dbo.Scanner_Operation  a
    where
    --计算2011年x月x日-2011年x月x日,工作时间。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, a.ScanTime) >= '12' THEN dateadd(d, 0, a.ScanTime) ELSE dateadd(d, - 1, a.ScanTime) END), 120), 10)) between '2011-01-01' and '2011-01-30'
    --上班时间:08:00:00--18:00:00(白班)。
    and  (DATEPART(HH, ScanTime)>='8' and DATEPART(HH, ScanTime)<='18' )--白班
    group by CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, a.ScanTime) >= '12' THEN dateadd(d, 0, a.ScanTime) ELSE dateadd(d, - 1, a.ScanTime) END), 120), 10))
    ,a.ScanUser
    )x1
    full join
    (
    select
    isnull(b.日期,c.日期)  as 日期2,
    isnull(b.工号,c.工号)  as 工号2,
    sum(datediff(minute,b.时间,c.时间))as 空闲时间
    from
    (
    --1、上一条记录。
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    (
    select
    ROW_NUMBER() over(order by
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)
    ) as 序号
    --一天定义:上一日12:00:00-下一日12:00:00。
    ,CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) ) as 日期
    ,ScanUser  as 工号
    ,convert(varchar,ScanTime,108) as 时间
    from dbo.Scanner_Operation   
    where
    --计算2011年x月x日-2011年x月x日,工作时间。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10)) between '2011-01-01' and '2011-01-30'
    --上班时间:08:00:00--18:00:00(白班)。
    and  (DATEPART(HH, ScanTime)>='8' and DATEPART(HH, ScanTime)<='18' )--白班
    group by CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)

    )b
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    full join
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    --2、下一条记录。
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    (select

    ROW_NUMBER() over(order by
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)
    ) as 序号
    --一天定义:上一日12:00:00-下一日12:00:00。
    ,CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) ) as 日期
    ,ScanUser  as 工号
    ,convert(varchar,ScanTime,108) as 时间
    from dbo.Scanner_Operation   
    where
    --计算2011年x月x日-2011年x月x日,工作时间。
    CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10)) between '2011-01-01' and '2011-01-30'
    --上班时间:08:00:00--18:00:00(白班)。
    and  (DATEPART(HH, ScanTime)>='8' and DATEPART(HH, ScanTime)<='18' )--白班
    group by CONVERT(varchar(19), LEFT(CONVERT(varchar(10), (CASE WHEN DATEPART(HH, ScanTime) >= '12' THEN dateadd(d, 0, ScanTime) ELSE dateadd(d, - 1, ScanTime) END), 120), 10) )
    ,ScanUser
    ,convert(varchar,ScanTime,108)

    )c
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    on   b.序号=c.序号-1 and b.日期=c.日期  and b.工号=c.工号
    )
    --空闲条件:(下一条记录-上一条记录)>5分钟。
    where datediff(minute,b.时间,c.时间)>5
    group by isnull(b.日期,c.日期)
    ,isnull(b.工号,c.工号)

    )y1
    on x1.日期1=y1.日期2 and x1.工号1=y1.工号2
    )
    order by 1,2

    • 已编辑 ma_jiang 2011年10月10日 4:35
    • 已标记为答案 ma_jiang 2011年10月10日 4:35
    2011年10月7日 5:52