2

I have write a query to get the total number assignments which had no activity from last 5 months including those assignments which were created five months ago and never had any visit on it.

Can i shorten this query any further so i don't have to use the outer query.

  SELECT @NumNoActivity = COUNT(QnoActivity.AssignmentID) FROM

  (

    SELECT a.AssignmentID
    FROM Assignments a
    LEFT JOIN VISITS v ON v.AssignmentID = a.AssignmentID
    WHERE a.CurrentStatus = 1  
    AND a.StaffID = @StaffID
    GROUP BY a.AssignmentID,  a.CreatedDate
    HAVING DATEDIFF(MONTH, ISNULL(MAX(v.VisitDate),a.CreatedDate ), GETDATE())  > =5

  ) QnoActivity
user1263981
  • 2,953
  • 8
  • 57
  • 98

2 Answers2

2

You could use NOT EXISTS to remove the join:

SELECT @NumNoActivity = COUNT(a.AssignmentID)
FROM Assignments a
WHERE a.CurrentStatus = 1  
AND a.StaffID = @StaffID
AND NOT EXISTS
        (SELECT * FROM VISITS v 
         WHERE v.AssignmentID = a.AssignmentID
         AND v.VisitDate>DATEADD(month,-5,GETDATE()))

UPDATE

Based on Gordon Linoff's comment, the query was missing the Assignments.CreatedDate condition. Here's an updated version:

SELECT @NumNoActivity = COUNT(a.AssignmentID)
FROM Assignments a
WHERE a.CurrentStatus = 1  
AND a.StaffID = @StaffID
AND (SELECT ISNULL(MAX(VisitDate), a.CreatedDate) FROM VISITS v 
         WHERE v.AssignmentID = a.AssignmentID) <= DATEADD(month,-5,GETDATE())

Here is SQL Fiddle to show how it works. Assignment results are:

  • 1 - not included as there's a recent visit
  • 2 - included as there's no visit and create date is old
  • 3 - included as there's only a recent visit
  • 4 - not included as there's no visit but create date is recent
  • 5 - not included as there's a recent visit
Szymon
  • 42,577
  • 16
  • 96
  • 114
  • you have changed the sql query to use `3 Select Statements`, instead of `2 Select Statements`, do you think its a good habit – HarshSharma Feb 03 '14 at 11:33
  • Number of selects in itself doesn't really matter much. What counts is how it will get resolved into an execution plan. Also, a join is effectively 2 selects as well. – Szymon Feb 03 '14 at 11:37
  • No, i dnt think so, you can see the good explanation here : http://stackoverflow.com/questions/19835485/two-selects-or-one-select-one-join-in-sql as you can see the execution plan, `Join` is taking less time to execute in the first case – HarshSharma Feb 03 '14 at 11:43
  • @Szymon +1 But I think you can use COUNT in the inner query there is no need to use outer query and I would use `v.VisitDate>DATEADD(month,-5,GETDATE())` instead of DATEDIFF so SQL server would be able to use index on `v.VisitDate` to make it faster. – valex Feb 03 '14 at 11:44
  • @HarshSharma That really depends. In this case, a lot on the distribution of data. Also, be mindful that this is not inner join but left join. – Szymon Feb 03 '14 at 11:45
  • @valex You're very right in both cases. Thank you, I updated my answer. – Szymon Feb 03 '14 at 11:48
  • 1
    @HarshSharma For this query logic if you use JOIN you should use also `GROUP BY` or `COUNT(DISTINCT AssignmentID)`. So `NOT EXISTS` here is instead of `JOIN + GROUP BY`. I don't think it will be slower but real speed sure depends on indexes and data distribution. – valex Feb 03 '14 at 11:50
  • I used the Left join bcz there might be a assignment which never had any visit on it. So if assignment was created 5 months ago and had no visit then it should be included in the list. – user1263981 Feb 03 '14 at 12:05
  • @user1263981 Yes, that makes sense for your logic. In this case `NOT EXISTS` will be true and it will work the same way. – Szymon Feb 03 '14 at 12:06
  • It would be better if you use `(SELECT 1 FROM VISITS)` in inner query instead of `(SELECT * FROM VISITS)`. It would reduce the number of rows selection time for inner query which is not required in this scenario. – Sheikh M. Haris Feb 03 '14 at 12:10
  • This doesn't include the condition on the created date, if there are no visits. – Gordon Linoff Feb 03 '14 at 12:11
  • It is not counting those assignments which had no visit. – user1263981 Feb 03 '14 at 12:27
  • @user1263981 . . . I think it is better to put it in the `where` clause of the outer query. – Gordon Linoff Feb 03 '14 at 12:31
  • @user1263981 What is `CreatedDate` on those assignments that had no visits? – Szymon Feb 03 '14 at 12:38
  • Assignment is kind of a parent table with one to many relation with Visit table. So there can be more than one visit or no visit on an assignment. We are basically checking that how many assignments are there with no visits in past 5 months. – user1263981 Feb 03 '14 at 12:42
  • I updated the answer with SQL Fiddle demonstrating how it works. – Szymon Feb 03 '14 at 12:53
  • Pls add last visit date '20130911' into your schema with assignment 6 and you will see it won't show the assignment 6 even though DATEDIFF(MONTH,'11/09/2013',GETDATE()) = 5 – user1263981 Feb 03 '14 at 13:52
  • I think it is that DATEADD(month,-5,GETDATE()) function which gives '03/09/2013' , DateDiff is slightly different DATEDIFF(MONTH,'11/09/2013',GETDATE()) = 5 – user1263981 Feb 03 '14 at 14:00
  • I have just run the execution plan on both mine and your query and i can't see any difference. The execution plan is just same. Looks like dateadd function is not making any difference. – user1263981 Feb 03 '14 at 14:09
  • I think i better use the 'Day' in DateDiff function instead of 'Month'. Please correct me if i am wrong, DATEDIFF( DAY ,ISNULL(MAX(v.VisitDate),a.CreatedDate), GETDATE()) >= 153 is same as ATEADD(month,-5,GETDATE()) . If i use the 'DAY' then @Szymon query is correct (it doesn't make any difference in performance) – user1263981 Feb 03 '14 at 15:14
  • `DATEDIFF` doesn't take any rounding into account, just the difference in month/day number. So yes, using 153 days will give you better precision (though it's not completely correct as months are different length but it's better anyway). – Szymon Feb 03 '14 at 18:58
1

There is an alternative way to write this query. You can look at it as the difference etween the total number of "assignments" and the total number of "assignments" that have had activity in the past 4/5 months.

The following takes this approach. It excludes new "assignments" in the where clause and the counts the different in the select clause:

SELECT (count(distinct a.AssignmentID) -
        count(distinct case when datediff(MONTH, v.VisitDate, GETDATE()) < 5 or
                                 v.VisitDate is null
                            then a.AssignmentId end)
       )
FROM Assignments a LEFT JOIN
     VISITS v
     ON v.AssignmentID = a.AssignmentID
WHERE a.CurrentStatus = 1 AND
      a.StaffID = @StaffID and
      a.CreatedDate <= DATEADD(month, -5, GETDATE());
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • there is a difference of 6. Should return 38 instead of 32. I think it is missing those assignments which had no visit. – user1263981 Feb 03 '14 at 12:22
  • @user1263981 . . . How embarrassing, because I was specifically thinking about that condition with the `where` clause. I added the appropriate logic for it. – Gordon Linoff Feb 03 '14 at 12:29