Skip to content
Advertisement

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

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

enter image description here

Advertisement

Answer

The actual reason for the unexpected results in this specific case is the (pcm_year >= 2015 and pcm_month >= 4) and (pcm_year <= 2016 and pcm_month < 4) part of the WHERE clause, where the use of the second AND operator is wrong.

In this situation, you may consider an additional calculation and a different WHERE clause:

...
WHERE 
   (pcm_year * 100 + pcm_month >= 201504) AND
   (pcm_year * 100 + pcm_month < 201604) AND
   (emp_no = 11101201)
...
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement