postgresql - 如何编写 Sql 查询以在计算 startDate 和 endDate 之间的日期差异后显示每月的休假总数

Question

有休假表。它有两列开始日期，结束日期。我需要显示每月休假总天数强>.起初我必须像这样计算 totalNumberOfLeave:

<table>
    <tr>
        <th>Person</th> 
        <th>startDate</th>
        <th>endDate</th>
        <th>totalNumberOfLeave</th>
    </tr>
    <tr>
        <td>PersonA</td>
        <td>2016-08-10</td>
        <td>2016-08-12</td>
        <td>3</td>
    </tr>
    <tr>
        <td>PersonB</td>
        <td>2016-08-31</td>
        <td>2016-09-03</td>
        <td>4</td>
    </tr>
    <tr>
        <td>PersonC</td>
        <td>2016-08-30</td>
        <td>2016-09-06</td>
        <td>9</td>
    </tr>
</table>

在计算完 TotalNumberOfLeave 之后，我需要像显示每月的 totalNumberOfLeave 一样

<table>
    <tr>
        <th>Month</th>
        <th>NumberOfLeave</th>
    </tr>
    <tr>
        <td>8</td>
        <td>6</td>
    </tr>
</table>

(A 2016-08-10,2016-08-11,2016-08-12 休假,B 2016-08-31 休假,C 2016-08-30,2016-08- 31 作为八月离开)

我应该如何写来显示 totalNumberOfLeave Per Month？我使用的数据库是Postgresql9.3。

Answer 1

SELECT 
    DATE_PART('MONTH',A.ATTENDANCEDATE) AS MONTH, 
    SUM(CASE WHEN (A.FULLDAY = '1') THEN 1 ELSE 0.5 END ) AS DURATION
FROM
    (
    SELECT GENERATE_SERIES(START_DATE,END_DATE, '1 DAY'::INTERVAL) AS ATTENDANCEDATE, IS_FULLDAY AS FULLDAY
    FROM LEAVE_TAKEN
    ) AS A
GROUP BY 
    DATE_PART('MONTH',A.ATTENDANCEDATE)
ORDER BY
    DATE_PART('MONTH',A.ATTENDANCEDATE);