SQL Server query to filter the financial year when month and year are in different columns-CodePudding

I've attached the sample data. Here actually I wanted to calculate the running total of emp_contribution and vpf column together. however that should be bounded within a financial year. suppose for 2015 it will start from April, 2015 to March, 2016. This is where I'm facing the challenges.

Below I've attached my attempt query but under the where clause filter is not working perfectly

select  
    case when sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_month)   sum(isnull(vpf,0)) over(partition by emp_no order by pcm_month) < 3000 
        then sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_month)   sum(isnull(vpf,0)) over(partition by emp_no order by pcm_month)
        else null
   end empcontbtnwithouttax,
   case 
       when sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_month)   sum(isnull(vpf,0)) over(partition by emp_no order by pcm_month) >= 3000
           then sum(isnull(emp_contribution,0)) over(partition by emp_no order by pcm_month)   sum(isnull(vpf,0)) over(partition by emp_no order by pcm_month)
           else null
    end empcontbtnwithtax,
    pcm_month, pcm_year, emp_no 
from 
    [dbo].[pf_contribution_master]
where 
    (pcm_year >= 2015 and pcm_month >= 4 )
    and (pcm_year <= 2016 and pcm_month < 4 )
    and emp_no= 11101201 
order by 
    pcm_year, pcm_month

CodePudding user response：

An additional calculation in the WHERE clause may help:

...
WHERE 
   (pcm_year * 100   pcm_month >= 201504) AND
   (pcm_year * 100   pcm_month < 201604) AND
   (emp_no = 11101201)
...