SELECT        --be.id,
                cog.clientorganizationid,
                be.clientid,
                be.providerid,
                DATEPART(MONTH,be.timeworkedfrom) AS MonthWorked,
                DATEPART(YEAR,be.timeworkedfrom) AS YearWorked,
                --be.timeworkedto,
                SUM(DATEDIFF(MINUTE, be.timeworkedfrom,be.timeworkedto)) AS MinutesWorked,
                be.procedurecodestring,
                cc.fullname AS ClientName,
                cc2.fullname AS CSL,
                CASE
                WHEN MAX(metrics.Employees) >= 501 THEN 'CS: 500+ Enterprise'
                WHEN MAX(metrics.Employees) >= 251 THEN 'CS: 250-500'
                WHEN MAX(metrics.Employees) >= 51 THEN 'CS: 51-250'
                WHEN MAX(metrics.Employees) >= 16 THEN 'CS: 16-50'
                WHEN MAX(metrics.Employees) >= 2 THEN 'CS: 2-15'
                WHEN MAX(metrics.Employees) = 1 THEN 'Single User'
                ELSE 'N/A'
                END AS CustomerSegment,
                MAX(metrics.Employees) AS ActiveEmployees,
                labelData.Industry

FROM            billing_entries be

INNER JOIN       (
                SELECT        bcc.*
                FROM            billing_codes_custom bcc

                LEFT JOIN       label_entities bcctv
                ON              bcctv.entityid = bcc.id

                WHERE           bcc.providerid = 65277
                                AND bcctv.entityId = 7149
                                AND bcc.deleteddate IS NULL
                                AND bcctv.entity = 'servicecode'
                )a
ON      a.Id = be.procedurecodeid

LEFT JOIN       contacts cc
ON              cc.id = be.clientid

LEFT JOIN       contacts cc2
ON              cc2.id = be.providerid

LEFT JOIN
        (
        SELECT  md.contactid,
                MAX(CASE WHEN md.fieldid = 38 THEN VALUE END) AS Seats
        FROM    dbo.contact_meta md WITH(NOLOCK)
        WHERE   md.fieldid IN (38)
        GROUP BY
                md.contactid
        ) metaData
ON      be.clientid = metaData.contactid

LEFT JOIN       dbo.contacts_client_organization cog WITH(NOLOCK)
ON              cog.clientid = be.clientid

LEFT JOIN       (
        SELECT  mu.organizationid,
                DATEPART(MONTH, mu.dy) AS [MONTH],
                DATEPART(YEAR, mu.dy) AS [YEAR],
                MAX(CASE me.metrickey WHEN 'contacts-client_active_count_day_org' THEN mu.VALUE ELSE 0 END) AS Clients,
                MAX(CASE me.metrickey WHEN 'contacts-employee_active_count_day_org' THEN mu.VALUE ELSE 0 END) AS Employees,
                SUM(CASE me.metrickey WHEN 'contacts-forms_submission_client_count_day_org' THEN mu.VALUE ELSE 0 END) AS ContactFormsClient,
                SUM(CASE me.metrickey WHEN 'contacts-forms_submission_employee_count_day_org' THEN mu.VALUE ELSE 0 END) AS ContactFormsEmployee,
                MAX(CASE me.metrickey WHEN 'contacts-employee_login_percent_day_org' THEN mu.VALUE ELSE 0 END) AS PercentEmployeeLogin,
                MAX(CASE me.metrickey WHEN 'contacts-forms_active_count_day_org' THEN mu.VALUE ELSE 0 END) AS ContactForms,
                SUM(CASE me.metrickey WHEN 'claims-claim_created_count_day_org' THEN mu.VALUE ELSE 0 END) AS Claims,
                SUM(CASE me.metrickey WHEN 'claims-claim_sent_count_day_org' THEN mu.VALUE ELSE 0 END) AS ClaimsSent,
                SUM(CASE me.metrickey WHEN 'claims-claim_payment_count_day_org' THEN mu.VALUE ELSE 0 END) AS ClaimPayments,
                SUM(CASE me.metrickey WHEN 'scheduling-event_count_day_org' THEN mu.VALUE ELSE 0 END) AS Appointments

                FROM    crarchive.dbo.module_utilization mu WITH(NOLOCK)
        JOIN    (
                SELECT  me.id,
                        me.channelid + '-' + me.metrickey AS MetricKey
                FROM    crarchive.dbo.module_metric me WITH(NOLOCK)
                ) AS me
        ON      me.id = mu.metricid
        WHERE   mu.dy >= '1/1/2017'
                --AND mu.dy <= <Parameters.enddate>
                AND me.metrickey IN
                       (
                        'contacts-client_active_count_day_org',
                        'contacts-employee_active_count_day_org',
                        'contacts-forms_submission_client_count_day_org',
                        'contacts-forms_submission_employee_count_day_org',
                        'contacts-employee_login_percent_day_org',
                        'contacts-forms_active_count_day_org',
                        'claims-claim_created_count_day_org',
                        'claims-claim_sent_count_day_org',
                        'claims-claim_payment_count_day_org',
                        'scheduling-event_count_day_org'
                        )
        GROUP BY
               mu.organizationid,
               DATEPART(MONTH, mu.dy),
               DATEPART(YEAR, mu.dy)
               )metrics
ON      metrics.organizationid  = cog.clientorganizationid
        AND metrics.MONTH = DATEPART(MONTH,be.timeworkedfrom)
        AND metrics.YEAR = DATEPART(YEAR,be.timeworkedfrom)

LEFT JOIN
        (
        SELECT  cl.ContactId,
                MAX(CASE WHEN clo.parentid = 993 THEN clo.name END) AS industry
        FROM    dbo.label_entities cl WITH(NOLOCK)
        JOIN    dbo.label_history clo WITH(NOEXPAND NOLOCK)
        ON      clo.id = cl.labelid
        WHERE   clo.parentid IN (993)
        GROUP BY
                cl.id
        ) labelData
ON      be.clientid = labelData.id

WHERE           be.timeworkedfrom >= '1/1/2017'

GROUP BY        cog.clientorganizationid,
                be.clientid,
                be.providerid,
                DATEPART(YEAR,be.timeworkedfrom),
                DATEPART(MONTH,be.timeworkedfrom),
                be.procedurecodestring,
                cc.fullname,
                cc2.fullname,
                labelData.Industry