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.
| Author |
Topic |
|
Paullys50
Starting Member
2 Posts |
 Posted - 2012-12-09 : 11:21:54
|
| 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 TimeRow 2 Data 1, Row 2 Start Time, Row 2 End TimeRow 3 Data 1, Row 3 Start Time, Row 3 End Time...Row n Data 1, Row n Start Time, Row n End TimeThe 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:[url]http://sqlserverbuddy.blogspot.com/2011/06/hour-minute-second-between-two-date.html[/url]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 DurationRow 2 Data 1, Row 2 Start Time, Row 2 End Time, Row 2 DurationRow 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 durationAm 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-10 : 07:43:20
|
If you are on SQL 2008, you can use the following to calculate the difference as hours:minutes:seconds:milliseconds.-- Sample datadeclare @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. |
 |
|
|
Paullys50
Starting Member
2 Posts |
Posted - 2012-12-10 : 11:06:07
|
| Never mind, figured it out use an expression in Reporting Services, thanks for looking! |
|
|
|
|
|
|
|
|
SQL - Generate Date Difference (Reporting or SQL)