UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Track Date Ranged Records To Identify 28 Th Day, Access 2013    
 
   
cwac60
post Nov 10 2017, 07:30 AM
Post#1



Posts: 808
Joined: 1-April 09
From: Queensland Australia


Hi Colleagues
I wish to track date ranged details stored in a table to flag when the 28 day is reached.
daFrom1|__________| daTo1
daFrom2|_____| daTo2
daFrom3|______________| daTo3
^28 th day
daFromN|___________________| daToN

These ranges are non contiguous and could refer to holiday periods.
I need your assistance in composing SQL (maybe VBA) to
1. Determine the date of the 28 day. This may not be reached but 2. would still apply.
2. Overall number of days elapsed
Your assistance would be appreciated.
Regards
cwac60
Go to the top of the page
 
GroverParkGeorge
post Nov 10 2017, 07:50 AM
Post#2


UA Admin
Posts: 30,971
Joined: 20-June 02
From: Newcastle, WA


As is almost always the case, you can accomplish the goal in more than one way. What have you tried so far?

There are a couple of date functions, called DateDiff() and DateAdd() that could be used. DateDiff() returns the number of intervals between two points in time. DateAdd() adds or substracts a specified number of intervals from a given point in time.

So, for example: DateDiff("d", date1, date2) will return the number of days between the first and second dates. DateAdd("d", 28, date1) will return the date 28 days after date1. DateAdd("d", -28, Date1) will return the date 28 days before date1.

You can make this dynamic by using the Date() function to specify the current date every day: DateDiff("d", date1, Date()) will return the number of days between date1 and "today", and will return a different number every day because Date() changes.

See if those functions can help you write a query that does what you need.

--------------------
Go to the top of the page
 
cwac60
post Nov 10 2017, 08:05 AM
Post#3



Posts: 808
Joined: 1-April 09
From: Queensland Australia


Hi Grover
Thank you for the rapid reply. Yes I am familiar with date functions.
The challenge is to calculate number of days for each period starting at smallest date and sum the days elapsed until 28 is exceeded then work backwards.
That's where the SQL becomes tricky.
Regards
cwac60
Go to the top of the page
 
GroverParkGeorge
post Nov 10 2017, 08:20 AM
Post#4


UA Admin
Posts: 30,971
Joined: 20-June 02
From: Newcastle, WA


Knowing whether a date is exactly 28 days from any other date is fairly simple: DateDiff("d", date1, date2) = 28 If date2 is 28 calendar days after date1, this will be a true statement, otherwise it will be false.

Specifying a date 28 days before or after any other date is also simple: DateAdd("d", 28, date1) = date2, or DateAdd("d", -28, date1) = date2 will return a value into date2 exactly 28 calendar days from date1.

With those two pieces of information, you can answer two questions:

a) Is date2 exactly 28 days after date1? Or the reverse, if you want to know if it is exactly 28 days before date1 would be: DateDiff("d", date1, date2) = -28

b) What date is exactly 28 calendar days after, or before, date1?

If you can answer those two questions, you have enough information, I would imagine, to achieve your desired goal.

====================

Something tells me, though, that you really don't want just that basic kind of information. You mention "holiday days", so that suggests the whole thing is more complicated and that you need to account for holidays "somehow". Is that right?

So, please explain the BUSINESS process this is intended to model so we can understand WHY this needs to be done; that may help us understand the requirements better. In other words, this description is a bit abstract, making it easy to guess wrong about what it is you need.

What's the importance of knowing when "28 days have elapsed"? Why do you then need to "work backwards"? What does that step give you? What are these periods you need to evaluate? What is the "smallest date"?

Thanks.

George
This post has been edited by GroverParkGeorge: Nov 10 2017, 08:51 AM
Reason for edit: oopsie.

--------------------
Go to the top of the page
 
projecttoday
post Nov 10 2017, 08:30 AM
Post#5


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


I suggest that in addition to explaining the process that you give an example case or two.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
cwac60
post Nov 11 2017, 03:07 AM
Post#6



Posts: 808
Joined: 1-April 09
From: Queensland Australia


Hi George and Robert,
Thank you for your patience. This relates to a situation where a client on a pension in a village requires intermittent care at a hospital.
The rule is that if the number of accumulated days exceeds 28 days, commencing at the start of the financial year (comm 1 Jul in Oz), the village will receive a much lesser pension, say 10 per cent.
So the periods are not contiguous, a calendar month arithmetic only gets to a certain stage.
daFrom1|__________| daTo1
daFrom2|_____| daTo2
daFrom3|______________| daTo3
^28 th day
daFromN|___________________| daToN

Query
SELECT tblAttendUA.CustomerID, tblAttendUA.AttendDescID, tblAttendUA.daFrom, tblAttendUA.daTo, DateDiff("d",[dafrom],[dato])+1 AS Dayz
FROM tblAttendUA
WHERE (((tblAttendUA.AttendDescID)=2))
order by daFrom

Result
CustomerID AttendDescID daFrom daTo Dayz Accumulated days Notes
10 2 1/07/2017 9/07/2017 23:59 9 9
10 2 10/07/2017 26/07/2017 23:59 17 26
10 2 27/07/2017 3/08/2017 23:59 8 34 28 th day on 29/7/17
10 2 19/08/2017 17/09/2017 23:59 30 64 36 days at lesser rate

Hopefully this explains the challenge better and show that I have made an effort to work through issues.. till reaching roadblock.
Regards
cwac60
Go to the top of the page
 
projecttoday
post Nov 11 2017, 07:00 AM
Post#7


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


What are the input data?

What are the results you're receiving?

What results do you want instead?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
cwac60
post Nov 11 2017, 11:58 PM
Post#8



Posts: 808
Joined: 1-April 09
From: Queensland Australia


Hi Robert,
The input data is as shown in the example except for the last 2 columns e.g.
9 days, using SQL between dates 1/07/2017 to 9/07/2017 23:59 17 26; I have annotated the last cumulative total (not SQL).
Likewise
1/07/2017 9/07/2017 10/07/2017 26/07/2017 23:59 : there are 17 days elapsed and 26 (9+17) accumulated

27/07/2017 3/08/2017 23:59 > 8 days and 34 (26+8) accumulated. Since 34 is greater than the 28 days, the date lies in this range.
This section to automate is:
Generate number of cumulative days
Test when it exceeds 28.
“Wind back” to derive date of 28 th day.
Regards
cwac60
Go to the top of the page
 
projecttoday
post Nov 12 2017, 01:10 AM
Post#9


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


If my understanding is correct, I'm thinking a Cartesian query. This would generate a record for each day. These records should be numbered. So the one with number 28 is the 28th day.

Let's see what others have to say.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
cwac60
post Nov 12 2017, 03:50 AM
Post#10



Posts: 808
Joined: 1-April 09
From: Queensland Australia


Thanks Robert
do you have any ideas how to generate one based on SQL I've provided?
Regards
cwac60
Go to the top of the page
 
projecttoday
post Nov 12 2017, 10:28 AM
Post#11


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


Have a look at this.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
MadPiet
post Nov 12 2017, 01:41 PM
Post#12



Posts: 2,225
Joined: 27-February 09



FWIW, that's what I was thinking... shame you can't create table-valued functions in Access. THen you could just explode the calendar table and add a ROW_NUMBER() to it and filter. =)
Go to the top of the page
 
MadPiet
post Nov 12 2017, 09:31 PM
Post#13



Posts: 2,225
Joined: 27-February 09



Okay, I'm a knucklehead. Allen Browne has an example of how to do a running total in Access. It's here. Not sure you need YTD, but the idea is the same.
Go to the top of the page
 
cwac60
post Nov 15 2017, 08:33 PM
Post#14



Posts: 808
Joined: 1-April 09
From: Queensland Australia


Hi Robert & MadPiet
Thank you for your suggestions.
I ended up wrapping the above in a outside query to amalgamate the days.
This was necessary because the sumplified query (9 Nov 17) given needed to contain unions to handle
various date range combinations.

SELECT CustomedId, SUM(Dayz)
FROM
(SELECT ....
UNION
SELECT ....
UNION
SELECT ....
UNION
SELECT .... ) as xyz

GROUP BY CustomedId

This took care of cumulative dates.

Calculating Consecutine days required use of DATEDIFF AND checking if any values exceeded 28 days and
number of residucl days ie September 30 - 2 days = 2.

Thanks again for input and suggestions.
Regards
cwac60
Go to the top of the page
 
projecttoday
post Nov 16 2017, 02:30 AM
Post#15


UtterAccess VIP
Posts: 8,604
Joined: 10-February 04
From: South Charleston, WV


Glad you got it working.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    20th November 2017 - 02:54 AM