How to make a single query

Question

I am using mssql. I have these below queries in a loop to get records of 12 months.

Is it possible to make the below queries as single query.

I need to get report for all the months based on the team and dept.

select sum(hour_val) from proj_duration_map where
cur_month=(select max(cur_month) from proj_duration_map where 
month_val='1' and team='M&T' and dept='GEF-SW') and
month_val='1' and team='M&T' and dept='GEF-SW'

select sum(hour_val) from proj_duration_map where
cur_month=(select max(cur_month) from proj_duration_map where 
month_val='2' and team='M&T' and dept='GEF-SW') and
month_val='2' and team='M&T' and dept='GEF-SW'

select sum(hour_val) from proj_duration_map where
cur_month=(select max(cur_month) from proj_duration_map where 
month_val='3' and team='M&T' and dept='GEF-SW') and
month_val='3' and team='M&T' and dept='GEF-SW'

.
.
.

select sum(hour_val) from proj_duration_map where
cur_month=(select max(cur_month) from proj_duration_map where 
month_val='12' and team='M&T' and dept='GEF-SW') and
month_val='12' and team='M&T' and dept='GEF-SW'

Table structure

[proj_duration_map]( 
    [id] [int] IDENTITY(1,1) NOT NULL,  
    [uid] [int] NOT NULL,  
    [dept] [nchar](25) NOT NULL,    
    [team] [nchar](200) NOT NULL,    
    [pid] [int] NOT NULL,    
    [week_val] [smallint] NOT NULL,   
    [month_val] [smallint] NULL,    
    [hour_val] [float] NOT NULL,
    [cur_month] [smallint] NULL,
    [year_val] [smallint] NOT NULL,
    [last_updated] [datetime] NOT NULL
   )

Any help would be great.

Answer 1

I think you just want an aggregation. To get the most recent month_val use window functions:

select team, dept, month_val, sum(hour_val)
from (select dm.*,
             max(cur_month) over (partition by month_val, team, dept) as max_curmonth
      from proj_duration_map dm
     ) dm
where team = 'M&T' and dept = 'GEF-SW' and cur_month = max_curmonth
group by team, dept, month_val;