Help With Query

Hi All,

I am trying to build a report where show all the numbers of orders completed and orders which are completed on time. However, I have a hard time to figure out how to build the where clause since I want to get all the completed orders and at the same time, I also want to count the completed orders on time. Can anyone help me with this query?

This is my query right now:

SELECT COUNT(o.OrderID)
FROM Order o
WHERE MONTH(o.InvoiceDT) = @.Month

TIA

hi,

first of all is there any field like order_to_be_finished_by (date) & finished_on(data) fields or not in your table.

Select Count(OrderID) As OrdersCompletedOnTime from table

where convert(varchar, to_be_Finished_Date, 103) = convert(varchar, finished_on, 103)

& second query

Select Count(OrderID) As OrdersCompletedOnTime from table

where finished_on is not null

hope it helps

regards,

satish.

|||

Thank you satish,

So, it is would be two different select statement in one single stored procedures.

|||

yep,

regards,

satish

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations ??
TNX
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.droptable.com
E B via droptable.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations ??
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.droptable.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
David Portas
SQL Server MVP

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations '?
TNX
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.droptable.comE B via droptable.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations '?
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.droptable.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
David Portas
SQL Server MVP
--

Help with phoneBook db design

Hi All!!
I need to build small phone book db.
There are phone numbers and name (to whom phone belong)
There are distribution lists that holds phone numbers and also can hold
another lists.
There are Users to whom distribution lists and or phones belong.
Any ideas, implementations '?
TNX
--
Imagination is more important then knowledge. (A.Einshtein)
Message posted via http://www.sqlmonster.comE B via SQLMonster.com wrote:
> Hi All!!
> I need to build small phone book db.
> There are phone numbers and name (to whom phone belong)
> There are distribution lists that holds phone numbers and also can hold
> another lists.
> There are Users to whom distribution lists and or phones belong.
> Any ideas, implementations '?
> TNX
> --
> Imagination is more important then knowledge. (A.Einshtein)
> Message posted via http://www.sqlmonster.com
Please don't multi-post!
Answered in comp.databases.ms-sqlserver
--
David Portas
SQL Server MVP
--

help with one more SQL query...

i am trying to figure out how to write one more sql query. basically i now have 2 tables, both filled with item numbers and quantities. i want to write queries that will produce what is missing between the two tables. here is what i need more specifically:

a query that looks for item numbers that are in one table and not the other, and vice versa (ie item number 20004 is in our table, but doesn't exist in the other table).

a query that prints out discrepancies between quantities for item numbers that do match up (ie item number 20004 is in both tables, but has a quantity of 10 in one and 20 in the other).

it seems that i should be using the SQL join functions for these? is that right, or is there a better way to do this??yes, for the first task (rows in one table but not the other) you will need two queries, each featuring a LEFT OUTER JOIN, with a test for IS NULL in the WHERE clauseselect table1.itemnumber
from table1
left outer
join table2
on table1.itemnumber
= table2.itemnumber
where table2.itemnumber is null

select table2.itemnumber
from table2
left outer
join table1
on table2.itemnumber
= table1.itemnumber
where table1.itemnumber is null

for the second task you will need a simple INNER JOINselect table1.itemnumber
, table1.quantity
, table2.quantity
from table1
inner
join table2
on table1.itemnumber
= table2.itemnumber
where table1.quantity
<> table2.quantity|||Essentially the queryselect table1.itemnumber
from table1 left outer join table2 on table1.itemnumber = table2.itemnumber
where table2.itemnumber is nullis a "set difference", hence can also be achieved by using an "EXCEPT" construction:select itemnumber from table1
EXCEPT ALL
select itemnumber from table2The main difference in the result being that in the former case, duplicates in table1 will be shown (as duplicates) only if they aren't present in table2, while in the latter case duplicates may be shown (but with a smaller repeat count) when they are present in the second table.
Without duplicates in table1, both queries give the same result.
In most situations, the EXCEPT query will be faster, though, especially if the second table is large.|||In most situations, the EXCEPT query will be faster...unless your database system doesn't support that syntax, in which case it will take positively forever ;) :)|||unless your database system doesn't support that syntaxin which case it will probably neither support the OUTER JOIN syntax ...
;)|||um, peter, they all support OUTER JOIN syntax ;)

which database(s) were you thinking of that do support EXCEPT?|||Just thinking of Oracle (up to version 9) which has no FULL OUTER JOIN, but has MINUS.
Maybe there are others as well, no idea.|||but you don't need FULL OUTER JOIN to create an "EXCEPT" query, just LEFT OUTER JOIN|||And which DB systems were you thinking of that do not support EXCEPT?|||many more than you were!! ;) :)|||That would (not) surprise me!
;)|||I call a remote/stored procedure on another server.

It then calls other stored procedure with the EXEC statement.

It seems to me that logically this would get done remotely as well no? But It seems not to be from my tests.

Is there a easy way to specify that the SP - and all it's sub calls get done remotely, or do I need to specifically specify for each of these that they should be run remotely in each and every EXEC statement?

Help with math in function

I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?

Here is the code:

create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);

begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);

ip_address := a||'.'||b||'.'||c||'.'||d;

return ip_address;
end readip;

Thank you much!

MonicaIf you want the decimals to be displayed, define the variables as
a number(10,3);

Hope this helps.|||Originally posted by DBW-Monica
I have a function that reads an unsigned integer and translates it into an IP address. The problem is that it is rounding the numbers so the IP numbers are off by 1 either up or down, i.e., 144.xxx.xxx.xx will show as 145.xxx.... or 144.456.xx... will show as 144.455.xx....Can someone help?

Here is the code:

create or replace function readip(unsint number)
return varchar
is
ip_address varchar(15);
a number(3);
b number(3);
c number(3);
d number(3);

begin
a := mod( ( unsint / 16777216 ), 256);
b := mod( ( unsint / 65536 ), 256);
c := mod( ( unsint / 256 ), 256);
d := mod( ( unsint ), 256);

ip_address := a||'.'||b||'.'||c||'.'||d;

return ip_address;
end readip;

Thank you much!

Monica
must be missing the obvious, but I just don't get it. Can you give an example of an input value, and what your function should return ?

help with join query

I am writing a query to find missing numbers. The query works, but is very very slow due to the number of records it is currently pulling.

I want to limit the records to only search where "records.event_year = 2007" but when I try to insert that after the "from dbo.records" it gives me an error.

Where exactly do I need to add this and is there anything else I can do to make the query run faster? The event_year, state_file_number, and isactive are all indexed.

================================================== =======
ALTER procedure [Migrate].[Chk_Missing_SFN]

@.beg as int,
@.end as int

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select BeforeSkip+1 as gapStart, NextValue-1 as gapEnd from (
select
a.state_file_number as BeforeSkip,
min(b.state_file_number) as NextValue
from dbo.records A join dbo.records b
on a.state_file_number < b.state_file_number

where a.state_file_number between @.beg and @.end and a.isactive = 'T' and b.isactive = 'T'

group by a.state_file_number
having min(b.state_file_number) > a.state_file_number + 1
) Xwhat error are you getting?

I imagine that the "A.event_year = 2007" needs to go after the "on A.state_file_number < b.state_file_number"|||Remember that this snippet creates a million row test set before you judge speed.-- ptp 20071130 See http://www.dbforums.com/showthread.php?t=1624988

CREATE TABLE patp (
patpId INT NOT NULL
CONSTRAINT XPKpatp
PRIMARY KEY CLUSTERED (patpId)
)

INSERT INTO patp (
patpID
) SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

INSERT INTO patp (
patpID
) SELECT d0.patpID + d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
FROM patp AS d0
CROSS JOIN (SELECT 10 * patpId AS patpID FROM patp) AS d1
CROSS JOIN (SELECT 100 * patpId AS patpID FROM patp) AS d2
CROSS JOIN (SELECT 1000 * patpId AS patpID FROM patp) AS d3
CROSS JOIN (SELECT 10000 * patpId AS patpID FROM patp) AS d4
CROSS JOIN (SELECT 100000 * patpId AS patpID FROM patp) AS d5
WHERE 0 < d1.patpId + d2.patpId + d3.patpId + d4.patpId + d5.patpId
ORDER BY 1

DECLARE @.i INT
SET @.i = 1

WHILE @.i < (SELECT Max(patpID) FROM patp)
BEGIN
DELETE FROM patp WHERE patpID = @.i
SET @.i = 2 * @.i
END

SELECT a.patpID AS block_begin
, (SELECT Min(b.patpID)
FROM patp AS b
WHERE a.patpID <= b.patpID
AND NOT EXISTS (SELECT *
FROM patp AS c
WHERE c.patpID = 1 + b.patpID)) AS block_end
FROM patp AS a
WHERE NOT EXISTS (SELECT *
FROM patp AS b
WHERE b.patpId = a.patpID - 1)

DROP TABLE patp-PatP|||Thank you very much. That worked as needed.