Sum values that are before a specific date

Question

I am struggling with the proper syntax for the following question: I would like to know for each end of the month, for a multi-year period, how many subsidiaries a company owned. Until now, I did the same exercise with a sum of all subsidiaries that were established before the end of a specific year (lets say 2008, 2009, 2010...).

This is the syntax I used for the sum at year-end: SELECT YEAR([Sub].[year]) AS [Year], Sub.[ID], Sub.[NAME], SUM(IIf(YEAR(Sub.year)>=YEAR(Sub.[Date of incorporation]),1,0)) AS [No of Subs] FROM Sub GROUP BY YEAR(Sub.[year]), Sub.[ID], Sub.[NAME];

Current Syntax output

However, I just cannot figure out how to do the same exercise on months-end. Tried out using "date", "dateserial", "datediff", "months"...instead of "YEAR".

FYI: For each end of the month I have the exact date in the underlying "Sub" table.

I would highly appreciate your support. Many thanks!

Gustav Gustav · Accepted Answer · 2018-06-24T09:11:45

This will probably do:

SELECT 
    Format([Sub].[year], "yyyymm") AS [YearMonth], 
    Sub.[ID], 
    Sub.[NAME], 
    SUM(IIf(Format([Sub].[year], "yyyymm") >= Format(Sub.[Date of incorporation], "yyyymm"), 1, 0)) AS [No of Subs]
FROM 
    Sub
GROUP BY 
    Format([Sub].[year], "yyyymm") AS [YearMonth], 
    Sub.[ID]

Sum values that are before a specific date

1 Answers