Floetic

Using Microsoft Visual Basic 6.0 Microsoft SQL Enterprise Manager 8.0 -- Form1.frm Option Explicit Private Sub cmdDone_Click() End End Sub Private Sub cmdLookUp_Click() If Trim(Text1.Text) <> "" Then Call GetStats Else MsgBox "Please enter a start date", vbExclamation End If If Trim(Text2.Text) <> "" Then Call GetStats Else MsgBox "Please enter an end date", vbExclamation End If End Sub Sub GetStats() Dim strSQL As String Dim rst As ADODB.Recordset strSQL = " CARLA_SEL '" & Text1.Text & "'" & Text2.Text & "'" Call ADO_Query(intCurrentConnection, strSQL, rst) lstInformation.Clear List1.Clear If rst.RecordCount > 0 Then Do While Not rst.EOF lstInformation.AddItem Trim(rst("SDATE")) & " " & Trim(rst("EDATE")) List1.AddItem Trim(rst("SDATE")) & " " & Trim(rst("EDATE")) rst.MoveNext Loop lstInformation.Visible = True Else MsgBox "No stats found for that date range" End If End Sub Private Sub Command1_Click() Call GetDetails End Sub Private Sub lstInformation_Click() Text1.Text = Trim(List1.List(lstInformation.ListIndex)) Text2.Text = Trim(List1.List(lstInformation.ListIndex)) Label3.Caption = List1.List(lstInformation.ListIndex) End Sub Private Sub lstInfo_Click() Text1.Text = (lstInformation.ListIndex + 1) & ". " & lstInformation.List(lstInformation.ListIndex) Text2.Text = (lstInformation.ListIndex + 1) & ". " & lstInformation.List(lstInformation.ListIndex) End Sub Sub GetDetails() Dim dateStartDate As Date Dim dateEndDate As Date Dim strSQL As String dateStartDate = Trim(Label3.Caption) dateStartDate = Trim(Text1.Text) dateEndDate = Trim(Text2.Text) strSQL = " CARLA_PROB1 '" & dateStartDate & "','" & dateEndDate & "','" Call ADO_Execute(intCurrentConnection, strSQL) End Sub Private Sub Form_Load() Top = 0 Left = 0 Height = 9000 Width = 12000 End Sub -- stored Procedure code; CARLA_SEL CREATE PROCEDURE CARLA_SEL as declare @startdate datetime, @enddate datetime begin select*from tblRequestMaster RM where RM.fldRequestDate=@startdate and RM.fldRequestDate=@enddate end GO --stored procedure code; CARLA_PROB1 CREATE PROCEDURE CARLA_PROB1 AS DECLARE @startdate datetime, @enddate datetime -- (1) Number of calls received for each priority of call [for a specified date range] select RM.fldPriorityCode as 'Priority', count(RM.fldRequestID) as 'Calls' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '' and '' and RM.fldRequestFlag like 'D' group by RM.fldPriorityCode union select 'Total' as 'Priority', count(RM.fldRequestID) as 'Calls' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '' and '' and RM.fldRequestFlag like 'D' order by RM.fldPriorityCode asc GO This is the code in my modules; --mod_ConnectionRoutines.bas Option Explicit ' '************************************************************************************ 'Module to house the MainConnection calls '************************************************************************************ ' Public Function ADO_Execute(intConn As Integer, _ strSQL) As Boolean Err.Clear On Error GoTo ErrHandle: 'set timeouts Call SetNewTimeouts ADO_Execute = True With clsConnectionObject Call .ADO_Execute(intConn, strSQL) End With 'reset timeouts Call ResetTimeouts Exit Function ErrHandle: Call ResetTimeouts ADO_Execute = False Err.Raise Err.Number End Function Public Function ADO_Query(intConn As Integer, _ strSQL As String, _ rst As ADODB.Recordset, _ Optional CursorType As ADODB.CursorTypeEnum, _ Optional LockType As ADODB.LockTypeEnum, _ Optional CursorLocation As ADODB.CursorLocationEnum) As Boolean Err.Clear On Error GoTo ErrHandle: 'set timeouts Call SetNewTimeouts ADO_Query = True With clsConnectionObject Call .ADO_Query(intConn, strSQL, rst, CursorType, LockType, CursorLocation) End With 'reset timeouts Call ResetTimeouts Exit Function ErrHandle: Call ResetTimeouts ADO_Query = False Err.Raise Err.Number End Function Public Function ADO_Recordset(intConn As Integer, _ ByVal strSQL As String, _ Optional CursorType As ADODB.CursorTypeEnum, _ Optional LockType As ADODB.LockTypeEnum, _ Optional CursorLocation As ADODB.CursorLocationEnum) As ADODB.Recordset Err.Clear On Error GoTo ErrHandle: 'set timeouts Call SetNewTimeouts With clsConnectionObject Set ADO_Recordset = .ADO_Recordset(intConn, strSQL, CursorType, LockType, CursorLocation) End With 'reset timeouts Call ResetTimeouts Exit Function ErrHandle: Call ResetTimeouts Err.Raise Err.Number End Function Sub SetNewTimeouts() 'routine to set the Component Request timeouts to 1 hour App.OleRequestPendingTimeout = 3600000 App.OleServerBusyTimeout = 3600000 End Sub Sub ResetTimeouts() 'routine to reset the Component Request timeouts App.OleRequestPendingTimeout = 10000 App.OleServerBusyTimeout = 10000 End Sub --modMainConnect.bas Option Explicit 'global variables for MainConnection Public gstrDSN As String Public gstrDBase As String Public gstrUser As String Public gstrPassword As String 'array of all current DB connections Public strConnections(10, 4) As String Public intCurrentConnections(10) As Integer Public intTotalConnections As Integer 'New global connection object for BOIS Public clsConnectionObject As MainConnection.clsMainConnection Public intCurrentConnection As Integer Public blnConnectionCompleted As Boolean Function ConnectToServer(strDataSource As String, _ strUsername As String, _ strPassword As String, _ strDBname As String) As Boolean '********************************************************* 'Create an instance of ConnectionObject Set clsConnectionObject = New clsMainConnection '********************************************************* On Error GoTo ErrorHandle 'now establish Connection to server database Call clsConnectionObject.ConnectToDB(Trim(strDataSource), Trim(strDBname), Trim(strUsername), Trim(strPassword)) ConnectToServer = True gstrUser = strUsername intCurrentConnection = clsConnectionObject.intConnection Exit Function ErrorHandle: ConnectToServer = False Err.Clear End Function --Module1.bas Option Explicit Sub Main() If ConnectToServer("BOISSQL", "itd", "2002", "BOIS") Then Form1.Show Else MsgBox "Unsuccessful :::: Unable To Connect" & Err.Description End If End Sub When I try to run the VB code I get the following error; 'Compile Error: User-defined type not defined' Can anyone tell me were I am going wrong? Thx ~ Floetic ~

-- (1) Number of calls received for each priority of call [for a specified date range] declare @startdate datetime, @finishdate datetime select RM.fldPriorityCode as 'Priority', count(RM.fldRequestID) as 'Calls' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '03-05-2007' and RM.fldRequestFlag like 'D' group by RM.fldPriorityCode union select 'Total' as 'Priority', count(RM.fldRequestID) as 'Calls' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '03-05-2007' and RM.fldRequestFlag like 'D' order by RM.fldPriorityCode asc Results: Priority Calls 1 20 2 2912 3 152 4 571 5 4 Total 3659 I would like to transfer these results to an excel sheet. For instance when the user opens up the excel worksheet and types in for a example a start date: 01-01-2007 and an end date: 03-05-2007 (into textboxes) then clicks a button say called 'Get stats' and then the results appear on the sheet. How can this be done?

--declare @startdate datetime, --@enddate datetime select RM.fldEditedBy as 'User Login', case Grouping(RM.fldPriorityCode) when 0 then RM.fldPriorityCode else 'Total' end as 'Priority', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode,RM.fldEditedBy with cube union select RM.fldEditedBy as 'User Login', case Grouping(RM.fldPriorityCode) when 0 then RM.fldPriorityCode else 'Total' end as 'Priority', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode,RM.fldEditedBy with cube Results; NULL 1 73 NULL 2 2753 NULL 3 230 NULL 4 15 NULL 5 23 NULL Total 3094 AMCCO039 1 9 AMCCO039 2 3 AMCCO039 Total 12 COBRI003 1 4 COBRI003 2 14 COBRI003 3 19 COBRI003 Total 37 JDONN001 1 2 JDONN001 2 954 JDONN001 3 31 JDONN001 Total 987 JFAIT001 1 9 JFAIT001 2 352 JFAIT001 3 28 JFAIT001 4 5 JFAIT001 5 20 JFAIT001 Total 414 MCATN001 1 23 MCATN001 2 930 MCATN001 3 53 MCATN001 4 4 MCATN001 Total 1010 PBRAD001 1 23 PBRAD001 2 119 PBRAD001 3 12 PBRAD001 4 2 PBRAD001 5 1 PBRAD001 Total 157 PHUDS001 1 3 PHUDS001 2 19 PHUDS001 3 30 PHUDS001 5 2 PHUDS001 Total 54 PKENN004 2 331 PKENN004 3 5 PKENN004 4 4 PKENN004 Total 340 PMISK001 2 31 PMISK001 3 52 PMISK001 Total 83

My code is now looking like this [i'm almost getting there] :::: --Number of calls resolved by each staff member [for a specified date range] --declare @startdate datetime, --@enddate datetime select RM.fldEditedBy as 'User Login', RM.fldPriorityCode as 'Priority', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode,RM.fldEditedBy with cube union select RM.fldEditedBy as 'User Login', RM.fldPriorityCode as 'Priority', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode,RM.fldEditedBy with cube Results; NULL NULL 3094 NULL 1 73 NULL 2 2753 NULL 3 230 NULL 4 15 NULL 5 23 AMCCO039 NULL 12 AMCCO039 1 9 AMCCO039 2 3 COBRI003 NULL 37 COBRI003 1 4 COBRI003 2 14 COBRI003 3 19 JDONN001 NULL 987 JDONN001 1 2 JDONN001 2 954 JDONN001 3 31 JFAIT001 NULL 414 JFAIT001 1 9 JFAIT001 2 352 JFAIT001 3 28 JFAIT001 4 5 JFAIT001 5 20 MCATN001 NULL 1010 MCATN001 1 23 MCATN001 2 930 MCATN001 3 53 MCATN001 4 4 PBRAD001 NULL 157 PBRAD001 1 23 PBRAD001 2 119 PBRAD001 3 12 PBRAD001 4 2 PBRAD001 5 1 PHUDS001 NULL 54 PHUDS001 1 3 PHUDS001 2 19 PHUDS001 3 30 PHUDS001 5 2 PKENN004 NULL 340 PKENN004 2 331 PKENN004 3 5 PKENN004 4 4 PMISK001 NULL 83 PMISK001 2 31 PMISK001 3 52 Is there any way of naming the 'NULLs' at the following {to be 'Total' instead}; NULL 3094 NULL 12 NULL 37 NULL 987 NULL 414 NULL 1010 NULL 157 NULL 54 NULL 340 NULL 83 ??? *[highlight]NULL NULL 3094[/highlight] NULL 1 73 NULL 2 2753 NULL 3 230 NULL 4 15 NULL 5 23 *can this be moved to here & how do I do that!?! *[highlight]AMCCO039 NULL 12[/highlight] AMCCO039 1 9 AMCCO039 2 3 *the same again for this

--Gives overall total and a breakdown of the number of calls resolved for each priority by I.T (excluding 3rd parties) declare @startdate datetime, @enddate datetime select RM.fldPriorityCode as 'Priority', --RM.fldEditedBy as 'User Login', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode union select 'Total' as 'Priority', --RM.fldEditedBy as 'User Login', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' order by RM.fldPriorityCode Results; Priority Calls Resolved -------- -------------- 1 73 2 2753 3 222 4 15 5 23 Total 3086 ------------------------------------------------------------ --Number of calls resolved by each staff member [for a specified date range] declare @startdate datetime, @enddate datetime select RM.fldPriorityCode as 'Priority', RM.fldEditedBy as 'User Login', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode,RM.fldEditedBy union select 'Total' as 'Priority', RM.fldEditedBy as 'User Login', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Calls Resolved' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldTrade = 'IT' --and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode,RM.fldEditedBy Results; Priority User Login Calls Resolved -------- -------------------- -------------- 1 AMCCO039 9 1 COBRI003 4 1 JDONN001 2 1 JFAIT001 9 1 MCATN001 23 1 PBRAD001 23 1 PHUDS001 3 2 AMCCO039 3 2 COBRI003 14 2 JDONN001 954 2 JFAIT001 352 2 MCATN001 930 2 PBRAD001 119 2 PHUDS001 19 2 PKENN004 331 2 PMISK001 31 3 COBRI003 19 3 JDONN001 31 3 JFAIT001 28 3 MCATN001 45 3 PBRAD001 12 3 PHUDS001 30 3 PKENN004 5 3 PMISK001 52 4 JFAIT001 5 4 MCATN001 4 4 PBRAD001 2 4 PKENN004 4 5 JFAIT001 20 5 PBRAD001 1 5 PHUDS001 2 Total AMCCO039 3 Total AMCCO039 9 Total COBRI003 4 Total COBRI003 14 Total COBRI003 19 Total JDONN001 2 Total JDONN001 31 Total JDONN001 954 Total JFAIT001 5 Total JFAIT001 9 Total JFAIT001 20 Total JFAIT001 28 Total JFAIT001 352 Total MCATN001 4 Total MCATN001 23 Total MCATN001 45 Total MCATN001 930 Total PBRAD001 1 Total PBRAD001 2 Total PBRAD001 12 Total PBRAD001 23 Total PBRAD001 119 Total PHUDS001 2 Total PHUDS001 3 Total PHUDS001 19 Total PHUDS001 30 Total PKENN004 4 Total PKENN004 5 Total PKENN004 331 Total PMISK001 31 Total PMISK001 52 Desired display of results; User Login Priority Calls Resolved ---------- -------- ------------- AMCCO039 1 9 2 3 3 0 4 0 5 0 Total 12 COBRI003 1 4 2 14 3 19 4 0 5 0 Total 37 ... OR User Login 1 2 3 4 5 Total ---------- - - - - - ----- AMCCO039 9 3 0 0 0 12 COBRI003 4 14 19 0 0 37 ... Any guidance that anyone could offer to me in how I would go about displaying my results in either of the following ways shown above?

declare @startdate datetime, @finishdate datetime select RM.fldPriorityCode as 'Priority', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 then 1 else 0 end) as 'Closed Calls 0-5 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10 then 1 else 0 end) as 'Closed Calls 6-10 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 then 1 else 0 end) as 'Closed Calls 11+ Days' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' group by RM.fldPriorityCode union select 'Total' as 'Priority', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 then 1 else 0 end) as 'Closed Calls 0-5 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10 then 1 else 0 end) as 'Closed Calls 6-10 Days', sum(case when datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 then 1 else 0 end) as 'Closed Calls 11+ Days' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' order by RM.fldPriorityCode asc I hope that someone else can find this helpful too

--Number of calls closed in 0-5 days; 6-10; 11+ [for a specified date range] declare @startdate datetime, @finishdate datetime select RM.fldPriorityCode as 'Priority', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Closed Calls 0-5' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' --and datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 --and datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10 and datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 group by RM.fldPriorityCode union select 'Total' as 'Priority', count(datediff(day,RM.fldRequestDate,RM.fldComCanDate)) as 'Closed Calls 0-5' from tblRequestMaster RM where RM.fldPriorityCode between 1 and 5 and RM.fldRequestDate between '01-01-2007' and '08-05-2007' and RM.fldRequestFlag like 'D' and RM.fldRequestStatus = 'Y' --and datediff(day,RM.fldRequestDate,RM.fldComCanDate) <=5 --and datediff(day,RM.fldRequestDate,RM.fldComCanDate) between 6 and 10 and datediff(day,RM.fldRequestDate,RM.fldComCanDate) >=11 order by RM.fldPriorityCode asc Results; Priority Closed Calls 0-5 Days -------- ---------------- 1 14 2 1868 3 59 4 149 Total 2090 Priority Closed Calls 6-10 Days -------- ---------------- 1 4 2 342 3 23 4 77 5 1 Total 447 Priority Closed Calls 11+ Days -------- ---------------- 1 3 2 516 3 60 4 225 5 3 Total 807 I'm looking to display my results like this: Priority Closed Calls 0-5 Days 6-10 Days 11+ Days -------- ---------------------------------- 1 14 4 3 2 1868 342 516 3 59 23 60 4 149 77 225 5 0 1 3 Total 2090 447 807 How would I go about doing this? Any help would be gratefully appreciated.