Daily aggregate calculations for a range of years

Question

The following ACCESS query returns daily aggregate sums for a range of years. It runs without an error but the results for the 3 columns are all zero. I would appreciate if someone could tell why it's not adding the sums correctly.

SELECT     
     Month(days.day_start) AS [MONTH], 
     Day(days.day_start) AS [DAY],
     Year(days.day_start) AS [YEAR], 

     (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2)  
      FROM SALES_RECEIPT
      INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID 
      WHERE SALES_RECEIPT.[SALE_DATE] >= days.day_start 
       AND SALES_RECEIPT.[SALE_DATE] < days.day_end) AS [Daily Sales Total],

     (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) 
      FROM SALES_RECEIPT
      INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID 
      WHERE SALES_RECEIPT.[SALE_DATE] >= days.day_start 
       AND SALES_RECEIPT.[SALE_DATE] < days.day_end  
       AND SALES_RECEIPT.SALES_TAX_EXEMPT="No") AS [Taxable Sales],

     (SELECT Round(Nz(Sum(sales_receipt.SELLING_PRICE * sales_receipt.quantity),0) ,2) 
     FROM SALES_RECEIPT
     INNER JOIN INVENTORY ON INVENTORY.INVENTORY_ID = SALES_RECEIPT.INVENTORY_ID 
     WHERE SALES_RECEIPT.[SALE_DATE] >= days.day_start 
       AND SALES_RECEIPT.[SALE_DATE] < days.day_end 
       AND SALES_RECEIPT.SALES_TAX_EXEMPT="Yes") AS [Tax Free Sales]

FROM    
   (SELECT 
         DateSerial(Year(sale_date), Month(sale_date), Day(sale_date)) AS day_start,
         DateAdd("s", -1, DateSerial(Year(sale_date), 
                 Month(sale_date), Day(sale_date))) AS day_end

    FROM SALES_RECEIPT
    WHERE sale_date >=  #1/1/2009# AND sale_date < #12/31/2016# 
    GROUP BY Year(sale_date), Month(sale_date), Day(sale_date) 
   ) AS days;

score 0 · Accepted Answer · answered Apr 25 '16 at 01:34

Your subquery aggregates return zero because day_end is less than day_start which the subqueries' WHERE clauses assumes the opposite for date ranges. In fact, day_end as specified in FROM clause's derived table is one second less than sale_date.

DateAdd("s", -1, DateSerial(Year(sale_date), 
                 Month(sale_date), Day(sale_date)) AS day_end

See DateAdd's interval values and re-think your needs. By the way DateSerial() is redundant here. Properly return a viable range between start and end dates with positive interval. Below is 100 days between start and end dates:

sale_date As day_start,
DateAdd("d", 100, sale_date) AS day_end

I was able to get it to work based on the information you provided. Thank you!! I have another [posting](http://stackoverflow.com/questions/36805999/aggregate-calculations-from-2-separate-tables) involving aggregate calculations that I hoping somone will provide a solution. — Code Mechanik, Apr 25 '16 at 16:34

Daily aggregate calculations for a range of years

1 Answers1