Day sum between specific date ranges, filtered results

[Nade85]

I have made multiple changes through this and am hitting a wall. Haven't found a similar problem in previous threads so hopefully some super genius will save me from myself.



I have a table with a massive number of entries (2.6 million+) each encapsulates a specific event and an assigned "AUIC" filtered by a "Category Code". These events cover various date ranges and I am trying to filter results to match up with their assigned AUIC and then SUM the number of days in a given month.

Basically I need to SUM all the days in the month of October in 2017 and have them aligned with their assigned AUIC. I accounted for the different variations in the SQL written below but for some reason it returns number that are way off. I have a few other revisions that returned negative numbers.

Table referenced is below (minus arbitrary fields used in other queries):

Category Code		Begin Date			Adj End Date				Event_AUIC
A		11/7/2015			3/13/2016				219
A		7/23/2016			12/2/2016				219
A		1/16/2017			1/25/2017				219
A		3/15/2017			8/8/2017				219
A		5/29/2018			6/1/2018				497
C		6/15/2018			6/29/2018				497
C		3/6/2019			12/18/2019				497

INSERT INTO tblPer_ByMonth_ByUIC ( AUIC, FY, Oct )
SELECT tblPer.Event_AUIC, [FY Start] AS Expr2, Sum(Switch([Begin Date] <= [FY Start] And [Adj End Date] >= DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", [FY Start], DateSerial(YEAR([FY Start]), MONTH(10)+1, 0)), [Begin Date] > [FY Start] And [Adj End Date] < DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", [Begin Date], [Adj End Date]), [Begin Date] > [FY Start] And [Adj End Date] >= DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", [Begin Date], DateSerial(YEAR([FY Start]), MONTH(10)+1, 0)), [Begin Date] <= [FY Start] And [Adj End Date] < DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", [FY Start], [Adj End Date]))) AS [Oct Sum]
FROM tblPer
WHERE (((tblPer.[Category Code]) In ("A","B","C","D","F")))
GROUP BY tblPer.Event_AUIC;

Eventually the plan is to replicate a working process to capture every month of the year so my coding was intentionally generic to be easier to shift to the next month. Any help on this would be fantastic.

[CJ_London]

your sql looks way more complicated that it needs to be (and suspect would error anyway as the syntax looks to be incomplete) and why an insert query? these sort of values should be calculated as and when required.

given the table data you have provided - what do you expect as a result?

[Nade85]

I had an old revision of the SQL in there that returned negative results (updated version below). I corrected it to get the results below. These numbers are WAY off target and I'm not sure where its adding them incorrectly. Some could be as high a 5 digit, but none should be 6 digits. Its far more likely to have them no higher than 4 digits. Currently, some results are at 8 digits.
As you can see I am trying to obtain a total number of days for each AUIC based off the events in tblPer. I am trying to get it to function where it takes the month of October (in this example) and looks at all events start and end dates. It should then return a, by AUIC, sum total of all the number of days for the many events. Some might be the full month, some might be a few days at the beginning or the end, and others might be a few days in the middle of the month. This is why I have the seemingly excessive parameters.
Its written as an append query because I have other tables and information that this will eventually feed into and I haven't determined my final path or look. I'll probably end up changing things later on as I still need to be able to query every month of a rolling five year period and generate a "by year" report.

Event_AUIC	Expr2	Oct Sum
011	10/1/2017	287630
013	10/1/2017	130468
014	10/1/2017	16974
015	10/1/2017	9931
018	10/1/2017	304192
019	10/1/2017	57101

INSERT INTO tblPerstempo_ByMonth_ByUIC ( AUIC, FY, Oct )SELECT tblPerstempo.Event_AUIC, [FY Start] AS Expr2, Sum(Switch([Begin Date] <= [FY Start] And [Adj End Date] >= DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), [FY Start]), [Begin Date] > [FY Start] And [Adj End Date] < DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", [Adj End Date], [Begin Date]), [Begin Date] > [FY Start] And [Adj End Date] >= DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), [Begin Date]), [Begin Date] <= [FY Start] And [Adj End Date] < DateSerial(YEAR([FY Start]), MONTH(10)+1, 0), DateDiff("d", [Adj End Date], [FY Start]))) AS [Oct Sum]
FROM tblPerstempo
WHERE (((tblPerstempo.[Category Code]) In ("A","B","C","D","F")))
GROUP BY tblPerstempo.Event_AUIC;

[CJ_London]

Dates are numbers, from your example in your first post they appear to be text (left justified) if they are, the first thing you need to do is store them as datetypes.

Not at my computer right now but will see if I can come up with a suggestion in a few hours time

[Nade85]

They are formatted as Date/Time on both tables. Its probably just the copy/paste job making it look different. The Category Code is formatted as Short Text and the sum is formatted Number.

[Minty]

I don't think you will get this done in one query because the dates can cross multiple months.
I suspect the easiest route will need to use a temp table (or at least a recordset, but suspect that will get slow given the amount of records) to separate out the days per month per Event_AUIC, and then total those figures by YYMM and Event_AUIC.

Is the Data in Access tables or a SQL backend?

[Nade85]

Its in Access tables. I'm pretty new so I'm open to any ideas that could improve the overall process.

[CJ_London]

Sorry, took longer to get to this than I thought

Try this example, you need to create a utility table and query first. Call the table utilCounter, just one column, populated with 0-9

utilCounter
num (byte)
0
1
2
3
4
5
6
7
8
9

no need for indexing

now the query, call it utilCount

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM utilCounter AS singles, utilCounter AS tens, utilCounter AS hundreds, utilCounter AS thousands

no need for sorting

this will provide a list of numbers from 0 to 9999 (about 27 years), if the maximum difference between start and end dates is greater than this then you will need to modify. If less than 2.7 years, you can remove the thousands part

now for your query, this is from mocked up data. I have removed spaces from your field names to save time

SELECT Format(BeginDate+Counter,"yyyymm") AS YearMonth, Count(Counter) AS DaysInMonth
FROM tblPer, utilCount
WHERE Counter<=(AdjEndDate-BeginDate) AND CategoryCode In ("A","B","C","D","F")
GROUP BY Format(BeginDate+Counter,"yyyymm")

This will provide your complete history by year/month, you can add additional criteria to limit to one month, a year, whatever

[Nade85]

Seems like a simpler to code concept, but when run this is asking for more input than I was expecting. It is asking for a BeginDate, Counter, AdjEndDate, and CategoryCode. It should be pulling all of these from tblPer for each entry. It also doesn't appear to be grouping by AUIC which I imagine is an easy enough fix of adding another Group By parameter.

When I run this query, depending on how I enter the various parameters it asks for, it either errors out for being too complex or it returns columns (YearMonth, DaysinMonth) and no other data at all. I have tried entering dates as YYYYMM and MM/DD/YYYY. Whatever I enter for the Counter input box above a certain point always returns the too complex error.

For this issue, I am trying to filter down by AUIC (group name effectively), totaling the number of days from each month as it goes. Each event is typically longer than two months, but there are a good amount of shorter events (less than a month).

[Welshgasman]

You have spaces in your field names?, never a great idea, but the field names still have to match, so alter accordingly and surround those fieldnames with spaces with [ & ].

[CJ_London]

It is asking for a BeginDate, Counter, AdjEndDate, and CategoryCode. It should be pulling all of these from tblPer for each entry.

As I said and Welshgasman has pointed out, I removed spaces to save time and a cleaner environment, Spaces are a bad idea

And Counter is in the query I provided, not tblPer.

You did create it?

It also doesn't appear to be grouping by AUIC which I imagine is an easy enough fix of adding another Group By parameter.

correct, I'm showing you how to do something, not doing it for you

Each event is typically longer than two months,

so long as they are less than 2.7 years you can drop the thousands part of the query - may give you a performance improvement. You would need to try it and see.

[Nade85]

Ajax,
I had put the spaces back in however I failed to notice I needed to put the brackets around all the instances of the different variables. I got that corrected and ran some tests. I'll be clearing spaces and changing practice going forward.

I did create the Counter and just discovered that somehow the "AS [Counter]" portion of the query's code was changed to "AS Expr1". No clue how that happened but now the query runs without asking for inputs which was throwing everything off. I am not expecting a finished product and apologize for things coming across as that. With this being a completely new way of looking at the coding aspect for me I was trying to clarify that adding in the AUIC parameter was that simple. Hindsight being 20/20, I realize my vagueness looked a unappreciative and disrespectful there.

I did remove the thousands portion of it. With it in there I feel it would have been an untenable option as without it, I'm was seeing a 3-4 hour run time on the query. Since my tests are running so long, its made this drag on for a while now. I am running a test now after correcting the Expr1/Counter issue. I'll let yall know what happens when it finishes. Thanks for everything so far though.

[CJ_London]

always better to include criteria to reduce running time. Selecting one or two months or year to date will have a dramatic improvement. There are other ways using non standard joins and of course relevant fields need to be indexed