gistfile1.sql

select *
into #ElightenedReasons
from   (select a.OwnerUserId as UserId,
               a.Id          as ReasonId
        from   Posts a with (nolock)
        where  a.PostTypeId = 2
           and a.DeletionDate is null
           and a.OwnerUserId is not null
           and a.Score >= 10
           and exists (select 1 /* question owner cannot own accepted answer */
                       from   Posts q with (nolock)
                       where  q.PostTypeId = 1
                          and q.AcceptedAnswerId = a.Id
                          and q.OwnerUserId <> a.OwnerUserId)
           and a.Id = (select min(firstAnswer.Id) /* answer must be the first one given */
                       from   Posts firstAnswer with (nolock)
                       where  firstAnswer.DeletionDate is null
                          and firstAnswer.ParentId = a.ParentId)) as X
where X.ReasonId is not null
                         
                         
select UserId, Total = COUNT(*)
into #Totals
from #ElightenedReasons
group by UserId 

update t
 set Total = t.Total - Actual 
from #Totals t
join (select COUNT(*) Actual, u.UserId from Users2Badges u where BadgeId = (select b.Id from Badges b where b.Name = 'Enlightened') group by u.UserId) X 
	on X.UserId = t.UserId 
	
delete #Totals 
where Total < 1


delete e 
from #ElightenedReasons e
join  Users2Badges u on BadgeId = (select b.Id from Badges b where b.Name = 'Enlightened') and e.ReasonId = u.ReasonId

delete from #ElightenedReasons
where UserId not in (select t.UserId from #Totals t)

insert Users2Badges
	(UserId, BadgeId, Date, Comment, ReasonId)
select 
	t.UserId, 
	(select b.Id from Badges b where b.Name = 'Enlightened'), 
	(select CreationDate + 7 from Posts where Id = ReasonId), 
	'granted by sam', 
	ReasonId  
from #Totals t
join
	(SELECT ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY ReasonId) AS ROWID, * FROM #ElightenedReasons) X on t.UserId = X.UserId 
where ROWID < Total


drop table #Totals
drop table #ElightenedReasons


Update Messages set IsRead = 1
where MessageTypeId = 5
and Text = 'You''ve earned the "Enlightened" badge.'
and CreationDate between '2010-30-09' and '2010-2-09'