/******* [CheckForOrderNumberDups] ******/
CREATE PROCEDURE [rdld].[CheckForOrderNumberDups]
as
    BEGIN
        SET NOCOUNT ON;
        SET DEADLOCK_PRIORITY LOW;

        SELECT x.XmlOrderLinkId, XmlSystem.ExternalSystem, x.ExternalKey, o.OrderNo, SysUser.FirstName, o.CreatedOn, o.PriKey
            FROM orders o
            left outer JOIN xmlorderlink x ON x.OrderFID = o.PriKey
            left  outer join XmlSystem on x.XmlSystemFID = XmlSystem.XmlSystemID
            left outer join SysUser on SysUser.SysUserID = o.CreatedBy
            WHERE  EXISTS (
                SELECT 1
                FROM orders o2
                WHERE o2.orderno = o.orderno
                and o2.CreatedOn > DATEADD(year, -1, GETDATE())
                GROUP BY o2.orderno
                HAVING COUNT(*) > 1
            )
            order by orderno
        FOR XML RAW;

        RETURN 0;
    END;
GO
GRANT EXECUTE ON  [rdld].[CheckForOrderNumberDups] TO [MssExec]
GO