Combine time for out put resuls

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 2005 Forums

Transact-SQL (2005)

Combine time for out put resuls

Author

Topic

danjapro
Starting Member

44 Posts

Posted - 2007-05-17 : 11:05:15

Or this is the correct method: I also want to have a time difference between a startTime an End time.
Start time = dateadn time the test was started
End time = datand time the test ended

I have colum called Elapse time which should be the time difference between start adn finish. How do I write that in.

So could my SQL statment look as such:

Select .....
CASE
WHEN dbo.TestRun.Result = 'Completed' THEN 'Passed'
WHEN dbo.TestRun.Result = 'Failed' THEN 'failed'
WHEN db0.testrun.result = '' THEN 'debatable'
END

CASE WHEN dbo.TestRun.Testtype= 'Unknown' THEN 'TestOne'
WHEN dbo.TestRun.Result = <> '' THEN 'APP'
WHEN db0.testrun.result = '' THEN 'Invalid'
END
From dbo.TestResults
inner join Testrun on dbo.testresults.testid = dbo.testrun.testid
left outer join database2.dbo.Testproduct on dbo.testresults.Testrunid = database2.dbo.testproduct.Runid
Where

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-17 : 11:07:56

Make use of DATEDIFF() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danjapro
Starting Member

44 Posts

Posted - 2007-05-17 : 11:39:30

How to do that: fix this one for me, or help me please.

'Elapsetime' as 'ElapseTime',
sum(case when datediff(day,dbo.Testrun.StartDate,dbo.Testrun.End Date))
end

Is that correct in anywat

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-17 : 12:03:08

Why CASE?

sum(datediff(day,dbo.Testrun.StartDate,dbo.Testrun.End Date))

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-17 : 12:07:56

Advice: don't delimit your column names with ' , either don't delimit them at all or use [ ]. Using ' makes your code harder to read and tougher to distinguish string literals from column names since they look exactly the same.

- Jeff
http://weblogs.sqlteam.com/JeffS

danjapro
Starting Member

44 Posts

Posted - 2007-05-17 : 13:11:34

Would I place it like this: woruld this be correct syntax.
SELECT dbo.TestResult.RunId, dbo.TestResult.TestName AS Project,
dbo.TestResult.IsRunnable,
dbo.TestResult.Owner, dbo.TestResult.CategoryFullName, dbo.TestResult.CategoryId, dbo.TestResult.StartTime, dbo.TestResult.EndTime As ElaspedTime
sum(datediff(day,dbo.Testrun.StartDate,dbo.Testrun.End Date))'
From

danjapro
Starting Member

44 Posts

Posted - 2007-05-17 : 13:13:39

Or is this correct

SELECT dbo.TestResult.RunId, dbo.TestResult.TestName AS Project,
dbo.TestResult.IsRunnable,
dbo.TestResult.Owner, dbo.TestResult.CategoryFullName, dbo.TestResult.CategoryId, dbo.TestResult.StartTime, dbo.TestResult.EndTime,
sum(datediff(day,dbo.Testrun.StartDate,dbo.Testrun.End Date)) as Elaspedtime,

danjapro
Starting Member

44 Posts

Posted - 2007-05-17 : 13:59:32

MY SQL query works good with the Status case and the TestType case.
But I cannot get the Elaspedtime To work. I need to get the difference in Time between StartTime and EndTime.
My code below = [ElaspedTime] = sum(datediff(day,dbo.TestResult.StartTime,dbo.TestResult.EndTime))

End,

Give unable to parse Error in SQL



                       

SELECT                dbo.TestResult.RunId,  dbo.TestResult.TestName AS Project,  
                      dbo.TestResult.IsRunnable, 
                      dbo.TestResult.Owner, dbo.TestResult.CategoryFullName, dbo.TestResult.CategoryId, dbo.TestResult.StartTime, dbo.TestResult.EndTime, 
                      dbo.TestResult.SubTestCount, dbo.TestResult.SubTestPass, dbo.TestRun.Name, dbo.TestRun.FileStorage, dbo.TestRun.TestsTotal, 
                      dbo.TestRun.TestsExecuted, dbo.TestRun.TestsPassed, dbo.TestRun.TestsCompleted, dbo.TestRun.TestsFailed, dbo.TestRun.TestsNotRun, 
                      dbo.TestRun.TestsInconclusive, dbo.TestRun.TestsAborted, dbo.TestRun.RunUser, dbo.TestRun.PublishUser, dbo.TestRun.DateCreated, 
                      dbo.TestRun.DateQueued, dbo.TestRun.DateStarted, dbo.TestRun.DateFinished, dbo.TestRun.RunHasCoverage, dbo.TestRun.RunPassed, 
                      dbo.TestRun.BuildUri, dbo.TestRun.TeamProject, 
                      NSEQAEngine.dbo.TestProducts.Product AS Product, 
                      NSEQAEngine.dbo.TestProducts.Script  AS Script,
                      NSEQAEngine.dbo.TestProducts.Component,
                      NSEQAEngine.dbo.TestProducts.Description, 
                      NSEQAEngine.dbo.TestProducts.Responsible, 
                      NSEQAEngine.dbo.TestProducts.ResponsibleEmail,
                      NSEQAEngine.dbo.TestProducts.Manual,
                      NSEQAEngine.dbo.TestProducts.Comment,
                       
                        [ElaspedTime] = sum(datediff(day,dbo.TestResult.StartTime,dbo.TestResult.EndTime))
                       
                        End,
                        
			[Status] = CASE  dbo.TestResult.TestOutcome 
			                    WHEN 'Completed'  THEN 'Passed' 
			                    WHEN 'Failed'     THEN 'Failed' 
			                    WHEN 'Incomplete' THEN 'Debatable' 
			ELSE 'Debatable' 
			                END, 
			                
			                
			[TestType] = CASE  dbo.TestResult.Testtype 
			                    WHEN 'Unknown'  THEN 'TestComplete' 
			                    WHEN 'other'    THEN 'NSE QA Portal Test Type' 
			                    When ''         Then 'Invalid'
			ELSE '@testType' 
                                       END                 
  
FROM                  dbo.TestResult 
                      INNER JOIN
                      dbo.TestRun ON dbo.TestResult.RunId = dbo.TestRun.RunId LEFT OUTER JOIN
                      NSEQAEngine.dbo.TestProducts ON dbo.TestResult.RunId = NSEQAEngine.dbo.TestProducts.RunID
WHERE                (NSEQAEngine.dbo.TestProducts.Component <> '')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-17 : 14:28:43

Do you really SUM()?
Either remove the SUM or add a GROUP BY clause.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danjapro
Starting Member

44 Posts

Posted - 2007-05-17 : 14:43:44

How to add the group by clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources