Custom Date Partition Function

[cjohns38]

Custom Date Partition Function

I need to create a custom date partition function so I can run counts between the dates the user sets and the number of segments someone sets. Below is a break down of things conceptually and then a rough outline on some VBA that tries to do this stuff………..any help on getting the function to work would be greatly appreciated.

*************************************************************
Manual Example
*************************************************************

****************
Start Date: 1/1/2009
End Date: 2/1/2009
Segments: 4
****************

****************
Calculate Segment Length and Segments
****************
(EndDate)-(StartDate) = Length of Segment in days
Example: (2/1/2009)-(1/1/2009)=31

Round((Length of Segment in Days) / (Segments)) = Days to add
Example: Round(31/ 4) = 8

Break dates into segments:
Segment1 which is 1/1/2009 = (StartDate)
Segment2 which is 1/9/2009 = (1/1/2009+8)
Segment3 which is 1/17/2009 = (1/9/2009+8)
Segment4 which is 1/25/2009 = (1/17/2009+8)

****************
Example Start Data
****************
ID Date
1 1/1/2009
2 1/8/2009
3 1/16/2009
4 1/24/2009
5 1/25/2009
6 2/1/2009





****************
Intended End Product so I can run counts on Id’s between dates
****************
ID Date DatePartition
1 1/1/2009 1/1/2009
2 1/8/2009 1/1/2009
3 1/16/2009 1/17/2009
4 1/24/2009 1/17/2009
5 1/25/2009 1/25/2009
6 2/1/2009 1/25/2009


****************
Rough Outline Of VBA Code…..doesn’t work but it’s conceptually what I’m trying to do……
****************

Function DatePartition(StartDate As Date, EndDate As Date, DateVar As Date, Segments As Integer)
'Calculate the difference in start date and end date in days then divide by the number of segments
SegmentLength = Round((datediff("d", StartDate, EndDate) / Segments))

'Create temporary segments using the text date seg and the counter i so dateseg1-datesegn
i = 1
Do Until i = Segments + 1
myvarname = "dateseg" & i 'increment the variable names by i
myvarname = DateAdd("d", SegmentLength * i, StartDate) 'increment the segment length by i
i = i + 1
Loop

'Loop through temporary segments and place queried dates into segments
x = Segments
Do Until x = 0
If DateVar < "dateseg" & 8 Then 'if the queried date is less than the datesegment it's in that segment
DatePartition = "dateseg" & x
x = x - 1
Loop
End Function

Any takers on helping me figure this out?

 

[jdraw]

Custom Date Partition Function

I need to create a custom date partition function so I can run counts between the dates the user sets and the number of segments someone sets. Below is a break down of things conceptually and then a rough outline on some VBA that tries to do this stuff………..any help on getting the function to work would be greatly appreciated.

*************************************************************
Manual Example
*************************************************************

****************
Start Date: 1/1/2009
End Date: 2/1/2009
Segments: 4
****************

****************
Calculate Segment Length and Segments
****************
(EndDate)-(StartDate) = Length of Segment in days
Example: (2/1/2009)-(1/1/2009)=31

Round((Length of Segment in Days) / (Segments)) = Days to add
Example: Round(31/ 4) = 8

Break dates into segments:
Segment1 which is 1/1/2009 = (StartDate)
Segment2 which is 1/9/2009 = (1/1/2009+8)
Segment3 which is 1/17/2009 = (1/9/2009+8)
Segment4 which is 1/25/2009 = (1/17/2009+8)

****************
Example Start Data
****************
ID Date
1 1/1/2009
2 1/8/2009
3 1/16/2009
4 1/24/2009
5 1/25/2009
6 2/1/2009





****************
Intended End Product so I can run counts on Id’s between dates
****************
ID Date DatePartition
1 1/1/2009 1/1/2009
2 1/8/2009 1/1/2009
3 1/16/2009 1/17/2009
4 1/24/2009 1/17/2009
5 1/25/2009 1/25/2009
6 2/1/2009 1/25/2009


****************
Rough Outline Of VBA Code…..doesn’t work but it’s conceptually what I’m trying to do……
****************

Function DatePartition(StartDate As Date, EndDate As Date, DateVar As Date, Segments As Integer)
'Calculate the difference in start date and end date in days then divide by the number of segments
SegmentLength = Round((datediff("d", StartDate, EndDate) / Segments))

'Create temporary segments using the text date seg and the counter i so dateseg1-datesegn
i = 1
Do Until i = Segments + 1
myvarname = "dateseg" & i 'increment the variable names by i
myvarname = DateAdd("d", SegmentLength * i, StartDate) 'increment the segment length by i
i = i + 1
Loop

'Loop through temporary segments and place queried dates into segments
x = Segments
Do Until x = 0
If DateVar < "dateseg" & 8 Then 'if the queried date is less than the datesegment it's in that segment
DatePartition = "dateseg" & x
x = x - 1
Loop
End Function

Any takers on helping me figure this out?

Initial thought is to research this:
http://office.microsoft.com/en-us/access/HA012288921033.aspx

 

[cjohns38]

It doesn't handle dates.

 

[gemma-the-husky]

why doesnt partition work with dates?

it does for me


it will partition the data into date ranges - you may then need another query to cast the date number back to a real date.


heres an example (sorry about the column definition names - hope you can work it out)

this uses a table I have called tblinputfiles, with fields which include filecollected of type date

this query (called query1) first partitions the data
in this example, the red bit splits the date range into 10 day bands

SELECT Count(tblInputFiles.filecollected) AS CountOffileid, Partition([filecollected],0,1000000,10) AS Expr1
FROM tblInputFiles
GROUP BY Partition([filecollected],0,1000000,10);

this second query then takes the first query, casts the start of each partiton, as a date

SELECT Query1.CountOffileid, CDate(Val([expr1])) AS Expr2
FROM Query1;

 

[cjohns38]

That is close I but I need to be able to limit the start/end dates………maybe I could just use subquery with parameters? Thoughts?

 

[gemma-the-husky]

the second and third parameters in the partition function do that

field, lower bound, upperbound, interval

i just used 0 and 10000000 to get everything


just use something like datevalue(startdate) and datevalue(enddate) instead

 

[cjohns38]

I ended up limiting the query by adding another variable but it does exactly what I was hoping for……now to make the graphs……..

Thanks for all your help. I figured there was a way to convert dates to values but I couldn’t get things to work properly.

***************
Query 1 – Limit to between dates and then put them into groups
***************
SELECT Tbl_Scheduling.SessionDate, Count(Tbl_Scheduling.sessiondate) AS CountOffileid, Partition([sessiondate],0,10000000,[Enter Groups]) AS Expr1
FROM Tbl_Scheduling
GROUP BY Tbl_Scheduling.SessionDate, Partition([sessiondate],0,10000000,[Enter Groups])
HAVING (((Tbl_Scheduling.SessionDate) Between [Start Date] And [End Date]));

***************
Query 2 – Pull the contents of query 1 and display the value as a date….things appear to be proper!
***************
SELECT test.CountOffileid, CDate(Val([expr1])) AS Expr2, test.SessionDate
FROM test;

 

[gemma-the-husky]

glad you got it sorted