Help! Complicated query with Date range, Code, and ID filtering and a sum feature

[Nade85]

I have a table (displays similar to below) that consists of nearly 1.3 million records (grows monthly). I inherited this database and am trying to run a query for a new information request. Most everything is tied to other tables and queries so altering layout and design could complicate things greatly so I want to work within the constraints of this table.



Ultimately I need a count of the number of ID's that have exceeded 220 days cumulative in a rolling year prior to a date that I manually enter at the start of the query. Consecutive_Begin_Date and Consecutive_End_Date data should be used as it will encompass the most updated dates.

ID	Category Code	Begin Date	Projected End Date	End Date	Adjusted End Date	Consecutive_Begin_Date	Consecutive_End_Date	Consecutive_Days
102378	A	07-Nov-15	14-Mar-16	13-Mar-16	13-Mar-16	07-Nov-15	13-Mar-16	127
102378	A	23-Jul-16	02-Dec-16	02-Dec-16	02-Dec-16	23-Jul-16	02-Dec-16	132
102378	A	16-Jan-17	25-Jan-17	25-Jan-17	25-Jan-17	16-Jan-17	25-Jan-17	9
102378	A	15-Mar-17	07-Aug-17	08-Aug-17	08-Aug-17	15-Mar-17	08-Aug-17	146
102378	A	29-May-18	01-Jun-18	01-Jun-18	01-Jun-18	29-May-18	01-Jun-18	3
102378	C	15-Jun-18	29-Jun-18	29-Jun-18	29-Jun-18	15-Jun-18	29-Jun-18	14
102378	C	06-Mar-19	31-Dec-19		31-Dec-19	06-Mar-19	31-Dec-19	300
302100	C	17-Jul-15	02-Aug-15	02-Aug-15	02-Aug-15	17-Jul-15	02-Aug-15	16
302100	C	22-Oct-15	31-Oct-15	31-Oct-15	31-Oct-15	22-Oct-15	31-Oct-15	9
302100	C	02-Dec-15	18-Dec-15	18-Dec-15	18-Dec-15	02-Dec-15	18-Dec-15	16
302100	C	22-Feb-16	05-Mar-16	05-Mar-16	05-Mar-16	22-Feb-16	05-Mar-16	12

Based off what's visible above, if I launch the query and input a date of 11/1/2017, ID #102378 would end up with a total of 187 and ID #302100 would have a total of 0. Output should return a table with no rows in this scenario. With full data there would be a list of ID's that exceeded 220 days cumulative and how many days had during the provided window.

If its helpful, there is also a calendar table already in the database that was used elsewhere. It looks like the below and extends well into the future.

ADate	BOM	EOM	CY	CYMonth	CYDay	CYDayOfYear	FY	FYMonth	DayOfWeek
10/1/2009	10/1/2009	10/31/2009	2009	10	1	274	2010	1	Thu
10/2/2009	10/1/2009	10/31/2009	2009	10	2	275	2010	1	Fri
10/3/2009	10/1/2009	10/31/2009	2009	10	3	276	2010	1	Sat
10/4/2009	10/1/2009	10/31/2009	2009	10	4	277	2010	1	Sun
10/5/2009	10/1/2009	10/31/2009	2009	10	5	278	2010	1	Mon
10/6/2009	10/1/2009	10/31/2009	2009	10	6	279	2010	1	Tue
10/7/2009	10/1/2009	10/31/2009	2009	10	7	280	2010	1	Wed
10/8/2009	10/1/2009	10/31/2009	2009	10	8	281	2010	1	Thu

[Micron]

I can't make sense out of your examples. You didn't say which of 6 date fields the input date is being compared against, and when I compared all 6 for dates 1 year prior to January 11 2017 I get 259 for a result for 102378. Regardless, what have you tried, if anything?

I'm thinking you need a Totals query, grouped by ID, count of ID Where Sum Consecutive_Days >= 220 and some date field is Between Date()-365 And [Enter a Date]. An alternative to using Date() could be Between DateAdd("d",-365) And [Enter a Date].

[CJ_London]

I think what you are saying is this

If you entered 1st November 2017 - your prior year is 1st Nov 2016 to 31st Oct 2017? So

ID	Category Code	Begin Date	Projected End Date	End Date	Adjusted End Date	Consecutive_Begin_Date	Consecutive_End_Date	Consecutive_Days
102378	A	07-Nov-15	14-Mar-16	13-Mar-16	13-Mar-16	07-Nov-15	13-Mar-16	127 excluded
102378	A	23-Jul-16	02-Dec-16	02-Dec-16	02-Dec-16	23-Jul-16	02-Dec-16	132 - 32 included
102378	A	16-Jan-17	25-Jan-17	25-Jan-17	25-Jan-17	16-Jan-17	25-Jan-17	9 9 included
102378	A	15-Mar-17	07-Aug-17	08-Aug-17	08-Aug-17	15-Mar-17	08-Aug-17	146 146 included
102378	A	29-May-18	01-Jun-18	01-Jun-18	01-Jun-18	29-May-18	01-Jun-18	3 3 excluded
102378	C	15-Jun-18	29-Jun-18	29-Jun-18	29-Jun-18	15-Jun-18	29-Jun-18	14 excluded
102378	C	06-Mar-19	31-Dec-19		31-Dec-19	06-Mar-19	31-Dec-19	300 excluded

[Nade85]

So the total for 102378 is different because I was screening from a November 1, 2017 rather than January.

The previous database owner was trying to reference other tables that have a massive amount of duplicate/unneeded data in them but their attempt to at least reduce that info for a manual filtering process is below.

SELECT tbl_Calendar.FY, tbl_Calendar.ADate, tbl_KM_PERS_TRACK.SSN, tbl_SSN_CTGY_ABCDF_Days_In_FY16.SailorID, tbl_KM_PERS_TRACK.EVENT_AUIC, tbl_KM_PERS_TRACK.PERSTEMP_CAT_CD, tbl_KM_PERS_TRACK.TRAK_STRT_DT, tbl_KM_PERS_TRACK.ACTL_PROJ_STOP_DT, GetDaysOverlap([TRAK_STRT_DT],[ACTL_PROJ_STOP_DT]-1,[ADATE]-365,[ADate]) AS Days
FROM tbl_Calendar, tbl_KM_PERS_TRACK INNER JOIN tbl_SSN_CTGY_ABCDF_Days_In_FY16 ON tbl_KM_PERS_TRACK.SSN = tbl_SSN_CTGY_ABCDF_Days_In_FY16.SSN
WHERE (((tbl_Calendar.ADate) Between [Start Dt] And [Stop Dt]) AND ((tbl_KM_PERS_TRACK.PERSTEMP_CAT_CD) In ("A","B","C","D","F")) AND ((tbl_KM_PERS_TRACK.TRAK_STRT_DT)<=[ADate]) AND ((tbl_KM_PERS_TRACK.ACTL_PROJ_STOP_DT)>[ADate]-365) AND ((GetDaysOverlap([TRAK_STRT_DT],[ACTL_PROJ_STOP_DT]-1,[ADATE]-365,[ADate]))>0));

As far as my latest attempt goes, I utilized a simpler table that is continually updated and still has all the data needed. I made some minor changes to fit the current request (like calendar year vice fiscal). These return results but it takes an eternity to run (more than a few hours) and the data is clearly skewed as some totals exceed 1000 days. At this point in my design, I haven't tried to filter by more than 220 yet.

SELECT DISTINCTROW tbl_Calendar.CY, tblTempo.ID, tblTempo.[Category Code], Count(tblTempo.Consecutive_Days) AS CountOfConsecutive_Days
FROM tbl_Calendar, tblTempo
WHERE (((tbl_Calendar.ADate) Between [Start Dt] And [Stop Dt]) AND ((tblTempo.Consecutive_Begin_Date)<=[ADate]) AND ((tblTempo.Consecutive_End_Date)>[ADate]-365) AND ((GetDaysOverlap([Consecutive_Begin_Date],[Consecutive_End_Date]-1,[ADATE]-365,[ADate]))>0))
GROUP BY tbl_Calendar.CY, tblTempo.ID, tblTempo.[Category Code]
HAVING (((tblTempo.[Category Code]) In ("A","B","C","D","F")));

[Nade85]

Yes, that's correct. This is a request that may appear at any time and be for a random period in an already over ten year database history. Basically at this point I'm trying to get to a functioning standpoint where I can at least provide data from a year prior to start point before I get into the complexity of some odd date requests and the such.

[CJ_London]

this is the query I created based on your example data and the reported output - see you have posted something, so you need to change names to suit. It does not use your calendar table

SELECT Table1.ID, Sum(Switch([Consecutive_Begin_Date]<DateAdd("yyyy",-1,[Enter date]),[Consecutive_End_Date]-DateAdd("yyyy",-1,[Enter Date])+1,[Consecutive_End_Date]>=[Enter Date],DateDiff("d",[Consecutive_Begin_Date],[Enter Date])-1,True,[Consecutive_End_Date]-[Consecutive_Begin_Date])) AS Expr1
FROM Table1
WHERE (((Table1.Consecutive_Begin_Date)<[enter date]) AND ((Table1.Consecutive_End_Date)>=DateAdd("yyyy",-1,[Enter date])))
GROUP BY Table1.ID
HAVING (((Sum(Switch([Consecutive_Begin_Date]<DateAdd("yyyy",-1,[Enter date]),[Consecutive_End_Date]-DateAdd("yyyy",-1,[Enter Date])+1,[Consecutive_End_Date]>=[Enter Date],DateDiff("d",[Consecutive_Begin_Date],[Enter Date])-1,True,[Consecutive_End_Date]-[Consecutive_Begin_Date])))>220));

Remove the having clause to see the actual 187 day returned


You have a potential inconsistency in your data in terms of your day count. If I said I start today and finish tomorrow - I would call that 2 days, but you are calling it 1 as far as your consecutive days calculation is concerned. i.e. ignoring one of the days

But when you calculate your 32 days for the second record, you are not ignoring one of the days (30 days of november plus 2 days of december)

I've gone with your inconsistency in this query

With regards performance, indexing will make a huge difference - in my query, your consecutive begin/end fields should be indexed

[Nade85]

Thanks for that. I've got to run it through its paces and make some more mods to get it flexible enough but I appreciate the help. My brain was getting fried from trying variants with only mediocre success.

[Nade85]

So I did a lot more digging into the efficacy of the SQL coding listed as a solution above. Where it worked for everything and seemed to be pretty accurate, I did find where there were some limitations and hiccups. The resulting code at the end is what I came to based off the code above from Ajax.

To start off, I always knew I would be modifying the code to query for a bunch of different date ranges. Since I'm pretty much brand new to Access, it was a steep learning curve and I spent a ton of time really digging into available functions and what I could and couldn't do. Long story short, found out I needed to just let the Excel file that I knew I would eventually have to use to create graphs do all the extra filtering for me. Biggest thing I needed was a simplified method to add up all the different record in a timeframe for each ID. So the first thing to go was the 220 filters/math in the expression. Basically I simplified it to a last 365 window. Since there is also a query to find the same info over a 2 year window, I just simply modified everything for 730 days and worked through the rest of the issues on the 365 version and updated the 730 query as I went.

Originally, the code seemed to work out fine and since my initial data pull using the more tedious, (completely) manual method returned slightly different data, I wasn't too concerned as I believed this to be more accurate. Did some follow up scrubbing and uncovered two problems. In large part, the first problem was strictly how the DB was built by its previous owner. Basically, since each record has a different set of dates and the DB was designed to filter based on consecutive days (rather than the new cumulative query this is about) the results could not be based off the original "Consecutive_Begin_Date" and "Consecutive_End_Date". Found a few scenarios like you would see below where multiple records would share the aforementioned dates and thus end up multiplying the reported sum for that person like in the example below:

ID	Category Code	Begin Date	Proj End Date	End Date	Adj End Date	Consecutive_Begin_Date	Consecutive_End_Date	Consecutive_Days
92	D	21-Jul-15	24-Jul-15	24-Jul-15	24-Jul-15	21-Jul-15	24-Jul-15	3
92	D	10-Aug-15	13-Aug-15	13-Aug-15	13-Aug-15	10-Aug-15	21-Aug-15	11
92	D	13-Aug-15	16-Aug-15	16-Aug-15	16-Aug-15	10-Aug-15	21-Aug-15	11
92	D	16-Aug-15	21-Aug-15	21-Aug-15	21-Aug-15	10-Aug-15	21-Aug-15	11
92	D	16-Mar-16	22-Mar-16	22-Mar-16	22-Mar-16	16-Mar-16	25-Mar-16	9

The above issue also made me realize that without the category code filter, I was pulling events out that I did not need as there was a limited list of category codes that I needed to consider. That was a simple add as I already had it from before and simply left it out when inputting and modifying the code from Ajax.

Then, I found that the above code only provided records that were dated after the "Enter Date" minus a year. This didn't seem like a problem until I realized that it never stopped it. Envision a date of 1/1/19 being entered and a "Begin Date" of 12/1/17 with and "Adj End Date" of 2/1/19. Effectively this record started before and ended after the window I am trying to count days on as the 1/1/19 date should have returned everything in 2018. The results I got ended up being a count of days from 1/1/18 (ok great, right?) but it didn't stop counting until the end date of 2/1/19. So basically I ended up with skewed data by however long the event ran over for the year.

The below is my current SQL coding with an added bit for the "Timeframe Entered" since I am exporting to Excel. Basically, the whole timeframe bit is just to make the Excel side a little more controlled and update things on that side of this whole evolution without more manual processes.

INSERT INTO tblTempo_Sum_365 ( ID, [Totals of Past 365], [Timeframe Entered] )
SELECT tblTempo.ID, Sum(Switch([Begin Date]<DateAdd("yyyy",-1,[Enter date]) And [Adj End Date]>=[Enter Date],365,[Begin Date]<DateAdd("yyyy",-1,[Enter date]),[Adj End Date]-DateAdd("yyyy",-1,[Enter Date]),[Adj End Date]>=[Enter Date],DateDiff("d",[Begin Date],[Enter Date])-1,True,[Adj End Date]-[Begin Date])) AS [Totals of Past 365], [Enter Date] AS [Timeframe Entered]
FROM tblTempo
WHERE (((tblTempo.[Begin Date])<[enter date]) AND ((tblTempo.[Adj End Date])>=DateAdd("yyyy",-1,[Enter date])) AND ((tblTempo.[Category Code]) In ("A","B","C","D","F")))
GROUP BY tblTempo.ID;

[CJ_London]

Away from a computer right now bye consider this mock up

require.............|................|
rec1.......|.....|
rec2...........|..................|
rec3.............|........................|
rec4..................|...|
rec5......................|..................|
rec6.......................................|.....|

the required is your 1 year range
the recs are all possible comparisons

for anything to be in range, the start must be less of the required end and the end must be greater than the required start.

So put that model against what you actually want to report