<!--#include file="dbconn.asp"-->
<script Language="VBScript">
sub fun_excel2()
RegionID = selRegionFilter.value
if ViewActive.checked = "True" then
msgbox("view active checked regionid" & ViewActive.checked & RegionID)
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE (((employees.RegionID)="& RegionID & ") AND ((employees.Active)=True))ORDER BY employees.LastName, employees.FirstName;"
if RegionID=12 then
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE ( ((employees.Active)=True))ORDER BY employees.LastName, employees.FirstName;"
'msgbox("am i trigger? for all checked")
end if
else
msgbox("view active No checked regionid" & ViewActive.checked & RegionID)
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE (((employees.RegionID)="& RegionID & "))ORDER BY employees.LastName, employees.FirstName;"
if RegionID=12 then
sqlstring = "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees ORDER BY employees.LastName, employees.FirstName;"
'msgbox("am i trigger? for all unchecked")
end if
end if
ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
strDivision = "<%=session("Division")%>"
if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb",sqlstring )
else
set rs = df.ReturnRs("skillset_DT97_db.mdb",sqlstring )
end if
' test.innerHTML="SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees WHERE (((employees.RegionID)="& RegionID & ")) ORDER BY employees.LastName, employees.FirstName;"
selEmployee.Options.length =0
Do While Not rs.EOF
Set objOption = Document.createElement("OPTION")
objOption.text = rs("name")
objOption.value = rs("id")
rs.MoveNext
j = j + 1
selEmployee.Add(objOption)
Loop
end sub
sub fun_excel()
Dim rds,rs,df,ServerStr
dim courseID
dim sessionID
EmployeeID=selEmployee.value
'msgbox(selEmployee.value)
if selEmployee.value <>"" then
EmployeeID = selEmployee.value
else
EmployeeID = 0
end if
ActiveEmployee.checked = "false"
ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
strDivision = "<%=session("Division")%>"
if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","SELECT Employees.FirstName, Employees.LastName, Employees.Password, Employees.AsiaName, Employees.Role as Role, Employees.RegionID as Region , Employees.Email as Email ,Active,EmployeeNumber FROM Employees WHERE (((Employees.ID)=" & employeeID &"));" )
else
set rs = df.ReturnRs("skillset_DT97_db.mdb","SELECT Employees.FirstName, Employees.LastName, Employees.Password, Employees.AsiaName, Employees.Role as Role, Employees.RegionID as Region ,Employees.Email as Email,Active,EmployeeNumber FROM Employees WHERE (((Employees.ID)=" & employeeID &"));" )
end if
'test.innerHTML= "SELECT Employees.EmployeeNumber, Employees.FirstName, Employees.LastName, Employees.Password, Role AS Role, Regions as Region, Employees.AsiaName FROM (Employees INNER JOIN Regions ON Employees.RegionID = Regions.ID) INNER JOIN Roles ON Employees.Role = Roles.ID WHERE (((Employees.ID)=" & employeeID &"));"
'Do While Not rs.EOF
' Set objOption = Document.createElement("OPTION")
' objOption.text = rs("name")
' objOption.value = rs("id")
' rs.MoveNext
' j = j + 1
' selCurrilum.Add(objOption)
'Loop
'set rs=nothing
inputEmployeeNumber.value = rs("EmployeeNumber")
inputFirstName.value = rs("FirstName")
inputLastName.value = rs("LastName")
inputEmployeeNumber.value = rs("EmployeeNumber")
if (rs("Active"))= true then ActiveEmployee.checked = "true" end if
if len((rs("Email")))>0 then
inputEmail.value = rs("Email")
else
inputEmail.value=""
end if
'AsiaName.value = rs("AsiaName")
'Password.value = rs("Password")
SelRole.value = rs("Role")
SelRegion.value = rs("Region")
'msgbox(inputEmployeeNumber.value)
end sub
sub inactive()
'msgbox(inputEmployeeNumber.value )
ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
strDivision = "<%=session("Division")%>"
if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","select employees.Active from employees WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
else
set rs = df.ReturnRs("skillset_DT97_db.mdb","select employees.Active from employees WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
end if
if (rs("Active")= true) then
if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","UPDATE employees SET employees.Active = false WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
else
set rs = df.ReturnRs("skillset_DT97_db.mdb","UPDATE employees SET employees.Active = false WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
end if
else
if strDivision="WB" then
set rs = df.ReturnRs("skillset_DT97.mdb","UPDATE employees SET employees.Active = true WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
else
set rs = df.ReturnRs("skillset_DT97_db.mdb","UPDATE employees SET employees.Active = true WHERE (((employees.EmployeeNumber)=" & inputEmployeeNumber.value & "));" )
end if
end if
end sub
sub add()
dim FirstName,rsTemp,TempNumber
FirstName = inputFirstname.value
LastName = inputLastname.value
Email = inputEmail.value
if Email = "" then Email = " " end if
'strPassword = inputPassword.value
'msgbox Email & strPassword
Region = SelRegion.value
Role = Selrole.value
' EmployeeNumber=inputEmployeeNumber.value
'msgbox "insert into Employee ( EmployeeNumber,LastName, FirstName, Role ,RegionID) VALUES ("&EmployeeNumber&", "&LastName&", " & FirstName &", " & "Region" & ", " & Role )"
ServerStr = "http://<%=Request.ServerVariables("SERVER_NAME")%>"
set rds = CreateObject("RDS.Dataspace")
Set df = rds.CreateObject("iacRDSObjAccess.rsop",ServerStr)
on error resume next
'msgbox "insert into Attendees ( SessionID, AttendeeID) VALUES ("&sessionID&", "&AttendeeID&")"
'msgbox("insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role ) VALUES ("&EmployeeNumber&", "&LastName&", " & FirstName &", " & Region & ", " & Role & ");" )
strDivision = "<%=session("Division")%>"
if strDivision="WB" then
'df.ReturnRs "skillset_DT97.mdb", "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Password ) VALUES ("&EmployeeNumber&", '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "','" & strPassword &"');"
set rsTemp = df.ReturnRs("skillset_DT97.mdb","SELECT Max(EmployeeNumber)+1 AS nextEmployeeNu FROM Employees;")
df.ReturnRs "skillset_DT97.mdb", "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Active) VALUES ( " & rsTemp("nextEmployeeNu") & " , '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "', Yes);"
'df.ReturnRs "skillset_DT97.mdb", "insert into Employees ( LastName, FirstName, RegionID,Role,Email) VALUES ('" & LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "');"
'msgbox "insert into Employees ( LastName, FirstName, RegionID,Role,Email) VALUES ('" & LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "');"
else
set rsTemp = df.ReturnRs("skillset_DT97_db.mdb","SELECT Max(EmployeeNumber)+1 AS nextEmployeeNu FROM Employees;")
df.ReturnRs "skillset_DT97_db.mdb", "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Active) VALUES ( " & rsTemp("nextEmployeeNu") & " , '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "',Yes);"
'df.ReturnRs "skillset_DT97_db.mdb", "insert into Employees ( LastName, FirstName, RegionID,Role,Email ) VALUES ( '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "');"
end if
test.innerHTML= "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email) VALUES ( " & rsTemp("nextEmployeeNu") & " , '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "');"
'test.innerHTML= "insert into Employees ( EmployeeNumber,LastName, FirstName, RegionID,Role,Email,Password ) VALUES ("&EmployeeNumber&", '"&LastName&"', '" & FirstName &"', " & Region & ", " & Role & ",'" & Email & "','" & strPassword &"');"
msgbox("FSE Data added" )
'window.location.reload true
'fun_excel
exit sub
end sub
sub closewindow()
window.close
end sub
</script>
<%
On Error Resume Next
Set Conn = Server.CreateObject("ADODB.Connection")
Set Cmd = Server.CreateObject("ADODB.Command")
Set RSRegion = Server.CreateObject("ADODB.Recordset")
Set RSRole = Server.CreateObject("ADODB.Recordset")
Set RS = Server.CreateObject("ADODB.Recordset")
Set RSSession = Server.CreateObject("ADODB.Recordset")
conn.Open session("connectstring")
RS.open "SELECT employees.id as id, employees.lastname + ', ' +employees.firstname AS name FROM employees ORDER BY employees.LastName, employees.FirstName;",conn,1,3
RSRole.open "SELECT Roles.ID, Roles.Name FROM Roles ORDER BY Roles.ID;",conn,1,3
RSRegion.open "SELECT Regions.ID, Regions.Name from Regions;",conn,1,3
if Err.Number<> 0 then
response.write err.description
response.write ("The database has encountered an error while trying to access.")
response.end
end if
%>
<html>
<head>
<link href="styles.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.barItem {
cursor: hand;
color: #666666;
font-size: 14;
font-family: Arial, Helvetica, sans-serif;
line-height: 42px;
}
.barItem:hover {
cursor: hand;
color: #C84777;
font-size: 14
}
a. { cursor: hand; font-size: 12; color: navy; text-decoration: none }
a:active {
cursor: hand;
color: #666666
}
a.cc:hover {
cursor: hand;
color: #333333;
text-decoration: underline
}
.box {
position: absolute
}
-->
</style>
<link href="SpryAssets/SpryMenuBarHorizontal.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
-->
</style>
</head>
<body scroll="yes">
<div name="test" id = "test"> </div>
<table width="100%" border="0" cellspacing="0" cellpadding="10">
<tr>
<td width="38%" align="left" valign="top">
<!--
<span class="style3">Employee Number: </span>
-->
<span class="style3">Region: </span>
<select name="selRegionFilter" onchange="fun_excel2" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:200px;" >
<option value=12 >All </option>
<option value=1 >Americas </option>
<option value=2 >Europe </option>
<option value=3 >Singapore </option>
<option value=4 >China </option>
<option value=5 >Japan </option>
<option value=6 >Korea </option>
<option value=7 >Thailand </option>
<option value=9 >Malaysia </option>
<option value=10 >Philippines </option>
<option value=11 >Taiwan </option>
</select>
<INPUT TYPE=CHECKBOX NAME="ViewActive" onClick="fun_excel2"><span class="style3">View Active Only</span>
<br>
<input name="inputEmployeeNumber" TYPE="HIDDEN" id="inputEmployeeNumber" />
</br><span class="style3">First Name: </span>
<input name="inputFirstName" type="text" id="inputFirstName" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
</br><span class="style3">Last Name: </span>
<input name="inputLastName" type="text" id="inputLastName" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
<INPUT TYPE=CHECKBOX NAME="ActiveEmployee"><span class="style3">Active</span>
</br><span class="style3">Email: </span>
<input name="inputEmail" type="text" id="inputEmail" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
</br><span class="style3">Role: </span>
<select name="selRole" type="text" id="selRole" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
<option value =0> </option>
<%
while not RSRole.EOF
Response.Write ("<option ")
Response.write("value=" & RSRole(0) & ">")
Response.Write (RSRole(1))
Response.Write ("</option>")
RSRole.MoveNext
wend
%>
</select>
</br> <span class="style3">Region: </span>
<select name="SelRegion" type="text" id="SelRegion" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:300px;" />
<option value =0> </option>
<%
while not RSRegion.EOF
Response.Write ("<option ")
Response.write("value=" & RSRegion(0) & ">")
Response.Write (RSRegion(1))
Response.Write ("</option>")
RSRegion.MoveNext
wend
%>
</select>
<input name="buttonNew" type="button" id="buttonNew" onClick="add" value=" New "/>
<input name="buttonDelete" type="button" id="buttonDelete" onClick="closeWindow" value=" close "/>
<input name="buttonInactive" type="button" id="buttonInactive" onClick="inactive" value=" Inactive/Active "/>
</span>
<br>
</p></td>
<td width="52%" align="left" valign="top">
<span class="style3">
Employees:</span><br>
<select id="selEmployee" name="selEmployee" onchange="fun_excel" size="7" style="color:Black;border-color:Silver;border-style:Solid;font-family:Arial;font-size:13px;width:450px;">
<%
while not RS.EOF
Response.Write ("<option ")
Response.write("value=" & RS(0) & ">")
Response.Write (RS(1))
Response.Write ("</option>")
RS.MoveNext
wend
%>
</select>
</td>
</tr>
<tr>
<td width="88%" align="left" valign="top">
<table width="200" border="0">
<tr> <p></p>
<td>
</p></td>
</tr>
</table> </td>
<td width="2%" align="left" valign="middle">
<table width="2%" border="0">
</table> </td>
<td width="10%"></td>
</tr>
</table>
</body>
</html>
List of the references that you must set when you work with Access 2000 or Access 97 database
http://support.microsoft.com/kb/197110/en-ushttp://72.14.205.104/search?q=cache:jb1HP--JlQMJ:bbs.flash2u.com.tw/FlashTopic18622_148_8_1.htm+access+%E8%A8%BB%E8%A7%A3&hl=en&gl=us&ct=clnk&cd=4&lr=lang_zh-TW***將活動日期以月月/年年年年顯示Format([ActivityTime],"mm\/yyyy")
***欄位與欄位的字串相加[Month] & "/" & [Year]
***取出參照日期的年及月Year([RefDate]) & "/" & Month([refdate])
*** 取出輸入日期enterdon及時間enteredAt_1, 再找出第一次回覆的時間,
找出經過了多少小時,再透過avg 取平均值Avg(DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime]))
*** 取出輸入日期enterdon及時間enteredAt_1, 再找出第一次回覆的時間,
找出經過了多少小時,再透過max 取最大值Max(DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime]))
*** 取出輸入日期enterdon及時間enteredAt_1, 再找出第一次回覆的時間,
找出經過了多少小時,再透過min 取最小值Min(DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime]))
*** 特定table 的特定欄位 [f_activity_local].[Initial State]
*** 將得到的時間顯示為 2006/05/10 Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00")
WHERE
***時間判斷(((Issue_record_local.IssueDate)>#7/31/2005#)
***字串判斷((ProcessedActivities.[Initial State])="New")
***字串判斷((Issue_record_local.ModifiedBy_1)="Boon Kheong Tiah")
***空值判斷((testIssues.issuenumber) Is Null))
***模糊判斷((Issue_record_memo_local.IssueNumber) Like [Issue_record_local].[issuenumber] & "*"))
*** group byGROUP BY Format([ActivityTime],"mm\/yyyy")
*** order byORDER BY Format([ActivityTime],"mm\/yyyy");
----------sub query
***判斷是否在今天之前,或在4/6/1997以後>#4/6/1997# And <Date()
***判斷CountOfIssueNumber是否為空值如果是,就給0,如果不是,就顯示CountOfIssueNumber
IIf(IsNull([subqClosedIssueCount].[CountOfIssueNumber]),0,[subqClosedIssueCount].[CountOfIssueNumber])
***找出新的issue數目,以輸入日來判斷當天此issue為新的issue,以count來計算資料比數SELECT Issue_record_local.EnteredOn, Count(Issue_record_local.IssueNumber) AS CountOfIssueNumber
FROM Issue_record_local
GROUP BY Issue_record_local.EnteredOn
ORDER BY Issue_record_local.EnteredOn;
***透過判斷DateClosed 是否為空值,以count來計算資料比數,以結案日來groupSELECT Issue_record_local.DateClosed, Count(Issue_record_local.IssueNumber) AS CountOfIssueNumber
FROM Issue_record_local
GROUP BY Issue_record_local.DateClosed
HAVING (((Issue_record_local.DateClosed) Is Not Null))
ORDER BY Issue_record_local.DateClosed;
***調整ActivityTime時間格式為yyyy/mm/dd,再作group跟計算資料比數SELECT Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00") AS Expr1, Count(f_activity_local.ActivityIssueNumber) AS CountOfActivityIssueNumber
FROM f_activity_local
WHERE (((f_activity_local.ActivityTime)>#5/1/2006#) AND ((f_activity_local.ActivityOwner)="xxxx"))
GROUP BY Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00")
ORDER BY Year([ActivityTime]) & "/" & Format(Month([ActivityTime]),"00") & "/" & Format(Day([ActivityTime]),"00");
***兩個先去掉null再相減是也IIf(IsNull([subqClosedIssueCountWitsTeam].[CountOfIssueNumber]),0,[subqClosedIssueCountWitsTeam].[CountOfIssueNumber])
-IIf(IsNull([subqNewIssueCountWitsTeam].[CountOfIssueNumber]),0,[subqNewIssueCountWitsTeam].[CountOfIssueNumber])
***找出模組為ultra,但是record type 並非為enhancement及evalSELECT Issue_record_local.DateClosed, Count(Issue_record_local.IssueNumber) AS CountOfIssueNumber
FROM Issue_record_local
WHERE (((Issue_record_local.Type) Not Like "*enhancement*" And
(Issue_record_local.Type) Not Like "*eval*") AND
((Issue_record_local.ModelNumber) Like "ultra"))
GROUP BY Issue_record_local.DateClosed
HAVING (((Issue_record_local.DateClosed) Is Not Null))
ORDER BY Issue_record_local.DateClosed;
UPDATE Skills
SET Skills.BackupOldName =Skills.Name
***找出現在還沒結案的(open)的issueSELECT Issue_Record.IssueNumber, Issue_Record.Status, Issue_Record.EnteredOn, Issue_Record.LastActivityDate, Issue_Record.Severity
FROM Issue_Record
WHERE (((Issue_Record.Status)<>"*closed*"));
***找出subquerySELECT Month([IssueDate]) & "/" & Year([IssueDate]) AS Month,
Sum(qryDailyCountNewIssues.CountOfIssueNumber) AS SumOfCountOfIssueNumber,
Avg(qryDailyCountNewIssues.CountOfIssueNumber) AS AvgOfCountOfIssueNumber
FROM qryDailyCountNewIssues
GROUP BY Month([IssueDate]) & "/" & Year([IssueDate]), Year([IssueDate]), Month([IssueDate]);
***找出一個issue經歷了多少時間,從tblElapsedTime去除以24求出幾天,除以30求出幾個月SELECT tblElapsedTime.IssueNumber, tblElapsedTime.State, [ElapsedTime]/24 AS Days, tblElapsedTime.DateIn, StateIndex.Index, Int([ElapsedTime]/24/30) AS Period, Issue_record_local.Owner, Issue_record_local.Status
FROM (tblElapsedTime LEFT JOIN StateIndex ON tblElapsedTime.State = StateIndex.Name) INNER JOIN Issue_record_local ON tblElapsedTime.IssueNumber = Issue_record_local.IssueNumber
WHERE (((Issue_record_local.Status) Not Like "*closed*") AND ((tblElapsedTime.DateOut) Is Null))
ORDER BY [ElapsedTime]/24 DESC , Int([ElapsedTime]/24/30) DESC;
***找出正在進行中的process,並以Issue及相關Activity的時間來排序SELECT ProcessedActivities.ActivityIssueNumber,
ProcessedActivities.ActivityTime,
ProcessedActivities.[Initial State],
ProcessedActivities.[Resultant State],
DateAdd("d",[EnteredOn],[EnterTime]) AS Entered
FROM ProcessedActivities INNER JOIN Issue_record_local
ON ProcessedActivities.ActivityIssueNumber = Issue_record_local.IssueNumber
WHERE (((ProcessedActivities.[Resultant State])="new"
Or (ProcessedActivities.[Resultant State])="initial response"
Or (ProcessedActivities.[Resultant State])="assigned/working"
Or (ProcessedActivities.[Resultant State])="resolution determined"
Or (ProcessedActivities.[Resultant State])="resolution available"
Or (ProcessedActivities.[Resultant State])="resolution implemented"
Or (ProcessedActivities.[Resultant State])="complete/customer accepted"))
ORDER BY ProcessedActivities.ActivityIssueNumber, ProcessedActivities.ActivityTime;
*** subquery如果ActivityIsseNumber是屬於tiah,並且狀態及擁有者非空白
SELECT ProcessedActivities.ActivityIssueNumber, ProcessedActivities.ActivityTime, ProcessedActivities.[Initial State], ProcessedActivities.[Resultant State], ProcessedActivities.[Initial Owner], ProcessedActivities.[Resultant Owner], Issue_record_local.Owner, Issue_record_local.Originator, Issue_record_local.Type, Issue_record_local.PPM, Issue_record_local.DDTS, DateAdd("d",[EnteredOn],[EnterTime]) AS Entered
FROM ProcessedActivities INNER JOIN Issue_record_local ON ProcessedActivities.ActivityIssueNumber = Issue_record_local.IssueNumber
WHERE (
(
(ProcessedActivities.ActivityIssueNumber) In
(SELECT ProcessedActivities.ActivityIssueNumber
FROM ProcessedActivities
WHERE (((ProcessedActivities.[Resultant Owner]) Like "*tiah*"))
)
)
AND
(
((ProcessedActivities.[Resultant State])<>"")
OR
((ProcessedActivities.[Resultant Owner])<>"")
)
)
ORDER BY ProcessedActivities.ActivityIssueNumber, ProcessedActivities.ActivityTime;
*** 找出是issue狀態改變的activitySELECT f_activity_local.ActivityIssueNumber, f_activity_local.ActivityTime, Mid([activitydescription],InStr([activitydescription],"Issue State changed from ")+25,InStr(InStr([activitydescription],"Issue State changed from "),[activitydescription]," to ")-InStr([activitydescription],"Issue State changed from ")-25) AS [Initial State], Mid([ActivityDescription],InStr(InStr([activitydescription],"Issue State changed from "),[activitydescription]," to ")+4,Len([activitydescription])-InStr(InStr([activitydescription],"Issue State changed from "),[activitydescription]," to ")-5) AS [Resultant State]
FROM f_activity_local
WHERE (((f_activity_local.ActivityDescription) Like "*issue state*"))
ORDER BY f_activity_local.ActivityIssueNumber, f_activity_local.ActivityDescription;
*** select case 找到了SELECT
Switch([type]="SoftwareEvaluation","Jill",
[responsiblediscipline]="Sapres","Elaine",
[SupportGroupID]=2,"MEG",
[SupportGroupID]=3,"Miriam",
[SupportGroupID]=4,"Adriana",
[SupportGroupID]=1,"Benny")
AS [Support Group]
, OpenIssues.IssueNumber, OpenIssues.EnteredOn, OpenIssues.Status, OpenIssues.Priority, OpenIssues.Severity, OpenIssues.LastActivityDate, DateDiff("d",[lastactivitydate],Date()) AS [No Activity], OpenIssues.Type, OpenIssues.ModelNumber, OpenIssues.ResponsibleDiscipline
FROM OpenIssues INNER JOIN ModelLookupGroups ON OpenIssues.ModelNumber = ModelLookupGroups.ModelNumber
ORDER BY
Switch([type]="SoftwareEvaluation","Jill",
[responsiblediscipline]="Sapres","Elaine",
[SupportGroupID]=2,"MEG",
[SupportGroupID]=3,"Miriam",
[SupportGroupID]=4,"Adriana",
[SupportGroupID]=1,"Benny")
, OpenIssues.Severity,
OpenIssues.LastActivityDate;
solve the problem of weekend response
SELECT Issue_record_local.IssueNumber, ProcessedActivities.ActivityTime, Switch(
(
(Weekday([EnteredOn])=2 And [EnteredAt_1]>="08:00:00")
Or (Weekday([EnteredOn])=6 And [EnteredAt_1]<="17:00:00")
Or Weekday([EnteredOn])=3
Or Weekday([EnteredOn])=4
Or Weekday([EnteredOn])=5
)
,
DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])
,
(
(Weekday([EnteredOn])=6 And [EnteredAt_1]>"17:00:00")
Or (Weekday([EnteredOn])=2 And [EnteredAt_1]<"08:00:00")
Or Weekday([EnteredOn])=7
Or Weekday([EnteredOn])=1
)
And
(
(Weekday([ProcessedActivities].[ActivityTime])=6 And Hour([EnteredAt_1])>17)
Or (Weekday([ProcessedActivities].[ActivityTime])=2 And Hour([EnteredAt_1])<8)
Or Weekday([ProcessedActivities].[ActivityTime])=7
Or Weekday([ProcessedActivities].[ActivityTime])=1
)
And
(
DateDiff("d",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])<4
),
0
,
(
(Weekday([EnteredOn])=6 And [EnteredAt_1]>"17:00:00")
Or (Weekday([EnteredOn])=2 And [EnteredAt_1]<"08:00:00")
Or Weekday([EnteredOn])=7
Or Weekday([EnteredOn])=1
)
And
(
(Weekday([ProcessedActivities].[ActivityTime])=6 And Hour([EnteredAt_1])>17)
Or (Weekday([ProcessedActivities].[ActivityTime])=2 And Hour([EnteredAt_1])<8)
Or Weekday([ProcessedActivities].[ActivityTime])=7
Or Weekday([ProcessedActivities].[ActivityTime])=1
)
And
(
DateDiff("d",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])>4
)
,
DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])
,
(
(Weekday([EnteredOn])=6 And [EnteredAt_1]>"17:00:00")
Or (Weekday([EnteredOn])=2 And [EnteredAt_1]<"08:00:00")
Or Weekday([EnteredOn])=7
Or Weekday([EnteredOn])=1
)
and
(
(Weekday([ProcessedActivities].[ActivityTime])=2 And Hour([EnteredAt_1])>=8)
Or (Weekday([ProcessedActivities].[ActivityTime])=6 And Hour([EnteredAt_1])<=17)
Or Weekday([ProcessedActivities].[ActivityTime])=3
Or Weekday([ProcessedActivities].[ActivityTime])=4
Or Weekday([ProcessedActivities].[ActivityTime])=5
)
,
DateDiff("h",DateAdd("d",[enteredon],[enteredAt_1]),[activitytime])
) AS responsetime, [enteredon], Issue_record_local.EnteredAt_1, Weekday([EnteredOn]) AS WeekDay1
FROM Issue_record_local INNER JOIN ProcessedActivities ON Issue_record_local.IssueNumber = ProcessedActivities.ActivityIssueNumber
WHERE (((ProcessedActivities.[Initial State])="New"))
ORDER BY Issue_record_local.IssueNumber DESC;
SELECT Format([IssueDate],"mm\/yyyy") AS DateChanged, (CInt(Avg(DateDiff("h",DateAdd("d",[enterDate],[EnterTime]),DateAdd("d",[DateClosed],[TimeClosed])))))/24 AS AvgResponseTime, (Max(DateDiff("h",DateAdd("d",[enterDate],[EnterTime]),DateAdd("d",[DateClosed],[TimeClosed]))))/24 AS MaxResponseTime, (Min(DateDiff("h",DateAdd("d",[enterDate],[EnterTime]),DateAdd("d",[DateClosed],[TimeClosed]))))/24 AS MinResponseTime, Count(*) AS issuenumber
FROM (Issue_record_local LEFT JOIN testIssues ON Issue_record_local.IssueNumber = testIssues.issuenumber) INNER JOIN ProcessedActivities ON Issue_record_local.IssueNumber = ProcessedActivities.ActivityIssueNumber
WHERE (((Issue_record_local.IssueDate)>#1/1/2006#) AND ((ProcessedActivities.[Initial State])="New") AND ((testIssues.issuenumber) Is Null) AND ((Issue_record_local.DateClosed) Is Not Null))
GROUP BY Format([IssueDate],"mm\/yyyy"), Format([IssueDate],"yyyy\/mm")
ORDER BY Format([IssueDate],"yyyy\/mm");