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
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 BusinessDaysCalendar
WHERE 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?