现在有几个表名称为 BAK200801,BAK200802,BAK200803........
表的结构是职工姓名,出勤天数,单位名称
现在想要把职工一段时间内出勤情况累加起来
但是有这种情况,职工在这段时间内有工作调动情况,要以最后那个月职工所在的单位为准.
还有比如要统计1-5月分的出勤,某人只出勤了四个月,也要统计出来
如果有工作调动情况,也要以最后那个月所在的单位为准.请文这条SQL如何写啊
union all的形式
表的结构是职工姓名,出勤天数,单位名称
现在想要把职工一段时间内出勤情况累加起来
但是有这种情况,职工在这段时间内有工作调动情况,要以最后那个月职工所在的单位为准.
还有比如要统计1-5月分的出勤,某人只出勤了四个月,也要统计出来
如果有工作调动情况,也要以最后那个月所在的单位为准.请文这条SQL如何写啊
union all的形式
先UNION ALL全部的月,
然后剔除掉有工作调动情况.
最后Group By ,
关键是剔除掉有工作调动情况比较难搞.
因为可有工作调动情况的出勤不是最后一个月,
(
select 职工姓名,单位名称,max(月) 月,sum(出勤天数) 出勤天数 from
(
select 职工姓名,出勤天数,单位名称,'200801' as 月 from BAK200801 union all
select 职工姓名,出勤天数,单位名称,'200802' as 月 from BAK200802 union all
select 职工姓名,出勤天数,单位名称,'200803' as 月 from BAK200803
) u group by
职工姓名,单位名称
) tt
where not exists
(
select 1 from
(
select 职工姓名,单位名称,max(月) 月,sum(出勤天数) 出勤天数 from
(
select 职工姓名,出勤天数,单位名称,'200801' as 月 from BAK200801 union all
select 职工姓名,出勤天数,单位名称,'200802' as 月 from BAK200802 union all
select 职工姓名,出勤天数,单位名称,'200803' as 月 from BAK200803
) u group by
职工姓名,单位名称
) vv
where vv.职工姓名=tt.职工姓名 and vv.月>tt.月
)
--try
declare @sql nvarchar(4000), @BeginMonth nvarchar(10),@EndMonth nvarchar(10),@i int
select @sql ='' ,@BeginMonth='20080101',@EndMonth ='20080701',@i=0
while (@i<=datediff(month,@BeginMonth,@EndMonth))
begin
if @i = 0
set @sql = ' SELECT [职工姓名],[出勤天数],[单位名称],'''
+ convert(nvarchar(6),@BeginMonth,112)+''' as [Month] FROM '+'BAK'+ltrim(convert(nvarchar(6),@BeginMonth,112))
else
set @sql = @sql + ' UNION ALL '
+' SELECT [职工姓名],[出勤天数],[单位名称], '''
+convert(nvarchar(6),dateadd(month,@i,@BeginMonth),112)+''' FROM '+'BAK'+ltrim(convert(nvarchar(6),dateadd(month,@i,@BeginMonth),112)) set @i = @i +1
end
SET @sql = ' SELECT [职工姓名],[出勤天数],[单位名称] FROM ( SELECT [职工姓名],SUM([出勤天数]),[单位名称],MAX([Month]) AS [Month] FROM ( '+ @sql +') A GROUP BY [职工姓名],[单位名称] ) B WHERE NOT EXISTS(SELECT 1 FROM ('+@sql+' )C WHERE C.[Month] >B.[Month] )'exec (@sql)
--print @sql
/*
SELECT [职工姓名],[出勤天数],[单位名称] FROM
( SELECT [职工姓名],SUM([出勤天数]),[单位名称],MAX([Month]) AS [Month]
FROM ( SELECT [职工姓名],[出勤天数],[单位名称],'200801' as [Month] FROM BAK200801 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200802' FROM BAK200802 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200803' FROM BAK200803 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200804' FROM BAK200804 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200805' FROM BAK200805 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200806' FROM BAK200806 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200807' FROM BAK200807 ) A GROUP BY [职工姓名],[单位名称]
) B
WHERE NOT EXISTS(
SELECT 1 FROM ( SELECT [职工姓名],[出勤天数],[单位名称],'200801' as [Month] FROM BAK200801 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200802' FROM BAK200802 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200803' FROM BAK200803 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200804' FROM BAK200804 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200805' FROM BAK200805 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200806' FROM BAK200806 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200807' FROM BAK200807 ) C
WHERE C.[Month] >B.[Month] )
*/
--最后加上[职工姓名],单位名称]这两个条件
declare @sql nvarchar(4000), @BeginMonth nvarchar(10),@EndMonth nvarchar(10),@i int
select @sql ='' ,@BeginMonth='20080101',@EndMonth ='20080701',@i=0
while (@i<=datediff(month,@BeginMonth,@EndMonth))
begin
if @i = 0
set @sql = ' SELECT [职工姓名],[出勤天数],[单位名称],'''
+ convert(nvarchar(6),@BeginMonth,112)+''' as [Month] FROM '+'BAK'+ltrim(convert(nvarchar(6),@BeginMonth,112))
else
set @sql = @sql + ' UNION ALL '
+' SELECT [职工姓名],[出勤天数],[单位名称], '''
+convert(nvarchar(6),dateadd(month,@i,@BeginMonth),112)+''' FROM '+'BAK'+ltrim(convert(nvarchar(6),dateadd(month,@i,@BeginMonth),112)) set @i = @i +1
end
SET @sql = ' SELECT [职工姓名],[出勤天数],[单位名称] FROM ( SELECT [职工姓名],SUM([出勤天数]),[单位名称],MAX([Month]) AS [Month] FROM ( '+ @sql +') A GROUP BY [职工姓名],[单位名称] ) B WHERE NOT EXISTS(SELECT 1 FROM ('+@sql+' )C WHERE C.[职工姓名] = B.[职工姓名] AND C.[单位名称] = B.[单位名称] AND C.[Month] >B.[Month] )'exec (@sql)
--print @sql
/*
SELECT [职工姓名],[出勤天数],[单位名称] FROM
( SELECT [职工姓名],SUM([出勤天数]),[单位名称],MAX([Month]) AS [Month]
FROM ( SELECT [职工姓名],[出勤天数],[单位名称],'200801' as [Month] FROM BAK200801 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200802' FROM BAK200802 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200803' FROM BAK200803 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200804' FROM BAK200804 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200805' FROM BAK200805 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200806' FROM BAK200806 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200807' FROM BAK200807 ) A GROUP BY [职工姓名],[单位名称]
) B
WHERE NOT EXISTS(
SELECT 1 FROM ( SELECT [职工姓名],[出勤天数],[单位名称],'200801' as [Month] FROM BAK200801 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200802' FROM BAK200802 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200803' FROM BAK200803 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200804' FROM BAK200804 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200805' FROM BAK200805 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200806' FROM BAK200806 UNION ALL
SELECT [职工姓名],[出勤天数],[单位名称], '200807' FROM BAK200807 ) C
WHERE C.[职工姓名] = B.[职工姓名] AND C.[单位名称] = B.[单位名称] AND C.[Month] >B.[Month] )
*/