Re: SQL statement to read previous records

On 18-May-2016 10:20 -0500, Bill Howie wrote:

Thanks so much for all the replies! Our company is moving toward
using SSRS for the type of report request that this is, so I've been
asked to develop this as an SSRS report. Changing the format of the
file is not an option. This file is a main file in the current ERP
setup and it would be a huge deal.

There may exist a feature\plug-in for enabling the reporting sfw to recognize and act against specific ERPs, or the ERP may also|instead provide various VIEW and Stored Procedures definitions to ease reporting requirements via SQL.

To elicit worthwhile responses [instead of uninformed guesses]. probably best to present some effective test-cases with some DDL to describe the TABLE and constraints on the data, plus DML INSERT to provide sample data for each of a simple-scenario, a complicated-scenario [e.g. unconstrained data esp. for which selection criteria by a layperson might effect accidental overly-inclusive results], and end-point scenarios [e.g. first and last attribute-record]; for each test-case using the given inputs [for an inquiry], both show and explain the expected output. The givens need not be the actual TABLE or data, in fact best not, and instead just a representation that well-mimics the actual data relationships and the reporting challenges.

As for an SQL requirement, do realize the SQL can be used to register non-SQL programs as routines that can produce /answers/ [scalar-values or result-sets], after which any SQL reporting feature can then access the data using those routines. Depending on what code exists already to accomplish the desired reporting\answers about the data, and how that code was written, exposing the results via the SQL could be somewhat trivial for some programmers. Thus even if the ERP sfw provider does not offer them, the VIEWs and routines can be created in-house.

I will look at some of the suggestions for the SQL statements and let
you all know what I find out. Thanks again!

[Some of] What has been offered so far, seems to limit the result to just one previous main-record. Or perhaps intending to suggest that one could obtain some maximum\constant number of main-records, but then apparently a result-set inclusive of main-records from prior\unrelated attr-records.? I do not recall any that returned only\all-of the previous main-records for a given attr-record.