Looping Transact SQL

what if the previous day too has no records? go back to day -1?

in that case you could use something like below

while(1=1)
begin
   //select query
    where date=@somedate
   if @@rowcount=0 
     //@somedate=@somedate-1
   else
     begin
     //do other process (CTE)
     break;//exit loop
     end     
end

---

Satheesh