CODEWITHSUNDEEP
sqloracle-databasedate
theacb
theacb

Reputation: 31

SQL Query Extract Totals by Month for Multiple Date Fields

I have an Oracle Database I am trying to query multiple date fields by dates and get the totals by month and year as output.

This was my original query. This just gets what I want for the dates I want to input.

SELECT COUNT(*) as Total
FROM Some_Table s
WHERE (s.Start_DATE <= TO_Date ('2019/09/01', 'YYYY/MM/DD'))
AND (s.End_DATE IS NULL OR (s.End_DATE > TO_Date ('2019/08/31', 'YYYY/MM/DD')))

I would like to get an output where it gives me a count by Month and Year. The count would be the number between the Start_DATE (beginning of the month) and the End_DATE (end of the month).

I can't do

Edit: this was an example from another query and has no relation to the query above. I was just trying to provide an example of what I cannot do because I have two separate date fields. The example below was stating my knowledge of extracting month and year from a single date field. Sorry for the confusion.

SELECT extract(year from e.DATE_OCCURRED) as Year
      ,to_char(e.DATE_OCCURRED, 'MONTH') as Month
      ,count (*) as totals

because the Start_DATE and End_DATE are two separate fields.

Any help would be appreciated

Edit: Example would be

----------------------------------
| Name | Start_DATE |  End_DATE  |
----------------------------------
| John | 01/16/2018 | 07/09/2019 |
| Sue  | 06/01/2015 | 09/01/2018 |
| Joe  | 04/06/2016 |    Null    |
----------------------------------

I want to know my total number of workers that would have been working by month and year. Would want the output to look like.

------------------------
| Year | Month | Total |
------------------------
| 2016 |  Aug  |   2   |
| 2018 |  May  |   3   |
| 2019 |  Aug  |   2   |
------------------------

So I know I had two workers working in August 2016 and three in May 2018.

Upvotes: 0

Views: 673

Answers (2)

OracleForLife
OracleForLife

Reputation: 7

Are you looking for this?(Hoping that end_date > start_date)

select extract (year from end_dt2)- extract(YEAR from st_dt1) as YearDiff ,
extract (month from end_dt2)- extract (month from st_dt1) as monthDiff from tab;

Upvotes: 0

Hogan
Hogan

Reputation: 70538

Do you want this?

SELECT count(*)
from some_table
where year(e.DATE_OCCURRED) > year(start_date)
  and year(e.DATE_OCCURRED) < year(end_date)
  and month(e.DATE_OCCURRED) > month(start_date)
  and month(e.DATE_OCCURRED) <  month(end_date)

note: using month and year functions is generally better when working with dates. If you convert to characters you might find that January comes after February (as an example) since J comes after F in the alphabet.

Upvotes: 0

Related Questions