question

ShreyasKale-8444 avatar image
0 Votes"
ShreyasKale-8444 asked IsabellaZhangMSFT-7746 answered

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

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..?

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Erland, Your given query helped me to get TOP 1 results for the unique UserID's but it is showing the results of past dates as well.

I wanted to get the results only for today's date for time range starting From 12:00 AM to 11:59 PM. Can you please suggest query for this please.

0 Votes 0 ·

So did you read this part of my post:

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.

Another way of putting it is that without this information, it will be a guessing game. And my guess this time is that you can add a condition to the subquery:

AND P.Edatetime > convert(date, sysdatetime()
0 Votes 0 ·
ShreyasKale-8444 avatar image
0 Votes"
ShreyasKale-8444 answered NaomiNNN converted comment to answer

197979-capture.png



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.


capture.png (103.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes, I understood that from your first post, but did you try my query?

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered

Hi,@ShreyasKale-8444
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.






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

IsabellaZhangMSFT-7746 avatar image
0 Votes"
IsabellaZhangMSFT-7746 answered

Hi @ShreyasKale-8444
Please check this,table is like this:

198521-image.png



 ;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:
198512-image.png


Best Regards,
Isabella


image.png (9.2 KiB)
image.png (8.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.