Very Weird Results

[michaela18]

If I run the same queries i get different results. The first query I dont specified the promotioncode, I just filter the results in excel to the promotioncode I want. The second query I specify the promotion code but I get different numbers.

The first query I run is

Select month(a.calldate), a.callvendorcode, a.promotioncode, count(*) 'AFGA'
from history.dbo.historyrecords a, Type.dbo.TypeRecords b
where a.recordid= b.recordid
and a.calldate between '20100201' and '20100228'
--and a.CallDate >= '20100220'
and a.Statein ('In', 'Order')
and a.Priceplan <> ''
and (a.priceplan <> b.price_plan)
and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593')
and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593') 

group by month(a.calldate), a.callvendorcode, a.promotioncode

Then i run the same query just just specifying the promotioncode

from history.dbo.historyrecords a, Type.dbo.TypeRecords b
where a.recordid= b.recordid
and a.calldate between '20100201' and '20100228'
--and a.CallDate >= '20100220'
and a.Statein ('In', 'Order')
and a.PromotionCode IN ('AV123', 'AV127', 'AV143')
and a.Priceplan <> ''
and (a.priceplan <> b.price_plan)
and a.priceplan in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593')
and b.price_plan not in ('72876','72877','72878','72879','72880','72881','72883','72885','72887','72889','72891','72893','73215',
'73216','73217','73218','73219','73220','73221','73222','73223','73224','73225','73226','73227','73228','73229','73230','73231',
'73232','73430','73431','73432','73433','73434','73435','73442','73443','73444','73445','73446','73448','73469','73470','73471',
'73472','73473','73474','73478','73479','73481','73482','73484','73485','73487','73488','73490','73491','73493','73494','83235',
'73946','73947','73956','73957','73958','73959','73960','73961','73962','74558','74566','74569','74577','74591','74592','74593') 

group by month(a.calldate), a.callvendorcode,

 

[simian336]

One would expect different results. Could you clarify and post some examples?

Simi

 

[michaela18]

Why would it get different results though if I am filtering the same promotion codes in the data as oppose to it being already filtered?

 

[simian336]

Without seeing you data it would be hard to verify but think abou the nature of "and". In the first query you specify a set of resultes. Then you filter out a some records based on that result set.

In the second query you add a additional criteria with "and" that changes the result set.

You are talking about apples and orages.

If you want to make an apples to apples comparision you could use CTE query on the first query or query or to a temp table and simply filter out the records. Then you should see more similar results.

Simi

 

[drlex]

May I just interject and say that the sections of code "a.priceplan in (<humungous list>)" and "b.price_plan not in (<ginormous list>)" make me cringe and mentally shiver?
Thanks!

soi là, soi carré

 

[markros]

Same here - I didn't even notice we're talking about two separate lists.

PluralSight Learning Library

 

[drlex]

They are probably identical lists, but compounding the horror is the fact their repetition is unnecessary, given that "(a.priceplan <> b.price_plan)" is already in the filter.

soi là, soi carré

 

[markros]

Right! LOL

PluralSight Learning Library