Forum Discussion
DateDIFF and a Where Clause
Tony2021 , you replace:
..
Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate],[LastBdMo])) AS DayCountDD
with:
Abs(DateDiff("d",[qryDrawsDecliningCumDrawn].[FundingDate], fncLastBusinessDayThisMo([qryDrawsDecliningCumDrawn].[FundingDate])) As DayCountDD
Also change the function to this:
Public Function fncLastBusinessDayThisMo(ByVal FundingDate As Date) As Date Static dte_static As Date Static dte_ans As Date Dim dte As Date dte = DateSerial(Year([FundingDate]), Month([FundingDate]) + 1, 0) If dte = dte_static Then fncLastBusinessDayThisMo = dte_ans Exit Function End If dte_static = dte Do Until InStr(1, "Sat/Sun", Format$(dte, "ddd")) = 0 dte = DateAdd("d", -1, dte) Loop dte_ans = dte fncLastBusinessDayThisMo = dte End Function
Are you trying to return the date difference over a subset of rows? If so you can do this by means of a correlated subquery. The following is an example, using the Orders table from Northwind, which returns the number of days from the date of each customer's first order to the date of their current order:
SELECT CustomerID, OrderDate,
INT(OrderDate) - (SELECT INT(MIN(OrderDate))
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID) AS DayCount
FROM Orders AS O1
ORDER BY CustomerID, OrderDate DESC;
Note that the INT function is used here because the OrderDate values include non-zero times of day. Assuming that you don't want the returned DayCount values to include fractions of a day, the subtraction uses integer values. If the values were to be constrained to integers (date only) by means of the column's ValidatioRule property, the INT function would be unnecessary of course. Alternatively the DateDiff function could be used to ignore the fractional part of the DateTime data type values.
If, on the other hand, you want to return the date difference between two values of DateTime data type in a single row, then you can use a simple DLookup function call:
DayCount:DLookup("Abs(FundingDate - LastBdMo)","TableNameGoesHere","TypeIDfk = " & [TypeIDfk])
Hi Ken, thank you.
To quickly answer your question, yes it is a SUBSET of rows (not a single row).
I am not exactly sure how to use the select query.
The LastBdMo uses a function like this:
LastBdNxtMo: fncLastBusinessDay([qryDrawsDecliningCumDrawn].[FundingDate])
noting that [FundingDate] is in tblDraws.
this is in my QryA
This is how I modified it (I get a syntax. pic below):
in the below Type is an ID. I should have named it TypeIDfk but I did not.
Not sure if I can use the function like I am.
SELECT ID, FundingDate, Type
INT(FundingDate) - (SELECT INT(MIN(fncLastBusinessDay(FundingDate))
FROM tblDraws AS O2
WHERE O2.Type = O1.Type) AS DayCount
FROM tblDraws AS O1
ORDER BY ID, FundingDate DESC;
here is the syntax (I assume its a paren and I tried to add a paren in a few places to no avail):
I do see quite possibly another issue. I would need to consider another criteria for tblDraws.FundingDate< tblDraws.FundingDate so essentially having 2 criteria instead of only Type = Type
I do realize this is difficult without the database in front of you. Apologies for that.
thank you for the help! I greatly appreciate it. If I had to do it all over again I would have chosen the computer route instead of finance. Its amazing the level of experience you guys have.
A subquery would remove the need to call the fncLastBusinessDay function. To do it all in SQL by means of a subquery, you'd probably need an auxiliary BusinessDaysCalendar table. Auxiliary tables can be extremely useful for many things (Joe Celko has a whole chapter on them in his excellent book 'SQL for Smarties' – full disclosure, I'm cited in it). In this case you'd use one along the following lines:
SELECT ID, FundingDate, [Type],
FundingDate - (SELECT MAX(BusinessDay)
FROM BusinessDaysCalendarWHERE BusinessDaysCalendar.[Type] = tblDraws.[Type]) AS DayCount
FROM tblDraws
ORDER BY ID, FundingDate DESC;Hi Ken,
Sorry but I mispoke. The function is simply returning the LAST business day of the month meaning if the last day is a Saturday then make it Friday (not considering any holiday....just last business day). I am looking for the count from the FundingDate to the last business day of the month but considering the [Type] criteria.
Does that change your response?
