what went wrong with max date selection?

[g_g_99]

the following query is supposed to list the latest data within min date(2015-11-24) and thru date(2015-12-06) for each fi_sym in an subacct: expected 89 rows but got more than 400 - listed data for 2015-12-06 as well previous dates in the range

declare @Fi_Sym varchar(20)
declare @Fi_Sym_ORIG varchar(20)
declare @rbc_Sym varchar(20)
declare @fi_name varchar(20)
declare @fi_desc varchar(20)
declare @fi_short_desc varchar(20)
declare @CUSIP varchar(20)
declare @fi_sym_US varchar(20)
declare @fi_class varchar(20)
declare @fi_Exchg varchar(20)
declare @acct_grp_ID integer
declare @acct_ID integer
declare @subAcct_ID integer
declare @excludeSubAcctAliasFilterLst varchar(1300)
declare @likeEscpChar varchar(1)
declare @Msg varchar(512)
declare @min_ValuatnDt date
declare @thru_valuatnDt date
declare @ignoreCost smallint
declare @excludeSubAcctAliasRegex varchar(1500)
declare @fi_symRegex varchar(1300)

select '2015-11-24', '2015-12-06', null , null , null, 0 into @min_ValuatnDt,@thru_ValuatnDt,@acct_grp_ID,  @subAcct_ID, @excludeSubAcctAliasRegex, @ignoreCost

select subAcct_ID into #subAcct_IDs from dba.subacct
     where (@acct_ID is null or acct_id = @acct_ID)
     and (@subacct_ID is  null or subAcct_id=@subacct_ID )
     and (@acct_grp_ID is null
               or acct_ID in (select acct_ID from dba.account where acct_grp_ID =@acct_grp_ID))

  select *,'Cost/share'=(select case when qty = 0 then null else cost/qty end),
          Pct=(select case when cost = 0 then null else(mkt_value-cost)/cost*100.0 end)
          from dba.PF_Daily_Holdg_detl as p
          where(@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)
          and(subAcct_ID = @subAcct_Id 
              or (@subAcct_Id is null and @acct_grp_ID is null)
              or (p.subAcct_Id in (select subAcct_ID from #subAcct_IDs)))
          and(@excludeSubAcctAliasRegex is null
              or $Regex_match(p.sub_Acct_alias_dd,@excludeSubAcctAliasRegex) = 0)
          and(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt)
          and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)
          and ((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11)
               or(@ignoreCost = 11 and(cost <= 0 or cost is null))
          and qty > 0 -- 151119 added or cost is null
          and valuation_dt =
            (select max(a.valuation_dt)
                   from dba.PF_Daily_Holdg_detl as a
                  where a.subacct_ID = p.subacct_ID 
                    and a.fi_sym = p.fi_sym
                    and a.acct_grp_ID = p.acct_grp_ID
                    and (@min_ValuatnDt is null or a.valuation_dt >= @min_ValuatnDt)
                    and (@thru_ValuatnDt is null or a.valuation_dt <= @thru_ValuatnDt)
                    and (((a.cost > 0 or a.cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(a.cost <= 0 or a.cost is null))) -- 151119 added or cost is null
        )

[VolkerBarth · Accepted solution]

and ((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11) or(@ignoreCost = 11 and(cost <= 0 or cost is null)) and qty > 0 -- 151119 added or cost is null

If the braces are as stated here, I would think here's a OR-condition that is not linked with the previous or further AND-conditions, so the date range will not be applied as desired. (In other words: the "...< 11) or(@ignoreCost...)" is not a nested condition but leads to two sets of conditions for the whole SELECT.) - I guess you might have to enclose the cited condition in its own braces, too.

Just my 2 cents, without knowing the schema...

[g_g_99 · Accepted solution]

thanks, are we talking about and (@min_ValuatnDt is null or a.valuation_dt >= @min_ValuatnDt) and (@thru_ValuatnDt is null or a.valuation_dt <= @thru_ValuatnDt) ? but in this case both min and thru date has been initialized.
the schema are

  CREATE TABLE "dba"."PF_Daily_Holdg_detl" (
    "Valuation_dt" DATE NOT NULL,
    "Acct_grp_ID" "TAcctGrp_ID" NOT NULL,
    "subAcct_ID" "TSubAcct_ID" NOT NULL,
    "Fi_Sym" VARCHAR(20) NOT NULL,
    "sub_acct_alias_dd" VARCHAR(16) NULL,
    "Qty" "TQty_stock" NULL,
    ....,
    "Cost" "Tmoney_2" NULL,
    ...,
    PRIMARY KEY ( "Valuation_dt" ASC, "Acct_grp_ID" ASC, "subAcct_ID" ASC, "Fi_Sym" ASC )
) IN "SYSTEM";
// *******
CREATE TABLE "dba"."account" (
    "acct_id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "acct_grp_id" "T__AcctGrp_ID" NOT NULL,
    ...,
    "sub_Acct_Alias" VARCHAR(16) NOT NULL,
    "Updated_By" VARCHAR(30) NULL,
    "Updated" "datetime" NULL,
    CONSTRAINT "PK_AccountID" PRIMARY KEY ( "acct_id" ASC )
) IN "SYSTEM";
// *******
CREATE TABLE "dba"."SubAcct" (
    "subAcct_ID" "TSubAcct_ID" NOT NULL DEFAULT AUTOINCREMENT,
    "Acct_ID" "TAcct_ID" NOT NULL,
    ...,
    "acct_type_short_name" VARCHAR(28) NOT NULL UNIQUE,
    PRIMARY KEY ( "subAcct_ID" ASC )
) IN "SYSTEM";

[VolkerBarth · Accepted solution]

No, that's not what I'm talking about.

My point (or guess) is: The condition I have cited in my answer should be enclosed in another pair of braces, otherwise your conditions are logically somewhat like

from dba.PF_Daily_Holdg_detl as p
          -- condition block 1
          where (@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)
          ... 
          and ((cost > 0 or cost is null or @ignoreCost = 1) and @ignoreCost < 11)

          or

          -- condition block 2
          (@ignoreCost = 11 and(cost <= 0 or cost is null))
          and qty > 0 -- 151119 added or cost is null
          and valuation_dt = ...

i.e. the ORed condition on "cost" separates your list of conditions into two logical blocks, and the filter on "(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt) and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)" is only part of the first condition block.

In contrast, the somewhat similar condition on "cost" in the subquery that returns "max(a.valuation_dt)" has a further pair of braces, so there is just one block of ANDed conditions. That makes me think the condition in the main query block needs more braces, too.

[g_g_99 · Accepted solution]

thank you, Volker.

Following your suggestion,I used an online brace match on the sql bit by bit, I found all the culprit and now works

BEGIN
//For an account grp within date range, find latest cost of each security within each subaccount in the acct grp
declare @Fi_Sym varchar(20)
declare @Fi_Sym_ORIG varchar(20)
declare @rbc_Sym varchar(20)
declare @fi_name varchar(20)
declare @fi_desc varchar(20)
declare @fi_short_desc varchar(20)
declare @CUSIP varchar(20)
declare @fi_sym_US varchar(20)
declare @fi_class varchar(20)
declare @fi_Exchg varchar(20)
declare @acct_grp_ID integer
declare @acct_ID integer
declare @subAcct_ID integer
declare @excludeSubAcctAliasFilterLst varchar(1300)
declare @likeEscpChar varchar(1)
declare @Msg varchar(512)
declare @min_ValuatnDt date
declare @thru_valuatnDt date
declare @ignoreCost smallint
declare @excludeSubAcctAliasRegex varchar(1500)
declare @fi_symRegex varchar(1300)
select '2015-11-24', '2015-12-06', null , null , null, 0 into @min_ValuatnDt,@thru_ValuatnDt,@acct_grp_ID,  @subAcct_ID, @excludeSubAcctAliasRegex, @ignoreCost
select subAcct_ID into #subAcct_IDs from dba.subacct
     where (@acct_ID is null or acct_id = @acct_ID)
     and (@subacct_ID is  null or subAcct_id=@subacct_ID )
     and (@acct_grp_ID is null or acct_ID in (select acct_ID from dba.account where acct_grp_ID =@acct_grp_ID))

  select *,'Cost/share'=(select case when qty = 0 then null else cost/qty end),
          Pct=(select case when cost = 0 then null else(mkt_value-cost)/cost*100.0 end)
          from dba.PF_Daily_Holdg_detl as p
          where(@acct_grp_ID is null or p.acct_grp_ID = @acct_grp_ID)
          and(subAcct_ID = @subAcct_Id or (@subAcct_Id is null and @acct_grp_ID is null) or (p.subAcct_Id in (select subAcct_ID from #subAcct_IDs)))
          and(@excludeSubAcctAliasRegex is null or $Regex_match(p.sub_Acct_alias_dd,@excludeSubAcctAliasRegex) = 0)
          and(@min_ValuatnDt is null or valuation_dt >= @min_ValuatnDt)
          and(@thru_ValuatnDt is null or valuation_dt <= @thru_ValuatnDt)
          and (((cost > 0 or (cost is null and @ignoreCost = 1)) and @ignoreCost < 11  ) or (@ignoreCost = 11 and(cost <= 0 or cost is null)) )
      and qty>0
      and valuation_dt =
            (select max(a.valuation_dt) from dba.PF_Daily_Holdg_detl as a
                where a.subacct_ID = p.subacct_ID and a.fi_sym = p.fi_sym and a.acct_grp_ID = p.acct_grp_ID
                and (@min_ValuatnDt is null or a.valuation_dt >= @min_ValuatnDt)
                and (@thru_ValuatnDt is null or a.valuation_dt <= @thru_ValuatnDt)
                and (((cost > 0 or (cost is null and @ignoreCost = 1)) and @ignoreCost < 11  ) or (@ignoreCost = 11 and(cost <= 0 or cost is null)) )-- 151119 added or cost is null
            )
end

[VolkerBarth · Accepted solution]

an online brace match on the sql bit by bit

Wow, I tend to use the old-school method: Counting the "brace level" on the fingers of my hands, I guess I've been trained by that in the early nineties when working with Turbo Vision:)