select
    ClientIdentifier,
    sourcesystem,
    methodtype,
    voicedatatype,
    sum(case when dtmdatecreated >= dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime)) and
                  dtmdatecreated <  dateadd(dd, 1, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime)))
             then 1
             else 0
        end) ErrorCountToday,
    sum(case when dtmdatecreated >= dateadd(dd, -1, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime))) and
                  dtmdatecreated <  dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime))
             then 1
             else 0
        end) ErrorCountYesterday,
    sum(case when dtmdatecreated >= dateadd(dd, -1, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime))) and
                  dtmdatecreated <  dateadd(dd, -7, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime)))
             then 1
             else 0
        end) ErrorCountLast7Days
from
    [logsserver].[uCSRLogs].[Logs].[tNACRTransaction] WITH(NOLOCK)
where
    dtmdatecreated >= dateadd(dd, -7, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime)))
    and dtmdatecreated < dateadd(dd, 1, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate), cast('19000101' as datetime)))
    and messagetype like 'RESP%'
    and outputResponse not like '%D000000%' 
    and methodtype <> 'UpdateAccountTerm'
    and ClientIdentifier in ('COG', 'COG_WB', 'COPPERNET', 'COFEE','NCOG', 'NSOP', 'LSOP', 'SSP', 'SSP_DL', 'VOSE', 'UCSR', 'UCSR-ORDER')
group by
    ClientIdentifier,
    sourcesystem,
    methodtype,
    VoiceDataType
order by
    ClientIdentifier,
    methodtype;