/// <summary>
/// 查询有效的社保记录
/// </summary>
/// <returns></returns>
public DataTable GetSocRightInfo(DateTime PayOn)
{
string sql = string.Format("select null as id,a.SocialsecurityManagementId,a.SocialsecurityNumber as {0}SocialsecurityManagementId,a.PaymentStandardId,d.Name as {0}PaymentStandardId,a.EffectiveDate as {0}EffectiveDate,a.EffectiveDate,a.EmployeeId, c.Name as {0}EmployeeId, 0 as Status,a.BasePay as BasePay, '{1}' as SocialsecurityPayMonth from t_ri_SocialsecurityManagement as a left join v_ri_Employee c on a.EmployeeId=c.employeeId left join t_ri_PaymentStandard d on a.PaymentStandardId = d.PaymentStandardId where not EXISTS (select * from v_ri_SocialsecurityPayItem where SocialsecurityManagementId=a.SocialsecurityManagementId and datediff(month,SocialsecurityPayMonth,'{1}')=0 and Status in (1,2,4) and a.Status = 2 and a.State =1 and a.EffectiveDate is not null and a.MoveOn is null )", ScoEntityListGrid.TEXT_FIELD_PREFIX, PayOn);
return ScoDataUtility.GetDataTableFromDataSet(AdoHelper.GetDataSetBySQL(sql));
}
我想问的是 SQL如何实现一个员工一个月内只能操作缴纳一次社保?
SQL中比较月份的函数是如何的？
------解决方案--------------------
SELECT CONVERT(VARCHAR(7),GETDATE(),120),DATENAME(mm,GETDATE()),DATEPART(mm,GETDATE())
/*
------- ------------------------------ -----------
2012-12 12 12
(1 行受影响)
*/