Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Subquery find previous record in a group

Posted on 2009-07-15
12
Medium Priority
?
414 Views
Last Modified: 2013-11-15
I would like some help with a subquery that I am trying to write, I have tried varies attempts and have not been able to figure it out.
A solution using SQL-99 syntax would be acceptable, although I am using Access to return the data.

Data sample:
TRAN_ID      DIST      ACT_COST      DATE
12345            19      100.00            12/31/2008
12345            20      100.00            1/31/2009
12345            21      100.00            1/31/2009    
12345            22      0                    1/31/2009    <- I want to return this record
12345            23      0                    2/28/2009
12345            24      0                    2/28/2009    <- and this record
12345            25      0                    3/31/2009
12345            26      0                    4/30/2009

Using the above data set I would like to write a query that will return the following
TRAN_ID     DIST      ACT_COST    DATE        PREV_DIST      PREV_ACT_COST      PREV_DATE
12345             24            0              2/28/2009        22            0                  1/31/2009

I have tried to write the subquery and I am lost, see the mess below.
SELECT DISTINCT
TRAN_ID,
MAX(DIST),
LAST(ACT_COST),
DATE
FROM TABLE A
WHERE DATE  = 2/28/2009
------   I NEED A SUBQUERY TO GET THE PREVIOUS RECORD USING THE GROUPING MAX(DIST) AND LAST(ACT_COST)
----------
GROUP BY TRAN_ID, DATE;
0
Comment
Question by:tashmyke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24860787
What logic are you using to select the previous record.
0
 

Author Comment

by:tashmyke
ID: 24861432

I'm going to simplify it, I think my original request might be too complex and hard to explain.

I 'm really looking for last change in the ACT_COST column by TRAN ID, I also will need the DIST and DATE value returned for that record.

Data sample:
TRAN_ID      DIST      ACT_COST      POST_DATE
12345            19      100.00            12/31/2008
12345            20      100.00            1/31/2009
12345            21      100.00            1/31/2009    
12345            22      0                    1/31/2009    
12345            23      0                    2/28/2009
12345            24      0                    2/28/2009    
12345            25      0                    3/31/2009
12345            26      0                    4/30/2009

Using the data set above

SELECT TABLE.TRAN_ID AS TRAN_ID, Max(TABLE.DIST) AS DIST, Last(TABLE.ACT_COST) AS ACT_COST, TABLE.POST_DATE AS POST_DATE
FROM [TABLE]
GROUP BY TABLE.TRAN_ID, TABLE.POST_DATE
HAVING (((TABLE.TRAN_ID)="12345") AND ((TABLE.POST_DATE)=#2/28/2009#))
ORDER BY TABLE.TRAN_ID, Max(TABLE.DIST);

It returns the following record:
TRAN_ID      DIST      ACT_COST      POST_DATE
12345            24      0                    2/28/2009

I would like to expand the query so that it will return that record along with the last change in ACT_COST which is the record where DIST=21

TRAN_ID      DIST      ACT_COST      POST_DATE     DIST_CHG     ACT_COST_CHG      POST_DATE_CHG
12345            24      0                    2/28/2009               21          100.00                  1/31/2009  

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24862047
Personally, I would say that the chances of you using a query for this are minimal.
This requires recordset processing so that positional changes can be recognised and stored for later re-use.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 43

Expert Comment

by:pcelba
ID: 24947950
If your DIST values are consecutive then it is easy.

Last change in ACT_COST calculated for MAX(DIST) which should be equivalent to MAX(POST_DATE):

SELECT t1.TRAN_ID, t1.DIST, t1.ACT_COST, t2.DIST, t2.ACT_COST, t2.POST_DATE
  FROM Table t1
  INNER JOIN Table t2 ON t2.TRAN_ID = t1.TRAN_ID AND t2.DIST = t1.DIST+1
 WHERE t1.ACT_COST <> t2.ACT_COST
    AND t2.DIST = (SELECT MAX(DIST) FROM Table WHERE TRAN_ID = t2.TRAN_ID );
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24949219
That's a nice try pcelba, but I can't see that it would give the poster the specific answer he used as an example.
The clause :
 INNER JOIN Table t2 ON t2.TRAN_ID = t1.TRAN_ID AND t2.DIST = t1.DIST+1
means that a record can only be matched with its immediate neighbour  and the sample result was using a more distant record.  
So you should get results showing pairs of records when changes occur but unless that is actually what the poster wants then I think the problem is more difficult.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24956171
I know my solution is not complete. It was just a reaction to simplified question mentioned in ID: 24861432 ("I 'm really looking for last change in the ACT_COST column by TRAN ID, I also will need the DIST and DATE value returned for that record.")

I am matching immediate neighbour to identify the ACT_COST column value change.

The more distant record can be joined using its TRAN_ID, it is not a problem but looking at my answer it cannot work because the last condition is wrong...

Much easier would be to upsize this table to SQL Server and create a complex query.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24956407
It seems Access 2007 allows rather complex queries. Example of what you could need is attached. It just uses Table4 instead of [Table] and the date has British format. The first part is your original query, the second is my part joined.

Sample data:
TRAN_ID      DIST    ACT_COST      POST_DATE
12345      19      100      31.12.2008
12345      20      100      31.1.2009
12345      21      100      31.1.2009
12345      22      50      31.1.2009
12345      23      0      31.1.2009
12345      24      0      31.1.2009
12345      25      0      28.2.2009
12345      26      0      28.2.2009
12345      27      0      31.3.2009
12345      28      0      30.4.2009

The last point to solve are values of DIST column. The query requires no gaps in DIST numbering.
SELECT orig.TRAN_ID, orig.DIST, orig.ACT_COST, orig.POST_DATE, chng.DIST_CHG, chng.ACT_COST_CHG, chng.POST_DATE_CHG
  FROM (
SELECT TABLE4.TRAN_ID AS TRAN_ID, Max(TABLE4.DIST) AS DIST, Last(TABLE4.ACT_COST) AS ACT_COST, TABLE4.POST_DATE AS POST_DATE
FROM TABLE4
GROUP BY TABLE4.TRAN_ID, TABLE4.POST_DATE
HAVING (((TABLE4.TRAN_ID)="12345") AND ((TABLE4.POST_DATE)=#28/2/2009#))
ORDER BY TABLE4.TRAN_ID, Max(TABLE4.DIST) )  orig 
INNER JOIN (
SELECT TOP 1 t1.TRAN_ID, t1.DIST AS DIST_CHG, t1.ACT_COST AS ACT_COST_CHG, t2.DIST, t2.ACT_COST, t1.POST_DATE AS POST_DATE_CHG
FROM Table4 AS t1 INNER JOIN Table4 AS t2 ON (t2.DIST = t1.DIST+1) AND (t2.TRAN_ID = t1.TRAN_ID)
WHERE t1.ACT_COST <> t2.ACT_COST
order by t1.DIST DESC ) chng  ON chng.TRAN_ID = orig.TRAN_ID
;

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 24956436
Please count it as an attempt - my part is working just for one TRAN_ID value only...
0
 

Author Comment

by:tashmyke
ID: 24957630
It's so close ... it returns the record below if "12345" is the only Tran ID.

TRAN_ID      DIST      ACT_COST      POST_DATE     DIST_CHG     ACT_COST_CHG      POST_DATE_CHG
12345            24      0                    2/28/2009               21          100.00                  1/31/2009  


It needs to work for multiple Tran ID's.

Like peter57r said I think it's too complex.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24959438
I know it is a little bit complex but you have no other solution yet...

You still did not answer the question if the DIST values are consecutive to allow
INNER JOIN Table t2 ON t2.TRAN_ID = t1.TRAN_ID AND t2.DIST = t1.DIST+1
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24959464
It seems the TRAN_ID is a text field. Does it contain digits only?
0
 
LVL 43

Accepted Solution

by:
pcelba earned 1500 total points
ID: 24959624
The following code supports multiple TRAN_ID values. It still needs consecutive DIST values inside one TRAN_ID.

If you don't have consecutive values in DIST column you may either create another table with new autonumbered column and insert records from your table to this new table or you may simulate the Record number calculation using the DSum function (it could be another nightmare :-)
SELECT orig.TRAN_ID, orig.DIST, orig.ACT_COST, orig.POST_DATE, chng.DIST_CHG, chng.ACT_COST_CHG, chng.POST_DATE_CHG
FROM (
  SELECT TABLE4.TRAN_ID AS TRAN_ID, Max(TABLE4.DIST) AS DIST, Last(TABLE4.ACT_COST) AS ACT_COST,
         TABLE4.POST_DATE AS POST_DATE 
    FROM TABLE4 
   GROUP BY TABLE4.TRAN_ID, TABLE4.POST_DATE 
  HAVING (((TABLE4.TRAN_ID)="12345") And ((TABLE4.POST_DATE)=#2/28/2009#)) 
   ORDER BY TABLE4.TRAN_ID, Max(TABLE4.DIST))  AS orig 
INNER JOIN (
  SELECT  lid.TRAN_ID, lid.DIST AS DIST_CHG, tbl.ACT_COST AS ACT_COST_CHG, tbl.POST_DATE AS POST_DATE_CHG
    FROM (
       SELECT t1.TRAN_ID, MAX(t1.DIST) AS DIST
         FROM Table4 AS t1 
        INNER JOIN Table4 AS t2 ON (t2.DIST=t1.DIST+1) AND (t2.TRAN_ID=t1.TRAN_ID)
        WHERE t1.ACT_COST<>t2.ACT_COST 
        GROUP BY t1.TRAN_ID) lid
    INNER JOIN Table4 tbl ON tbl.TRAN_ID = lid.TRAN_ID AND tbl.DIST = lid.DIST
          )  AS chng ON chng.TRAN_ID=orig.TRAN_ID ;

Open in new window

0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In this article, we’ll look at how to deploy ProxySQL.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question