Public Sub Cnt(grbf As Integer, rq As String)
Dim ssql As String
Dim col As Integer
Dim grb1 As String
Dim grb2 As String
Dim grb3 As String
Dim grb4 As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.provider = "MSDASQL"
cn.ConnectionString = "Provider = SQLOLEDB;Data Source =xx.xx.x.xx;User ID =xx;Password =xx;"
cn.ConnectionTimeout = 20000
Sheet2.Cells() = ""
Select Case grbf
Case 1
grb1 = "bsc"
grb2 = "a.bsc"
grb3 = "c.bsc"
grb4 = "c.bsc = d.bsc"
Case 2
grb1 = "period"
grb2 = "a.period"
grb3 = "c.period"
grb4 = "c.period = d.period"
Case 3
grb1 = "bsc,b.period"
grb2 = "a.bsc,a.period"
grb3 = "c.bsc,c.period"
grb4 = "c.bsc = d.bsc and c.period = d.period"
Case 4
GoTo line2
End Select
line1:
cn.Open
ssql = "if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tptable') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table gsmsts.dbo.tptable" & vbCrLf
ssql = ssql & "if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tptable1') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table gsmsts.dbo.tptable1" & vbCrLf
cn.Execute (ssql)
ssql = " select b." & grb1 & ",sum(b.ALLPDCHPCUFAIL) as af into gsmsts.dbo.tptable "
ssql = ssql & " from gsmsts.dbo.bscgprsday" & rq & " b "
ssql = ssql & " group by b." & grb1 & vbCrLf
ssql = ssql & " SELECT " & grb2 & ","
ssql = ssql & " (CASE SUM(a.ALLPDCHSCAN) WHEN 0 THEN NULL ELSE SUM(a.ALLPDCHACC)/ CONVERT(real, SUM(a.ALLPDCHSCAN)) END) AS 平均分配PDCH," & vbCrLf
ssql = ssql & " (CASE SUM(a.ALLPDCHSCAN) WHEN 0 THEN NULL ELSE SUM(a.ALLPDCHACTACC)/ CONVERT(real, SUM(a.ALLPDCHSCAN)) END) AS 平均占用PDCH," & vbCrLf
ssql = ssql & " (CASE SUM(a.ALLPDCHACC) WHEN 0 THEN NULL ELSE SUM(a.ALLPDCHACTACC)/ CONVERT(real, SUM(a.ALLPDCHACC)) END) AS PDCH占用率," & vbCrLf
ssql = ssql & " SUM(PCHALLATT) AS PDCH分配次数," & vbCrLf
ssql = ssql & " SUM(PCHALLFAIL) AS PDCH分配失败数," & vbCrLf
ssql = ssql & " (CASE SUM(a.PCHALLATT)WHEN 0 THEN NULL ELSE (SUM(a.PCHALLATT) - SUM(a.PCHALLFAIL))/ CONVERT(real, SUM(a.PCHALLATT)) END) AS PDCH分配成功率," & vbCrLf
ssql = ssql & " SUM(CS12ULSCHED) AS CS12上行流量, " & vbCrLf
ssql = ssql & " SUM(CS12DLSCHED) AS CS12下行流量, " & vbCrLf
ssql = ssql & " (CASE SUM(a.PSCHREQ) WHEN 0 THEN NULL ELSE (SUM(a.PREJTFI) + SUM(a.PREJOTH)) / CONVERT(real,SUM(a.PSCHREQ)) END) AS 接入失败率," & vbCrLf
ssql = ssql & " SUM(LDISTFI) + SUM(LDISRR)+ SUM(LDISOTH) + SUM(FLUDISC) AS 下行掉线数, " & vbCrLf
ssql = ssql & " SUM(IAULREL) AS 上行掉线数," & vbCrLf
ssql = ssql & " sum(a.PCHALLATT) as pat into gsmsts.dbo.tptable1" & vbCrLf
ssql = ssql & " FROM gsmsts.dbo.cellgprsday" & rq & " as a " & vbCrLf
ssql = ssql & " GROUP BY " & grb2 & vfcrlf
cn.Execute (ssql)
ssql = " select " & grb3 & ",c.平均分配PDCH,c.平均占用PDCH,c.PDCH占用率,c.PDCH分配次数,c.PDCH分配失败数,c.PDCH分配成功率,c.CS12上行流量,c.CS12下行流量,c.接入失败率,c.下行掉线数,c.上行掉线数," & vbCrLf
ssql = ssql & " (case c.pat when 0 then null else (convert(real,d.af) / c.pat) end) as PLU拥堵率" & vbCrLf
ssql = ssql & " from gsmsts.dbo.tptable1 c right outer join gsmsts.dbo.tptable d on " & grb4 & vbCrLf
ssql = ssql & " order by " & grb3 & vbCrLf
rs.ActiveConnection = cn
rs.Open (ssql)
For col = 0 To rs.Fields.Count - 1
Sheet2.Range("A1").Offset(0, col).Value = rs.Fields(col).Name
Next
Sheet2.Range("A1").Offset(1, 0).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
GoTo lastline
line2:
cn.Open
ssql = "if exists (select * from dbo.sysobjects where id = object_id(N'dbo.tptable') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table gsmsts.dbo.tptable" & vbCrLf
cn.Execute (ssql)
ssql = " SELECT a.object_ID," & vbCrLf
ssql = ssql & "(CASE SUM(a.ALLPDCHSCAN) WHEN 0 THEN NULL ELSE SUM(a.ALLPDCHACC)/ CONVERT(real, SUM(a.ALLPDCHSCAN)) END) AS 平均分配PDCH," & vbCrLf
ssql = ssql & "(CASE SUM(a.ALLPDCHSCAN) WHEN 0 THEN NULL ELSE SUM(a.ALLPDCHACTACC)/ CONVERT(real, SUM(a.ALLPDCHSCAN)) END) AS 平均占用PDCH," & vbCrLf
ssql = ssql & "(CASE SUM(a.ALLPDCHACC) WHEN 0 THEN NULL ELSE SUM(a.ALLPDCHACTACC)/ CONVERT(real, SUM(a.ALLPDCHACC)) END) AS PDCH占用率," & vbCrLf
ssql = ssql & " SUM(PCHALLATT) AS PDCH分配次数," & vbCrLf
ssql = ssql & " SUM(PCHALLFAIL) AS PDCH分配失败数," & vbCrLf
ssql = ssql & "(CASE SUM(a.PCHALLATT)WHEN 0 THEN NULL ELSE (SUM(a.PCHALLATT) - SUM(a.PCHALLFAIL))/ CONVERT(real, SUM(a.PCHALLATT)) END) AS PDCH分配成功率," & vbCrLf
ssql = ssql & " SUM(CS12ULSCHED) AS CS12上行流量, " & grb3 & vbCrLf
ssql = ssql & " SUM(CS12DLSCHED) AS CS12下行流量, " & grb3 & vbCrLf
ssql = ssql & "(CASE SUM(a.PSCHREQ) WHEN 0 THEN NULL ELSE (SUM(a.PREJTFI) + SUM(a.PREJOTH)) / CONVERT(real,SUM(a.PSCHREQ)) END) AS 接入失败率," & grb3 & vbCrLf
ssql = ssql & " SUM(LDISTFI) + SUM(LDISRR)+ SUM(LDISOTH) + SUM(FLUDISC) AS 下行掉线数, " & grb3 & vbCrLf
ssql = ssql & " SUM(IAULREL) AS 上行掉线数" & vbCrLf
ssql = ssql & " into gsmsts.dbo.tptable " & vbCrLf
ssql = ssql & " FROM gsmsts.dbo.cellgprsday" & rq & " as a " & vbCrLf
ssql = ssql & " GROUP BY " & "a.object_id" & vbCrLf
ssql = ssql & " select e.*,d.站名 from gsmsts.dbo.cellinfo as d right outer join gsmsts.dbo.tptable as e on e.object_ID = d.小区号"
rs.ActiveConnection = cn
rs.Open (ssql)
For col = 0 To rs.Fields.Count - 1
Sheet2.Range("A1").Offset(0, col).Value = rs.Fields(col).Name
Next
Sheet2.Range("A1").Offset(1, 0).CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
lastline:
End Sub
请教下为什么，刚以为是用了临时表的原因，用查询器完全能搞定，在vb下不行，改了直接建个表，貌似不是。
