Qry Help

Qry Help

Post by Don Grove » Mon, 12 Jan 2004 23:36:42



I need to return a qry in QA that produces the following and am finding it
difficult I am new at this so I may not put it as clearly as I should.
The table holds ping responses from an iis box to its nearest router and I
am trying to return uptime of iis server and some statistics.

Return a qry in QA that retrieves the following.
Return one row per day for the current month , with the following
Total Count of  conStatusCode
Percentage returned of conStatusCode  = 0 against all other codes for that
day
Average conTimeMS for each day

something like this,.
conDate,  totcount ,    percentage,  avgms
1/1/2004,    143      ,      98,            32
2/1/2004,    140      ,      98,            31
3/1/2004,    138      ,      98,            35

any help with this would be appreciated.

'****   table structure below

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblConnection1]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblConnection1]
GO

CREATE TABLE [dbo].[tblConnection1] (
 [conDate] [datetime] NULL ,
 [conSiteName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
 [conResponse] [nvarchar] (10) COLLATE Latin1_General_CI_AS NULL ,
 [conIpNumber] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
 [conBuffSize] [int] NULL ,
 [conTimeMS] [int] NULL ,
 [conTTL] [int] NULL ,
 [conStatusCode] [int] NULL ,
 [conStatusDesc] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

 
 
 

Qry Help

Post by David Porta » Tue, 13 Jan 2004 00:05:42


SELECT CAST(CONVERT(CHAR(8),MIN(condate),112) AS DATETIME),
 COUNT(constatuscode),
 COUNT(CASE WHEN constatuscode=0 THEN 1 END)/
 CAST(COUNT(CASE WHEN constatuscode<>0 THEN 1 END) AS REAL),
 AVG(contimems)
 FROM tblConnection1
 GROUP BY CONVERT(CHAR(8),condate, 112)

--
David Portas
------------
Please reply only to the newsgroup
--

 
 
 

1. Help porting MS-Access qry to Syb11

I have a query that was originally developed in Access.  I need to
port it to Sybase 11.
The original query (using the JOIN keyword which is not supported by
Sybase11) is:
/*original*/
SELECT Distinct
    PERS.D_DHPID,
    PERS.P_LAST,
    PERS.P_FIRST,
    PERS.TITLE_ID,
    TITLES.TGROUP_ID,
    PERS.P_MCCN,
    SYS_PREFIX.PREFIX_ID,
    SYS_HONORIFIC.HONOR_ID
FROM
(((PERS LEFT JOIN SYS_PREFIX ON PERS.PREFIX_ID = SYS_PREFIX.PREFIX_ID)
LEFT JOIN SYS_HONORIFIC ON PERS.HONOR_ID = SYS_HONORIFIC.HONOR_ID)
LEFT JOIN TITLES ON PERS.TITLE_ID = TITLES.TITLE_ID)
RIGHT JOIN HPP ON PERS.D_DHPID = HPP.D_DHPID
WHERE
    TITLES.TGROUP_ID<>1037 AND
    HPP.D_INACTIVE=False

I modified it as below, and it runs in Sybase SQL Anywhere, but not in
Sybase11:
/* Works in SQLAny, but takes a year and a day to run - and won't run
in Sybase11 -
'The table TITLES ia an inner member of an outer-join clause. This is
not allowed if the table also participates in a regular join clause'
*/
select distinct
    PERS.D_DHPID,
    PERS.P_LAST,
    PERS.P_FIRST,
    PERS.TITLE_ID,
    TITLES.TGROUP_ID,
    PERS.P_MCCN,
    SYS_PREFIX.PREFIX_ID,
    SYS_HONORIFIC.HONOR_ID
from
    PERS,
    SYS_PREFIX,
    SYS_HONORIFIC,
    TITLES,
    HPP
where
    (PERS.PREFIX_ID*=SYS_PREFIX.PREFIX_ID and
     PERS.HONOR_ID*=SYS_HONORIFIC.HONOR_ID and
     PERS.TITLE_ID*=TITLES.TITLE_ID and
     PERS.D_DHPID=*HPP.D_DHPID)
    and HPP.D_INACTIVE=0
    and TITLES.TGROUP_ID = any(select TGROUP_ID from TITLES
                                   where TITLES.TGROUP_ID<>1037)

Any ideas on rewriting this for Sybase will be greatly appreciated...

Thanks,
Lenny

2. How/where do I get BDE CAB?

3. Help with linked server qry (error doing delete!)

4. Paradox and ODBC -- help

5. newbie help with summary qry (sql 2k)

6. SSCE2, CE3.0, lack of Performance vs. SSCE1

7. help with * in sql qry

8. MS Access and ADO

9. Help -- Grouping qry ---- Sub Grouping -Is this possible?

10. conditions in a Qry???

11. Update qry (sql 2k) subquery returned more than 1 value

12. schema qry

13. Simple qry question