SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL - Generate Date Difference (Reporting or SQL)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Paullys50
Starting Member

2 Posts

Posted - 12/09/2012 :  11:21:54  Show Profile  Reply with Quote
Hello all, quick background:

I work in the world of industrial automation, over the past few years I've gotten more and more familiar with Microsoft SQL and Microsoft Reporting Services as we are starting to use these tools for our industrial automation solutions. I've gone to basic SQL classes and I don't work with it enough to be proficient. When I do work with it, I Google a lot and experiment until I get my desired results. I am certainly more of a "Hack" then a seasoned pro, hence my asking for some help.

Currently I am struggling to figure out how to show a time "duration" of a process. I collect data for various plant processes and generate various reports from that data. The current report I am working on summarizes plant activities, from the summary report a user can click on the data results for details of a specific process. The data query for the summary report returns process data as well as a "start time" and an "end time" for each process. A user can input start date and end date parameters to manage the report results. The result looks like this:

Process Summary:
Row 1 Data 1, Row 1 Start Time, Row 1 End Time
Row 2 Data 1, Row 2 Start Time, Row 2 End Time
Row 3 Data 1, Row 3 Start Time, Row 3 End Time
.
.
.
Row n Data 1, Row n Start Time, Row n End Time

The report uses a stored procedure with a simple SELECT * query to generate the data set. In the report I just choose the data I wish to display (all column data in the query table is available). The table where the data resides only contains start and end times. So I need to calculate the time difference for a "duration".

I have tried to calculate this within the report itself by using an expression. I've yet to find a solution, and the research I have done makes it seem that it takes a lot of work to achieve a solution.

I am looking for an HH:MM:SS format.

I did come across a function that would generate exactly what I need:

http://sqlserverbuddy.blogspot.com/2011/06/hour-minute-second-between-two-date.html

But I am not sure how I could use it with my existing stored procedure that I have which already queries the data. Is it possible to pass the individual row results of start time and end time to this function and return the "duration" and have it available within the report? Within the same dataset? Part of me feels like I need to generate a temporary table with the process data, then run the function as a secondary action to populate a "duration" column. Then my report dataset would contain all the column data I require as it would now reference the temporary table.

My desired results would be:

Process Summary:
Row 1 Data 1, Row 1 Start Time, Row 1 End Time, Row 1 Duration
Row 2 Data 1, Row 2 Start Time, Row 2 End Time, Row 2 Duration
Row 3 Data 1, Row 3 Start Time, Row 3 End Time, Row 3 Duration
.
.
.
Row n Data 1, Row n Start Time, Row n End Time, Row n duration

Am I on the right track? Or do I need to re-consider how I am doing everything? Right now calculating on the "insert" of the data into the database is not an option as the software that populates the tables are a 3rd party, and I don't want to manipulate the database in fear of how that may or may not affect the 3rd part software.

Appreciate any feedback! Thanks!

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/10/2012 :  07:43:20  Show Profile  Reply with Quote
If you are on SQL 2008, you can use the following to calculate the difference as hours:minutes:seconds:milliseconds.
-- Sample data
declare @dt1 datetime, @dt2 datetime;
set @dt1 = '2012-12-08 01:40:47.297';
set @dt2 = getdate();

-- If you can guarantee that the difference is always less than 24 hours, you can simply
-- use the following.
select cast(@dt2-@dt1 as time);

-- If there is possibility that the difference will be more than 24 hours, use the following
-- it converts the days to hours and adds up.
select stuff(cast(cast(@dt2-@dt1 as time) as varchar(32)),1,2,datediff(hh,'19000101',@dt2-@dt1));
BTW, this is in T-SQL, not in SSRS.
Go to Top of Page

Paullys50
Starting Member

2 Posts

Posted - 12/10/2012 :  11:06:07  Show Profile  Reply with Quote
Never mind, figured it out use an expression in Reporting Services, thanks for looking!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000