The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `batches` ADD INDEX `batches_idx_workid_itemid` (`workid`,`itemid`);
ALTER TABLE `details` ADD INDEX `details_idx_reject_itemid_siteid_documen` (`reject`,`itemid`,`siteid`,`documenttype`);
ALTER TABLE `details` ADD INDEX `details_idx_reject_documen_itemid_siteid` (`reject`,`documenttype`,`itemid`,`siteid`);
ALTER TABLE `groups` ADD INDEX `groups_idx_siteid_workid` (`siteid`,`workid`);
ALTER TABLE `worksrcdesc` ADD INDEX `worksrcdesc_idx_workid_site_date` (`workid`,`site`,`date`);

The optimized query:

SELECT
        b.workid,
        b.siteid,
        (SELECT
            TOP 1 a.description 
        FROM
            worksrcdesc a 
        WHERE
            a.workid = b.workid 
            AND a.site = b.site 
        ORDER BY
            a.date DESC) AS descript,
        b.itemid,
        b.mode,
        b.systemdate,
        (SELECT
            count(a.sequence) 
        FROM
            details a 
        WHERE
            a.reject = 0 
            AND a.itemid = b.itemid 
            AND a.siteid = b.siteid 
            AND a.documenttype IN (
                1, 2
            )) AS documentcount,
        (SELECT
            count(a.sequence) 
        FROM
            details a 
        WHERE
            a.reject = 0 
            AND a.itemid = b.itemid 
            AND a.siteid = b.siteid 
            AND a.documenttype = 15) AS othercount,
        (SELECT
            count(DISTINCT a.tranno) 
        FROM
            details a 
        WHERE
            a.reject = 0 
            AND a.itemid = b.itemid 
            AND a.siteid = b.siteid) AS transactioncount,
        (SELECT
            TOP 1 d.groupnum 
        FROM
            groups d 
        WHERE
            b.siteid = d.siteid 
            AND b.workid = d.workid) AS sitegrouping 
    FROM
        batches b WITH (NOLOCK) 
    WHERE
        b.workid IN (
            12345, 67980
        ) 
    ORDER BY
        b.workid ASC,
        b.itemid ASC