Get the data of only latest date and time stamp from SQL Query.

Question

I am creating PowerBI dashboard using SQL Database of attendance system. In dashboard I want to show employees count on each floor. (For Example - If employee punch-in on 6th floor attendance machine then employee count of 6th floor will increase by 1 number.

For this I have created new table in SQL database & written below query.

Select [UserID]
,[Username]
,[Edatetime]
,[DeviceName]
,[INOuttype]
From [PowerBI].[dbo].[PowerBI_data] order by edatetime desc

Currently whenever I execute this query output result is showing multiple IN & Out entries as employees Punch-In & Punch-out many times in a day.

Instead of showing all the IN & Out entries, I wanted to see ONLY RECENT Punch-In/Punch-Out entry results of each employee.

Can you please suggest what query I have write to get this data..?

Answer 1

First of all, for a question like this, it is always a good idea to post the CREATE TABLE statement for your table(s) and INSERT statements with sample data together with the desired result given the sample. This makes it easy to copy and paste into a query window to develop a tested query.

What you have below is an entirely untested query.

SELECT U.UserID, U.UserName, P.Edatetime, P.DeviceName, P.InOutType
FROM   Users U
CROSS APPLY (SELECT TOP (1) P.Edatetime, P.DeviceName, P.InOutType
             FROM  PowerBI_data P
             WHERE P.UserID = U.UserID
             ORDER BY P.Edatetime DESC) AS P

If you don't have a Users table, you can replace FROM Users with

FROM  (SELECT DISTINCT UserID, UserName FROM PowerBI_data) AS U

Answer 2

As you can see in table, whenever I execute query, it shows multiple records for each employee as they have puch-in and punch-out multiple times in a day.

Instead of this, I want to get the details of only latest Punch-in or Punch-out.

Answer 3

Do you want 2 rows for each employee (one for punch in, one for punch out). If yes, it would be small modification of Erland's query:

    ;with cte1 as (SELECT U.UserID, U.UserName, P.Edatetime as InTime, P.DeviceName as InDevice, P.InOutType
    FROM   Users U
    OUTER APPLY (SELECT TOP (1) P.Edatetime, P.DeviceName, P.InOutType
                 FROM  PowerBI_data P
                 WHERE P.UserID = U.UserID and InOutType = 1
                 ORDER BY P.Edatetime DESC) AS P),
cte2 as ( (SELECT U.UserID, U.UserName, X.Edatetime as OutTime, P.DeviceName as OutDevice, P.InOutType
    FROM   Users U
    OUTER APPLY (SELECT TOP (1) P.Edatetime, P.DeviceName, P.InOutType
                 FROM  PowerBI_data P
                 WHERE P.UserID = U.UserID and InOutType = 0
                 ORDER BY P.Edatetime DESC) AS X)
select ISNULL(c1.UserID, c2.UserID) as UserId, isnull(c1.UserName, c2.UserName) as UserName, c1.InTime, c1.InDevice, c2.OutTime, c2.OutDevice
from cte1 c1 FULL JOIN cte2 c2 ON C1.UserId = c2.UserId

Answer 4

Hi，@Shreyas Kale
Welcome to Microsoft T-SQL Q&A Forum!
Please check this:

SELECT TOP (1) P.Edatetime, U.UserID, P.DeviceName, P.InOutType  
FROM   Users U inner join  PowerBI_data P on P.UserID = U.UserID  
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, Edatetime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))  
order by P.Edatetime Desc  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi @Shreyas Kale
Please check this，table is like this：

;WITH CTE AS(select USEID,USERNAME,EDATETIME,DEVICENAME,INOuttype,ROWNUM = ROW_NUMBER()OVER(PARTITION BY USEID,USERNAME ORDER BY EDATETIME DESC) from csvtable where EDATETIME BETWEEN ( select DATEADD(hour,12,cast(cast(getdate() as date) as datetime))  
) AND(  select DATEADD(hour,24,cast(cast(getdate() as date) as datetime))  
) )  
SELECT * FROM CTE WHERE ROWNUM = 1  

Result：

Best Regards，
Isabella