May 7, 2010 at 10:10 am
select Distinct ClientIdentifier, sourcesystem, methodtype, voicedatatype
,Count(CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10), dtmdatecreated ,101))
= CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(),101)) THEN 1 END ) as TodayErrorCount
,Count(CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10), dtmdatecreated ,101))
= CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE()-1,101)) THEN 1 END ) as YesterdayErrorCount
,Count(CASE WHEN dtmdatecreated < GETDATE()-2 THEN 1 END ) as LastWeekErrorCount
from [logsserver].[uCSRLogs].[Logs].[tNACRTransaction] WITH(NOLOCK)
where convert(char(8), dtmdatecreated, 112) >= convert(char(8), GETDATE()-7, 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
in this query i need to add extracolumn contain % value..
May 7, 2010 at 10:16 am
Percent of what?
Also, you may want to change this:
where convert(char(8), dtmdatecreated, 112) >= convert(char(8), GETDATE()-7, 112)
to this:
where
dtmdatecreated >= dateadd(dd, -7, dateadd(dd, datediff(dd, cast('19000101' as datetime), getdate()), cast('19000101' as datetime)))
This will all SQL Server to us an index on dtmdatecreated. Also, it isn't necessary to strip the time portion off dtmdatecreated as you are using >= in the query.
May 7, 2010 at 10:20 am
% of errorcount...so need 3 more column...
May 7, 2010 at 10:28 am
san-1015392 (5/7/2010)
% of errorcount...so need 3 more column...
Doesn't answer the question really. How is this supposed to be computed? If you have included this in the query above, it is hard to tell since it isn't formatted very well.
It would also help if you could post the the DDL for the table(s) (CREATE TABLE statements), sample data (as a series of INSERT INTO tablename statements) for the table(s) involved, and the expected results of the query for testing purposes.
Doing this, people will trip over themselves to help you with your problem and you get the added benefit of getting TESTED code in return.
Without that, anything provided is simply best guesses as to what you need.
May 7, 2010 at 11:31 am
This are table script and sample data..pls run this and then run the query given b4...
I want to modify query to get result set of % of createaccount, %of terminateaccount,and % of updateaccount in each errorcount.. so might be 3 more column…can u help me to this query
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Logs].[tNACRTransaction]') AND type in (N'U'))
BEGIN
CREATE TABLE [Logs].[tNACRTransaction](
[tNACRTransactionID] [bigint] NOT NULL,
[TransactionID] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[MethodType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ClientIdentifier] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SourceSystem] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ExternalAccountIdentifier] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CAN] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BTN] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrderStatus] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EffectiveBillDate] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[VoiceDataType] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StateCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AttributeList] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MessageType] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OutputResponse] [varchar](2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dtmDateCreated] [datetime] NOT NULL
)
END
GO
INSERT [Logs].[tNACRTransaction] ([tNACRTransactionID], [TransactionID], [MethodType], [ClientIdentifier], [SourceSystem], [ExternalAccountIdentifier], [CAN], [BTN], [OrderStatus], [EffectiveBillDate], [VoiceDataType], [StateCode], [AttributeList], [MessageType], [OutputResponse], [dtmDateCreated]) VALUES (1, N'2bc77415-2ce6-44f2-9a48-c0bce0514fc3', N'TerminateAccount', N'VOSE', N'', N'2872321153', N' ', N' ', N'A', N'20100430', N'V', N' ', N'', N'INPUT', N'', CAST(0x00009D6900010854 AS DateTime))
INSERT [Logs].[tNACRTransaction] ([tNACRTransactionID], [TransactionID], [MethodType], [ClientIdentifier], [SourceSystem], [ExternalAccountIdentifier], [CAN], [BTN], [OrderStatus], [EffectiveBillDate], [VoiceDataType], [StateCode], [AttributeList], [MessageType], [OutputResponse], [dtmDateCreated]) VALUES (2, N'2bc77415-2ce6-44f2-9a48-c0bce0514fc3', N'TerminateAccount', N'VOSE', N'', N'2872321153', N' ', N' ', N'A', N'20100430', N'V', N' ', N'', N'RESPOF', N'<Response><message><messageNumber>D356356</messageNumber><messageText>EXTERNAL ACCOUNT ID NOT FOUND ON TABLE CBST0021</messageText></message></Response>', CAST(0x00009D690001085E AS DateTime))
INSERT [Logs].[tNACRTransaction] ([tNACRTransactionID], [TransactionID], [MethodType], [ClientIdentifier], [SourceSystem], [ExternalAccountIdentifier], [CAN], [BTN], [OrderStatus], [EffectiveBillDate], [VoiceDataType], [StateCode], [AttributeList], [MessageType], [OutputResponse], [dtmDateCreated]) VALUES (3, N'e21c4423-a21b-494e-84c3-7372c65f4e4c', N'AssignCAN', N' ', N' ', N'', N' ', N' ', N' ', N' ', N' ', N' ', N'1', N'INPUT', N'', CAST(0x00009D6900012CA2 AS DateTime))
INSERT [Logs].[tNACRTransaction] ([tNACRTransactionID], [TransactionID], [MethodType], [ClientIdentifier], [SourceSystem], [ExternalAccountIdentifier], [CAN], [BTN], [OrderStatus], [EffectiveBillDate], [VoiceDataType], [StateCode], [AttributeList], [MessageType], [OutputResponse], [dtmDateCreated]) VALUES (4, N'e21c4423-a21b-494e-84c3-7372c65f4e4c', N'AssignCAN', N'', N'', N'', N' ', N' ', N' ', N' ', N' ', N' ', N'', N'RESPOS', N'<Response><message><messageNumber>D000000</messageNumber><messageText>NO ERRORS</messageText></message><results><resultValue>A118012171</resultValue></results></Response>', CAST(0x00009D6900012CA3 AS DateTime))
INSERT [Logs].[tNACRTransaction] ([tNACRTransactionID], [TransactionID], [MethodType], [ClientIdentifier], [SourceSystem], [ExternalAccountIdentifier], [CAN], [BTN], [OrderStatus], [EffectiveBillDate], [VoiceDataType], [StateCode], [AttributeList], [MessageType], [OutputResponse], [dtmDateCreated]) VALUES (5, N'48bae17e-6fca-4a82-b690-b9246ffaa1de', N'CreateAccount', N'COG', N'NS', N'4012533525910', N'A117738314', N'4012533525', N'C', N'20100501', N'V', N'RI', N'', N'INPUT', N'', CAST(0x00009D6900017E77 AS DateTime))
INSERT [Logs].[tNACRTransaction] ([tNACRTransactionID], [TransactionID], [MethodType], [ClientIdentifier], [SourceSystem], [ExternalAccountIdentifier], [CAN], [BTN], [OrderStatus], [EffectiveBillDate], [VoiceDataType], [StateCode], [AttributeList], [MessageType], [OutputResponse], [dtmDateCreated]) VALUES (6, N'48bae17e-6fca-4a82-b690-b9246ffaa1de', N'CreateAccount', N'COG', N'NS', N'4012533525910', N'A117738314', N'4012533525', N'C', N'20100501', N'V', N'RI', N'', N'RESPOF', N'<Response><message><messageNumber>S9900E8</messageNumber><messageText>Invalid column name ''OrderTrackingId''.
May 7, 2010 at 11:51 am
How is this percentage supposed to be calculated?
May 7, 2010 at 12:00 pm
% of create,update and terminate acct.. in methodtypecolumn...
did u run the create table and insert data script...
i also dont know but we need how many % create acct,updateacct,terminateacct also..based on today,yesterday and wholeweek..
May 7, 2010 at 1:24 pm
Really? Let's take a step back. This is your data, you need to show us how to perform the necessary calculations. I look at the data, and it means nothing to me, I have no way of interpreting it and knowing what needs to be done to meet your requirements.
So, how do we do this? First, you explain and show how the calculation needs to be done. Second, based on the sample data you provided, you need to show us what the expected results should look like so we have something to check our results against.
May 8, 2010 at 5:57 pm
san-1015392 (5/7/2010)
select Distinct ClientIdentifier, sourcesystem, methodtype, voicedatatype,Count(CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10), dtmdatecreated ,101))
= CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE(),101)) THEN 1 END ) as TodayErrorCount
,Count(CASE WHEN CONVERT(DATETIME,CONVERT(VARCHAR(10), dtmdatecreated ,101))
= CONVERT(DATETIME,CONVERT(VARCHAR(10), GETDATE()-1,101)) THEN 1 END ) as YesterdayErrorCount
,Count(CASE WHEN dtmdatecreated < GETDATE()-2 THEN 1 END ) as LastWeekErrorCount
from [logsserver].[uCSRLogs].[Logs].[tNACRTransaction] WITH(NOLOCK)
where convert(char(8), dtmdatecreated, 112) >= convert(char(8), GETDATE()-7, 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
in this query i need to add extracolumn contain % value..
San... there are errors in your data generation code and it's a bit date sensitive making it no longer valid. I don't have the time to fix that so I'll just make a suggestion.
1) Add an "open filter" count to the code above for each category (today, yesterday, prev week).
2) Remove the ORDER BY from the above so step 3 will work.
3) Use the code above (modified by step 1 and 2, of course) either as a CTE or a derived table to read from. That will make doing the % calculations a snap.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply