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
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) ...