| Author |
Message |
Tupacmoche
Groupie
Joined: 04 Apr 2007
Online Status: Offline
Posts: 52
|

Posted: 29 Jan 2014 at 9:22am |
|
I think, I'm following and understanding you better now. From your comments, I guess you can use PREVIOUS() to look back at not just the prior row (what I assumed) but group or other logical set. With that understanding in mind here is my answer. After, all the tables are joined together and the filter is applied for any given Division lets say Division 45 and cycle 17 you are looking at the privious row of that result set. Since, this filter with the previous() function is used in one script at a time when placed in the where clause in would simple be:
Where
Report level filter items
ws.StartDateTime IS NOT NULL and wa.RetiredFlag = 0 and wa.LoadStatusIndicator NOT IN (0, 4) and msi.P4UtilityTypeReadIndicator != 'F' and
Expression level filter
if ( {MeterSessionInput.P4UtilityTypeReadIndicator} = "L" and PREVIOUS ({MeterSessionInput.P4UtilityTypeReadIndicator}) = "E" and {Meter.MeterNumber} = PREVIOUS ({Meter.MeterNumber}) ) then ( if ({MeterSessionOutput.ReadCode} <> 0 and {MeterSessionOutput.ReadCode} <> 2 and {MeterSessionOutput.ReadCode} <> 99 and {MeterSessionOutput.BestResultIndicator} = 2 and (isNull({CustomDataField_MeterLink.CustomData}) = true or IsDemo({CustomDataField_MeterLink.CustomData}) = false) ) or (PREVIOUS({MeterSessionOutput.ReadCode}) <> 0 and PREVIOUS({MeterSessionOutput.ReadCode}) <> 2 and PREVIOUS({MeterSessionOutput.ReadCode}) <> 99 and PREVIOUS({MeterSessionOutput.BestResultIndicator}) = 2 and IsDemo(PREVIOUS({CustomDataField_MeterLink.CustomData})) = false) then true ) ;
So, the short & skinny answer is it simple looks at the previous row of the result set. I hope that, I'm clear.
|
|
Rob
|
IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|

Posted: 30 Jan 2014 at 9:49am |
|
No, "Previous()" in Crystal goes to the previous row in the data based on how the report is grouped and sorted. However, there is no "Previous" function in SQL. So, you have to somehow create a sub-query that will provide a single row that contains the data from that previous row. However, in order to do that, you have know how the data is ordered (grouping may or may not give you the order in SQL) to determine what to put in the where clause to get that information.
-Dell
-Dell
|
|
|
IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|

Posted: 31 Jan 2014 at 3:55am |
|
to get previous you could add
ROW_NUMBER ( )
OVER ( order by somefield ) as link
function to the query then create same query but with
ROW_NUMBER ( )
OVER ( order by somefield )-1 as link
link them together using row_number and it should give you the previous.
|
IP Logged |
|
|
|