Postgres sql 8.4分别使用时间差和日期差

Question

我有这个问题，但我无法解决，因为我认为这在sql 中是不可能的

我有这张表

Shedule
    id_emp    Name       `time initial` time end   
    1         juan`       09:00`          12:00
    2        Francisco    10:00           11:30
    3        Sebastian    11:00           15:00
    6        Roberto      15:00           18:00

Suspension
id_emp          suspension_initial     suspension_end
1                    2013-06-01           2013-06-01
2                    2013-06-01           2013-06-03
3                    2013-06-03           2013-06-04 
6                    2013-06-01           2013-06-01
2                    2013-07-01           2013-07-01
3                    2013-07-05           2013-07-05
1                    2013-07-06           2013-07-06

我想了解工作时间（（time_end-time_initial）-停职）（如果我有1天的停职时间，那就是一个小时单位。例如：Juan每天工作3小时，6月有1天停职，7月有一天停职。所以我假设他在一个月内工作3*20（hours*dayworked）-6月3小时，7月3小时我怎样才能得到这个结果

id_emp name `       June-2013               July-2013
1       Juan           57 (hours Worked)        57 (hours Worked)
2       Francisco      24 (hours worked)        27 (hours worked)
3       Sebastián
6       Roberto

Answer 1

这是SQLFiddel演示

下面是您可以尝试的查询

select EmpHrs.ID_EMP,
       EmpHrs.Name,
       (
        (EmpHrs.NOOFHRS*20)-
        (EmpHrs.NOOFHRS*
          JuneSuspension.MONTHSUSPENSION)
       )  as "June-2013",
       (
        (EmpHrs.NOOFHRS*20)-
        (EmpHrs.NOOFHRS*
          JulySuspension.MONTHSUSPENSION)
       )  as "July-2013"
from 
(
select ID_EMP,NAME,
       Extract(Hours from time_end-time_initial)+
       Extract(Minutes from time_end-time_initial)/60 as NoOfHrs
  from schedule
) EmpHrs
Left join
(select ID_EMP,to_char(to_timestamp (Extract(Month from suspension_initial)::text, 'MM'),'Mon') as MonthIni,(suspension_end::date - suspension_initial::date)+1 MonthSuspension
from suspension
where Extract(Month from suspension_initial) = 6) JuneSuspension
On JuneSuspension.ID_EMP = EmpHrs.ID_EMP
Left join
(select ID_EMP,to_char(to_timestamp (Extract(Month from suspension_initial)::text, 'MM'),'Mon') as MonthIni,(suspension_end::date - suspension_initial::date)+1 MonthSuspension
from suspension
where Extract(Month from suspension_initial) = 7) JulySuspension
On JulySuspension.ID_EMP = EmpHrs.ID_EMP