CODEWITHSUNDEEP
sqloracle-database
Arise
Arise

Reputation: 13

Oracle SQL - Query for begin and end month/year combination

I'm attempting to write a query that will take four pieces of user input:

Beginning Month

Beginning Year

Ending Month

Ending Year

It seemed pretty straight forward until I came across one little problem with the way the query was coded which happens when the following inputs are used as an example:

Beginning Month: January

Beginning Year: 2012

Ending Month: August

Ending Year: 2013

With the way I coded my query, it only picked up Jan through Aug of 2012 as well as Jan through Aug of 2013. I want it to pick up all of 2012, and then Jan through Aug of 2013. I'm guessing that I need a union statement for this to work, but I'll admit that I'm not very good at Oracle SQL, as I don't get many opportunities to practice it often. So it's possible that I'm going about this the wrong way.

I tried updating my query to include a union statement, and Oracle threw an error about I was using an invalid identifier on the three fields in my group by expression (trsum.project_id, trsum.month, and trsum.year). When I attempted to change these to the aliases that I've assigned to the columns (ordered_id, ordered_month, and ordered_year), I receive an error about how the project_id field is not a single group function. I'm clearly missing something with regards to creating a union query that includes a group by expression, but I'll be darned if I can figure out what at this point.

I've included a simplified version of my updated query that keeps all of the essentials that I've mentioned here in tact for reference. The first half before the union statement is the original query I wrote, and the second half is my attempt at trying to get the data that I was seeking from my above example. If anyone has any suggestions or advice on what I'm doing wrong, or if there's a better method for obtaining the data that I seek, I'm open to all suggestions. If there's anything I need to clarify further, feel free to ask.

select * from(
    select  trsum.project_id as ordered_id
          , trsum.month as ordered_month
          , trsum.year as ordered_year
          , sum(trsum.costs) "Costs"
          , sum(trsum.hours) "Hours"
    from 
        substituteTableName trsum
    where
        trsum.month between :START_MONTH and :END_MONTH
        and trsum.year between :START_YEAR and :END_YEAR

    union

    select  trsum2.project_id as ordered_id
          , trsum2.month as ordered_month
          , trsum2.year as ordered_year
          , sum(trsum2.costs) "Costs"
          , sum(trsum2.hours) "Hours"
    from 
        substituteTableName trsum2
    where
        trsum2.month between :START_MONTH and 11
        and trsum2.year = :START_YEAR
)
group by trsum.project_id, trsum.month, trsum.year
order by
      ordered_id
    , ordered_year
    , ordered_month

Upvotes: 1

Views: 458

Answers (2)

Thrash Bean
Thrash Bean

Reputation: 658

You should use dates, not just month between because that's not correct. Something like:

WHERE 
  to_date((trsum.ppt_fiscal_month + 1 || '-' || trsum.ppt_fiscal_year), 'MM-YYYY') >=
      to_date((start_month || '-' || start_year), 'MM-YYYY'
AND 
to_date((trsum.month + 1 || '-' || trsum.year),'MM-YYYY') <= 
        to_date((end_month || '-' || end_year), 'MM-YYYY'

and similar way the other condition.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think an easy way to approach these situation is to convert the values to the form YYYYMM. Your where clauses become:

where trsum.year*100 + trsum.month between :START_YEAR * 100 + :START_MONTH and
                                           :END_YEAR * 100 + :END_MONTH


where trsum.year*100 + trsum.month between :START_YEAR * 100 + :START_MONTH
                                           :START_YEAR * 100 + 11;

Of course, the second one doesn't have to be changed. It should work correctly as written.

Upvotes: 0

Related Questions