Re: help with SQL

Posted in 2000

Carlos Benjamin — Thu, 27 Jul 2000 13:10:12 -0700 (PDT) — source: Informix-list mailing list archive (1991-1998)

Are you sure COUNT() is what you want? I would think you want your count to reflect the attempts/calls made to the dest_country and it looks like you're requesting (and getting) a count of all attempts/calls made instead. To do that you need to join dest_country in the main query to dest_country in the subqueries. Or are these test records with equivalent attempts/completions for each country code? Either that or your SMDR output is really bad. --- "Jeff Bluemel" <jeff@mta.everyone.net> > wrote: >OK, I modified the query to the following which reflects my Informix >database; > >SELECT dest_country AS Country, > (SELECT COUNT(outbound_duration) > FROM master_call > WHERE outbound_duration != 0 > AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26>23:59:59' > AS Attempts, > (SELECT COUNT(answered_duration) > FROM master_call > WHERE answered_duration != 0 > AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26 >23:59:59' > AS Calls >FROM master_call >WHERE out_trunk_group=18 > AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26 >23:59:59' >GROUP BY dest_country; > >This is the output that I received; > >country attempts callspts > >46 31806 14664 >62 31806 14664 >81 31806 14664 >44 31806 14664 >47 31806 14664 >212 31806 14664 >86 31806 14664 >39 31806 14664 >671 31806 14664 > >How can I modify this so the it calculates the calls and attempts by my >other group by and selected information? There will be several other things >that I add to this. > >Thanks, > >Jeff > >"Art S. Kagel" <kagel@bloomberg.net> wrote in message >news:397F6287.40082BB0@bloomberg.net... >> Why not just specify: >> >> AND Vendor_Records.OutDuration != 0 >> AND Vendor_Records.SeizedDuration != 0 >> >> in the WHERE clause. >> >> Now I know these are not mutual conditions so this becomes: >> >> SELECT >> (SELECT COUNT(Vendor_Records.OutDuration) >> FROM Vendor_Records >> WHERE Vendor_Records.OutDuration != 0 >> AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000') >> AS Calls, >> (SELECT COUNT(Vendor_Records.SeizedDuration) >> FROM Vendor_Records >> WHERE Vendor_Records.SeizedDuration != 0 >> AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000') >> AS Attempts >> FROM systables >> WHERE tabid = 1; >> >> Must be a fancy way but this works and is more efficient and portable. >> >> Art S. Kagel >> >> >> Jeff Bluemel wrote: >> > >> > I have a query that I am using a similar syntax for a couple of >different >> > databases, but I am unable to find the equivalent in informix. Any help >> > would be greatly appreciated. Here is the syntax from SQL Server; >> > >> > SELECT COUNT(NULLIF(Vendor_Records.OutDuration, 0)) AS Calls, >> > COUNT(NULLIF(Vendor_Records.SeizedDuration, 0)) As Attempts >> > FROM Vendor_Records >> > WHERE (Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')>> > >> > The nullif allows me to set the value to null if certain conditions >exist. >> > In this case if the value is zero then it sets the value to a null so >that >> > it will not be counted and I can accurately detect the Completed Calls >from >> > the Attempted Calls. >> > >> > Any help on a command and a syntax for Informix would be EXTREMELY >helpful. >> > >> > Thanks in advance for your assistance. >> > >> > Jeff >> > Network Engineer == Maintainer of the procrastinator's FAQ. Well, maybe tomorrow I will be. _____________________________________________________________ Want a new web-based email account ? ---> http://www.firstlinux.net

↪ replying to Carlos Benjamin

Jeff Bluemel — Sun, 30 Jul 2000 10:56:27 -0600 — source: Informix-list mailing list archive (1991-1998)

I have some other area's that need I need to qualify, but yes this is to be based for every country code etc. There is a query sample left by Rudy that solved the problem. Thanks for you input... Jeff "Carlos Benjamin" <benj@firstlinux.net> wrote in message news:8lq769$hmj$1@news.xmission.com... > > Are you sure COUNT() is what you want? I would think you want your count to reflect the attempts/calls made to the dest_country and it looks like you're requesting (and getting) a count of all attempts/calls made instead. To do that you need to join dest_country in the main query to dest_country in the subqueries. > > Or are these test records with equivalent attempts/completions for each country code? > > Either that or your SMDR output is really bad. > > > --- "Jeff Bluemel" <jeff@mta.everyone.net> > > wrote: > >OK, I modified the query to the following which reflects my Informix > >database; > > > >SELECT dest_country AS Country, > > (SELECT COUNT(outbound_duration) > > FROM master_call > > WHERE outbound_duration != 0 > > AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26> >23:59:59' > > AS Attempts, > > (SELECT COUNT(answered_duration) > > FROM master_call > > WHERE answered_duration != 0 > > AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26 > >23:59:59' > > AS Calls > >FROM master_call > >WHERE out_trunk_group=18 > > AND call_date_time_k BETWEEN '2000-07-25 00:00:00' AND '2000-07-26 > >23:59:59' > >GROUP BY dest_country; > > > >This is the output that I received; > > > >country attempts callspts > > > >46 31806 14664 > >62 31806 14664 > >81 31806 14664 > >44 31806 14664 > >47 31806 14664 > >212 31806 14664 > >86 31806 14664 > >39 31806 14664 > >671 31806 14664 > > > >How can I modify this so the it calculates the calls and attempts by my > >other group by and selected information? There will be several other things > >that I add to this. > > > >Thanks, > > > >Jeff > > > >"Art S. Kagel" <kagel@bloomberg.net> wrote in message > >news:397F6287.40082BB0@bloomberg.net... > >> Why not just specify: > >> > >> AND Vendor_Records.OutDuration != 0 > >> AND Vendor_Records.SeizedDuration != 0 > >> > >> in the WHERE clause. > >> > >> Now I know these are not mutual conditions so this becomes: > >> > >> SELECT > >> (SELECT COUNT(Vendor_Records.OutDuration) > >> FROM Vendor_Records > >> WHERE Vendor_Records.OutDuration != 0 > >> AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000') > >> AS Calls, > >> (SELECT COUNT(Vendor_Records.SeizedDuration) > >> FROM Vendor_Records > >> WHERE Vendor_Records.SeizedDuration != 0 > >> AND Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000') > >> AS Attempts > >> FROM systables > >> WHERE tabid = 1; > >> > >> Must be a fancy way but this works and is more efficient and portable. > >> > >> Art S. Kagel > >> > >> > >> Jeff Bluemel wrote: > >> > > >> > I have a query that I am using a similar syntax for a couple of > >different > >> > databases, but I am unable to find the equivalent in informix. Any help > >> > would be greatly appreciated. Here is the syntax from SQL Server; > >> > > >> > SELECT COUNT(NULLIF(Vendor_Records.OutDuration, 0)) AS Calls, > >> > COUNT(NULLIF(Vendor_Records.SeizedDuration, 0)) As Attempts > >> > FROM Vendor_Records > >> > WHERE (Vendor_Records.Date BETWEEN '7/1/2000' AND '7/20/2000')> >> > > >> > The nullif allows me to set the value to null if certain conditions > >exist. > >> > In this case if the value is zero then it sets the value to a null so > >that > >> > it will not be counted and I can accurately detect the Completed Calls > >from > >> > the Attempted Calls. > >> > > >> > Any help on a command and a syntax for Informix would be EXTREMELY > >helpful. > >> > > >> > Thanks in advance for your assistance. > >> > > >> > Jeff > >> > Network Engineer > > == > Maintainer of the procrastinator's FAQ. Well, maybe tomorrow I will be. > > _____________________________________________________________ > Want a new web-based email account ? ---> http://www.firstlinux.net