SQL日期转换，只要年、月
1 DataView GetSource(string month,string contion)
2 {
3 string sql = @"select
4 序号 = row_number() over(order by c.Name desc),
5 c.Name+'工会' 部门,
6 100-isnull((select sum(d.Score)
7 from MM_Tab_Assessment_Criteria d , MM_Tab_Assessment_Score e
8 where d.ObjectID = e.CriteriaID and e.IsComplete=0 and e.IsAppoved = 1
9 and convert(varchar(7),e.Month,120) = convert(varchar(7),dateadd(mm,-1,getdate
10 ()),120) and e.DeptID = b.DeptID group by DeptID),0) as 基础分,
11 isnull((select sum(Score) from dbo.MM_Tab_Assessment_Score_Add
12 where convert(varchar(7),Month,120) = convert(varchar(7),dateadd(mm,-1,getdate
13 ()),120) and DeptID = b.DeptID group by DeptID),0) as 加分,
14 100-AVG(a.Score)+isnull((select sum(Score) from dbo.MM_Tab_Assessment_Score_Add
15 where convert(varchar(7),Month,120) = convert(varchar(7),dateadd(mm,-1,getdate
16 ()),120) and DeptID = b.DeptID group by DeptID),0) as 总分
17 from
18 dbo.MM_Tab_Assessment_Criteria a,
19 dbo.MM_Tab_Assessment_Score b,
20 OT_OrganizationUnit c
21 where a.ObjectID = b.CriteriaID and b.DeptID = c.ObjectID
22 and convert(varchar(7),b.Month,120) = convert(varchar(7),dateadd(mm,-1,getdate
23 ()),120)
24 and b.IsAppoved = 1
25 group by b.DeptID,c.Name order by c.Name desc";
26 DataView dv = MMSHFunction.ExecuteTableSql(sql).DefaultView;
27 dv.RowFilter = contion;
28 return dv;
29 }
