SELECT CAST(dtDateTime as Date) As TheDate, count(*) as TotalRecs, COUNT(CASE WHEN nDBWTS_EffluentFlow<0 THEN 1 END) AS CountNegatives, Min(nDBWTS_EffluentFlow) as MinFlow, Max(nDBWTS_EffluentFlow) as MaxFlow, SUM(nDBWTS_EffluentFlow) as TotAllFlow, AVG(nDBWTS_EffluentFlow) as AvgAllFlow, AVG(nDBWTS_EffluentFlow)*0.001 * 60 * 1440 as AllEnvirSum, SUM(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) as TotPosFlow, AVG(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END) AS AvgPosFlow, AVG(CASE WHEN nDBWTS_EffluentFlow>=0 THEN nDBWTS_EffluentFlow END)*0.001 * 60 * 1440 AS PosEnvirSum FROM [EnvolvData].[dbo].[Environmental] WHERE CAST(dtDateTime AS Date) BETWEEN '2022-09-01' AND '2022-09-20' GROUP BY CAST(dtDateTime as Date) ORDER BY CAST(dtDateTime as Date) ASC