May 3, 2011 at 6:06 am
Hi,
i need TO calculate time difference base on defectid and ap_old_value, when defectid is same then difference between defectlogtime by row wise.
for eg.. if defect id is 4 and ap_old_value is different then i want result set
defect_id,ap_old_value,defectlogtime difference.
Note: most of time difference between previous and next row only
CREATE TABLE [dbo].[temp](
[Defect_ID] [int] NULL,
[DefectLogTime] [datetime] NULL,
[AP_NEW_VALUE] [nvarchar](2000) NULL,
[AP_OLD_VALUE] [nvarchar](2000) NULL
) ON [PRIMARY]
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 7 2011 2:28PM','acc_csupport','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 7 2011 2:50PM','uk_isupport','acc_csupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 7 2011 3:16PM','hew_hpintegrationteam','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 7 2011 4:29PM','uk_isupport','hew_hpintegrationteam')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 7 2011 4:49PM','acc_ydev','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 8 2011 1:28PM','uk_isupport','acc_ydev')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 8 2011 2:38PM','hew_hpintegrationteam','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 8 2011 3:27PM','uk_isupport','hew_hpintegrationteam')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 8 2011 6:16PM','hew_hpintegrationteam','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 8 2011 6:24PM','uk_isupport','hew_hpintegrationteam')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 8 2011 6:48PM','acc_ydev','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 16 2011 6:49PM','acc_csupport','acc_ydev')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 17 2011 9:18AM','uk_isupport','acc_csupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('4','Mar 17 2011 9:49AM','acc_ydev','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('7','Mar 14 2011 11:36AM','acc_csupport','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('7','Mar 14 2011 11:45AM','uk_isupport','acc_csupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('7','Mar 14 2011 1:36PM','acc_ydev','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('7','Mar 16 2011 6:50PM','acc_csupport','acc_ydev')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('7','Mar 17 2011 9:10AM','uk_isupport','acc_csupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('7','Mar 17 2011 9:49AM','acc_ydev','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('8','Mar 15 2011 2:13PM','acc_csupport','uk_isupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('8','Mar 16 2011 8:40AM','uk_isupport','acc_csupport')
INSERT INTO TEMP ([Defect_ID],[DefectLogTime],[AP_NEW_VALUE],[AP_OLD_VALUE]) VALUES ('8','Mar 16 2011 8:41AM','acc_ydev','uk_isupport')
May 3, 2011 at 6:30 am
30 people watching this thread and no answer... not normal.
can you post the exact expected answer from your sample data?
Also how much data to do you have in that table (# rows)?
What's the max amount of rows you think you'll be supporting?.. there's easy solution and then there's fast.
What indexes do you have too?
May 3, 2011 at 11:09 am
Thanks for your reply,
rows > 10000
my requirement is very simple i need difference between each log time in minute.
when id is same and name is different then i need difference between time
for eg...
id datetime name difference
1 1/1/2011 11:50:123 a 00:000
1 1/1/2011 12:10:123 b 20:000
May 3, 2011 at 11:19 am
I don't have time to do it now but here's the gist of it.
SELECT MIN(t2.time), t1.time, T1.Col1, T1.Col2 FROM Base T1 INNER JOIN Base T2 ON T1.Col1 = T2.Col1 AND T1.Col2 = T2.COl2 AND T2.Time > T1.Time GROUP BY t1.time, T1.Col1, T1.Col2
Once you have that you can join a 3rd time to get the rest of the columns.
Datediff will get you the difference.
May 3, 2011 at 9:22 pm
Thanx a lot , it will help me.
my requirement is bit different but your query gave me idea how to reach that
once again thank you so much for your time 😀 🙂
May 3, 2011 at 9:26 pm
latitiacasta (5/3/2011)
Thanx a lot , it will help me.my requirement is bit different but your query gave me idea how to reach that
once again thank you so much for your time 😀 🙂
Cool, let me know if you need help tuning this. That kind of query gets expansive, really expansive and with surprisingly little rows to process.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply