daveyboy23
Board Regular
- Joined
- Jun 11, 2014
- Messages
- 59
Hi guys,
Hopefully someone can help.
Hopefully someone can help.
Function convert_date(ByVal date_val As Date) As Date
convert_date = CDate(CStr(CStr(Month(date_val)) + "/" + CStr(day(date_val)) + "/" + CStr(Year(date_val))))
End Function
' DB Productivity queries------------------------------------------------------------------------------------
Function qry_OFS_productivity(ByVal wc As Date, ByVal prod As String, ByVal stage As String) As String
qry_OFS_productivity = _
"SELECT tblQACheckDetails.RespondRef, tblQACheckDetails.QARespondID, tblQACheckDetails.DateOfQA " _
+ "FROM tblQACheckDetails INNER JOIN tblQACaseDetails ON ((tblQACheckDetails.Stage = tblQACaseDetails.Stage) AND (tblQACheckDetails.RespondRef = tblQACaseDetails.RespondRef)) " _
+ "WHERE (((tblQACheckDetails.DateOfQA)>=#" + CStr(convert_date(wc)) + "# And (tblQACheckDetails.DateOfQA)<#" + CStr(convert_date(wc + 7)) + "#)) " _
+ "AND ((tblQACheckDetails.CheckNumber)=1) " _
+ prod + stage + ";"
End Function
Function qry_CML_productivity(ByVal wc As Date, ByVal prod As String) As String
qry_CML_productivity = _
"SELECT tblCCInitialCheck.RespondRef, tblCCInitialCheck.CCRespondID, tblCCInitialCheck.CCDate " _
+ "FROM tblCCInitialCheck INNER JOIN tblCaseDetails ON tblCCInitialCheck.RespondRef=tblCaseDetails.RespondRef " _
+ "WHERE (((tblCCInitialCheck.CCDate)>=#" + CStr(convert_date(wc)) + "# And (tblCCInitialCheck.CCDate)<#" + CStr(convert_date(wc + 7)) + "#)) " _
+ prod + ";"
End Function
Function qry_CML_re_checks(ByVal wc As Date) As String
qry_CML_re_checks = _
"SELECT tblCCReCheck.RespondRef, tblCCReCheck.CCRespondID, tblCCReCheck.CCDate " _
+ "FROM tblCCReCheck " _
+ "WHERE (((tblCCReCheck.CCDate)>=#" + CStr(convert_date(wc)) + "# And (tblCCReCheck.CCDate)<#" + CStr(convert_date(wc + 7)) + "#));"
End Function
Function qry_set_ch_productivity(ByVal wc As Date) As String
qry_set_ch_productivity = _
"SELECT tblDone.Ref, tblDone.AllocatedTo, tblDone.DoneDate " _
+ "FROM tblDone " _
+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType) Like '*Perform Acceptance')) " _
+ "ORDER BY tblDone.Ref;"
'productivity from SAS
'"SELECT tblSetCaseDetails.RespondRef, tblSetCaseDetails.CHRespondID, tblSetCaseDetails.DateWorked " _
'+ "FROM tblSetCaseDetails " _
'+ "WHERE (((tblSetCaseDetails.DateWorked)>=#" + CStr(convert_date(wc)) + "# And (tblSetCaseDetails.DateWorked)<#" + CStr(convert_date(wc + 7)) + "#));"
End Function
Function qry_TEL_QA_productivity(ByVal wc As Date) As String
qry_TEL_QA_productivity = _
"SELECT tblTelQAChecks.CaseRef, tblTelQAChecks.QAName, tblTelQAChecks.QADate " _
+ "FROM tblStaffList INNER JOIN tblTelQAChecks ON tblStaffList.RespondID = tblTelQAChecks.QAName " _
+ "WHERE (((tblTelQAChecks.QADate)>=#" + CStr(convert_date(wc)) + "# And (tblTelQAChecks.QADate)<#" + CStr(convert_date(wc + 7)) + "#)) AND ((tblTelQAChecks.Stage)='FLQA');"
End Function
Function qry_ack_productivity(ByVal wc As Date) As String
qry_ack_productivity = _
"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone LEFT JOIN qry_Acknowledgement_Tasks_Done ON tblDone.Ref = qry_Acknowledgement_Tasks_Done.Ref " _
+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType)='[AUTO] Complaint Acknowledgement' Or (tblDone.TaskType)='[AUTO] PBR Acknowledgement' Or (tblDone.TaskType)='[AUTO] PPI Acknowledgement')) OR (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType)='PPI NCR Reject') AND ((qry_Acknowledgement_Tasks_Done.Ref) Is Null)) " _
+ "UNION ALL " _
+ "SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone INNER JOIN tblStaffList ON tblDone.DoneBy = tblStaffList.RespondID " _
+ "WHERE (((tblDone.TaskType) Like '*complex query ack*') AND ((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblStaffList.SubRole) Not Like '*tel*')) "
End Function
Function qry_offer_productivity(ByVal wc As Date) As String
qry_offer_productivity = _
"SELECT tblDone.Ref, tblDone.AllocatedTo, tblDone.DoneDate " _
+ "FROM (tblDone INNER JOIN qryData ON (tblDone.Ref = qryData.Ref) AND (tblDone.DoneDate = qryData.MaxOfDoneDate)) " _
+ "LEFT JOIN qryPT_Acknowledged ON tblDone.Ref = qryPT_Acknowledged.Ref " _
+ "WHERE (((tblDone.DoneDate) >= #" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate) < #" + CStr(convert_date(wc + 7)) + "#) And ((tblDone.TaskType) Like '*Final Letter') And ((IIf([tblDone].[AllocatedTo] = [qryPT_Acknowledged].[DoneBy], 'y', 'n')) = 'n' Or (IIf([tblDone].[AllocatedTo] = [qryPT_Acknowledged].[DoneBy], 'y', 'n')) Is Null)) " _
+ "ORDER BY tblDone.Ref;"
'Previous query
'"SELECT tblDone.Ref, Min(tblDone.AllocatedTo) AS MinOfAllocatedTo, Min(Int([tblDone.DoneDate])) AS [Date] " _
'+ "FROM tblDone LEFT JOIN qryPT_Acknowledged ON tblDone.Ref = qryPT_Acknowledged.Ref " _
'+ "WHERE (((tblDone.TaskType)='[AUTO] Final Letter') AND ((IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n'))='n' Or (IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n')) Is Null)) OR (((tblDone.TaskType)='[AUTO] PBR Final Letter') AND ((IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n'))='n' Or (IIf([tblDone].[AllocatedTo]=[qryPT_Acknowledged].[DoneBy],'y','n')) Is Null)) " _
'+ "GROUP BY tblDone.Ref " _
'+ "HAVING (((Min(Int([tblDone.DoneDate])))>=#" + CStr(convert_date(wc)) + "# And (Min(Int([tblDone.DoneDate])))<#" + CStr(convert_date(wc + 7)) + "#)) OR (((Min(Int([tblDone.DoneDate])))>=#" + CStr(convert_date(wc)) + "# And (Min(Int([tblDone.DoneDate])))<#" + CStr(convert_date(wc + 7)) + "#)); "
End Function
Function qry_set_qa_productivity(ByVal wc As Date, ByVal check As String) As String
qry_set_qa_productivity = _
"SELECT tblSetStageCheckA.RespondRef, tblSetStageCheckA.CheckerRespondID, tblSetStageCheckA.CheckDate " _
+ "FROM tblSetStageCheckA INNER JOIN tblSetInitialCheck ON (tblSetStageCheckA.RespondRef=tblSetInitialCheck.RespondRef AND tblSetStageCheckA.CaseInstance=tblSetInitialCheck.CaseInstance) " _
+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblSetStageCheckA.CheckNumber)=1)) " _
+ check + ";"
End Function
Function qry_SetReKey_productivity(ByVal wc As Date) As String
qry_SetReKey_productivity = _
"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone " _
+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblDone.TaskType)='Z - PPI Request Re-Key'));"
End Function
Function qry_DG_productivity(ByVal wc As Date) As String
qry_DG_productivity = _
"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
+ "FROM tblDone " _
+ "WHERE tblDone.Ref Not Like 'SLC*' AND (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND " _
+ "((tblDone.TaskType)='PPI Request Micrographics (New)' Or (tblDone.TaskType)='PPI DG Complete - Assessment Required' Or (tblDone.TaskType)='PPI DG Reject'));"
'Doesn't exclude SLC cases
'qry_DG_productivity = _
'"SELECT tblDone.Ref, tblDone.DoneBy, tblDone.DoneDate " _
'+ "FROM tblDone " _
'+ "WHERE (((tblDone.DoneDate)>=#" + CStr(convert_date(wc)) + "# And (tblDone.DoneDate)<#" + CStr(convert_date(wc + 7)) + "#) AND " _
'+ "((tblDone.TaskType)='PPI Request Micrographics (New)' Or (tblDone.TaskType)='PPI DG Complete - Assessment Required' Or (tblDone.TaskType)='PPI DG Reject'));"
End Function
Function qry_NCRBP_productivity(ByVal wc As Date) As String
qry_NCRBP_productivity = _
"SELECT tblCreated.Ref, tblCreated.CreatedBy, tblDone.DoneDate " _
+ "FROM tblCreated INNER JOIN tblDone ON (tblCreated.TaskNote = tblDone.TaskNote) AND (tblCreated.TaskType = tblDone.TaskType) AND (tblCreated.Ref = tblDone.Ref) " _
+ "WHERE ((tblCreated.CreatedDate)>=#" + CStr(convert_date(wc)) + "# And (tblCreated.CreatedDate)<#" + CStr(convert_date(wc + 7)) + "#) AND (((tblDone.TaskType)='Ad Hoc') AND (tblDone.TaskNote Like 'Blue Prism Request*' OR tblDone.TaskNote Like 'BP Request*') AND ((IIf([CreatedBy]=[DoneBy],'Y','N'))='N'));"
End Function
Function qry_NCRScan_productivity(ByVal wc As Date) As String
qry_NCRScan_productivity = _
"SELECT Ref, CreatedBy, CreatedDate " & _
"FROM tblCreated " & _
"WHERE TaskType='[AUTO] View New Correspondence' " & _
"AND ((tblCreated.CreatedDate)>=#" + CStr(convert_date(wc)) + "# And (tblCreated.CreatedDate)<#" + CStr(convert_date(wc + 7)) + "#);"
End Function
Function qry_SARS_productivity(ByVal wc As Date) As String
qry_SARS_productivity = _
"SELECT SARSKey, CHRespondID, CHLastModDate " & _
"FROM tblSARSData INNER JOIN tblStatuses ON tblSARSData.CHStatus=tblStatuses.Status " & _
"WHERE CHLastModDate>=#" + CStr(convert_date(wc)) + "# AND CHLastModDate<#" + CStr(convert_date(wc + 7)) + "# AND Complete=True;"
End Function
Function qry_SARS_QA_productivity(ByVal wc As Date) As String
qry_SARS_QA_productivity = _
"SELECT SARSKey, QARespondID, QADate " & _
"FROM tblSARSQAChecks " & _
"WHERE QADate>=#" + CStr(convert_date(wc)) + "# AND QADate<#" + CStr(convert_date(wc + 7)) + "#;"
End Function
' DB Productivity queries------------------------------------------------------------------------------------
' DB Quality queries-----------------------------------------------------------------------------------------
Function qry_OFS_quality(ByVal wc As Date, ByVal stage As Integer) As String
qry_OFS_quality = _
"SELECT tblQACaseDetails.RespondRef, tblQACaseDetails.CHRespondID, IIf([tblQACheckDetails]![OutcomeFail]=True,'Outcome',[tblQACheckDetails]![QAGrade]) " _
+ "FROM tblQACaseDetails INNER JOIN tblQACheckDetails ON ((tblQACaseDetails.RespondRef = tblQACheckDetails.RespondRef) AND (tblQACaseDetails.Stage = tblQACheckDetails.Stage)) " _
+ "WHERE (((tblQACheckDetails.DateOfQA)>=#" + CStr(convert_date(wc)) + "# And (tblQACheckDetails.DateOfQA)<#" + CStr(convert_date(wc + 7)) + "#)) " _
+ "AND ((tblQACheckDetails.CheckNumber)=1) AND tblQACheckDetails.Stage=" + CStr(stage) + ";"
End Function
Function qry_CML_quality(ByVal wc As Date, ByVal table As String) As String
qry_CML_quality = _
"SELECT tblCaseDetails.RespondRef, tblCaseDetails.CHRespondID, " + table + ".Agree " _
+ "FROM (tblCaseDetails INNER JOIN " + table + " ON tblCaseDetails.RespondRef = " + table + ".RespondRef) " _
+ "WHERE (((" + table + ".CCDate) >= #" + CStr(convert_date(wc)) + "# And (" + table + ".CCDate) < #" + CStr(convert_date(wc + 7)) + "#));"
End Function
Function qry_CPI_Logged(ByVal wc As Date, ByVal table As String) As String
qry_CPI_Logged = _
"sELECT" Ref, PA Product, Logged Date, Logged By " & _
"FROM
End Function
Function qry_SAS_quality(ByVal wc As Date, ByVal check As String) As String
qry_SAS_quality = _
"SELECT tblSetCaseDetails.RespondRef, tblSetStageCheckA.CHRespondID, iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) " _
+ "FROM (tblSetCaseDetails INNER JOIN tblSetInitialCheck ON (tblSetCaseDetails.RespondRef = tblSetInitialCheck.RespondRef) AND (tblSetCaseDetails.CaseInstance = tblSetInitialCheck.CaseInstance)) INNER JOIN tblSetStageCheckA ON (tblSetInitialCheck.RespondRef = tblSetStageCheckA.RespondRef) AND (tblSetInitialCheck.CaseInstance = tblSetStageCheckA.CaseInstance) " _
+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) " _
+ "AND ((tblSetInitialCheck.CheckType)" + check + ") AND ((tblSetStageCheckA.CheckNumber)=1)) " _
+ "ORDER BY iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) ASC;"
'old version with CH Respond ID coming from SetCaseDetails table
'qry_SAS_quality = _
'"SELECT tblSetCaseDetails.RespondRef, tblSetCaseDetails.CHRespondID, iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) " _
'+ "FROM (tblSetCaseDetails INNER JOIN tblSetInitialCheck ON (tblSetCaseDetails.RespondRef = tblSetInitialCheck.RespondRef) AND (tblSetCaseDetails.CaseInstance = tblSetInitialCheck.CaseInstance)) INNER JOIN tblSetStageCheckA ON (tblSetInitialCheck.RespondRef = tblSetStageCheckA.RespondRef) AND (tblSetInitialCheck.CaseInstance = tblSetStageCheckA.CaseInstance) " _
'+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) " _
'+ "AND ((tblSetInitialCheck.CheckType)" + check + ") AND ((tblSetStageCheckA.CheckNumber)=1)) " _
'+ "ORDER BY iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome) ASC;"
'"SELECT tblSetCaseDetails.RespondRef, First(tblSetCaseDetails.CHRespondID), First(iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome)) " _
'+ "FROM (tblSetCaseDetails INNER JOIN tblSetInitialCheck ON (tblSetCaseDetails.RespondRef = tblSetInitialCheck.RespondRef) AND (tblSetCaseDetails.CaseInstance = tblSetInitialCheck.CaseInstance)) INNER JOIN tblSetStageCheckA ON (tblSetInitialCheck.RespondRef = tblSetStageCheckA.RespondRef) AND (tblSetInitialCheck.CaseInstance = tblSetStageCheckA.CaseInstance) " _
'+ "WHERE (((tblSetStageCheckA.CheckDate)>=#" + CStr(convert_date(wc)) + "# And (tblSetStageCheckA.CheckDate)<#" + CStr(convert_date(wc + 7)) + "#) " _
'+ "AND ((tblSetInitialCheck.CheckType)" + check + ") AND ((tblSetStageCheckA.CheckNumber)=1)) " _
'+ "GROUP BY tblSetCaseDetails.RespondRef " _
'+ "ORDER BY First(iif(tblSetStageCheckA.OutcomeFail=true,'_Outcome',tblSetStageCheckA.CheckOutcome)) ASC;"
End Function
Function qry_cc_flqa(ByVal wc As Date) As String
qry_cc_flqa = _
"SELECT tblCCInitialCheck.RespondRef, tblCCInitialCheck.CCRespondID, " _
+ "IIF([tblQACheckDetails]![CCPWL]=true,'PWL',IIF([tblQACheckDetails]![CCCalcFail]=true,'Outcome',IIF([tblQACheckDetails]![CCEvidenceFail]=true,'Fail','Pass'))) AS CCGrade " _
+ "FROM tblCCInitialCheck INNER JOIN tblQACheckDetails ON tblCCInitialCheck.RespondRef = tblQACheckDetails.RespondRef " _
+ "WHERE (((tblQACheckDetails.DateOfQA)>=#" + CStr(convert_date(wc)) + "# And (tblQACheckDetails.DateOfQA)<#" + CStr(convert_date(wc + 7)) + "#)) AND ((tblQACheckDetails.CheckNumber)=1) AND tblQACheckDetails.Stage=3;"
End Function
Function qry_TEL_CH_quality(ByVal wc As Date) As String
qry_TEL_CH_quality = _
"SELECT tblTelQAChecks.CaseRef, tblTelQAChecks.CHName, IIf([QAOutcome]='Fail (QC)','Fail',IIf([QAOutcome]='Fail (Out)','Outcome',[QAOutcome])) AS Grade " _
+ "FROM tblStaffList AS tblStaffList_1 INNER JOIN (tblStaffList INNER JOIN tblTelQAChecks ON tblStaffList.RespondID = tblTelQAChecks.CHName) ON tblStaffList_1.RespondID = tblTelQAChecks.QAName " _
+ "WHERE (((tblTelQAChecks.QADate)>=#" + CStr(convert_date(wc)) + "# And (tblTelQAChecks.QADate)<#" + CStr(convert_date(wc + 7)) + "#) AND ((tblTelQAChecks.Stage)='FLQA'));"
End Function
Function qry_SARS_quality(ByVal wc As Date) As String
qry_SARS_quality = _
"SELECT tblSARSQAChecks.SARSKey, tblSARSData.CHRespondID, tblSARSQAChecks.QAOutcome " & _
"FROM tblSARSQAChecks " & _
"INNER JOIN tblSARSData ON tblSARSQAChecks.SARSKey=tblSARSData.SARSKey " & _
"WHERE tblSARSQAChecks.QADate>=#" + CStr(convert_date(wc)) + "# " & _
"AND tblSARSQAChecks.QADate<#" + CStr(convert_date(wc + 7)) + "# " & _
"AND tblSARSQAChecks.Check=1;"
End Function
' DB Quality queries-----------------------------------------------------------------------------------------
Function nlookup(ByRef sht As Worksheet, ByVal val As String, ByVal c_val As Integer, ByVal c_ret As Integer) As Single
On Error GoTo errCap
Dim r As Integer: r = 1
With sht
Do Until .Cells(r, c_val).Value = 0
If UCase(CStr(.Cells(r, c_val).Value)) = UCase(val) Then
nlookup = .Cells(r, c_val + c_ret - 1).Value
Exit Function
End If
r = r + 1
Loop
End With
errCap:
Exit Function
MsgBox (Err.Description)
MsgBox ("Error occured in Sheet: " + CStr(sht.Name) + " at Row: " + CStr(r) + " and Column: " + CStr(c_val + c_ret - 1))
End Function
Public Function SumNLookups(ByRef sht As Worksheet, ByVal val As String, ByVal numberofweeks As Integer, ReturnColumn) As Single
Dim i As Integer
For i = 1 To numberofweeks
SumNLookups = SumNLookups + nlookup(sht, val, ((i - 1) * 9) + 1, ReturnColumn)
Next i
End Function
Function qry_CPI_Logged(ByVal wc As Date, ByVal table As String) As String
qry_CPI_Logged = _
"sELECT" Ref, PA Product, Logged Date, Logged By " & _
"FROM
End Function