May 3, 2016 at 11:51 pm
Table Name : message_queue_details
Field Name : ID, MobileNumber, SubmitDate, MsgStatus, MsgID
Table Name : Message_Identifier
Field Name : MsgID, TotalCount
Requirement: I have 100 records in the table for the MsgID 111, I need to calculate time taken to process 80% of records. That will be the difference between SubmitDate for the 1st record and 80th record.
Issue : In the below query MIN(DMQD.SubmitDate) takes the first record but MAX(DMQD.SubmitDate) takes the 100th record which is wrong.
select TOP (select ((MD.TotalCount*80)/100) as TotalCount from [dbo].[Message_Identifier] MD where MD.MessageID = 1141)
DATEDIFF(SECOND,MIN(DMQD.SubmitDate), MAX(DMQD.SubmitDate)) as TimeDiff
from [dbo]. message_queue_details
DMM DMM.MessageID = 1141
Can anyone help me to provide a single query because this i need to put it in different query for reporting
May 4, 2016 at 12:05 am
it would be very helpful if you could provide some sample data and your desired output.
May 4, 2016 at 12:16 am
Maybe this query?
;with cte as (
select TOP (select ((MD.TotalCount*80)/100) as TotalCount from [dbo].[Message_Identifier] MD where MD.MessageID = 1141)
DMQD.SubmitDate
from [dbo]. message_queue_details
DMM DMM.MessageID = 1141
order by SubmitDate
)
select
DATEDIFF(SECOND,MIN(SubmitDate), MAX(SubmitDate)) as TimeDiff
from cte
I think, you need to use order by (in original query too).
May 4, 2016 at 12:18 am
Sure, Thanks for your response
Table : message_queue_details
ID MobileNumberMsgStatusSubmitDate MsgID
160503011378 919746922555 D2016-05-03 12:27:31.8701141
160503011379 919746922556D2016-05-03 12:27:33.8731141
160503011380 919746922556D2016-05-03 12:27:34.8731141
160503011381 919746922556D2016-05-03 12:27:38.8771141
160503011382 919746922556D2016-05-03 12:27:38.8771141
160503012587 919746922556D2016-05-03 12:27:40.6201141
160503012588 919746922556D2016-05-03 12:27:41.6231141
160503012589 919746922556D2016-05-03 12:27:42.6231141
160503012576 919746922556D2016-05-03 12:27:43.6231141
Table : Message_Identifier
MsgID TotalCount
1141 10
By considering the above example, for the 80% records processing time would be the difference of 1st record and 8th record thats 11 seconds
May 4, 2016 at 12:21 am
Thank you so much salliven
Its worked,
May 4, 2016 at 8:10 am
salliven (5/4/2016)
Maybe this query?
;with cte as (select TOP (select ((MD.TotalCount*80)/100) as TotalCount from [dbo].[Message_Identifier] MD where MD.MessageID = 1141)
DMQD.SubmitDate
from [dbo]. message_queue_details
DMM DMM.MessageID = 1141
order by SubmitDate
)
select
DATEDIFF(SECOND,MIN(SubmitDate), MAX(SubmitDate)) as TimeDiff
from cte
I think, you need to use order by (in original query too).
The TOP clause takes an optional PERCENT keyword, so you could write this as
;with cte as (
select TOP (80) PERCENT
DMQD.SubmitDate
from [dbo]. message_queue_details
DMM DMM.MessageID = 1141
order by SubmitDate
)
select
DATEDIFF(SECOND,MIN(SubmitDate), MAX(SubmitDate)) as TimeDiff
from cte
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply