如何查询指定日期内所有部门未写日志的记录

Question

DECLARE @a TABLE(UserName VARCHAR(20),Dept VARCHAR(20))
INSERT @a SELECT 'A','soft'  UNION ALL SELECT 'B','design'   UNION ALL SELECT 'C','admin'   UNION ALL SELECT 'D','debug'   UNION ALL SELECT 'E','debug' DECLARE @b TABLE(StaffName VARCHAR(20),Dept VARCHAR(20),ReportDate SMALLDATETIME) 
insert @b select 'A','soft','2009-03-10' 
union all select 'A','soft','2009-03-11'
union all select 'A','soft','2009-03-12'
union all select 'A','soft','2009-03-13'
union all select 'A','soft','2009-03-14'
union all select 'C','admin','2009-03-10'
union all select 'C','admin','2009-03-11'
union all select 'B','design','2009-03-13'
union all select 'B','design','2009-03-14'
union all select 'D','debug','2009-03-10'
union all select 'E','debug','2009-03-11'
DECLARE @Ds SMALLDATETIME,@De SMALLDATETIMESET @Ds='2009-03-10'
set @De='2009-03-14'DECLARE @c TABLE(id INT IDENTITY(0,1),b INT)
INSERT @c SELECT TOP 30  0 FROM syscolumns sSELECT UserName,aa.Dept,convert(varchar(10),d,120) ReportDate FROM 
(
    SELECT UserName,Dept,@Ds+Id d FROM @a a,@c c WHERE @Ds+id<=@De
)aa
LEFT JOIN 
@b bb
ON aa.UserName=bb.StaffName AND aa.d=bb.ReportDate
WHERE bb.StaffName IS NULL  ORDER BY UserName/*UserName             Dept                 ReportDate 
-------------------- -------------------- ---------- 
B                    design               2009-03-10
B                    design               2009-03-11
B                    design               2009-03-12
C                    admin                2009-03-12
C                    admin                2009-03-13
C                    admin                2009-03-14
D                    debug                2009-03-14
D                    debug                2009-03-13
D                    debug                2009-03-12
D                    debug                2009-03-11
E                    debug                2009-03-10
E                    debug                2009-03-12
E                    debug                2009-03-13
E                    debug                2009-03-14（所影响的行数为 14 行）
*/

Answer 1

declare @UserList table(UserName varchar(10),Dept varchar(10))
insert into @UserList 
select 'A', 'soft' union all
select 'C', 'admin'  union all
select 'B', 'design' union all
select 'D', 'debug' union all
select 'E', 'debug'declare @WeeklyReport table(staffName varchar(10), dept varchar(10), reportDate datetime)
insert into  @WeeklyReport
select 'A', 'soft', '2009-03-10' union all
select 'A', 'soft', '2009-03-11'  union all
select 'A', 'soft', '2009-03-12'  union all
select 'A', 'soft', '2009-03-13'  union all
select 'A', 'soft', '2009-03-14' union all select 'C' ,'admin', '2009-03-10'  union all
select 'C' ,'admin', '2009-03-11'  union allselect 'B' ,'design', '2009-03-13'  union all
select 'B', 'design', '2009-03-14'  union allselect 'D', 'debug', '2009-03-10'  union all
select 'E' ,'debug', '2009-03-11' 
select * from
(
select a.*,ReportDate = dateadd(day,number,'2009-03-10')
from @UserList a ,master..spt_values b 
where b.type = 'p' and number<=datediff(day,'2009-03-10','2009-03-14')
)a 
where not exists
  (select 1 from @WeeklyReport b where a.username = b.staffname and a.ReportDate = b.reportDate and a.Dept = b.dept)
order by UserName/*
B design 2009-03-10 00:00:00.000
B design 2009-03-11 00:00:00.000
B design 2009-03-12 00:00:00.000
C admin 2009-03-12 00:00:00.000
C admin 2009-03-13 00:00:00.000
C admin 2009-03-14 00:00:00.000
D debug 2009-03-11 00:00:00.000
D debug 2009-03-12 00:00:00.000
D debug 2009-03-13 00:00:00.000
D debug 2009-03-14 00:00:00.000
E debug 2009-03-10 00:00:00.000
E debug 2009-03-12 00:00:00.000
E debug 2009-03-13 00:00:00.000
E debug 2009-03-14 00:00:00.000
*/

Answer 2

create proc wsp 
@star datetime,
@end datetime
as
declare @t table(dt datetime)
while(datediff(dd,@star,@end)>=0)
begin
insert into @t select @star
set @star=dateadd(dd,1,@star)
end
select * from (select * from UserList a full join @t b on 1=1)a
where not exists(select 1 from WeeklyReport where staffName=a.UserName and datediff(dd,reportDate,a.dt)=0)
order by a.username,dt
goexec wsp @star='2009-03-10',@end='2009-03-14'