SQL: Need formula to subtract one row from another
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=23015
Printed Date: 29 Apr 2024 at 3:46am
Topic: SQL: Need formula to subtract one row from another
Posted By: DrewEC
Subject: SQL: Need formula to subtract one row from another
Date 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
|
Replies:
Posted By: kevlray
Date 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.
|
Posted By: hilfy
Date 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
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: DrewEC
Date 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.
|
Posted By: DrewEC
Date 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).
|
|