How do I calculate the difference between two line items?

Hot Topic

Multi-valued Parameter
Broken hyperlink in subscription emails
Best Practice - Lookup or SQL from Variable?
Using UDL files as File Name
HOWTO: Script object as execute?
Transmission Queue problem
Retrieving rdl from Server
From PL/SQL to SQL Server
Can not Subscribe a Report
programmatically set the paper size and and paper layout

SQL Server

Only apply border to left and right of stacked column bar chart
Can't connect w/Visual Studio 2005
Using Message Queues in stored procedures
Can't see stored proc results
Grant All function giving errors
Hidden objects causes additional pages in pdf
SQL Server 2005 SP1 issues
VS 2005 Report Preview Toolbar different from RS 2005 report Manager preview
Upgrade Desktop Engine (MSDE 2000) to standard edition 2005
Management Studio Error

How do I calculate the difference between two line items?

I am trying to calculate the difference between two date/times as they relate to two separate errors that have occurred on a production line. The available date fields are as follows:

Error Date

First Error Date

Last Error Date

First Repair Date

Last Repair Date

1

4/17/2006 08:23:29 AM

4/17/2006 08:23:29 AM

4/17/2006 08:23:29 AM

4/17/2006 08:23:37 AM

4/17/2006 08:23:37 AM

2

4/17/2006 08:34:56 AM

4/17/2006 08:34:56 AM

4/17/2006 08:34:56 AM

4/17/2006 08:35:43 AM

4/17/2006 08:35:43 AM

I need to calculate the delta from 4/172006 08:23:29 AM and 4/17/2006 08:34:56 AM. Any combination of dates yields the delta within the line item, not between two different line items.

The goal is to be able to calculate the mean time between failure or frequency of a failure.

In summary, how can I calculate the delta between two distinct events I really need help with this. Thank you.

Answer this question

How do I calculate the difference between two line items?

  • PLechner

    Thank you. I tried you suggestion, but it didn't work. I also checked help, it appears that "Previous" is not a valid command in Report Writer. Thanks again. Any other ideas

  • bensterdev

    Can you provide more info on what didn't work I'm using SQL 2005 RS and it works for me:

    I created a report with data set:

    select 1 as a, 1 as b
    union
    select 2 as a, 2 as b
    union
    select 3 as a, 3 as b

    Then I added a table with the following expressions in two columns:

    a b
    =Previous(Fields!a.Value) + Fields!a.Value =Fields!b.Value

    And I got this output:

    a b
    1 1
    3 2
    5 3

    I'm just adding the values, but it should be just the same for you just subtract, create a column for "delta" then use the Avg (average) function in a grouping to determine the average...

    Hope that helps,

    -Lukasz



  • Praj1

    See if this helps:

    Previous function: http://msdn2.microsoft.com/en-us/library/ms156372.aspx

    DateDiff: http://msdn2.microsoft.com/en-us/library/b5xbyt6f.aspx

    For example,
    =DateDiff(DateInterval.Seconds,Previous(Fields!MyDate.Value),Fields!MyDate.Value))

    ---------------------------------------------------------------------------
    Download the latest Books Online update:
    http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

    This posting is provided "AS IS" with no warranties, and confers no rights.



  • How do I calculate the difference between two line items?
Answer this question