CODEWITHSUNDEEP
sql-server-2008
Philip Sams
Philip Sams

Reputation: 29

quarter year wise filteration in sql

I have a table having structure as follows

id cust_id target month year fiscal_ID
1  234     50     4     2013 1 
2  234     50     5     2013 1
3  234     50     6     2013 1
4  234     150    7     2013 1
5  234     150    8     2013 1
6  234     150    9     2013 1

I need to get the result as follows

cust_id target quarter year fiscal_ID
234     150    Q1      2013 1
234     450    Q2      2013 1

months 4,5,6 in Q1, 7,8,9 in Q2 etc

Upvotes: 0

Views: 410

Answers (1)

Taryn
Taryn

Reputation: 247720

Since you are storing the month and year in separate columns, one way you can get the result is to use a derived table that references the month and quarter and you join to that data:

select t.cust_id,
  sum(target) target,
  d.qtr,
  t.year,
  t.fiscal_id
from yourtable t
inner join
(
  select 4 mth, 'Q1' qtr union all
  select 5 mth, 'Q1' qtr union all
  select 6 mth, 'Q1' qtr union all
  select 7 mth, 'Q2' qtr union all
  select 8 mth, 'Q2' qtr union all
  select 9 mth, 'Q2' 
) d
  on t.month = d.mth
group by t.cust_id, d.qtr, t.year, t.fiscal_id;

See SQL Fiddle with Demo.

Upvotes: 2

Related Questions