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 |
Code:
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.