A, B and C are the same but at least D or E are different (was "noob question")

hi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DBhi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DB

is this wat u wanted ?
Select A,B,C,D,E From Table1 Where Exists (Select 1 from Table1 Where (A=B and B=C) and (( A <>E) or (A <>D)))

Gurus, please comment|||I'm afraid your query won't work :o

Firstly, Exists isn't necessary. Secondly, if the condition is true for any of the rows it will display all the rows (the exists statement is not corrolated with the main query). Minor but SELECT * is recommended within an exists statement as the optimser will then chose the best index for the job (doubt it would make much difference here but I like to use it by habit).

Nearly there - a slight reworking:
Select A,B,C,D,E
From Table1
Where A=B and B=C and (A <> E or A <> D)|||Select A,B,C,D,E From Test
Where A=B
and A=C
and ((A=D or A=E)
or D<>E)

hope this works for u.|||Thanks for the suggessions|||Here's some NZDF code that will do what you've asked for:CREATE TABLE g11DB (
A INT
, B INT
, C INT
, D INT
, E INT
)

INSERT INTO g11DB (
A, B, C, D, E)
SELECT 1, 2, 3, 4, 5 UNION ALL
SELECT 1, 1, 1, 4, 5 UNION ALL
SELECT 1, 1, 1, 1, 5 UNION ALL
SELECT 1, 1, 1, 4, 1 UNION ALL
SELECT 1, 2, 1, 4, 1

SELECT *
FROM g11DB
WHERE 0 = A - B
AND A = C
AND A != D
UNION SELECT *
FROM g11DB
WHERE A = B
AND -A = -C
AND 0 != (A - E)-PatP|||Always the skeptic, Pat.|||Thanks guys but it seems i didn't properly state what I wanted... The code I was looking for is something like this (although i'm still wondering if there's some more efficient version...
SELECT IPA.*
FROM dbo.IRS_PAYMENT IPA INNER JOIN
dbo.IRS_PAYMENT IPB ON IPA.ip_employee_id = IPB.ip_employee_id
AND IPA.ip_paydate = IPB.ip_paydate
AND IPA.ip_pay_indicator = IPB.ip_pay_indicator
AND (NOT (IPA.ip_total_tax = IPB.ip_total_tax)
OR NOT (IPA.ip_net_pay = IPA.ip_net_pay))
ORDER BY IPA.ip_employee_id

what's NSDF stand for btw? :)|||How about:SELECT A.*
FROM dbo.IRS_PAYMENT AS A
INNER JOIN dbo.IRS_PAYMENT AS B
ON (B.ip_employee_id = A.ip_employee_id
AND B.ip_paydate = A.ip_paydate
AND B.ip_pay_indicator = A.ip_pay_indicator)
WHERE B.ip_total_tax != A.ip_total_tax
OR B.ip_net_pay != A.ip_net_pay
ORDER BY A.ip_employee_id, A.ip_paydate, A.ip_pay_indicator-PatP

A more efficient query

I am trying to add and subtract a few fields in a table to determine return
on investment
Currently i'm performing this as follows:
SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue,
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
ReturnOnInvestment
FROM
TableName
Is there a more efficient way of doing this, i am calling the same
calcutions twice so it seems there must be.
I tried setting variables to the costs and revenues, but multiple results
are being returned so this proved difficult.
Any help would be appreciated, thanks
You could try a derived table, but I seriously doubt you'll see any
performance increase:
SELECT
Cost, Revenue
(Revenue - Cost) as ReturnOnInvestment
FROM
(SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue
FROM TableName) x(Cost, Revenue)
"GrantMagic" <grant@.magicalia.com> wrote in message
news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> I am trying to add and subtract a few fields in a table to determine
return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
>
> Any help would be appreciated, thanks
>
|||Yeah, i tested the two methods against each other and there is no difference
between the two
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> You could try a derived table, but I seriously doubt you'll see any
> performance increase:
>
> SELECT
> Cost, Revenue
> (Revenue - Cost) as ReturnOnInvestment
> FROM
> (SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue
> FROM TableName) x(Cost, Revenue)
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> return
ColumnCost2))[vbcol=seagreen]
> as
results
>
|||You could try creating computed columns and index them. That might be quite
a bit faster...
ALTER TABLE TableName
ADD Cost AS (ColumnCost1 + ColumnCost2)
ALTER TABLE TableName
ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
ALTER TABLE TableName
ADD ReturnOnInvestment AS
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
ReturnOnInvestment)
-- You should probably try to make this into a covering index, with the
rest of the columns in your real query
"GrantMagic" <grant@.magicalia.com> wrote in message
news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Yeah, i tested the two methods against each other and there is no
difference
> between the two
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> ColumnCost2))
> results
>
|||Thanks, i will give that a try.
Would i need to drop those columns after my query, or only create them once?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eBh6oPdkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> You could try creating computed columns and index them. That might be
quite
> a bit faster...
> ALTER TABLE TableName
> ADD Cost AS (ColumnCost1 + ColumnCost2)
> ALTER TABLE TableName
> ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
> ALTER TABLE TableName
> ADD ReturnOnInvestment AS
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
> CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
> ReturnOnInvestment)
> -- You should probably try to make this into a covering index, with the
> rest of the columns in your real query
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> difference
>
|||"GrantMagic" <grant@.magicalia.com> wrote in message
news:uD9r5idkEHA.556@.tk2msftngp13.phx.gbl...
> Thanks, i will give that a try.
> Would i need to drop those columns after my query, or only create them
once?
Only once, they'll be columns in your table after that, just like any
other column (except you won't be able to update them; they'll be
automatically computed when you insert or update the other columns)
|||GrantMagic,
These calculations are so basic and highly optimized for any CPU, that
there will be no way to create any significant performance gain by
rewriting the statement. The current cost of the calculation part is
simply too low (in comparison with I/O, network speed, logical reads,
etc.)
Gert-Jan
GrantMagic wrote:
> I am trying to add and subtract a few fields in a table to determine return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
> Any help would be appreciated, thanks
(Please reply only to the newsgroup)

A more efficient query

I am trying to add and subtract a few fields in a table to determine return
on investment
Currently i'm performing this as follows:
SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue,
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
ReturnOnInvestment
FROM
TableName
Is there a more efficient way of doing this, i am calling the same
calcutions twice so it seems there must be.
I tried setting variables to the costs and revenues, but multiple results
are being returned so this proved difficult.
Any help would be appreciated, thanksYou could try a derived table, but I seriously doubt you'll see any
performance increase:
SELECT
Cost, Revenue
(Revenue - Cost) as ReturnOnInvestment
FROM
(SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue
FROM TableName) x(Cost, Revenue)
"GrantMagic" <grant@.magicalia.com> wrote in message
news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> I am trying to add and subtract a few fields in a table to determine
return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
>
> Any help would be appreciated, thanks
>|||Yeah, i tested the two methods against each other and there is no difference
between the two
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> You could try a derived table, but I seriously doubt you'll see any
> performance increase:
>
> SELECT
> Cost, Revenue
> (Revenue - Cost) as ReturnOnInvestment
> FROM
> (SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue
> FROM TableName) x(Cost, Revenue)
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> > I am trying to add and subtract a few fields in a table to determine
> return
> > on investment
> >
> > Currently i'm performing this as follows:
> >
> > SELECT
> > (ColumnCost1 + ColumnCost2) as Cost,
> > (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> > ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 +
ColumnCost2))
> as
> > ReturnOnInvestment
> > FROM
> > TableName
> >
> > Is there a more efficient way of doing this, i am calling the same
> > calcutions twice so it seems there must be.
> >
> > I tried setting variables to the costs and revenues, but multiple
results
> > are being returned so this proved difficult.
> >
> >
> > Any help would be appreciated, thanks
> >
> >
>|||You could try creating computed columns and index them. That might be quite
a bit faster...
ALTER TABLE TableName
ADD Cost AS (ColumnCost1 + ColumnCost2)
ALTER TABLE TableName
ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
ALTER TABLE TableName
ADD ReturnOnInvestment AS
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
ReturnOnInvestment)
-- You should probably try to make this into a covering index, with the
rest of the columns in your real query
"GrantMagic" <grant@.magicalia.com> wrote in message
news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Yeah, i tested the two methods against each other and there is no
difference
> between the two
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> > You could try a derived table, but I seriously doubt you'll see any
> > performance increase:
> >
> >
> > SELECT
> > Cost, Revenue
> > (Revenue - Cost) as ReturnOnInvestment
> > FROM
> > (SELECT
> > (ColumnCost1 + ColumnCost2) as Cost,
> > (ColumnRevenue1 + ColumnRevenue2) as Revenue
> > FROM TableName) x(Cost, Revenue)
> >
> >
> > "GrantMagic" <grant@.magicalia.com> wrote in message
> > news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> > > I am trying to add and subtract a few fields in a table to determine
> > return
> > > on investment
> > >
> > > Currently i'm performing this as follows:
> > >
> > > SELECT
> > > (ColumnCost1 + ColumnCost2) as Cost,
> > > (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> > > ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 +
> ColumnCost2))
> > as
> > > ReturnOnInvestment
> > > FROM
> > > TableName
> > >
> > > Is there a more efficient way of doing this, i am calling the same
> > > calcutions twice so it seems there must be.
> > >
> > > I tried setting variables to the costs and revenues, but multiple
> results
> > > are being returned so this proved difficult.
> > >
> > >
> > > Any help would be appreciated, thanks
> > >
> > >
> >
> >
>|||Thanks, i will give that a try.
Would i need to drop those columns after my query, or only create them once?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eBh6oPdkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> You could try creating computed columns and index them. That might be
quite
> a bit faster...
> ALTER TABLE TableName
> ADD Cost AS (ColumnCost1 + ColumnCost2)
> ALTER TABLE TableName
> ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
> ALTER TABLE TableName
> ADD ReturnOnInvestment AS
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
> CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
> ReturnOnInvestment)
> -- You should probably try to make this into a covering index, with the
> rest of the columns in your real query
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> > Yeah, i tested the two methods against each other and there is no
> difference
> > between the two
> >
> > "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> > news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> > > You could try a derived table, but I seriously doubt you'll see any
> > > performance increase:
> > >
> > >
> > > SELECT
> > > Cost, Revenue
> > > (Revenue - Cost) as ReturnOnInvestment
> > > FROM
> > > (SELECT
> > > (ColumnCost1 + ColumnCost2) as Cost,
> > > (ColumnRevenue1 + ColumnRevenue2) as Revenue
> > > FROM TableName) x(Cost, Revenue)
> > >
> > >
> > > "GrantMagic" <grant@.magicalia.com> wrote in message
> > > news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> > > > I am trying to add and subtract a few fields in a table to determine
> > > return
> > > > on investment
> > > >
> > > > Currently i'm performing this as follows:
> > > >
> > > > SELECT
> > > > (ColumnCost1 + ColumnCost2) as Cost,
> > > > (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> > > > ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 +
> > ColumnCost2))
> > > as
> > > > ReturnOnInvestment
> > > > FROM
> > > > TableName
> > > >
> > > > Is there a more efficient way of doing this, i am calling the same
> > > > calcutions twice so it seems there must be.
> > > >
> > > > I tried setting variables to the costs and revenues, but multiple
> > results
> > > > are being returned so this proved difficult.
> > > >
> > > >
> > > > Any help would be appreciated, thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||"GrantMagic" <grant@.magicalia.com> wrote in message
news:uD9r5idkEHA.556@.tk2msftngp13.phx.gbl...
> Thanks, i will give that a try.
> Would i need to drop those columns after my query, or only create them
once?
Only once, they'll be columns in your table after that, just like any
other column (except you won't be able to update them; they'll be
automatically computed when you insert or update the other columns)|||GrantMagic,
These calculations are so basic and highly optimized for any CPU, that
there will be no way to create any significant performance gain by
rewriting the statement. The current cost of the calculation part is
simply too low (in comparison with I/O, network speed, logical reads,
etc.)
Gert-Jan
GrantMagic wrote:
> I am trying to add and subtract a few fields in a table to determine return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
> Any help would be appreciated, thanks
--
(Please reply only to the newsgroup)