-2
SELECT 
    TheDate, 00 AS ReportID, 
    (SELECT SiteID FROM LocalVariables) AS SiteID,      
    (SELECT COUNT(COALESCE([ReviewID], 0)) AS Expr1 
     FROM [RAAudits]  
     WHERE (CAST([DateCompleted] AS date) = DateExtended.TheDate) 
    AND pass = 1) * 100 /
          (SELECT COUNT(COALESCE([ReviewID], 0)) AS Expr2 
           FROM [RAAudits]  
           WHERE (CAST([DateCompleted] AS date) = DateExtended.TheDate)) AS DataValue
FROM   
    DateExtended  
WHERE  
    TheDate BETWEEN (SELECT ServiceStartDate FROM LocalVariables) 
                AND GETDATE() -1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Prashanthan Kailainathan
  • 15
  • 2
  • show your example data in #TEMP table and we can help with query – Adamszsz Jul 29 '21 at 11:20
  • 1
    There is too much implied and hidden information here. For example, I'm inferring that `localVariables` only has one row, and that `[DateCompleted]` comes from the `DateExtended` table, but in neither case can I be sure. You should give enough example data that we can replicate what you want to do, which is all explained here : https://stackoverflow.com/help/minimal-reproducible-example – MatBailie Jul 29 '21 at 11:23

1 Answers1

0

I would suggest using avg() instead. Assuming pass only takes on the values 0 and 1:

SELECT TheDate, 00 As ReportID,
       (SELECT SiteID from LocalVariables) as SiteID,      
       (SELECT AVG(pass * 100.0)
        FROM [RAAudits]  
        WHERE cast([DateCompleted] as date) = DateExtended.theDate
       )
FROM DateExtended  
WHERE TheDate BETWEEN (SELECT ServiceStartDate from LocalVariables) AND GETDATE() -1;

Note: It seems strange to use subqueries instead of JOINs, but they should not affect performance. However, the query will break if LocalVariables has more than one row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786