19-03-2020----------DataBase Changes

DataBase Changes






create procedure USPcheckAnyWorkDoneorNot
(
 @srno int
)
as
begin
      declare @message varchar(100)=''

      if exists(select 1 from tbl_Attendance where user_id=@srno and cast(Coming_Time as date)=cast(getDate() as date))
  begin
        declare @working_per varchar(30)=''

select @working_per=user_id from tbl_Login where srno=@srno

        if exists(select 1 from tbl_ProjDetails where cast(ddate as date)=cast(getDate() as date) and working_per=@working_per)
begin
     declare @time time(0)

                 select top 1 @time=Convert(time(0),(Convert(datetime,Convert(varchar,getDate(),8)) -Convert(datetime,cast(ddate as time)) ),8)
from tbl_ProjDetails where cast(ddate as date)=cast(getDate() as date)
                 and working_per=@working_per order by ddate desc

                 if(@time>='1:00:00')
                  begin
        set @message='You are sitting ideal since last '+Cast(@time as varchar)
  end
  --else
  --begin
  --      set @message='You are sitting ideal since last '+Cast(@time as varchar)
  --end
end
else
begin
       declare @comingTime time(0);

       select @comingTime=Convert(time(0),(Convert(datetime,Convert(varchar,getDate(),8)) 
   -Convert(datetime,cast(Coming_Time as time)) ),8) from tbl_Attendance where user_id=@srno
   and cast(Coming_Time as date)=cast(getDate() as date)

   if(@comingTime>='1:00:00')
                  begin
        set @message='You are sitting ideal since last '+Cast(@time as varchar)
  end
  else
  begin
        set @message='You have not started any work till now.'
  end
end

  end
  select @message as msg

end