Hello guys,
I need help in calculating number of working days between 2 dates with a little twist...
I have a Job Tracking database wherein a record (a loan/work we do) in the database goes through a different phases/status to track status of the record (Loan received, In Process, Ready for Review, Sent to Client etc)
For a quick background here is my table structure (that relates to my question):
tblJobTracking:
SitusID -- PK
WeekNumber
DealName
AssetType
PropertyCount
Etc (other fields)
tblDealStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst -- user who is assigned for the record/deal
StatusChangeID -- FK to tblStatusChange
StatusDate -- Date
StatusHours -- how many hours did the user worked on that specific status
tblStatusChange
StatusChangeID -- PK
Status -- different status/phase (Unassigned, Received, In Process etc)
tblStatusChange:
StatusChangeID Status
1 Unassigned (also when we received the deal)
2 In Process
3 Ready for Initial Review
4 Initial Review
5 Sent Back for Corrections
6 Ready for Final Review
7 Final Review
8 Sent back to Client
9 On Hold
10 Dropped
We are now trying to create a query that will calculate how many days did a deal/record take before it was sent back to the client
What I have so far is I created these queries:
qryDateReceived
qryDateSent
then I researched for a module in the internet to calculate how many days excluding weekends...
then I created another query:
qryDifference
It's a little weird that there are right and wrong results from qryDifference.
sample output:
SitusID DateReceived.StatusDate DateSent.StatusDate WkDays
1 9/10/2010 9/21/2010 8
2 9/10/2010 9/16/2010 5
9 9/10/2010 9/16/2010 0
27 9/14/2010 9/15/2010 2
33 9/15/2010 9/15/2010 1
based on the sample output above, SitusID 2 and 9 have the same Received and Sent date, it however returns a different WkDay number?
Also, with SitusID 33, it returns WkDays = 1, but is there a way that if we received and sent back a deal within the same day, it'll return WkDays = Same Day, and with SitusID WkDays = 1?
I could pretty much change the column expression to:
WkDays: DateDiffExclude2([DateReceived].[StatusDate],[DateSent].[StatusDate],"17")-1
But I am still having problems with SitusID 9 giving WkDays = 0 and if a deal has been received and sent back at the same day, WkDays = Same Day.
Please... any help is greatly appreciated.
Thank you...
I need help in calculating number of working days between 2 dates with a little twist...
I have a Job Tracking database wherein a record (a loan/work we do) in the database goes through a different phases/status to track status of the record (Loan received, In Process, Ready for Review, Sent to Client etc)
For a quick background here is my table structure (that relates to my question):
tblJobTracking:
SitusID -- PK
WeekNumber
DealName
AssetType
PropertyCount
Etc (other fields)
tblDealStatus
DealStatusID -- PK
SitusID -- FK to tblJobTracking
Analyst -- user who is assigned for the record/deal
StatusChangeID -- FK to tblStatusChange
StatusDate -- Date
StatusHours -- how many hours did the user worked on that specific status
tblStatusChange
StatusChangeID -- PK
Status -- different status/phase (Unassigned, Received, In Process etc)
tblStatusChange:
StatusChangeID Status
1 Unassigned (also when we received the deal)
2 In Process
3 Ready for Initial Review
4 Initial Review
5 Sent Back for Corrections
6 Ready for Final Review
7 Final Review
8 Sent back to Client
9 On Hold
10 Dropped
We are now trying to create a query that will calculate how many days did a deal/record take before it was sent back to the client
What I have so far is I created these queries:
qryDateReceived
Code:
SELECT tblDealStatus.SitusID, tblDealStatus.StatusChangeID, tblDealStatus.StatusDate
FROM tblDealStatus
WHERE (((tblDealStatus.StatusChangeID)=1))
ORDER BY tblDealStatus.SitusID;
qryDateSent
Code:
SELECT tblDealStatus.SitusID, tblDealStatus.StatusChangeID, tblDealStatus.StatusDate
FROM tblDealStatus
WHERE (((tblDealStatus.StatusChangeID)=8))
ORDER BY tblDealStatus.SitusID;
then I researched for a module in the internet to calculate how many days excluding weekends...
Code:
Option Compare Database
Option Explicit
Function DateDiffExclude2(pstartdte As Date, _
penddte As Date, _
pexclude As String) As Integer
'*********************************************
'Purpose: Return number of days between two
' dates, excluding weekdays(Sun = 1
' thru Sat = 7) specified by user
'Coded by: raskew
'Inputs: from debug (immediate) window:
' -excludes Saturday (7) and Sunday (1) from count
' ? DateDiffExclude2(#2/10/06#, #7/13/06#, "17")
'Output: 110
'*********************************************
Dim WeekHold As String
Dim WeekKeep As String
Dim FullWeek As Integer
Dim OddDays As Integer
Dim n As Integer
WeekHold = "1234567123456"
'get # of full weeks (7 days) & convert to # of days
FullWeek = Int((penddte - pstartdte + 1) / 7) * (7 - Len(pexclude))
'get # of days remaining after FullWeek is determined
OddDays = (penddte - pstartdte + 1) Mod 7
'string representation of the weekdays contained in OddDays
WeekKeep = Mid(WeekHold, Weekday(pstartdte), OddDays)
'use boolean statement to reduce OddDays by 1 for each
'pexclude weekday found in WeekKeep
For n = 1 To Len(pexclude)
OddDays = OddDays + (InStr(WeekKeep, Mid(pexclude, n, 1)) > 0)
Next n
DateDiffExclude2 = FullWeek + OddDays
End Function
then I created another query:
qryDifference
Code:
SELECT qryDateReceived.SitusID, qryDateReceived.StatusDate, qryDateSent.StatusDate, qryDateDiffExclude2([qryDateReceived].StatusDate,[qryDateSent].StatusDate,"17") AS WkDays
FROM qryDateReceived INNER JOIN qryDateSent ON qryDateReceived.SitusID = qryDateSent.SitusID;
It's a little weird that there are right and wrong results from qryDifference.
sample output:
SitusID DateReceived.StatusDate DateSent.StatusDate WkDays
1 9/10/2010 9/21/2010 8
2 9/10/2010 9/16/2010 5
9 9/10/2010 9/16/2010 0
27 9/14/2010 9/15/2010 2
33 9/15/2010 9/15/2010 1
based on the sample output above, SitusID 2 and 9 have the same Received and Sent date, it however returns a different WkDay number?
Also, with SitusID 33, it returns WkDays = 1, but is there a way that if we received and sent back a deal within the same day, it'll return WkDays = Same Day, and with SitusID WkDays = 1?
I could pretty much change the column expression to:
WkDays: DateDiffExclude2([DateReceived].[StatusDate],[DateSent].[StatusDate],"17")-1
But I am still having problems with SitusID 9 giving WkDays = 0 and if a deal has been received and sent back at the same day, WkDays = Same Day.
Please... any help is greatly appreciated.
Thank you...