如何显示两个月和一年之间的记录 [英] How to show records between two month and year

问题描述

你好，先生，我想显示总收入的总和，财政年度之间的扣除和我写下面的查询，但它显示错误的输出所以请建议我



参数：

Hello sir, i want to show sum of total earning, deduction between financial year and i wrote below query but it show wrong output so please suggest me

Parameters:

frmYr =2013
toYr =2014
frmMonth =3
toMonth =4

查询：

Query:

select
s.compCode,
s.compName, 
s.Location,
s.empId,
s.empName,
sum(s.BasicSalary) as BasicSalary,
sum(totalEarning) as totalEarning,
sum(totalDeduction) as totalDeduction,
sum(totalGrossSalary) as totalGrossSalary
from PAY_Process_Salary s  where s.compCode=@compCode  and s.TranMonth between @frmMonth and @toMonth and s.TranYear between @frmYr and @toYr GROUP BY s.compCode,s.compName,s.empId,s.empName,s.location order by s.empId

推荐答案

结果不正确，因为您将该月份与年份进行独立比较只有第3个月和第4个月将包含在您的任一年的结果中。即您的总数将反映2013 / 03,2013 / 04,2014 / 03& 2014/04

您需要做的是根据参数创建日期范围，然后根据数据创建日期，然后检查它是否在所需范围内。



所以你的where子句应该是这样的：

The results are incorrect because you are comparing the month independently from the year so only month 3 and 4 will be included in your results for either year. i.e. your totals will reflect 2013/03, 2013/04, 2014/03 & 2014/04
What you need to do is create a date range from your parameters and then create a date from your data and then check to see if it falls within the desired range.

So your where clause should look something like:

where convert(datetime, s.tranyear + s.tranmonth + s.trandate) between 
          convert(datetime, '20130401') and convert(datetime, '20140331')

请注意，不同的日期文化需要不同的转换参数常规如此你可能需要多挖一点。



祝你好运

Be aware that different date cultures need different parameters to the convert routine so you may need to do a bit more digging.

Good luck

这篇关于如何显示两个月和一年之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！