Qasim0787 Qasim0787 - 7 months ago 15
SQL Question

How to group by dates into quarterly manner

Hi I have table with column ID and DATES I want to make another column which store dates group by quarterly.

For example

Table

ID DATES

123 5/1/2005
123 7/1/2001
123 4/1/2003
123 2/1/2002
123 6/1/2005
123 6/1/2004


expected output:

ID DATES QUATER

123 5/1/2005 Q2-2005
123 7/1/2001 Q3-2001
123 4/1/2003 Q2-2003
123 2/1/2002 Q1-2002
123 6/1/2005 Q2-2005
123 6/1/2004 Q2-2004

Answer

If you don't already have a column for the quarter, then you should create one:

ALTER TABLE yourTable
ADD QUATER varchar2(7)

Now you can update this column to contain the data you want:

UPDATE yourTable
SET QUATER = 'Q' || 1 + TRUNC((EXTRACT(MONTH FROM DATES) - 1) / 3) || '-' ||
             EXTRACT(YEAR FROM DATES)