assistance with sql query

Thanks in advance, I am trying to display the count of uptimes and downtimes in a single query. i started with something like
SELECT DISTINCT servername,
(SELECT COUNT(*)
FROM pingtable
WHERE (status = '0')) AS Uptime,
(SELECT COUNT(*)
FROM pingtable
WHERE (status <> '0')) AS DownTime
FROM pingtable

but this gives me the
server1 7 2
server1 7 2
...

Table layout and data:
servername status

server1 up
server1 up
server1 down
server2 up
server2 up
server2 up
server3 down
server3 up
server3 up

the output I would like to have is

Server UpCount DownCount
Server1 2 1
Server2 3 0
Server3 2 1Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

You'll end up with something like this:

Select Server,
sum(Case Status when 0 then 1 else 0) Uptime,
sum(Case Status when <> 0 then 1 else 0) Downtime
From PingTable
Group by Server

I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

blindman|||Thanks so much for holding my hand there,
Here is the final query that worked perfectly
Select Server,
sum(Case Status when 0 then 1 else 0 end) as Uptime,
sum(Case Status when 0 then 0 else 1 end) as Downtime
From eladmin.PingstatsNT
Group by Server

Originally posted by blindman
Lookup crosstab queries in books online. Near the bottom is some sample code you can modify for your needs.

You'll end up with something like this:

Select Server,
sum(Case Status when 0 then 1 else 0) Uptime,
sum(Case Status when <> 0 then 1 else 0) Downtime
From PingTable
Group by Server

I'm not sitting at a server console now, so I had to draft it from memory and it probably has syntax errors in it, but you should be able to get the idea.

blindman|||FYI, if your Status field always holds zeros or ones, set it's data type to bit to ensure that your code will always work correctly.

blindman

assistance with rounding

Hi,
I have the foll
round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)
The result I get is -54.00000000000
How can I change it to only display -54
ThanksCONVERT(INT, ROUND(...))
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:A140E6DC-02B6-4A2D-93DB-7BFF7667F6F4@.microsoft.com...
> Hi,
> I have the foll
> round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)
> The result I get is -54.00000000000
> How can I change it to only display -54
> Thanks|||hi
you can do it as
round((convert(decimal(4,0),d.new - d.old)/d.old)*100)
remove the decimal after 100.
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chris" wrote:

> Hi,
> I have the foll
> round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)
> The result I get is -54.00000000000
> How can I change it to only display -54
> Thanks|||Hi,
If I use int I get 0.
"Aaron Bertrand [SQL Server MVP]" wrote:

> CONVERT(INT, ROUND(...))
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:A140E6DC-02B6-4A2D-93DB-7BFF7667F6F4@.microsoft.com...
>
>|||You said you have:
SELECT round((decimal(4,0),d.new - d.old)/d.old)*100,0)
...and this yields -54.000000. So you are saying that if you change that
to:
SELECT CONVERT(INT, round((convert(decimal(4,0),d.new -
d.old)/d.old)*100,0) )
...suddenly it yields 0? I don't quite buy that, could you post a repro
(DDL, sample data)? See http://www.aspfaq.com/5006
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:906750AF-BB06-462E-91CD-5416359E89A6@.microsoft.com...
> Hi,
> If I use int I get 0.|||Are you sure you are evaluating convert(int,
round((convert(decimal(4,0),d.new - d.old)/d.old)*100,0)) ?
AMB
"Chris" wrote:
> Hi,
> If I use int I get 0.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Hi,
I got it to work. Wouldn't doind all these convert slow down a hugh query or
put a strain on the server?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You said you have:
> SELECT round((decimal(4,0),d.new - d.old)/d.old)*100,0)
> ...and this yields -54.000000. So you are saying that if you change that
> to:
> SELECT CONVERT(INT, round((convert(decimal(4,0),d.new -
> d.old)/d.old)*100,0) )
> ...suddenly it yields 0? I don't quite buy that, could you post a repro
> (DDL, sample data)? See http://www.aspfaq.com/5006
>
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:906750AF-BB06-462E-91CD-5416359E89A6@.microsoft.com...
>
>|||YES! This is why formatting is better done a tthe presentation layer, not
in the database...
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:D622E951-68D1-4AA5-955D-5C4EB34F4B95@.microsoft.com...
> Hi,
> I got it to work. Wouldn't doind all these convert slow down a hugh query
> or
> put a strain on the server?

ASP.NET SQL CONTAINS statement

I have an SQL db that I need to be able to search and display. I haveabout seven different columns and would like results to be returned toa datagrid based upon the search criteria entered by the user.
How do I construct a SELECT statement such as below using the ASP.NET 1.1+?
SELECT * FROM db.table WHERE CONTAINS ("searchable term")
I can get a column searched and return the results already, but if Imodify to search all columns and use the CONTAINS it will fail. Isthere a way to do this easily?
Thanks,
TRKneller

Your query is failing because you are using CONTAINS Microsoft Proprietry FULL TEXT search predicate when you need to use LIKE which is ANSI SQL used to search table column based data. Full Text is used for Text and NText data but it is an add on to SQL Server dependent on Microsoft Search Service and the Catalog must be populated to get search results. SQL Server creates an Arithmetic Pointer to Text and NText data in your file system. Run a search for LIKE and FULL Text in the BOL(books online). Hope this helps.

ASP.NET Query

I need to display certain files from a directory based on a password. Could
someone point me in the right direction on how to do this. In my database i
store the file name and the password entered on the upload of the document.
When a user comes to download the document he/she needs to enter a password
before seeing any of the files available.
thanks in adv. for any and all help.I'm not sure I understand - can you elaborate? If you are already asking the
m
for a name and password for the upload, why not use that same method for the
download?
"Ben" wrote:

> I need to display certain files from a directory based on a password. Coul
d
> someone point me in the right direction on how to do this. In my database
i
> store the file name and the password entered on the upload of the document
.
> When a user comes to download the document he/she needs to enter a passwor
d
> before seeing any of the files available.
> thanks in adv. for any and all help.

ASP.NET 2 Data Binding with SQL 2005 XML

I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
Any leads to web sites and books that would help along these lines would also be appreciated.
Greg Collins [Microsoft MVP]
Visit Braintrove ( http://www.braintrove.com )
On Mar 21, 6:15 pm, "Greg Collins [Microsoft MVP]"
<gcollins_AT_msn_DOT_com> wrote:
> I am starting a project where I will be using the XML data type in SQL Server 2005, and ASP.NET to display and update the content of the XML.
> I've been digging around for a bit trying to find some sources that will explain the process of doing data binding in ASP.NET with the SQL XML, but haven't been able to locate anything too useful yet.
> The XML will be charts, each holding 3 groups of 70 rows of data, each row having 5 fields (boolean, datetime, datetime, int, string).
> I will need to pull 1 or more charts down to be rendered in a web page, modified, and then stored back to SQL.
> I'm assuming from what I've read so far that this is possible, but I need more information and hopefully some examples.
> I've worked somewhat with SQL 2000 and ASP.NET 2 (mostly hand-written pages), but want to get full bore into using VS2005 to do this. I am very familiar with XML, XSLT, HTML, C# and JavaScript, moderately familiar with SQL and VS.
> Any leads to web sites and books that would help along these lines would also be appreciated.
> --
> Greg Collins [Microsoft MVP]
> Visit Braintrove (http://www.braintrove.com)
Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0
http://www.developer.com/net/net/article.php/3406251
XML data type tips in SQL Server 2005
http://www.codeproject.com/dotnet/XMLdDataType.asp
and...
http://www.google.com/search?hl=en&q=XML+data+type+SQL+2005+asp.net