I do a gazillion different daily reports --- sql v2k --- at the moment, i am referring to my Daily and Month-to-Date reports. Same logic, one just looks at current day, one looks at the entire month-to-date. As of late, it's come to my attention that there are some variances between the reports that shouldn't be. so i'm going in double checking all the logic is the same between the two (except for the time parms)
anyway, i used to do a bunch of IN lists.
-where endpoint IN (great big huge in list)
-CASE where lflag = this, then that.... a ton of these
-CASE where symbol = this, then that.... a ton of these
so, for perfomance reasons, I got rid of my IN lists, replacing each w/joins to the appropriate definitional tables, retrieving the values i need where exists. but again, for whatever reason, i'm too high on the monthly. or,this past monday (oct 1st) the two did not equate
like i said, i'm comparing the logic and i wonder if i may be inadvertently excluding from the dataset because of my LEFT JOINS. Don't LEFT's only return where there are matches, yet LEFT OUTER will return all rows in the left table, wehter matched in the right or not. yes?
i was just wondering --- might the current version down there exclude from the resultset? if so, will the proposed new version take care of things?
and if LEFT vs LEFT OUTER changes nothing, are there any other suggestions regarding what the problem may be, or even how to improve the code at all?
current:
SELECT tr.EndPoint,
ISNULL(lft.Description, ltrim(rtrim(tr.lflag))) AS Liquidity,
sdd.Duration,
Symbol,
CAST(count(*) AS MONEY) as countOfTrades,
SUM(tr.Quantity) AS 'Volume ',
SUM(tr.Quantity*CAST(tr.Pr
ice AS Money)) AS 'Total $ '
FROM database.dbo.historicaltab
le tr WITH (NOLOCK)
LEFT JOIN database.dbo.LFlagTranslat
e lft
ON tr.lFlag = lft.LFlag
LEFT JOIN database.dbo.SymbolDuratio
nDescripti
on sdd
ON tr.symbol LIKE sdd.SubSymbol
INNER JOIN database.dbo.grouptable
ON tr.endpoint = eg.endpoint
AND eg.epgroup = 'EQTY'
WHERE tradetime BETWEEN @start AND @stop
AND tr.lflag NOT IN ('LA','LP')
GROUP BY
tr.endpoint,
ISNULL(lft.Description, ltrim(rtrim(tr.lflag))),
tr.Symbol,
sdd.Duration
proposed new:
SELECT tr.EndPoint,
ISNULL(lft.Description, ltrim(rtrim(tr.lflag))) AS Liquidity,
sdd.Duration,
Symbol,
CAST(count(*) AS MONEY) as countOfTrades,
SUM(tr.Quantity) AS 'Volume ',
SUM(tr.Quantity*CAST(tr.Pr
ice AS Money)) AS 'Total $ '
FROM database.dbo.historicaltab
le tr WITH (NOLOCK)
INNER JOIN database.dbo.grouptable epg
ON tr.endpoint = epg.endpoint
LEFT OUTER JOIN database.dbo.LFlagTranslat
e lft
ON tr.lFlag = lft.LFlag
LEFT OUTER JOIN database.dbo.SymbolDuratio
nDescripti
on sdd
ON tr.symbol LIKE sdd.SubSymbol
WHERE tradetime BETWEEN @start AND @stop
AND epg.epgroup = 'eqty'
AND tr.lflag NOT IN ('LA','LP')
GROUP BY
tr.endpoint,
ISNULL(lft.Description, ltrim(rtrim(tr.lflag))),
tr.Symbol,
sdd.Duration
please advise. any thoughts/guidance is very much appreciated
Start Free Trial