Solved

SQL Subquery find previous record in a group

Posted on 2009-07-15
12
391 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
  • 7
  • 3
  • 2
12 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
What logic are you using to select the previous record.
0
 

Author Comment

by:tashmyke
Comment Utility

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
Comment Utility
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
 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 41

Expert Comment

by:pcelba
Comment Utility
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 41

Expert Comment

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

Author Comment

by:tashmyke
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
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 41

Expert Comment

by:pcelba
Comment Utility
It seems the TRAN_ID is a text field. Does it contain digits only?
0
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now