Query Help

I have a table <ClearanceORDER>
The Fields of interest are <ClearDate> , <ClearanceType>, <ClearanceOrderID>
The Problem:
When an order is Canceled (CNCL) or Change of address (COA) there is an
entry in this table for it. The entry should look like this:
ClearDate ClearanceType ClearanceOrderID
09/14/2007 COA 212
canceled order
07/09/2007 CNCL 547
Change of address
A person can NOT be 'canceled' and 'change of address' in the same
clearanceorderid and the same cleardate!
IN an other way.
How can I list all the records that have COA and CNCL with the same
cleardate and clearanceorderid?
I dont know how else to explain this.
Scott BurkeScott,
To prevent this from happening in the future, you could add an unique
index/constraint on ClearDate and ClearanceOrderID, so there will be only
one entry per day and per OrderID, no matter what type that is.
To display what entries in the table break that rule, use this:
select ClearanceOrderID, ClearDate
from ClearanceOrder
group by ClearanceOrderID, ClearDate
having count(*) > 1
Andrei.
"Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
news:5497C818-B7C0-4A17-835E-2AA139899281@.microsoft.com...
>I have a table <ClearanceORDER>
> The Fields of interest are <ClearDate> , <ClearanceType>,
> <ClearanceOrderID>
> The Problem:
> When an order is Canceled (CNCL) or Change of address (COA) there is an
> entry in this table for it. The entry should look like this:
> ClearDate ClearanceType ClearanceOrderID
> 09/14/2007 COA 212
> canceled order
> 07/09/2007 CNCL 547
> Change of address
> A person can NOT be 'canceled' and 'change of address' in the same
> clearanceorderid and the same cleardate!
> IN an other way.
> How can I list all the records that have COA and CNCL with the same
> cleardate and clearanceorderid?
> I dont know how else to explain this.
> Scott Burke
>|||Thanks Andrei !
For some dam reason I thought I had to generate a list of COA and CNCL then
compare them.
Tunnal vission I guess. :)
Thanks again.
Just for giggles......
is is possibe to do the above?
Scott Burke
"Andrei" wrote:
> Scott,
> To prevent this from happening in the future, you could add an unique
> index/constraint on ClearDate and ClearanceOrderID, so there will be only
> one entry per day and per OrderID, no matter what type that is.
> To display what entries in the table break that rule, use this:
> select ClearanceOrderID, ClearDate
> from ClearanceOrder
> group by ClearanceOrderID, ClearDate
> having count(*) > 1
>
> Andrei.
> "Scott Burke" <ScottBurke@.discussions.microsoft.com> wrote in message
> news:5497C818-B7C0-4A17-835E-2AA139899281@.microsoft.com...
> >I have a table <ClearanceORDER>
> > The Fields of interest are <ClearDate> , <ClearanceType>,
> > <ClearanceOrderID>
> >
> > The Problem:
> > When an order is Canceled (CNCL) or Change of address (COA) there is an
> > entry in this table for it. The entry should look like this:
> > ClearDate ClearanceType ClearanceOrderID
> > 09/14/2007 COA 212
> > canceled order
> > 07/09/2007 CNCL 547
> > Change of address
> >
> > A person can NOT be 'canceled' and 'change of address' in the same
> > clearanceorderid and the same cleardate!
> >
> > IN an other way.
> > How can I list all the records that have COA and CNCL with the same
> > cleardate and clearanceorderid?
> >
> > I dont know how else to explain this.
> > Scott Burke
> >
>
>sql

Query Help

I have a database table with data like this (some fields ommited):
reserveID owner creator
-- -- --
39009 1 0
39009 0 1
39100 0 1
I would like to do a query that would return all rows in which a reserveID
has a row with creator = 1 but no rows with owner = 1. So with the rows in m
y
example above, 39100 would be returned by my query but 39009 would not.
I've been experimenting with different queries with out much luck. How would
I accomplish this?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You might also want to learn why rows and columns are nothing
whatsoever like records and fields. You will never write RDBMS until
you do. Let's make a wild guess with bad names, no constraints, etc.
CREATE TABLE Reservations
(reservation_nbr INTEGER NOT NULL PRIMARY KEY,
reservation_owner INTEGER NOT NULL,
reservation_creator INTEGER NOT NULL);
SELECT R1.reservation_nbr, R1.reservation_owner,
R1.reservation_creator
FROM Reservations AS R1
WHERE R1.reservation_creator = 1
AND NOT EXISTS
(SELECT *
FROM Reservations AS R2
WHERE R2.reservation_owner = 1
A ND R1.reservation_creator = 1);|||Hi There,
I think this will solve your problem.
Select * From tmpData4 T4 Where cr = 1 And
Not Exists
(
Select * from tmpData4 T5 Where own = 1 And T5.res = T4.res
)
Where tmpdata4 is your tableName
With Warm regards
Jatinder Singh|||select * from <tblname> where creator = 1 and reserveID not in (select
distinct reserveid from <tblname> where owner = 1)
i hope this will work
with reagards
Rajeev Shukla|||not sure what your requirements are, but a let me have a shot in the
dark, assuming that reserveID is not nullable:
select * from your_table
where reserveID in(select reserveID from your_table where creator = 1)
and reserveID NOT in(select reserveID from your_table where owner = 1)
if reserveId is nullable , go for EXISTS/NOT EXISTS instead of IN/NOT IN

Query help

Hi,
I have an employee table having fields empid and managerid.
I need a query which returns all the children once the parentid is given.
Here is the script
create table employee (empid int , parentID int )
GO
insert into employee select 1,null
insert into employee select 2,1
insert into employee select 3,1
insert into employee select 4,3
insert into employee select 5,3
insert into employee select 6,4
insert into employee select 7,4
insert into employee select 8,5
insert into employee select 9,5
insert into employee select 10,6
insert into employee select 11,6
go
So, when i pass the empid 3, it should return all the children under 3; i.e.
4,5,6,7,8,9,10 and 11
Thanking in advance
regards
Laraselect * from employee as e
where e.parentid=<managerID>
"Lara" <lara169@.gmail.com> wrote in message
news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
>
> Thanking in advance
> regards
> Lara
>|||Thanks martin,
But this is not the one i needed. i need all the children under this
parentid
"Martin" <x@.y.z> wrote in message
news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
> select * from employee as e
> where e.parentid=<managerID>
> "Lara" <lara169@.gmail.com> wrote in message
> news:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
>|||Do you mean recursively?
"Lara" <lara169@.gmail.com> wrote in message
news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Thanks martin,
> But this is not the one i needed. i need all the children under this
> parentid
>
> "Martin" <x@.y.z> wrote in message
> news:%23tqUlX18FHA.636@.TK2MSFTNGP10.phx.gbl...
>|||Select
c.*
from employee as m
left join employee as c
on c.ParentID = m.EmpID
"Lara" <lara169@.gmail.com>, haber iletisinde unlar
yazd:OTS6tH18FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
>
> Thanking in advance
> regards
> Lara
>|||Something like this (I've not tested it - there are some syntax errors)
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab (select * from employee where empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0 -- stop when there are no more employees added to
table
begin
insert into @.tab (select * from employee
where
parentid in (select * empid from @.tab) and -- select next level down
from those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
> Do you mean recursively?
> "Lara" <lara169@.gmail.com> wrote in message
> news:exAsfZ18FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||Here's the correct syntax
declare @.tab table (empid int ,parentid int)
declare @.rowsaffected int
set @.rowsaffected=-1
declare @.empid int
set @.empid=3
insert into @.tab(empid,parentid) (select empid,parentid from employee where
empid=@.empid)
set @.rowsaffected=@.@.rowcount
while @.rowsaffected<>0
begin
insert into @.tab(empid,parentid) (select empid,parentid from employee
where
parentid in (select empid from @.tab) and -- select next level down from
those already selected
empid not in (select empid from @.tab) -- don't double select
)
set @.rowsaffected=@.@.rowcount
end
select empid from @.tab where empid<>@.empid -- don't return orginal employee
id
"Martin" <x@.y.z> wrote in message
news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
> Something like this (I've not tested it - there are some syntax errors)
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab (select * from employee where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0 -- stop when there are no more employees added to
> table
> begin
> insert into @.tab (select * from employee
> where
> parentid in (select * empid from @.tab) and -- select next level down
> from those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
>
> "Martin" <x@.y.z> wrote in message
> news:Oi$Jzb18FHA.808@.TK2MSFTNGP09.phx.gbl...
>|||Have a look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
When I say 'look' I mean copy/paste/test.
ML|||Lara (lara169@.gmail.com) writes:
> I have an employee table having fields empid and managerid.
> I need a query which returns all the children once the parentid is given.
> Here is the script
> create table employee (empid int , parentID int )
> GO
> insert into employee select 1,null
> insert into employee select 2,1
> insert into employee select 3,1
> insert into employee select 4,3
> insert into employee select 5,3
> insert into employee select 6,4
> insert into employee select 7,4
> insert into employee select 8,5
> insert into employee select 9,5
> insert into employee select 10,6
> insert into employee select 11,6
> go
> So, when i pass the empid 3, it should return all the children under 3;
> i.e. 4,5,6,7,8,9,10 and 11
Here is how you can do this on SQL 2005:
with emp (empid, parentID) as
(select empid, parentID = NULL
from employee
where empid = 3
union all
select e.empid, e.parentID
from emp
join employee e on e.parentID = emp.empid)
select empid from emp where empid <> 3
I reckon that you are likely to still use SQL 2000, but I wanted to show
that this is a lot easier on SQL 2005.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Martin, But can we do it in a single query ?
"Martin" <x@.y.z> wrote in message
news:%23loxEq18FHA.4084@.TK2MSFTNGP10.phx.gbl...
> Here's the correct syntax
>
> declare @.tab table (empid int ,parentid int)
> declare @.rowsaffected int
> set @.rowsaffected=-1
> declare @.empid int
> set @.empid=3
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where empid=@.empid)
> set @.rowsaffected=@.@.rowcount
> while @.rowsaffected<>0
> begin
> insert into @.tab(empid,parentid) (select empid,parentid from employee
> where
> parentid in (select empid from @.tab) and -- select next level down from
> those already selected
> empid not in (select empid from @.tab) -- don't double select
> )
> set @.rowsaffected=@.@.rowcount
> end
> select empid from @.tab where empid<>@.empid -- don't return orginal
> employee id
> "Martin" <x@.y.z> wrote in message
> news:eA1O6n18FHA.3984@.TK2MSFTNGP11.phx.gbl...
>sql

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>

Query Help

Hoping the community can help me with this problem. I have a stored
procedure that returns a few alias fields.
Here is the select clause to give you an idea what I am talking about (FROM
and WHERE omitted to shorten the length):
SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
Table].LocationName,
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
END AS DLPhoto,
CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
[DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current Driver
Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
[DriverEmploymentDateHired] >= '8/17/2001' AND
[DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
This query has 4 alias's to be returned; DLRenewDate,DLPhoto,[DPre-Employ],
and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
not want the record to be returned with the query. If I could have a WHERE
like this:
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULL
then it would not be a problem, but you cannot test alias's in this manner
(as far as I can tell). What are my options for solving this problem? If I
was in Access I could run a second query on the first and test my
conditions. Can I use another Stored procedure in this manner?
For Example a second stored procedure that works like this:
Select * From StoredProcedure1
WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
NOT NULL AND DPre4025DrugTest IS NOT NULLHi
To reduce the amount of typing you have to do, you can use a table alias for
the table. see Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_05_4ofn.htm
Instead of using the column alias in the WHERE clause, use the actual
expression. As these are CASE statements returning non null values when they
are satisfied they will only return null if non of the clauses are returned,
so these can be tested in the WHERE clause
e.g
For:
CASE WHEN DriverLicenseRenewDate <= GETDATE()
THEN 'Drivers Licanse has Expired'
WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE())
THEN 'Drivers Licanse expires within the next 60 days'
END AS DLRenewDate
This will only be null if
NOT ( DriverLicenseRenewDate <= GETDATE()
OR ( DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
DATEADD(dd, 60, GETDATE()) ) )
Which is the same as
DriverLicenseRenewDate > GETDATE()
AND DriverLicenseRenewDate >= DATEADD(dd, 60, GETDATE()) )
HTH
John
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>|||That makes sense, thank you.
"REB" <russ@.bjorgaard.com> wrote in message
news:%23HAwGAZ8DHA.488@.TK2MSFTNGP12.phx.gbl...
> Hoping the community can help me with this problem. I have a stored
> procedure that returns a few alias fields.
> Here is the select clause to give you an idea what I am talking about
(FROM
> and WHERE omitted to shorten the length):
> SELECT DriverTable.DriverLastName, DriverTable.DriverFirstName,
> DriverTable.DriverMiddleInitial,[Company Table].CompanyName, [Locations
> Table].LocationName,
> CASE WHEN DriverLicenseRenewDate <= GETDATE()
> THEN 'Drivers Licanse has Expired'
> WHEN DriverLicenseRenewDate > GETDATE() AND DriverLicenseRenewDate <
> DATEADD(dd, 60, GETDATE())
> THEN 'Drivers Licanse expires within the next 60 days' END AS DLRenewDate,
> CASE WHEN [Driver'sLicensePhoto] = 0 THEN 'Send Copy of Driver''s License'
> END AS DLPhoto,
> CASE WHEN DriverEmploymentDateHired >= '8/17/2001' AND
> [DriverPre-employmentDrugTestDate]IS NULL THEN 'Send Copy of Current
Driver
> Pre-employment Drug Test' END AS [DPre-Employ], CASE WHEN
> [DriverEmploymentDateHired] >= '8/17/2001' AND
> [DriverPre-employment4025(j)DrugQuestion] = 0 THEN 'Send Copy of Driver
> Pre-employment Form (§40.25(j)' END AS DPre4025DrugTest
> This query has 4 alias's to be returned;
DLRenewDate,DLPhoto,[DPre-Employ],
> and DPre4025DrugTest. My problem is that if all 4 are returned NULL I do
> not want the record to be returned with the query. If I could have a
WHERE
> like this:
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
> then it would not be a problem, but you cannot test alias's in this manner
> (as far as I can tell). What are my options for solving this problem? If
I
> was in Access I could run a second query on the first and test my
> conditions. Can I use another Stored procedure in this manner?
> For Example a second stored procedure that works like this:
> Select * From StoredProcedure1
> WHERE DLRenewDate IS NOT NULL AND DLPhoto IS NOT NULL AND [DPre-Employ] IS
> NOT NULL AND DPre4025DrugTest IS NOT NULL
>sql

query generator

hi,
can u plz recomend some utility that generates queries against SQL Server,
something w/ GUI, where one can say click on some fields and get a SELECT
statement
thanks> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
MS Query tool in included in Excel. Just go to Data - Import External Data -
New Database Query and the tool will be started. If it is not installed yet,
it should install on first usage.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Check out free PowerQuery, or DataStudio from AgileInfoSoftware
http://www.agileinfollc.com
"Bruce" <no@.spam.com> wrote in message
news:%23X%235S$hpFHA.1204@.TK2MSFTNGP12.phx.gbl...
> hi,
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
> thanks
>sql

query generator

hi,
can u plz recomend some utility that generates queries against SQL Server,
something w/ GUI, where one can say click on some fields and get a SELECT
statement
thanks> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
MS Query tool in included in Excel. Just go to Data - Import External Data -
New Database Query and the tool will be started. If it is not installed yet,
it should install on first usage.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Check out free PowerQuery, or DataStudio from AgileInfoSoftware
http://www.agileinfollc.com
"Bruce" <no@.spam.com> wrote in message
news:%23X%235S$hpFHA.1204@.TK2MSFTNGP12.phx.gbl...
> hi,
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
> thanks
>

query generator

hi,
can u plz recomend some utility that generates queries against SQL Server,
something w/ GUI, where one can say click on some fields and get a SELECT
statement
thanks
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
MS Query tool in included in Excel. Just go to Data - Import External Data -
New Database Query and the tool will be started. If it is not installed yet,
it should install on first usage.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Check out free PowerQuery, or DataStudio from AgileInfoSoftware
http://www.agileinfollc.com
"Bruce" <no@.spam.com> wrote in message
news:%23X%235S$hpFHA.1204@.TK2MSFTNGP12.phx.gbl...
> hi,
> can u plz recomend some utility that generates queries against SQL Server,
> something w/ GUI, where one can say click on some fields and get a SELECT
> statement
> thanks
>

query for row creation/last update

I'm working on a database migration. The destination DB has date fields in each table for row creation and last update the source DB does not. Is there a system table or way to query the transaction long to find the entry date and last update date for a row using the primary key in the source DB?

Thanks for any helpThere is no builtin audit function for that. The typical audit is to create a trigger and keep track of your insert/update/delete.

Query for Difference between two Fields

Can anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??Looks to me like the difference is 9:627 !
;)

When you say you need to find the difference, are you talking about determining that they ARE different, or finding the elapsed time?

If it's the latter, take a look into DATEDIFF in BOL...

Also, it would help if you tell us what type of field you have there...as that would also effect the answer.|||I need to find the the difference between two times.

Data Types - Field1 and Field2 are both nvarchar|||Look up DATEDIFF in BOL for starters...

DATEDIFF(ms, Field1, Field2)|||DECLARE @.foo NVARCHAR(50)
, @.bar NVARCHAR(50)

SET @.foo = N'16:12:27:123'
SET @.bar = N'16:12:36:750'

IF 0 = IsDate(@.foo)
PRINT 'foo is not a date'
IF 0 = Isdate(@.bar)
PRINT 'bar is not a date'
ELSE
SELECT DateDiff(ms, @.foo, @.bar)-PatP|||http://weblogs.sqlteam.com/jeffs/archive/2007/07/03/60248.aspx
Well worth a read.sql

Query Editor: how to display individual row vertically

In query editor I displayed a single row from a table. The row is so long that I need to scroll horizontally back and forth to check out it's fields. Using t-sql (or otherwise) can I display the row like this: (vertically)

\

Field Name 1: < data value 1>

Field Name 2: < data value 2>

Field Name 3: < data value 3>

Field Name 4: < data value 4>

etc.

TIA,

barkingdog

Dog:

If you are using SQL Server 2005, you ought to be able to use an UNPIVOT

Dave

|||

Dog:

That SQL 2005 code might look something like this:

-- -
-- In order for UNPIVOT to work, the data must be homogeneous.
-- For this reason, all of the fields are transformed into a
-- varchar (40) string.
--
-- Also note the ISNULL function on field 3. If this is not
-- done, this row will not be displayed.
-- -
select convert (varchar (25), FieldName + ':') as FieldName,
FieldValue
from ( select convert (varchar (40), 'This is a test.')
as [Field Name 1],
convert (varchar (40), 'This is the 2nd field.')
as [Field Name 2],
convert (varchar (40), isnull (null, '[Null]'))
as [Field Name 3],
convert (varchar (40), 45.27)
as [Field Name 4]
) x
unpivot (FieldValue for FieldName
in ( [Field Name 1], [Field Name 2],
[Field Name 3], [Field Name 4]
)
) as xx

--
-- Sample Output:
--

-- FieldName FieldValue
-- - -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- Field Name 3: [Null]
-- Field Name 4: 45.27

-- (4 row(s) affected)

|||

If you are using SQL 2000 you might try something like:

select 'Field Name 1:' as FieldName,
'This is a test.' as fieldValue
union all
select 'Field Name 2:',
'This is the 2nd field.'
union all
select '...', ' '
union all
select 'Field Name N: ', 'Nth piece of data'

--
-- Sample Output:
--

-- FieldName fieldValue
-- -
-- Field Name 1: This is a test.
-- Field Name 2: This is the 2nd field.
-- ...
-- Field Name N: Nth piece of data

-- (4 row(s) affected)

Query Dbt

I have a dbt da in sql statement for Access

I have a table with

Production table with fields(Date1,FILE,MT_ID,NAME,LINE_COUNT,QUALITY)
This table has got lots of entries for a particular date itself
Example : 1/6/2003 456777887 4023 SINDHU 89.0 98%
1/6/2003 78889899 4023 SINDHU 78 95%

Now I want to create a seperate table with the fields

DATE -- which has just the unique dates in them
NAME-unique user
MT_ID --unique ID
TOTAL LINE COUNT -- The total line count for unique employees for unique date
AVERAGE ACCURACY -- The average accuracy for unique employee for unique date
LINECOUNT BELOW 98% -- Total No. of Line Count for the unique employee for unique date whose ACCURACY IS BELOW 98%

So for this i wrote a query which gave me

UNIQUE DATE
UNIQUE NAME
TOTAL LINE COUNT
AVERAGE ACCURACY

This is the query

SELECT DATE1,NAME,SUM(LINE_COUNT) AS LCOUNT,AVG(QUALITY) AS ACCURACY FROM Production GROUP BY DATE1,NAME

but i need the MT_ID and LINECOUNT BELOW 98% too

Pls let me know how the subquery will beokay, you realize that for each DATE1,NAME combination, there will be multiple rows, so which value of MT_ID do you want? or is it a new field?

also, i don't understand LINECOUNT BELOW 98% -- could you please explain it again?

rudy
http://r937.com/|||Table1 -- Production

fields(Date1,FILE,MT_ID,NAME,LINE_COUNT,QUALITY)

DATE FILE MT_ID NAME LINE COUNT ACCURACY
1/6/2003 456777887 4023 SINDHU 89.0 98%
1/6/2003 78889899 4023 SINDHU 78 95%
2/6/2003 67899 2344 RETA 789 100%
.....................
............

Now I want to insert into the table --Daywise_Report

FIELDS(DATE1 , MT_ID , Name , TLine_Count,AV_Accuracy, LCB_98)

Where we have to find the total line count and average accuracy for each employees for each date and insert into TLine_Count and AV_Accuracy

And also find the LCB_98 ie -- TOTAL(LINE_COUNT) who's ACCURACY is less than 98%

And insert to appropriate fields
Hope U can help me out
Thanks
regards
Smitha|||if there are multiple MT_IDs in each group, you need to decide how to choose just one of them, i.e. which one

in addition, i still don't understand which lines in each group should be counted if the group's accuracy is less than 98% -- all the lines in a group go towards calculating the group average, no?

i am afraid this is as far as i can help you --

insert
into Daywise_Report
( DATE1
, MT_ID
, Name
, TLine_Count
, AV_Accuracy
, LCB_98 )
select DATE1
, MT_ID
, NAME
, SUM(LINE_COUNT)
, AVG(QUALITY)
, 0
from Production
group
by DATE1
, NAME

Query Date Type - Somebody help me!p

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.where u have

Select * from Apro where data between Inicio and Final

should data be date ?? --> Date - Datetime
or u did that on purpose

Can u output the record source that is being executed ?

Query Date Type

Hi,

I have a table with the follow fields :

ID - Int
Date - Datetime

I need to make a simple query to result the records between to dates
with a single ID.

Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230

Im using the follow :

ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
Final');

ADOQuery1.Parameters[0].Value:=Inicio;
ADOQuery1.Parameters[2].Value:=Final;

ADOQuery1.Open;

When I open the query it doesnt work cos its result a null set
How can I solve this?

Im using SQL Server 2000 and Delphi 6

Thanks for atention.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Leonardo,

There are two problems in your code:

1. Parameters should start with a colon. (e.g. :Inicio and :Final)
2. You have used Parameters[0] and Parameters[2], what about Parameters[1]!?

So this is the correct code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'Select * from Apro where data between Inicio and Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Good luck,
Shervin

"Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
news:3f8d396f$0$199$75868355@.news.frii.net...
>
> Hi,
> I have a table with the follow fields :
> ID - Int
> Date - Datetime
> I need to make a simple query to result the records between to dates
> with a single ID.
> Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> Im using the follow :
> ADOQuery1.Close;
> ADOQuery1.SQL.Clear;
> ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> Final');
> ADOQuery1.Parameters[0].Value:=Inicio;
> ADOQuery1.Parameters[2].Value:=Final;
> ADOQuery1.Open;
> When I open the query it doesnt work cos its result a null set
> How can I solve this?
> Im using SQL Server 2000 and Delphi 6
> Thanks for atention.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Oops! Sorry I forgot to add colons before parameters :-) This is the correct
code:

with ADOQuery1 do
begin
Close;
SQL.Text := 'select * from Apro where data between :Inicio and :Final';
Parameters[0].Value := Inicio;
Parameters[1].Value := Final;
Open;
end;

Shervin

"Shervin Shapourian" <ShShapourian@.hotmail.com> wrote in message
news:voquva8in6re84@.corp.supernews.com...
> Leonardo,
> There are two problems in your code:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;
> Good luck,
> Shervin
>
> "Leonardo Almeida" <lfaa2004@.yahoo.com> wrote in message
> news:3f8d396f$0$199$75868355@.news.frii.net...
> > Hi,
> > I have a table with the follow fields :
> > ID - Int
> > Date - Datetime
> > I need to make a simple query to result the records between to dates
> > with a single ID.
> > Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230
> > Im using the follow :
> > ADOQuery1.Close;
> > ADOQuery1.SQL.Clear;
> > ADOQuery1.SQL.Add('Select * from Apro where data between Inicio and
> > Final');
> > ADOQuery1.Parameters[0].Value:=Inicio;
> > ADOQuery1.Parameters[2].Value:=Final;
> > ADOQuery1.Open;
> > When I open the query it doesnt work cos its result a null set
> > How can I solve this?
> > Im using SQL Server 2000 and Delphi 6
> > Thanks for atention.
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!|||Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> 2. You have used Parameters[0] and Parameters[2], what about
> Parameters[1]!?
> So this is the correct code:
> with ADOQuery1 do
> begin
> Close;
> SQL.Text := 'Select * from Apro where data between Inicio and Final';
> Parameters[0].Value := Inicio;
> Parameters[1].Value := Final;
> Open;
> end;

Shervin, did you not for get the colons?

Select * from Apro where data between :Inicio and :Final

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You are right, I forgot them. I fixed it.
Thanks Erland.

Shervin

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns9415F34D56661Yazorman@.127.0.0.1...
> Shervin Shapourian (ShShapourian@.hotmail.com) writes:
> > 1. Parameters should start with a colon. (e.g. :Inicio and :Final)
> > 2. You have used Parameters[0] and Parameters[2], what about
> > Parameters[1]!?
> > So this is the correct code:
> > with ADOQuery1 do
> > begin
> > Close;
> > SQL.Text := 'Select * from Apro where data between Inicio and Final';
> > Parameters[0].Value := Inicio;
> > Parameters[1].Value := Final;
> > Open;
> > end;
> Shervin, did you not for get the colons?
> Select * from Apro where data between :Inicio and :Final
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

query database user input from ListBox

I have a problem selecting fields from a table where fields are equal to user input from a listbox. example

listbox of zip codes:

33023
[red]22300[/red]
39844
[red]29339[/red]
23883
[red]38228[/red]

user wants to retreive highlight zip codes from database.
connection working perfect.
Thank you for your help.

you could use IN. check out books on line for more info.

SELECT

<columnlist>

FROM

yourtable

WHERE

zipcode IN ( ....)

|||

Hi There:

You need to retrieve the selected item from yous listbox first. Like this:

<code>

Dim sYourZipString As String = nothing
Dim sItem As ListItem
For Each sItem In zipListBox.Items
If sItem.Selected Then
sYourZipString = sYourZipString & "'" & sItem.Text & "', "
End If
Next

sYourZipString = sYourZipString.Remove(sYourZipString.Length-2, 2) ' remove the tail

</code>

You can use a select IN clause with a parameter which use the value(s) user.

Assume zipcode field is a text field (Char(5), or varchar(5) something not a number).

<code>
Dim conn As SqlConnection
Dim yourcmd As SqlCommand
Dim strConn as string = "yourconnection"

Dim yourSQLString As String
yourSQLString ="Select * FROM youTableIncludeZip WHERE zipcode IN (@.YourZipString)"

conn = New SqlConnection(strConn)

yourcmd = New SqlCommand(strUpdateStmt, conn)
yourcmd.Parameters.Add(New SQLParameter("@.Fname", txtFirstName.text))

Dim objDR as SQLDataReader
dim yourItem1fromDB as string =nothing
dim yourItem2fromDB as string =nothing
dim yourItem3fromDB as string =nothing

Try
conn.Open()
objDR=yourCmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)

While objDR.Read()

yourItem1fromDB=objDR("yourItem1")
yourItem2fromDB=objDR("yourItem2")
yourItem3fromDB=objDR("yourItem3")
' ... get what you want from you Db table
End While

Finally
conn.close

End Try

</code>

Hope this help.

Limno

Query Cost - Big diffrence

I have a table called test with five fields of which no is the primary
key
When I run the following query
select * from tt where no not in (23,76)
select * from tt where no !=23 and no!=76
The cost of first query is 0.53% and that of second is 99.47%
Why is this huge difference?
MadhivananIt's hard to offer any help without the exact table definitions and exact
query you are running. Is your table called tt or test?
In any case, the percentages you quote are estimates, and they
may or may not correspond to actual relative costs. My guess
is that the difference is the result of how the literal constants 23 and 76
are typed by the query processor. Assuming the column [no] is of
type INT, try
select * from tt where [no] != cast(23 as int) and [no] != cast(76 as int)
If this doesn't help, please post the CREATE TABLE statements and
the exact query, along with the query plans for each query.
Steve Kass
Drew University
Madhivanan wrote:

>I have a table called test with five fields of which no is the primary
>key
>When I run the following query
>select * from tt where no not in (23,76)
>select * from tt where no !=23 and no!=76
>The cost of first query is 0.53% and that of second is 99.47%
>Why is this huge difference?
>Madhivanan
>
>|||There are a few places in MSDN which make passing mention of the possibility
that SQL Server's query optimizer may not choose indexes efficiently when
the OR operator a negative comparisons (ex: != ) are used in a WHERE clause.
Look closely at the execution plan and see if index selection is different
between the two queries.
http://msdn.microsoft.com/library/d...
etchapt14.asp
http://support.microsoft.com/defaul...=kb;en-us;68470
You can explicitly tell the query processor which index to use by specifying
an index hint.
http://msdn.microsoft.com/library/d...r />
_8upf.asp
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1123770945.884995.22410@.g43g2000cwa.googlegroups.com...
>I have a table called test with five fields of which no is the primary
> key
> When I run the following query
> select * from tt where no not in (23,76)
> select * from tt where no !=23 and no!=76
> The cost of first query is 0.53% and that of second is 99.47%
> Why is this huge difference?
> Madhivanan
>

Query between Times

i have two DateTime fields One called 'Open' and one 'Closed' . I need to
know if a given time is inbetween the Open and Closed times ?Here's an example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
if @.dt >= @.OpenDate and @.dt <= @.CloseDate
print 'yep'
else
print 'nope'
Bryce|||Woops, I just noticed the word "QUERY" in your post. Here's another
example:
declare @.dt datetime
declare @.OpenDate datetime
declare @.CloseDate datetime
set @.dt = '1/5/2005'
set @.OpenDate = '1/1/2005'
set @.CloseDate = '1/30/2005'
select case when @.dt >= @.OpenDate and @.dt <= @.CloseDate then 1 else 0
end
Of course, when selecting from a table, the variables can be replaced
with column names.
Bryce|||Assuming Open and Cl;osed values are in Variables @.Open and @.Closed,
If this is being done in a Select Statement, then put a Predicate expression
"GivenTimeColName Between Open And Closed"
in the Where Clause.
Select <col List>
From <Table(S)>
Where <GivenTimeColName > Between Open And Closed
If it's being done in T-SQL Code, just put the predicate after an "If "
If @.GivenTimeVariable Between @.Open And @.Closed
Begin
<T-SQL Statements to execute if true
End
Else
Begin
<T-SQL Statements t oexecute if false>
End
"Peter Newman" wrote:

> i have two DateTime fields One called 'Open' and one 'Closed' . I need to
> know if a given time is inbetween the Open and Closed times ?
>|||Use the BETWEEN keyword, as in:
select blablabla
from blablabla
where a_given_time between Open and Closed
Does this help?
Raj|||> set @.dt = '1/5/2005'
Ugh. Is that January 5th or May 1st? Who knows? It depends on several
factors, including regional settings on the machine, SET LANGUAGE, SET
DATEFORMAT settings, etc.
If you're going to hard code dates in the query like that, at least use a
non-ambiguous format.
SET @.dt = '20050105'

> set @.CloseDate = '1/30/2005'
> if @.dt >= @.OpenDate and @.dt <= @.CloseDate
Ugh again. You are including rows from midnight on the close date, but
nothing else. So if something happened at 12:01, it's ignored by your where
clause. I'm not sure if the original poster wanted to include that day or
use it as a cutoff. It is better to use:
IF @.dt >= '20050105' AND @.dt < '20050130'
or
IF @.dt >= '20050105' AND @.dt < '20050131'
(Depending on whether the data from throughout the day on 20050130 should be
included or not.)|||> Select <col List>
> From <Table(S)>
> Where <GivenTimeColName > Between Open And Closed
FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
queries (though it is great for INTs, I will agree). Except in the rare
case where only whole dates (with a midnight timestamp) are stored, it is
always better to use >= start_of_range and < (end_of_range + 1).
http://www.aspfaq.com/2280|||Thanks, Aaron. Very informative.
Bryce|||Aaron,
Is your main rationale for feeling this way becuase of Betweens
inflexible "Inclusive" behavior, which always also returns records which are
equal to the enddate value?
This IS a problem I have not found a clean answer for...
I still like Between, however, because it "reads" much moore naturally, (I
mean closer to the problem you are trying to solve.. It's not a big
difference, I grant you, but when you have constructions like:
Where My_DateCOlumnValue >= @.BeginDateRangeValue And My_DateCOlumnValue <
@.EndDateRangeValue
You have to read the token for the My_DateCOlumnValue twice, in both places,
to ensure that it is indeed the same token... this issue is more significant
when it's NOT a token or column name, but a longer expression based on one o
r
more columns, (Not desired, but sometimes necessary)
In those cases, the Between construction "reads" easier to me than the >= ..
... And ... < ... since the complex expression would only be there once...
But you still have to solve the Inclusive Issue...
on another note, I believe that the Query Processor converts the between
syntax into >= AND <= to execute it anyway ...
"Aaron [SQL Server MVP]" wrote:

> FYI, I find BETWEEN a difficult and ambiguous style to use for DATETIME
> queries (though it is great for INTs, I will agree). Except in the rare
> case where only whole dates (with a midnight timestamp) are stored, it is
> always better to use >= start_of_range and < (end_of_range + 1).
> http://www.aspfaq.com/2280
>
>|||Aaron,
The only point you made worth speaking to is
<<<
Then you will include rows from 20040105 at exactly midnight, but nothing
later. This trips *many* people up, because they assume any row from
20040105 (say, 20040105 15:43:04.872) should be included.
And there are several solutions ot this, some of which I mentioned already.
You're right, this trips up a lot of folks. But I guess your approach is
everybody else (except for you of course) is too stupid to understand this
and therefore should just NOT use such potentially dangerous tools.
There are unfortunately, quite too many people who think that way in this
world.
I prefer to assume, (until proven otherwise) that anyone is capable of
undersstanding the dangers (and the power) of any tool enough to learn to us
e
it intelligently and wisely, at least if someone is kind enough to teach the
m
how, and offer them the knowledge and the opportunity.
So, t oanswer yr final thought, NO, I will not stop attempting to "drown
out" anyone who attempts to limit or restrict what information orr technique
s
are made available, especially whne done because in someone's opinion, some
technique is "too dangerous" for the average person to use...
"Aaron [SQL Server MVP]" wrote:
> places,
> How complex do you name your columns? This wouldn't be an issue if you us
e
> sensible names and avoid nedlessly long and complicated identifiers. And
> unless you are dealing solely with DATETIME columns throughout your entire
> environment, it's something you have to approach from a broader perspectiv
e
> than simply whether to use BETWEEN or separate clauses.
>
> or
> If you are performing calculations against dates on the left side of the
> equation, it is always possible to do tose same calculations on the right
> side of the equation (not only avoiding your complaint, but also giving th
e
> query a better chance to use an index). For example:
> WHERE DATEADD(MONTH, -2, DateTimeColumn) + 1 < '20050101'
> is the same as
> WHERE DateTimeColumn < DATEADD(MONTH, 2, '20050101') - 1
> Comparing these this way is actually more logical, in my opinion, and stil
l
> allows you to use an index and avoid scanning the left side for expression
s
> involving the column name.
>
> Yes, and that's WHY it's a problem. If you have:
> BETWEEN '20040101' AND '20040105'
> Then you will include rows from 20040105 at exactly midnight, but nothing
> later. This trips *many* people up, because they assume any row from
> 20040105 (say, 20040105 15:43:04.872) should be included. If you have the
> true boundary on the right side, you would have to use
> '2004-01-04T23:59:59.997' and pray it doesn't get implicitly converted to
> SMALLDATETIME (which rounds up, yielding the exact same problem).
> But go ahead, keep using BETWEEN. Doesn't sound like the points I'm raisi
ng
> are very important to you anyway (but please don't try to drown them out f
or
> those people who might consider them worthwhile).
> A
>
>

query based on a field in the same row correction

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
A0_01 1,37
(18-ItemPersonPersonal)+ItemPinax_AMM
A0_02 1,4
(18-ItemPersonPersonal)+ItemPinax_AMM
I need to calculate the value of ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
ItemPersonAFM ItemPersonCode ItemPersonPersonal
041 A0_01 10
041 A0_02 12
the answer must be for the first row (18-10)+1,37
for the second row (18-12)+1,4
I use this code
DECLARE @.sql VARCHAR(655)
SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
where ItemPersonAFM='041'
EXEC(@.sql)
and I get 1,37 and 1,4. How can I have the right answers?Helen
DDL Means table definition and sample data means insert commadns that you
use. so that we can simulate here
Regards
R.D
"Helen" wrote:

>
>
> I have two tables ItemsPinax , ItemPerson. The first table has these fie
lds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
> A0_01 1,37
> (18-ItemPersonPersonal)+ItemPinax_AMM
> A0_02 1,4
> (18-ItemPersonPersonal)+ItemPinax_AMM
> I need to calculate the value of ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
> ItemPersonAFM ItemPersonCode ItemPersonPersonal
> 041 A0_01 10
> 041 A0_02 12
> the answer must be for the first row (18-10)+1,37
> for the second row (18-12)+1,4
> I use this code
> DECLARE @.sql VARCHAR(655)
> SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode
> where ItemPersonAFM='041'
> EXEC(@.sql)
> and I get 1,37 and 1,4. How can I have the right answers?|||Helen
CREATE TABLE TAB1 (COL1 INT) IS DDL
INSERT INTO TAB1 VALUES(1) is sample data
Regards
R.D
"R.D" wrote:
> Helen
> DDL Means table definition and sample data means insert commadns that you
> use. so that we can simulate here
> Regards
> R.D
> "Helen" wrote:
>|||CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
decimal ,ItemPinax_FUNCTION varchar(100)
INSERT INTO ItemsPinax
VALUES( A0_01, 1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
varchar(6), ItemPersonPersonal int)
INSERT INTO ItemPerson VALUES(041, A0_01, 10)|||> > the answer must be for the first row (18-10)+1,37
what is 1,37 and 1,4 ?
I dont really understand what exactly you want calculated feild or
substracted from function definition.
it is already 6 pm here, we will tomorrow
Regards
R.D
"Helen" wrote:
> CREATE TABLE ItemsPinax (ItemPinax_CODE varchar(6),ItemPinax_AMM
> decimal ,ItemPinax_FUNCTION varchar(100)
> INSERT INTO ItemsPinax
> VALUES( A0_01, 1.37, 18-ItemPersonPersonal+ItemPinax_AMM)
> CREATE TABLE ItemPerson (ItemPersonAFM char(3), ItemPersonCode
> varchar(6), ItemPersonPersonal int)
> INSERT INTO ItemPerson VALUES(041, A0_01, 10)
>
>|||the answer must be for the first row (18-10)+1.37
1.37 AND 1.4 IS THE VALUES FOR ItemPinax_AMM WHICH I USE FOR THE CALCULATION
"R.D" wrote:
> what is 1,37 and 1,4 ?
> I dont really understand what exactly you want calculated feild or
> substracted from function definition.
> it is already 6 pm here, we will tomorrow
> Regards
> R.D
> "Helen" wrote:
>

query based on a field in the same row

I have two tables ItemsPinax , ItemPerson. The first table has these fields
ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
A0_01 1,37
(18-ItemPersonPersonal)*ItemPinax_AMM
A0_02 1,4
(18-ItemPersonPersonal)*ItemPinax_AMM
I need to calculate the value of ItemPinax_FUNCTION taking the value of
ItemPersonPersonal from table ItemPerson who has these fields
ItemPersonAFM ItemPersonCode ItemPersonPersonal
041 A0_01 10
041 A0_02 12
the answer must be for the first row (18-10)*1,37
for the second row (18-12)*1,4
I use this code
DECLARE @.sql VARCHAR(655)
SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode where
ItemPersonAFM='041'
EXEC(@.sql)
and I get 1,37 and 1,4. How can I have the right answers?pl.post ddl
"Helen" wrote:

> I have two tables ItemsPinax , ItemPerson. The first table has these fiel
ds
> ItemPinax_CODE,ItemPinax_AMM,ItemPinax_F
UNCTION with values:
> A0_01 1,37
> (18-ItemPersonPersonal)*ItemPinax_AMM
> A0_02 1,4
> (18-ItemPersonPersonal)*ItemPinax_AMM
> I need to calculate the value of ItemPinax_FUNCTION taking the value of
> ItemPersonPersonal from table ItemPerson who has these fields
> ItemPersonAFM ItemPersonCode ItemPersonPersonal
> 041 A0_01 10
> 041 A0_02 12
> the answer must be for the first row (18-10)*1,37
> for the second row (18-12)*1,4
> I use this code
> DECLARE @.sql VARCHAR(655)
> SELECT @.sql = 'SELECT ItemPinax_CODE,ItemPinax_AMM,ItemPersonP
ersonal,
> ItemPinax_FUNCTION,'+ ItemPinax_FUNCTION + ' FROM ItemsPinax inner join
> ItemPerson on ItemPinax_CODE=ItemPersonCode where ItemPersonAFM=''041''
> FROM ItemsPinax inner join ItemPerson on ItemPinax_CODE=ItemPersonCode whe
re
> ItemPersonAFM='041'
> EXEC(@.sql)
> and I get 1,37 and 1,4. How can I have the right answers?|||sorry, I don't understand. What to do?
"R.D" wrote:
> pl.post ddl
> "Helen" wrote:
>

query assistance -return most recent date

I have a table that has two fields, pkg_num, which is a number, and
del_date_time, which is a date-time. The table can contain duplicate pkg_num
values, as long as the del_date_time values are different for any given
number. I need a query that will return the most recent del_date_time for
each pkg_num. Any ideas?
On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:

>I have a table that has two fields, pkg_num, which is a number, and
>del_date_time, which is a date-time. The table can contain duplicate pkg_num
>values, as long as the del_date_time values are different for any given
>number. I need a query that will return the most recent del_date_time for
>each pkg_num. Any ideas?
Hi Rich_A2B,
Probably
SELECT pkg_num, MAX(del_date_time)
FROM MyTable
GROUP BY pkg_num
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||That works, thanks! Now to complicate things, I have a third field,
DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
fields in the query result, but only show records with the most recent
DEL_DATE_TIME?
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:
>
> Hi Rich_A2B,
> Probably
> SELECT pkg_num, MAX(del_date_time)
> FROM MyTable
> GROUP BY pkg_num
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||

Quote:

Originally posted by Hugo Kornelis
On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

>That works, thanks! Now to complicate things, I have a third field,
>DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
>DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
>fields in the query result, but only show records with the most recent
>DEL_DATE_TIME?
Hi Rich_A2B,
I guess I should have seen that one coming :-)
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num
AND b.del_date_time > a.del_date_tim)
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
FROM MyTable
GROUP BY pkg_num) AS b
ON a.pkg_num = b.pkg_num
AND a.del_date_time = b.max_del_date_time
or
SELECT a.pkg_num, a.del_date_time, a.del_recip_name
FROM MyTable AS a
WHERE a.del_date_time = (SELECT MAX(del_date_time)
FROM MyTable AS b
WHERE b.pkg_num = a.pkg_num)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)