May 6, 2010 at 2:42 pm
select
Distinct ClientIdentifier, sourcesystem, methodtype, voicedatatype, Count(*) as ErrorCount
from
[logsserver].[uCSRLogs].[Logs].[tNACRTransaction] WITH(NOLOCK)
where
convert(char(8), dtmdatecreated, 112) = convert(char(8), GETDATE()-1, 112)
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
can u pls tell me this query give me result of getdate()...
i want for today(getdate), getdate()-1 and total count from yesterday-7....
i want 2 more columns needed for getdate()-1 and yesterday-7
May 6, 2010 at 2:48 pm
Slightly confused, exactly what are you looking for here, a count of records for today, yesterday, and the past 7 days excluding today?
May 6, 2010 at 2:53 pm
EG.. i want error count for today mean wed,tue mean yesterday and total errorcount from wed(today) till last wed...
May 6, 2010 at 3:26 pm
Still confused, so here is some code. Work with it and see if it solves your problem. You may need to tweak it some, you didn't provide anything to allow us to test our code or results against.
For help on asking for help, please read the first article I reference below in my signature block. Follow the instructions in that article, plus provide the expected results as well, and you will get people tripping over themselves to help AND get tested code in return.
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;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply