WITH WorkOrders AS
(
    SELECT [GROUP Name], [OPEN DATE & TIME]
    FROM _SMDBA_.[WORK Orders]
    WHERE [GROUP Name] IN ('CS LOGISTICS', 'CS HELPDESK', 'CS LEAD', 'CS FT RESEARCH', 'CS EI RESEARCH', 'CS MANAGEMENT')
)
SELECT g.[GROUP Name]
    , [Today] = SUM(CASE WHEN wo.[OPEN DATE & TIME] <= GETDATE() AND wo.[OPEN DATE & TIME] >= CAST( GETDATE() AS DATE) THEN 1 ELSE 0 END)
    , [Week] = SUM(CASE WHEN wo.[OPEN DATE & TIME] <= GETDATE() AND wo.[OPEN DATE & TIME] >= CAST(DATEADD([week], - 1, GETDATE()) AS DATE) THEN 1 ELSE 0 END)
    , [Month] = SUM(CASE WHEN wo.[OPEN DATE & TIME] <= GETDATE() AND wo.[OPEN DATE & TIME] >= CAST(DATEADD([MONTH], - 1, GETDATE()) AS DATE) THEN 1 ELSE 0 END)
FROM _SMDBA_.[Groups] g
    LEFT OUTER JOIN WorkOrders wo ON g.[GROUP Name] = wo.[GROUP Name]
GROUP BY [GROUP Name]