FInd out diff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

FInd out diff

Author

Topic

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-23 : 17:39:58

Hi All

I have requirement where I need to find out the diff of the time_taken between to date for a particular job .Please find my below query which I have written to find the time taken by the jobs
for every day . Now I want to get that how many minutes differ the job from previous to next date ..I hope I clear with my question :

Here is the Query :

DECLARE @COUNTER INT,@MAX INT

SET @COUNTER = 0
SET @MAX = 3

CREATE TABLE #TEMP(Source VARCHAR(30),RunDate varchar(20), TimeTakenInMinutes INT)
WHILE (@COUNTER <= @MAX)
BEGIN

INSERT INTO #TEMP(Source,RunDate,TimeTakenInMinutes) select
'EnterpriseJPFactEnroll' AS SOURCE , convert (varchar(20),GETDATE()-@COUNTER,101) AS dATE,
datediff(MINUTE, (select LogDateTime from SSIS..LogDetail where MessageID ='JEDI-0201'
and DATEDIFF(DD,LogDateTime,GETDATE())=@COUNTER ),(select LogDateTime from SSIS..LogDetail
where MessageID ='JEDI-0232'
and DATEDIFF(DD,LogDateTime,GETDATE())=@COUNTER ))AS MINUTE

SET @COUNTER = @COUNTER + 1
END

SELECT * FROM #TEMP
DROP TABLE #TEMP

My OutPut:

Source RunDate TimeTakenInMinutes
EnterpriseJPFactEnroll 10/24/2011 NULL
EnterpriseJPFactEnroll 10/23/2011 88
EnterpriseJPFactEnroll 10/22/2011 68
EnterpriseJPFactEnroll 10/21/2011 74

I want extra colum added Diff and output mentioned in that.I just manully calulated the diff and populated the columns..

Source RunDate TimeTakenInMinutes Diff
EnterpriseJPFactEnroll 10/24/2011 NULL Null
EnterpriseJPFactEnroll 10/23/2011 88 20
EnterpriseJPFactEnroll 10/22/2011 68 -6
EnterpriseJPFactEnroll 10/21/2011 74 0

Suggestion and Solutions Both are welcome

Thanks
VIjay Sahu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-24 : 04:14:26

just add below after insert


ALTER TABLE #TEMP ADD DIff Int NULL

UPDATE t
SET t.DIff =t1.TimeTakenInMinutes 
FROM #Temp t
OUTER APPLY (SELECT TOP 1 TimeTakenInMinutes 
             FROM #Temp
             WHERE Source = t.Source 
             AND RunDate > t.RunDate 
             ORDER BY RunDate ASC) t1

SELECT * FROM #TEMP
DROP TABLE #TEMP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-10-24 : 04:42:44

Try this:


WITH CTE AS
(
   SELECT T.Source, T.RunDate, T.TimeTakenInMinutes,
          ROW_NUMBER() OVER(PARTITION BY T.Source ORDER BY CONVERT(DATE, T.RunDate, 101)) AS row_num
     FROM #TEMP AS T
)
SELECT T.Source, T.RunDate, T.TimeTakenInMinutes,
       T.TimeTakenInMinutes - ISNULL(P.TimeTakenInMinutes, T.TimeTakenInMinutes) AS Diff
  FROM CTE AS T
       LEFT OUTER JOIN
       CTE AS P
       ON P.Source = T.Source
          AND P.row_num = T.row_num - 1

And it is better to change RunDate datatype from VARCHAR(20) to DATE. This way you will eliminate the chance of bad data, and get better performance.

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-24 : 16:56:17

quote:
Originally posted by visakh16

just add below after insert

ALTER TABLE #TEMP ADD DIff Int NULL

UPDATE t
SET t.DIff =t1.TimeTakenInMinutes
FROM #Temp t
OUTER APPLY (SELECT TOP 1 TimeTakenInMinutes
FROM #Temp
WHERE Source = t.Source
AND RunDate > t.RunDate
ORDER BY RunDate ASC) t1

SELECT * FROM #TEMP
DROP TABLE #TEMP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi Vikas

Thanks for your effort on my problem ..But It does not give output What i need . After your query Output is coming like this

Source RunDate TimeTakenInMinutes DIff
EnterpriseJPFactEnroll 10/25/2011 NULL NULL
EnterpriseJPFactEnroll 10/24/2011 71 NULL
EnterpriseJPFactEnroll 10/23/2011 88 71
EnterpriseJPFactEnroll 10/22/2011 68 88

and I want ouput like below

Source RunDate TimeTakenInMinutes DIff
EnterpriseJPFactEnroll 10/25/2011 NULL NULL
EnterpriseJPFactEnroll 10/24/2011 71 -17
EnterpriseJPFactEnroll 10/23/2011 88 20
EnterpriseJPFactEnroll 10/22/2011 68 0

ANyways I got the reply from malpashaa on this and he has used CTE to implement this and desired output came.
Can you please try to modify your query for the desire output so that I may get different ideas on this problem

Thanks For your support..

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-24 : 16:57:54

quote:

Originally posted by malpashaa

Try this:


WITH CTE AS
(
   SELECT T.Source, T.RunDate, T.TimeTakenInMinutes,
          ROW_NUMBER() OVER(PARTITION BY T.Source ORDER BY CONVERT(DATE, T.RunDate, 101)) AS row_num
     FROM #TEMP AS T
)
SELECT T.Source, T.RunDate, T.TimeTakenInMinutes,
       T.TimeTakenInMinutes - ISNULL(P.TimeTakenInMinutes, T.TimeTakenInMinutes) AS Diff
  FROM CTE AS T
       LEFT OUTER JOIN
       CTE AS P
       ON P.Source = T.Source
          AND P.row_num = T.row_num - 1

And it is better to change RunDate datatype from VARCHAR(20) to DATE. This way you will eliminate the chance of bad data, and get better performance.

Thanks malpashaa your query works exactlty what I needed..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:32:20

sorry it should have been


ALTER TABLE #TEMP ADD DIff Int NULL

UPDATE t
SET t.DIff =t.TimeTakenInMinutes -t1.TimeTakenInMinutes 
FROM #Temp t
OUTER APPLY (SELECT TOP 1 TimeTakenInMinutes 
             FROM #Temp
             WHERE Source = t.Source 
             AND RunDate > t.RunDate 
             ORDER BY RunDate ASC) t1

SELECT * FROM #TEMP
DROP TABLE #TEMP

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2011-10-25 : 19:51:07

Thanks Vikas it works...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:42:53

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources