Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: SQL: Need formula to subtract one row from another Post Reply Post New Topic
Author Message
DrewEC
Newbie
Newbie


Joined: 02 Sep 2020
Online Status: Offline
Posts: 7
Quote DrewEC Replybullet Topic: SQL: Need formula to subtract one row from another
    Posted: 22 Aug 2022 at 11:16am
Need assistance with creating a "datediff" type of formula.

I have timestamps in the {incilog} table, in different rows across the same column {incilog.timestamp} that I need to subtract from one another, but only if the {incilog.descript} equals "Time Received" from the row that has the {incilog.descript} equal "Entered Nature".

Logic is: If {incilog.descript} equals "Time Received" and {incilog.descript} equals "Entered Nature", then subtract {incilog.timestamp} from {inciog.timestamp} in the corresponding row.

Basically, I need to get how many seconds it took from "Time Received" to "Entered Nature".

NOTE: I CANNOT USE THE "transtype" COLUMN, as there are other unique descript that use this same transtype

THANK YOU FOR YOUR TIME AND ASSISTANCE!

EXAMPLE OF DATA

timestamp              inci_id       transtype   descript
8/22/2022 12:14:46 AM    2022264051    TR        Time Received
8/22/2022 12:17:00 AM    2022264051    ENT        Entered Nature
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 23 Aug 2022 at 3:54am
Depending on what the database type, version (Oracle, MS-SQL, etc.).  There may be a solution.  I know with more recent versions of MS-SQL there is a function to look at the next row (or the previous row).  Crystal Report has had these functions available for years.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 23 Aug 2022 at 6:40am
How are you grouping your data? Will "Entered Nature" always be the record right after the "Time Received" record or could there be other records between them?

-Dell
IP IP Logged
DrewEC
Newbie
Newbie


Joined: 02 Sep 2020
Online Status: Offline
Posts: 7
Quote DrewEC Replybullet Posted: 23 Aug 2022 at 7:42am
You are correct in that Crystal has the previous function, but in this instance, when I tried to use the previous function, it didn't work.
IP IP Logged
DrewEC
Newbie
Newbie


Joined: 02 Sep 2020
Online Status: Offline
Posts: 7
Quote DrewEC Replybullet Posted: 23 Aug 2022 at 7:43am
It is being grouped by the "inci_id" column.

Unfortunately Entered Nature will not always be right after Time Received (other records are between them).
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.033 seconds.