Filtering data based on select parameter values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filtering data based on select parameter values

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-06-06 : 15:08:30

Hi,

I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:

FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE

Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?

@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1

SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)

Thanks in advance!!!
sqlnovice123

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-06-06 : 23:25:17

Why don't you test for @subaccount = NULL then call FunctionSub1 or FunctionSub2 based on the answer. Your main proc serves as a wrapper and you don't get recompiles based on having wildly different criteria.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2006-06-08 : 11:13:26

Hi derrick,

Using NULL would return all values but the data does not list data for the subaccount values("FRMM","ROBECO" and numDaysCutoff(4) , numDaysPcts(7) that I am interested in .

True the procedure restricts a single the subaccount parameter value like
exec QCRS_AcctHoldingsVsDailyVol "MAIL",4,7

Can I use a function that would act like executing the procedure multiple times ?

For example:

exec QCRS_AcctHoldingsVsDailyVol "MAIL",4,7
exec QCRS_AcctHoldingsVsDailyVol "FRMM",4,7
exec QCRS_AcctHoldingsVsDailyVol "FRICE",4,7

returns
Subaccount NDaysCutoff NDaysPcts
MAIL 4 7
Subaccount NDaysCutoff NDaysPcts

Subaccount NDaysCutoff NDaysPcts

Thanks
sqlnovice123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources