Count Functionality with Query Result
hi there,
i am trying to produce a report in excel and not knowing much i am sort of stuck. Hopefully, one of you can help me on this matter.
The sql below is pulling out how many days help logs from a certain facility have been open. using that information, i want to create a report that puts the query result into brackets of days open.
Basically, i want to show the count of logs in a particular bracket [I am attaching a sample report (samplereport.doc) to help you visualize the information and layout].
The report should provide a count of the number of logs that remain open at each facility for a varying numbers of days. The last column in the report does a count of how many call logs with the call status of "Long Term" were recived by the facility.
[vba]
Sub LogAging()
' Log Aging
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=xxxxxxxserver;UID=xxxxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xxxxxxxdatabase;Trusted_Connection=Yes" _
, Destination:=Range("A7"))
.CommandText = Array( _
"SELECT CallLog.CallStatus, CallLog.RecvdDate, DATEDIFF(Day, CallLog.RecvdDate, GETDATE()) AS Exp1, Subset.HospID, Subset.CallID" & Chr(13) & "" & Chr(10) _
"FROM heatprod.dbo.CallLog CallLog, heatprod.dbo.Subset Subset" & Chr(13) & "" & Chr(10) & "WHERE CallLog.CallID = Subset.CallID AND ((CallLog.CallStatus" _
, " Not Like 'closed%'))" & Chr(13) & "" & Chr(10) & "ORDER BY Subset.CallID")
.Name = "LogAging"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub
[/vba]
The above query supplies the information need to complete the report, the only two columns that are needed for creating the report are Exp1 and HospID. The file--queryresult.xls is populated with values for the above two columns.
I would really appreciate any help regarding this post.
Thanks,
Anupam
