More efficient qurty

1

As part of a learning curve, is there a better way to write the below query. I’m looking for a 3 counts of items from one table 1 with different ‘status’ and one count from table2.

Declare @AccountId uniqueidentifier

DECLARE @countapproval int = 0
DECLARE @countaccepted int = 0
DECLARE @countunsubmitted int = 0
DECLARE @unreadbacsreports int = 0

Select @countunsubmitted = Count(id] FROM [dbo].[Table1] WHERE [Status] = 1 AND [Accountid] = @AccountId and datediff(d, [CreatedOn] , getdate()) <= 60
Select @countapproval = Count(id] FROM [dbo].[Table1] WHERE [Status]= 7 AND [Accountid] = @AccountId and datediff(d, [CreatedOn] , getdate()) <= 60
Select @countaccepted = Count(id] FROM [dbo].[Table1] WHERE [Status] = 2 AND [Accountid] = @AccountId and datediff(d, [SubmittedOn] , getdate()) = 0
Select @unreadbacsreports = Count([ReportId]) FROM [dbo].[Table2] where [Accountid] = @AccountId and [Viewed] = 0

Select @countunsubmitted, @countapproval, @countaccepted, @unreadbacsreports

2 

Declare @AccountId uniqueidentifier

DECLARE @countapproval int = 0
DECLARE @countaccepted int = 0
DECLARE @countunsubmitted int = 0
DECLARE @unreadbacsreports int = 0

Select 
    @countunsubmitted = sum(case when Status = 1 then 1 else 0 end),
    @countapproval = sum(case when Status = 7 then 1 else 0 end),
    @countaccepted = sum(case when Status = 2 then 1 else 0 end)
FROM [dbo].[Table1] 
WHERE AccountId = @AccountId AND
    ((Status = 1 AND CreatedOn >= DATEADD(DAY, -60, CAST(GETDATE() AS date))) OR
     (Status = 7 AND CreatedOn >= DATEADD(DAY, -60, CAST(GETDATE() AS date))) OR
     (Status = 2 AND SubmittedOn >= CAST(GETDATE() AS date))
    )
3

It’s vastly more efficient to do any calcs on static values, such as GETDATE(), rather than on table columns. That’s always true.

Efficiency also depends on how the table is indexed. “Table1” presumably should be indexed on AccountId, which will make a single query more efficient than multiple queries. I will admit that “OR” conditions sometimes “confuse” the optimizer and make it read more rows than necessary, but in this case I think it will be OK.

For completeness, you really should check retrieving submitted and approved in 1 query, and accepted in a different query.

4

Thank you Scott,

The example and explanation makes perfect sense and easy to understand