gbbosmiya gbbosmiya - 1 month ago 8
SQL Question

MSSQL 2008: How do i achieve this output

I have table data like below

EID Adate ALogTime
1 2016/04/05 10:06:00 AM
1 2016/04/05 06:16:00 PM
1 2016/04/06 10:15:00 AM
1 2016/04/06 02:06:00 PM
1 2016/04/06 08:49:00 PM
2 2016/04/05 10:06:00 AM
2 2016/04/05 01:21:00 PM
2 2016/04/05 02:20:00 PM
2 2016/04/05 06:30:00 PM
2 2016/04/06 10:06:00 AM
2 2016/04/06 06:20:00 PM
3 2016/04/05 10:10:00 AM
3 2016/04/05 10:35:00 AM
3 2016/04/05 11:49:00 AM
3 2016/04/05 01:06:00 PM
3 2016/04/05 04:19:00 PM
3 2016/04/05 05:36:00 PM
3 2016/04/06 10:45:00 AM
3 2016/04/06 10:48:00 AM
3 2016/04/06 04:06:00 PM
3 2016/04/06 06:13:00 PM


Required output

EID Date Log1 Log2 Log3 Log4 Log5 Log6…..
1 2016/04/05 10:06:00 AM 06:16:00 PM NULL NULL NULL NULL
2 2016/04/05 10:06:00 AM 01:21:00 PM 02:20:00 PM 06:30:00 PM NULL NULL
3 2016/04/05 10:10:00 AM 10:35:00 AM 11:49:00 AM 01:06:00 PM 04:19:00 PM 05:36:00 PM

1 2016/04/06 10:15:00 AM 02:06:00 PM 08:49:00 PM NULL NULL NULL
2 2016/04/06 10:06:00 AM 06:20:00 PM NULL NULL NULL NULL
3 2016/04/06 10:45:00 AM 10:48:00 AM 04:06:00 PM 06:13:00 PM NULL NULL


How to achieve this using MSSQL server any idea pls help.
EID is employee id, Adate is date, ALogTime logtime when user mark attendance in biometric device

Answer
Declare @EmpId varchar(20)
Declare @Ecount int
Declare @count int =1
Declare @Locount int =1
DECLARE @QUERY VARCHAR(255)
DECLARE @COLUMN VARCHAR(50)
DECLARE @Elog VARCHAR(50)
Declare @CurrentDate datetime
Declare @ToDate datetime
 SET @QUERY = 'drop TABLE #temp'
         EXEC(@QUERY)
CREATE TABLE #temp (
    EmpId varchar(20),
    Edate datetime
    )

        set @Ecount = (select top 1 COUNT(EmployeeNo)as r from tbl_Employeeinout where EDate>=@ADate and EDate<=@ADate group by EmployeeNo order by r desc)     
        While(@count<=@Ecount)
        Begin
            SET @QUERY = 'ALTER TABLE #temp ADD ' +'Logtime'+ cast(@count as varchar(2)) + ' VARCHAR (50)'          
            EXEC(@QUERY)
            Set @count=@count+1
        End

if @EmployeeId is not null  
    begin
        Declare Cur_1 cursor for 
                select EmployeeID from tbl_Employee where EEmployeeStatus='Active' and EmployeeID = @EmployeeId
    end
else
    begin
        Declare Cur_1 cursor for 
                select EmployeeID from tbl_Employee where EEmployeeStatus='Active'
    end


Open Cur_1

    Fetch next from Cur_1 into @EmpId   
    While @@FETCH_STATUS=0
    Begin

            SET @CurrentDate = CAST(@ADate as datetime)
            While(@CurrentDate <= CAST(@ADate as datetime))
            begin
                    Set @Locount=1
                    Declare Cur_2 cursor for                    
                        select Elogtime from tbl_EmployeeInOut where ADate=@CurrentDate and EmployeeNo=@EmpId order by cast(@CurrentDate+''+Elogtime as datetime)
                    Open Cur_2
                    Fetch Next From Cur_2 into @Elog
                    while @@FETCH_STATUS=0
                    Begin
                        if(@Locount=1)
                            Begin
                                insert into #temp(EmpId,Adate,Logtime1)values(@EmpId,@CurrentDate,@Elog)
                                Set @Locount=@Locount + 1
                            End
                        Else
                            Begin           
                                --Update #temp SET Logtime2 = '07:39:00 PM' where EmpId='0166' and Adate='2016-10-01'                   
                                declare @te varchar(50)
                                Set @te='Logtime'+ cast(@Locount as varchar(2))
                                SET @QUERY = 'Update #temp SET ' + 'Logtime'+ cast(@Locount as varchar(2)) + ' = '''+@Elog+''' where EmpId='''+@EmpId+''' and Adate='''+ convert(varchar(10),@CurrentDate,126)+''''
                                print @QUERY
                                EXEC(@QUERY)
                                update #temp set @te= @Elog where EmpId=@EmpId and Adate=@CurrentDate
                                Set @Locount=@Locount + 1
                            End

                        Fetch Next From Cur_2 into @Elog
                    End
                    Close Cur_2
                    deallocate Cur_2
                SET @CurrentDate = DateADD(day,1,@CurrentDate)
            End
            set @Ecount=0
            set @count=1

    Fetch next from Cur_1 into @EmpId
    End 
Close Cur_1
deallocate Cur_1
select * from #temp

Done !!!!!!!!!!!!!!