Query to get Latest Subscription

[Sonic]

I need a specific query containing information from two tables, and I'm struggling to work out how to achieve it. The tables hold data representing sales of software products which are sold on an renewable annual subscription basis.

The first table is called "CONTRACTS". It contains information for each unique customer contract (such as customer name, contract start date, product sku, product serial number, and purchase order number).

The second table is called "SUBSCRIPTIONS". It represents every annual subscription which is sold for every customer contract. So there is a field called ContractID which links back to the CONTRACTS table. Then there are fields for Subscription Start Date, Subscription End Date, Subscription SKU, and Purchase Order Number.

So for a given contract, there will likely be a series of associated subscriptions, and a new subscription is created every time a customer renews their contract.

I can easily build a query which gets me all the subscriptions associated with a given contract. However, I need a query which gets *only* the latest subscription for each contract. I want to use this query to build a report which identifies forthcoming subscription renewals in date order. Then I need another report which gets only the recently expired subscription renewals - but again - it should only be looking at the most recent subscription for each given contract.

How would I achieve this?

 

[isladogs]

Create an aggregate query based on both tables.
Group by the fields you want and change GroupBy to Max or Last for the Subscription date end field. You can also sort by date and filter for those dates >Date() to get those due to expire .....etc

Also, welcome to AWF

 

[arnelgp]

take the Subscriptions table and create a New Query (qrySubscribe1).
add all the fields.
create a Sort order (the sequence is important) by Subscription Start Date field (Descending order).

next take the Contracts table and create a New Query.
add the fields you need to display.
add an Expression as follows, eg:

select ContractID, [customer name], [contract start], [product sku],
   (select top 1 t1.[subscription sku]
      from [qrySubscribe1] as t1 where t1.[contractid] = [Contracts].[contractid]) as [subscription sku], 
   (select top 1 t1.[subscription start date]
      from [qrySubscribe1] as t1 where t1.[contractid] = [Contracts].[contractid]) as [subscription start date], 
   (select top 1 t1.[subscription end date]
      from [qrySubscribe1] as t1 where t1.[contractid] = [Contracts].[contractid]) as [subscription end date] 
from Contracts;

 

[pbaldy]

Getting all the fields without a subquery for each:

http://www.baldyweb.com/LastValue.htm