Conditional Sum of Date Ranges in Access SQL Query

As a technical blogger, I’m often asked to tackle complex problems and share solutions with others. In this article, we’ll delve into the world of Access SQL queries and explore how to conditionally sum date ranges for outstanding invoice amounts.

Problem Statement

We have a table ORDERHIST containing transaction data with client IDs, dates, transaction types, and invoice amounts. We want to create a table that shows the sum of all outstanding invoice amounts for each business day, including only transactions with a TypeCode of SERV or CONS.

Understanding the Requirements

To approach this problem, we need to understand what’s being asked:

• We have a list of dates (PurchaseDate and PaymentDate) for each transaction.
• We want to sum up the outstanding invoice amounts (InvoiceAmt) only if the PurchaseDate is less than or equal to the date and the PaymentDate is greater than or equal to the date.
• The TypeCode should be either SERV or CONS.

Breaking Down the Query

To tackle this problem, we’ll break down the query into several steps:

Step 1: Creating a List of Dates

We need to create a list of distinct dates from the ORDERHIST table. We can do this using the following query:

SELECT DISTINCT Purchasedate AS dte
FROM ORDERHIST;

This will give us a list of unique dates for each transaction.

Step 2: Correlated Subquery

Next, we’ll use a correlated subquery to calculate the outstanding balance for each date. The correlated subquery will filter the transactions based on the conditions specified in the problem statement (PurchaseDate ≤ Date and PaymentDate ≥ Date) and sum up the InvoiceAmt.

SELECT d.dte,
       (SELECT SUM(oh2.InvoiceAmt)
        FROM ORDERHIST AS oh2
        WHERE oh2.PurchaseDate <= d.dte AND
              oh2.PaymentDate >= d.dte AND
              oh2.TypeCode IN ("SERV", "CONS")
       ) AS OutstandingBalance
FROM (SELECT DISTINCT Purchasedate AS dte
      FROM ORDERHIST) AS d
GROUP BY d.dte;

However, we need to make sure that the date range is inclusive. This means that if a transaction’s PaymentDate is on the same day as its PurchaseDate, it should be included in the calculation.

Step 3: Handling Date Ranges

To handle date ranges inclusively, we can use the following query:

SELECT d.dte,
       (SELECT SUM(oh2.InvoiceAmt)
        FROM ORDERHIST AS oh2
        WHERE CASE
            WHEN oh2.PurchaseDate = oh2.PaymentDate THEN
              (CASE WHEN d.dte BETWEEN oh2.PurchaseDate AND oh2.PaymentDate THEN 1 ELSE 0 END)
            ELSE
              (CASE WHEN d.dte BETWEEN oh2.PaymentDate AND oh2.PurchaseDate THEN 1 ELSE 0 END)
          END = 1 AND
            oh2.TypeCode IN ("SERV", "CONS")
       ) AS OutstandingBalance
FROM (SELECT DISTINCT Purchasedate AS dte
      FROM ORDERHIST) AS d
GROUP BY d.dte;

This query will correctly handle date ranges inclusively, ensuring that transactions with a PaymentDate on the same day as their PurchaseDate are included in the calculation.

Example Use Case

Let’s take a look at an example use case for this query:

Suppose we have the following data in the ORDERHIST table:

Running the query with this data will produce the following result:

As we can see, the query correctly calculates the outstanding balance for each business day, taking into account the TypeCode and date ranges inclusively.

Conclusion

In this article, we explored how to conditionally sum date ranges in an Access SQL query. We broke down the problem into several steps, creating a list of dates, using correlated subqueries, and handling date ranges inclusively. By following these steps, you should be able to create a query that accurately calculates outstanding balances for each business day.

Note: This is just one way to solve this problem in Access SQL. Depending on your specific requirements, you may need to modify the query to suit your needs.

Last modified on 2023-06-07