0

in this query i need to get how much minute has passed away from current time, this is my query :

select Status,CallDate,
CONVERT(int,DATEDIFF(MINUTE,CallDate,GETDATE())) as downtime
from servicerequest
where status = 'Open' and downtime < 180;

the query show data like this

Status  Calldate                downtime  
Open    2020-06-30 02:03:17.927  548           
Open    2020-06-30 02:18:07.800  533           
Open    2020-06-30 08:11:42.600  180           
Open    2020-06-30 08:11:00.000  180           
Open    2020-06-30 08:22:16.740  169           
Open    2020-06-30 08:02:52.610  189           
Open    2020-06-30 08:39:30.760  152           
Open    2020-06-30 08:39:00.000  152

as you can see the query is not working properly, because it should not show data below 180 (int), any ideas how to do it? thank you

Tray Scott
  • 13
  • 4
  • I didn't understand your query well, you aliased converted valued with `downtime`. and tried to use it in the `where` clause. you can't use an alias name in the same query. – Muhammad Vakili Jun 30 '20 at 04:25
  • Do you have any field named `downtime` in the table `servicerequest`? – Muhammad Vakili Jun 30 '20 at 04:26
  • hi thank for your comment, no i dont have `downtime` in my table., downtime = datetime type data that i get only minute and convert it to integer, so in my opinion this data can now be used for searching what rows has more than 180 minute of downtime, – Tray Scott Jun 30 '20 at 04:28
  • This is the point I want to aware you, the `downtime` in the `select` clause is not the same `downtime` in the `where` clause. you should write something like this in the `where` clause: `where status = 'Open' and CONVERT(int,DATEDIFF(MINUTE,CallDate,GETDATE()))< 180;` – Muhammad Vakili Jun 30 '20 at 04:31
  • 1
    wow its working, thank vakili !!!!!!!!!!!!!!!!!!!!!!!!!! – Tray Scott Jun 30 '20 at 04:35

0 Answers0