CODEWITHSUNDEEP
sqloracle-database
Bharat Guda
Bharat Guda

Reputation: 117

sql Date Function

i have a 'date1' field with values starting from 01/01/1960 to 09/14/2014. I have to group this field into

starting today group last two months into 

'1' (sept 2014, aug 2014)
(july 2014, june 2014) to '2'
(may 2014, apr 2014) to '3'
(mar 2014, feb 2014) to '4'
(jan 2014, dec 2013) to '5'
(nov 2013,oct 2013) to '6'
(<=sept 2013) to '7'

how to group the data ?

Upvotes: 0

Views: 67

Answers (1)

wvdz
wvdz

Reputation: 16651

A simple, verbose and static solution is like this:

WITH t1 AS
(
    SELECT CASE WHEN date1 >= TO_DATE('1-AUG-2014') AND date1 < TO_DATE('1-OCT-2014') THEN 1 ELSE
           CASE WHEN date1 >= TO_DATE('1-JUN-2014') AND date1 < TO_DATE('1-AUG-2014') THEN 2 ELSE
           ... etc. END AS dategroup,
           othercolumns
    FROM Table1
)
SELECT dategroup FROM t1
GROUP BY dategroup;

Upvotes: 1

Related Questions