Query to pick columns from multiple tables
-
Hi,
I have 2 tables. Table1 and Table2.
Table1 contains
ID Name Address
1 A XYZ
2 B ABC
3 C RST
Table2 Contains ---
ID Salary Month Year
1 2000 01 2006
1 3000 04 2006
3 5000 10 2006
3 3000 06 2006
1 4000 11 2006
2 8000 07 2006
I want to write a query that gives the o/p in the following format--
Quaterly1(1st four months),Quaterly2(2nd four months) and Quaterly 3(3rd four months) are salaries
ID Name Year Quaterly1 Quaterly2 Quaterly3
1 A 2006 5000 0 4000
2 B 2006 0 8000 0
3 C 2006 0 3000 5000
Any help will be highly appreciated.
Thanks,
Ankur
-
this may be a little hard coded but you are talking months here, which will not be changing anytime soon. however, you may want to look into a time table so you can break down the months into groups a little bit better than the hard code approach.
regardless, using the data with what you have here, this should help:
select t1.id, t2.name, t1.year,
case when sqtr = 1 then sum(salary) else 0 end as qtr_1,
case when sqtr = 2 then sum(salary) else 0 end as qtr_2,
case when sqtr = 3 then sum(salary) else 0 end as qtr_3
from
(select id,
case when month between 1 and 4 then 1
when month between 5 and 8 then 2
else 3 end as sqtr,
month, year, salary
from table1) t1
inner join table2 t2
on t1.id = t2.id
group by t1.id, t1.sqtr, t1.year, t2.name
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply