Click here to Skip to main content
15,029,162 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to get the entries where the difference between two Columns bigger as 2 min and then get just the top 100 entreis ?

I have two Datetime Columns which they called :

AddedToQueue / LastStatusUpdate
2019-03-13 12:06:43.0430000 / 2019-03-21 17:50:00.0000000

thats just an Example i have 8000 records and want to an Query to find the difference between those two columns where difference bigger as 2 min and then select the top 100 and sorted them by AddesToQueue .

I will be so glad for your help .

What I have tried:

SELECT AddedToQueue, LastStatusUpdate, LastStatusUpdate - AddedToQueue AS Difference, 
case when ( LastStatusUpdate - AddedToQueue)>2 then 'Greater than 0' else 'Less than/equal to 0' 
end as My_Flag 
FROM PrintQueue
Posted
Updated 12-Apr-21 2:58am

SELECT TOP 100 ... CASE WHEN DATEDIFF(mi, LastStausUpdate, AddedToQueue) > 2 THEN ...
ORDER BY ...
You need the order by or SQL can use any criteria it likes to select which rows to display.
   
Comments
Maciej Los 22-Aug-19 6:28am
   
5ed!
[no name] 22-Aug-19 6:37am
   
@OrginalGriff, thank you for your answer but like i did
SELECT Top 100 Case When DATEDIFF(mi, LastStatusUpdate, AddedToQueue) > 2

FROM dbo.PrintQueue;

it does not work .. sorry i am new in SQL .
OriginalGriff 22-Aug-19 6:55am
   
"it does not work" tells us nothing!
What did it do that you didn't expect, or not do that you did?

Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.

[no name] 23-Aug-19 3:57am
   
I thank you .. i have got the Solution . you have helped me
Quote:
How to get the entries where the difference between two Columns bigger as 2 min and then get just the top 100 entreis ?


You need to write WHERE clause[^] with DATEDIFF[^] method:
SQL
SELECT TOP (100) ...
FROM ...
WHERE DATEDIFF(mi, LastStausUpdate, AddedToQueue) > 2
   
Line 1: Limits the results to 100 rows

Line 2: Subtracting one DateTime from another gives you a new DateTime object... Did you realize your data will return 1900-01-09 05:43:17.000 when you do it that way? Use the DateDiff function to give you a unit to work with (in this case minutes). This method with your data returns 11864

Line 4: Use the WHERE to limit your returns to only those where the difference in minutes is greater than 2.

Line 5: Use the ORDER BY to set which column these will be sorted by.

Notice I removed the CASE statement; it really is not needed for functionality
SQL
SELECT   TOP (100) AddedToQueue, LastStatusUpdate
     ,   Difference = DateDiff(mi,LastStatusUpdate, AddedToQueue)
FROM     PrintQueue
WHERE    DATEDIFF(mi, AddedToQueue, LastStatusUpdate) > 2
ORDER BY AddedToQueue
   
Comments
[no name] 23-Aug-19 3:56am
   
@MadMyche, i liked your Query it gives me what i wanted with an easy explanation could you please tell me what is the different between your Query and the following Query :

SELECT TOP 100 * FROM PrintQueue WHERE DATEDIFF(n, AddedToQueue, LastStatusUpdate) > 2
ORDER BY AddedToQueue

the question what is the different between Difference and Datediff ?
MadMyche 23-Aug-19 7:22am
   
The difference between the query I provided and
SELECT TOP 100 * FROM... is that my query returns only the 2 DateTime columns, and adds a third column which is the difference the 2 DateTimes.

the question what is the different between Difference and Datediff ?
Difference is just the name I gave to the new column which is equal to the DateDiff function. Your original question had
LastStatusUpdate - AddedToQueue AS Difference
and that is where I came up with it.
These two lines are functional equivelants
Difference = DateDiff(mi,LastStatusUpdate, AddedToQueue)
DateDiff(mi,LastStatusUpdate, AddedToQueue) as Difference
that can be used assign a name (or rename, aka Alias) to a value in a query. My preference is to use [NewName] = formula especially in lonq queries that cover many lines as I find it easier to read when the code is formatted
[no name] 23-Aug-19 9:14am
   
one more question please ... if i want to give a variable as parametrs like next Ex :
Hide Copy Code
declare @month int
declare @year int
set @month = 6
set @year = 2019

select top 1 PrinterName
from PrintQueue
where MONTH(AddedToQueue) = @month and YEAR (AddedToQueue)=@year
group by PrinterName
order by Count(PrinterName) desc


even to be able to change the year and month ... is this right what i did ?!
MadMyche 23-Aug-19 10:20am
   
Yes
[no name] 23-Aug-19 8:47am
   
oh man thanks alot for make it clear for me .

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900