CODEWITHSUNDEEP
postgresql
sawyinwaimon
sawyinwaimon

Reputation: 769

How to Write Sql Query to show total number of leaveDays per months after calculating date difference between startDate and endDate

There is Leave Table.It has two columns start-date,end-date.I need to show total Number of Leave Days Per Month. At first I have to calculate totalNumberOfLeave just like:

<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>

After calculating TotalNumberOfLeave,Then I need to show totalNumberOfLeave Per Month just like 

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

(Person A takes 2016-08-10,2016-08-11,2016-08-12 as Leave,Person B takes 2016-08-31 as Leave,PersonC takes 2016-08-30,2016-08-31 as Leave in August)

How should I write for showing totalNumberOfLeave Per Month? DB I use is Postgresql9.3.

Upvotes: 1

Views: 79

Answers (1)

May Thet
May Thet

Reputation: 61

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);

Upvotes: 1

Related Questions