We help IT Professionals succeed at work.

how to get quaryters based on sysdate

pardeshirahul
on
suppose i have this query
SELECT mmt.inventory_item_id,
                        p_org_id,
                        p_date,
                        msi.item_type,
                        SUM (primary_quantity)
                   FROM mtl_material_transactions mmt, mtl_system_items_b msi
                  WHERE     mmt.inventory_item_id = msi.inventory_item_id
                        AND mmt.organization_id = msi.organization_id
                        AND mmt.organization_id = p_org_id
                        AND mmt.transaction_type_id IN (35, 33, 62, 34)
                        AND TO_CHAR (mmt.transaction_date, 'YYYY') = p_date
                        AND msi.item_type = p_item_type
               GROUP BY mmt.inventory_item_id,
                        p_org_id,
                        p_date,
                        msi.item_type;

now p_org_id=116
p_item_type='P'
and p_date='2011'

now quarters are defined like this JAN-MAR -- 1st quarter
APR-JUN-  2nd quarter
JUL-SEP  3rd quarter
OCT-DEC 4 quarter

so if i an running this query today
it should take the transaction date from m 1-oct-2011 to 31-dec-2011

how can i do it in this query
Comment
Watch Question

Author

Commented:
xx

Author

Commented:
I want to remome the filter of the year that is p_date and instead take the qurters
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
There is a 'Q' mask for TO_CHAR that displays the Quarter for dates.

I'm not fully understanding the question but to get the current 'Quarter':  to_char(sysdate,'Q')
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>I want to remome the filter of the year that is p_date and instead take the qurters

something may like:

...
AND TO_CHAR (mmt.transaction_date, 'Q') = to_char(sysdate,'Q')
...
Top Expert 2011

Commented:
- use to_char(date,'q') to get the quater for that datecolumn. eg: select * from tablename where  to_char(datecolumn,'q') = x. specify x= which quater are you intend to run againts.
awking00Information Technology Specialist

Commented:
What is the datatype of p_date?
awking00Information Technology Specialist
Commented:
Not necessary.
Where to_char(transaction_date,'Q') = to_char(sysdate,'Q')

Author

Commented:
p_date        IN     VARCHAR2,
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>p_date        IN     VARCHAR2,

I thought you wanted to eliminate this and run based off sysdate?

Please clarify.
Top Expert 2011

Commented:
p_date        IN     VARCHAR2,

- based on my earlier suggestion, you can specify p_date = 1,2,3,4 which represent the quater period,  to_char(datecolumn,'q') = x.
- if you need to run based on current quater, you can use the suggestion by earlier expert on  to_char(datecolumn,'q') = to_char(sysdate,'q')