rohitkocharda

Forum Replies Created

Viewing 15 posts - 16 through 30 (of 30 total)

Reply To: performance tuning for a subquery
is this better?
ALTER PROCEDURE [dbo].[Spgetentities_ro_test]
---1:19:15
@AsOfDate            DATE = '9999-12-31',
@PrivateMarketStatus BIT = 0
AS
SET nocount ON;
SELECT countryofissueid,
masterentityid,
validfromdate,
validtodate,
countryofincorporationid,
countryofdomicileid,
marketstatusid,
primarycurrencyid,
primaryexchangeid,
sourceentityid,
NAME,
primaryexchangeticker,
tickerexchangecode,
recentperiodenddate,
entityid,
recentsemiend,
recentquarterend,
recentquarterenddate,
recentbsperiod,
iscurrent
INTO   dbo.#entity
FROM   [dbo].[entity]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
CREATE CLUSTERED INDEX ix_t1
ON dbo.#entity(entityid)
ON [PRIMARY];
CREATE NONCLUSTERED INDEX #ice1
ON dbo.#entity ( masterentityid);
SELECT *
INTO   dbo.#entityindustry
FROM   [dbo].[entityindustry]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
CREATE CLUSTERED INDEX ix_t3
ON dbo.#entityindustry([entityindustryid])
ON [PRIMARY]
SELECT *
INTO   dbo.#securityentityindustry
FROM   [dbo].[securityentityindustry]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
CREATE CLUSTERED INDEX ix_t4
ON dbo.#securityentityindustry(securityentityindustryid)
ON [PRIMARY]
SELECT securityid,
[masterentityid],
validfromdate,
validtodate,
[mastersecurityid],
countryissuedid,
isprimarysecurity,
hasmultipleshares,
countryofdomicileid,
countryofincorporationid
INTO   dbo.#security
FROM   [dbo].[security]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
CREATE CLUSTERED INDEX ix_t1
ON dbo.#security(securityid)
ON [PRIMARY]
CREATE NONCLUSTERED INDEX #icc1
ON dbo.#security ( countryissuedid, masterentityid, isprimarysecurity);
CREATE NONCLUSTERED INDEX #icc2
ON dbo.#security ( countryofincorporationid, masterentityid,
isprimarysecurity);
CREATE NONCLUSTERED INDEX #icc3
ON dbo.#security ( countryofdomicileid, masterentityid, isprimarysecurity)
;
CREATE NONCLUSTERED INDEX #icc4
ON dbo.#security ( mastersecurityid, masterentityid, isprimarysecurity);
SELECT *
INTO   dbo.#vwbics
FROM   vwbics
SELECT e.[masterentityid],
[entityid],
e.[name]                AS NAME,
e.[sourceentityid]      AS ID_BB_COMPANY,
ID_BB_UNIQUE=map.sourcesecurityid,
[primaryexchangeticker] AS TICKER,
ex.code                 AS EXCH_CODE,
cr.code                 AS EQY_FUND_CRNCY,
[tickerexchangecode]    AS TICKER_AND_EXCH_CODE,
ms.code                 AS MARKET_STATUS,
(SELECT Max(Cast(hasmultipleshares AS TINYINT))
FROM   [#security] s
WHERE  s.masterentityid = e.masterentityid
--AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate
)                       AS MULTIPLE_SHARE,
CNTRY_ISSUE_NAME = COALESCE ((SELECT TOP 1 ciss.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] ciss
WITH(
nolock)
ON ciss.countryid =
e2.countryofissueid
AND @AsOfDate BETWEEN
ciss.validfromdate
AND
ciss.validtodate
WHERE  e2.masterentityid =
e.masterentityid
),
(SELECT TOP 1 ciss.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[country] ciss
WITH
(nolock
)
ON ciss.countryid =
s.countryissuedid
AND @AsOfDate BETWEEN
ciss.validfromdate
AND
ciss.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_ISSUE_ISO = COALESCE((SELECT TOP 1 ciss.isocode2
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] ciss WITH(
nolock)
ON ciss.countryid =
e2.countryofissueid
AND @AsOfDate BETWEEN
ciss.validfromdate AND
ciss.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 ciss.isocode2
FROM   dbo.#security s
LEFT JOIN [dbo].[country] ciss WITH(
nolock)
ON ciss.countryid =
s.countryissuedid
AND @AsOfDate BETWEEN
ciss.validfromdate AND
ciss.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_INCORPORATION_NAME = COALESCE((SELECT TOP 1 cinc.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country]
cinc
WITH(nolock)
ON cinc.countryid =
e2.countryofincorporationid
AND @AsOfDate BETWEEN
cinc.validfromdate
AND
cinc.validtodate
WHERE  e2.masterentityid =
e.masterentityid),
(SELECT TOP 1 cinc.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid =
s.countryofincorporationid
AND @AsOfDate BETWEEN
cinc.validfromdate
AND
cinc.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_INCORPORATION = COALESCE((SELECT TOP 1 cinc.isocode2
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cinc
WITH(
nolock)
ON cinc.countryid =
e2.countryofincorporationid
AND @AsOfDate BETWEEN
cinc.validfromdate AND
cinc.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cinc.isocode2
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cinc
WITH(nolock)
ON cinc.countryid =
s.countryofincorporationid
AND
@AsOfDate BETWEEN cinc.validfromdate AND
cinc.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_DOMICILE_NAME = COALESCE((SELECT TOP 1 cdom.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cdom WITH(
nolock)
ON cdom.countryid =
e2.countryofdomicileid
AND @AsOfDate BETWEEN
cdom.validfromdate AND
cdom.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cdom.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cdom
WITH(nolock)
ON cdom.countryid =
s.countryofdomicileid
AND
@AsOfDate BETWEEN
cdom.validfromdate AND
cdom.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_DOMICILE = COALESCE((SELECT TOP 1 cdom.isocode2
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid =
e2.countryofdomicileid
AND @AsOfDate BETWEEN
cdom.validfromdate AND
cdom.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cdom.isocode2
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cdom WITH(
nolock)
ON cdom.countryid =
s.countryofdomicileid
AND @AsOfDate BETWEEN
cdom.validfromdate AND
cdom.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
[recentperiodenddate]   AS MOST_RECENT_PERIOD_END_DT,
[recentsemiend]         AS EQY_RECENT_SEMI_END,
[recentquarterend]      AS EQY_RECENT_QT_END_QT,
[recentquarterenddate]  AS EQY_RECENT_QT_END_DT,
[recentbsperiod]        AS EQY_RECENT_BS_QT,
EQY_SIC_NAME = COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 1), (SELECT
TOP 1 i.NAME
FROM
dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei WITH
(nolock)
ON sei.mastersecurityid =
s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 1)),
EQY_SIC_CODE = COALESCE ((SELECT TOP 1 i.code1
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 1), (SELECT
TOP 1 i.code1
FROM
dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid =
s.mastersecurityid
---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 1)),
INDUSTRY_SECTOR = COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid =
e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 2), (SELECT
TOP 1 i.NAME
FROM
dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry]
sei
ON sei.mastersecurityid =
s.mastersecurityid
AND @AsOfDate BETWEEN
sei.validfromdate AND
sei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 2)),
INDUSTRY_GROUP = COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid =
e2.masterentityid
AND @AsOfDate BETWEEN
ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 3), (SELECT
TOP 1 i.NAME
FROM
dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid =
s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 3)),
INDUSTRY_SUBGROUP = COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid =
e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 4), (
SELECT TOP 1 i.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock
)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 4)),
EQY_FUND_IND = COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 5), (SELECT
TOP 1 i.NAME
FROM
dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid =
s.mastersecurityid
----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 5)),
BICS_1=bics_1.bics_name,
BICS_1_CODE=bics_1.bics_code,
BICS_2=bics_2.bics_name,
BICS_2_CODE=bics_2.bics_code,
BICS_3=bics_3.bics_name,
BICS_3_CODE=bics_3.bics_code,
BICS_4=bics_4.bics_name,
BICS_4_CODE=bics_4.bics_code,
BICS_5=bics_5.bics_name,
BICS_5_CODE=bics_5.bics_code,
BICS_6=bics_6.bics_name,
BICS_6_CODE=bics_6.bics_code,
BICS_7=bics_7.bics_name,
BICS_7_CODE=bics_7.bics_code,
e.[validfromdate]       AS ValidFromDate,
e.[validtodate]         AS ValidToDate,
[iscurrent]
FROM   [dbo].[#entity] e WITH(nolock)
JOIN [dbo].[marketstatus] ms WITH(nolock)
ON ms.marketstatusid = e.marketstatusid
JOIN [dbo].currency cr WITH(nolock)
ON cr.currencyid = e.primarycurrencyid
JOIN [dbo].exchange ex WITH(nolock)
ON ex.exchangeid = e.primaryexchangeid
LEFT JOIN [dbo].entityidmapping map WITH(nolock)
ON map.sourceentityid = e.sourceentityid
LEFT JOIN (SELECT masterentityid,
[6]  AS BICS_1_CODE,
[7]  AS BICS_2_CODE,
[8]  AS BICS_3_CODE,
[9]  AS BICS_4_CODE,
[10] AS BICS_5_CODE,
[11] AS BICS_6_CODE,
[12] AS BICS_7_CODE
FROM   (SELECT e2.masterentityid,
i.industryclassificationid,
Code1=Cast(i.code1 AS BIGINT)
FROM   dbo.#entity e2 WITH(nolock)
LEFT JOIN [dbo].[#entityindustry] ei WITH(
nolock)
ON ei.masterentityid = e2.masterentityid
AND '99991231' BETWEEN
ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e2.masterentityid
AND i.industryclassificationid BETWEEN 6 AND 12
-- BICS level 1 to 7
AND '99991231' BETWEEN e2.validfromdate AND
e2.validtodate
--    and e2.MasterEntityId=926860 -- Abbvie Inc.
) up
PIVOT ( Avg(code1)
FOR industryclassificationid IN ( [6],
[7],
[8],
[9],
[10],
[11],
[12] ) ) AS x)
bics
ON e.masterentityid = bics.masterentityid
LEFT JOIN dbo.#vwbics bics_1
ON bics.bics_1_code = bics_1.bics_code
LEFT JOIN dbo.#vwbics bics_2
ON bics.bics_2_code = bics_2.bics_code
LEFT JOIN dbo.#vwbics bics_3
ON bics.bics_3_code = bics_3.bics_code
LEFT JOIN dbo.#vwbics bics_4
ON bics.bics_4_code = bics_4.bics_code
LEFT JOIN dbo.#vwbics bics_5
ON bics.bics_5_code = bics_5.bics_code
LEFT JOIN dbo.#vwbics bics_6
ON bics.bics_6_code = bics_6.bics_code
LEFT JOIN dbo.vwbics bics_7
ON bics.bics_7_code = bics_7.bics_code
WHERE
--@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate
---AND
( ( ms.code = 'PRIV'
AND @PrivateMarketStatus = 1 )
OR ( ms.code <> 'PRIV'
AND @PrivateMarketStatus = 0 ) )
----AND e.SourceEntityId = 28046509
--- 1:20 seconds
DROP TABLE #entity
DROP TABLE #entityindustry
DROP TABLE #vwbics
DROP TABLE #securityentityindustry
DROP TABLE #security
- This reply was modified 4 years, 6 months ago by rohitkocharda.
- This reply was modified 4 years, 6 months ago by rohitkocharda.
June 6, 2020 at 9:22 pm

#3761112
Reply To: performance tuning for a subquery
Than you! Creating clustered index is a good idea.
CREATE CLUSTERED INDEX ix_t1 ON dbo.#Entity(EntityId) ON [PRIMARY]
CREATE CLUSTERED INDEX ix_t3 ON dbo.#EntityIndustry([EntityIndustryId]) ON [PRIMARY]
CREATE CLUSTERED INDEX ix_t4 ON dbo.#SecurityEntityIndustry(SecurityEntityIndustryId) ON [PRIMARY]
CREATE...
- This reply was modified 4 years, 6 months ago by rohitkocharda.
June 6, 2020 at 9:01 pm

#3761109
Reply To: performance tuning for a subquery

Thank you I just deleted

June 6, 2020 at 7:53 pm

#3761101
Reply To: performance tuning for a subquery

can moderator please delete my post with all html above? I apologize for that.

June 6, 2020 at 6:28 pm

#3761089
Reply To: performance tuning for a subquery
This is deleted
- This reply was modified 4 years, 6 months ago by rohitkocharda.
June 6, 2020 at 5:57 pm

#3761087
Reply To: performance tuning for a subquery

Here is the complete code:
I a now using coalesce so that extra query inside when exists is removed. I do not see a huge performance gain though.

DECLARE @AsOfDate            DATE = '20200529',
@PrivateMarketStatus BIT = 0
SET nocount ON;
SELECT countryofissueid,
masterentityid,
validfromdate,
validtodate,
countryofincorporationid,
countryofdomicileid,
marketstatusid,
primarycurrencyid,
primaryexchangeid,
sourceentityid,
NAME,
primaryexchangeticker,
tickerexchangecode,
recentperiodenddate,
entityid,
recentsemiend,
recentquarterend,
recentquarterenddate,
recentbsperiod,
iscurrent
INTO   dbo.#entity
FROM   [dbo].[entity]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO   dbo.#entityindustry
FROM   [dbo].[entityindustry]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
---create NONclustered INDEX (index_ei) on dbo.#EntityIndustry ( validfrmdate, validtodate,
SELECT *
INTO   dbo.#securityentityindustry
FROM   [dbo].[securityentityindustry]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
SELECT masterentityid,
validfromdate,
validtodate,
[mastersecurityid],
countryissuedid,
isprimarysecurity,
hasmultipleshares,
countryofdomicileid,
countryofincorporationid
INTO   dbo.#security
FROM   [dbo].[security]
WHERE  @AsOfDate BETWEEN validfromdate AND validtodate
SELECT *
INTO   dbo.#vwbics
FROM   vwbics
CREATE NONCLUSTERED INDEX ix1_security
ON [dbo].[#Security] ([validfromdate], [validtodate])
include ([MasterSecurityId], [MasterEntityId], [CountryIssuedId],
[CountryOfIncorporationId], [CountryOfDomicileId], [IsPrimarySecurity],
[HasMultipleShares])
CREATE NONCLUSTERED INDEX [ix1_EntityIndustry]
ON [dbo].[#EntityIndustry] ([validfromdate], [validtodate])
include ([EntityIndustryId], [IndustryId], [MasterEntityId], [LoadDate],
[LoadedBy], [UpdateDate], [UpdatedBy])
CREATE NONCLUSTERED INDEX [ix1_secentInd]
ON [dbo].[#SecurityEntityIndustry] ([validfromdate], [validtodate])
include ([SecurityEntityIndustryId], [IndustryId], [MasterSecurityId],
[LoadDate], [LoadedBy], [UpdateDate], [UpdatedBy])
SELECT e.[masterentityid],
[entityid],
e.[name]                AS NAME,
e.[sourceentityid]      AS ID_BB_COMPANY,
ID_BB_UNIQUE=map.sourcesecurityid,
[primaryexchangeticker] AS TICKER,
ex.code                 AS EXCH_CODE,
cr.code                 AS EQY_FUND_CRNCY,
[tickerexchangecode]    AS TICKER_AND_EXCH_CODE,
ms.code                 AS MARKET_STATUS,
(SELECT Max(Cast(hasmultipleshares AS TINYINT))
FROM   [#security] s
WHERE  s.masterentityid = e.masterentityid
--AND @AsOfDate BETWEEN s.ValidFromDate and s.ValidToDate
)                       AS MULTIPLE_SHARE,
CNTRY_ISSUE_NAME =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--                              --and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--        )
--  then (SELECT top 1 ciss.Name FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId                          ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 ciss.Name     FROM dbo.#Security s with(nolock)
--    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = s.CountryIssuedId      and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 ciss.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = e2.countryofissueid
AND @AsOfDate BETWEEN ciss.validfromdate AND
ciss.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 ciss.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = s.countryissuedid
AND @AsOfDate BETWEEN ciss.validfromdate AND ciss.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_ISSUE_ISO =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  then (SELECT top 1 ciss.ISOCode2 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = e2.CountryOfIssueId    and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 ciss.ISOCode2     FROM dbo.#Security s with(nolock)
--    left join [dbo].[Country] ciss with(nolock) ON ciss.CountryId  = s.CountryIssuedId      and @AsOfDate between ciss.ValidFromDate and ciss.ValidToDate
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate
--    )
--end
COALESCE((SELECT TOP 1 ciss.isocode2
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = e2.countryofissueid
AND @AsOfDate BETWEEN ciss.validfromdate AND
ciss.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 ciss.isocode2
FROM   dbo.#security s
LEFT JOIN [dbo].[country] ciss WITH(nolock)
ON ciss.countryid = s.countryissuedid
AND @AsOfDate BETWEEN ciss.validfromdate AND ciss.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_INCORPORATION_NAME =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  then (SELECT top 1 cinc.Name FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 cinc.Name     FROM dbo.#Security s with(nolock)
--    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = s.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--    --  and @AsOfDate between s.ValidFromDate    and s.ValidToDate
--    )
--end
COALESCE((SELECT TOP 1 cinc.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = e2.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND
cinc.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cinc.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = s.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND cinc.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_INCORPORATION =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  then (SELECT top 1 cinc.ISOCode2 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = e2.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 cinc.ISOCode2     FROM dbo.#Security s with(nolock)
--    left join [dbo].[Country] cinc with(nolock) ON cinc.CountryId  = s.CountryOfIncorporationId  and @AsOfDate between cinc.ValidFromDate and cinc.ValidToDate
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate
--    )
--end
COALESCE((SELECT TOP 1 cinc.isocode2
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = e2.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND
cinc.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cinc.isocode2
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cinc WITH(nolock)
ON cinc.countryid = s.countryofincorporationid
AND @AsOfDate BETWEEN cinc.validfromdate AND cinc.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_DOMICILE_NAME =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  then (SELECT top 1 cdom.Name FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 cdom.Name     FROM dbo.#Security s with(nolock)
--    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = s.CountryOfDomicileId      and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--    --  and @AsOfDate between s.ValidFromDate    and s.ValidToDate
--    )
--end
COALESCE((SELECT TOP 1 cdom.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = e2.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND
cdom.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cdom.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = s.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND cdom.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
CNTRY_OF_DOMICILE =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    ---  and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  then (SELECT top 1 cdom.ISOCode2 FROM dbo.#Entity e2 with(nolock)
--    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = e2.CountryOfDomicileId    and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
--    WHERE e2.MasterEntityId = e.MasterEntityId
--    --and @AsOfDate between e2.ValidFromDate    and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 cdom.ISOCode2     FROM dbo.#Security s with(nolock)
--    left join [dbo].[Country] cdom with(nolock) ON cdom.CountryId  = s.CountryOfDomicileId      and @AsOfDate between cdom.ValidFromDate and cdom.ValidToDate
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1
--    --and @AsOfDate between s.ValidFromDate    and s.ValidToDate
--    )
--end
COALESCE((SELECT TOP 1 cdom.isocode2
FROM   dbo.#entity e2
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = e2.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND
cdom.validtodate
WHERE  e2.masterentityid = e.masterentityid),
(SELECT TOP 1 cdom.isocode2
FROM   dbo.#security s
LEFT JOIN [dbo].[country] cdom WITH(nolock)
ON cdom.countryid = s.countryofdomicileid
AND @AsOfDate BETWEEN cdom.validfromdate AND cdom.validtodate
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1)),
[recentperiodenddate]   AS MOST_RECENT_PERIOD_END_DT,
[recentsemiend]         AS EQY_RECENT_SEMI_END,
[recentquarterend]      AS EQY_RECENT_QT_END_QT,
[recentquarterenddate]  AS EQY_RECENT_QT_END_DT,
[recentbsperiod]        AS EQY_RECENT_BS_QT,
EQY_SIC_NAME =
---case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
--    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
--    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
--    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
--    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=1
--    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 1),
(SELECT TOP 1 i.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei WITH(nolock)
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 1)),
EQY_SIC_CODE =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
--    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  then (SELECT TOP 1 i.Code1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=1
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 i.Code1 FROM dbo.#Security s with(nolock)
--    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
--    ---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=1
--    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 i.code1
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 1),
(SELECT TOP 1 i.code1
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---- AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 1)),
INDUSTRY_SECTOR =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=2
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=2
--    --and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
--    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
--    ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=2
--    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 2),
(SELECT TOP 1 i.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
AND @AsOfDate BETWEEN sei.validfromdate AND sei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 2)),
INDUSTRY_GROUP =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=3
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=3
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
--    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
--    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=3
--    ---and @AsOfDate between s.ValidFromDate and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
AND @AsOfDate BETWEEN ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 3),
(SELECT TOP 1 i.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 3)),
INDUSTRY_SUBGROUP =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    --- AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=4
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=4
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
--    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
--    ---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=4
--    --and @AsOfDate between s.ValidFromDate and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 4),
(SELECT TOP 1 i.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
---AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 4)),
EQY_FUND_IND =
--case
--  when exists (SELECT 1 FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ---AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=5
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  then (SELECT TOP 1 i.Name FROM dbo.#Entity e2 with(nolock)
--    LEFT join [dbo].[#EntityIndustry] ei with(nolock) ON ei.MasterEntityId = e2.MasterEntityId
--    ----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on ei.IndustryId = i.IndustryId
--    WHERE e2.MasterEntityId=e.MasterEntityId and i.IndustryClassificationId=5
--    ---and @AsOfDate between e2.ValidFromDate and e2.ValidToDate
--    )
--  else
--    (SELECT TOP 1 i.Name FROM dbo.#Security s with(nolock)
--    LEFT join [dbo].[#SecurityEntityIndustry] sei with(nolock) ON sei.MasterSecurityId = s.MasterSecurityId
--    ----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
--    left join [dbo].[Industry] i with(nolock) on sei.IndustryId = i.IndustryId
--    WHERE s.MasterEntityId = e.MasterEntityId and s.IsPrimarySecurity = 1 and i.IndustryClassificationId=5
--    --- and @AsOfDate between s.ValidFromDate and s.ValidToDate
--    )
--end
COALESCE ((SELECT TOP 1 i.NAME
FROM   dbo.#entity e2
LEFT JOIN [dbo].[#entityindustry] ei
ON ei.masterentityid = e2.masterentityid
----AND @AsOfDate between ei.ValidFromDate and ei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e.masterentityid
AND i.industryclassificationid = 5),
(SELECT TOP 1 i.NAME
FROM   dbo.#security s
LEFT JOIN [dbo].[#securityentityindustry] sei
ON sei.mastersecurityid = s.mastersecurityid
----AND @AsOfDate between sei.ValidFromDate and sei.ValidToDate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON sei.industryid = i.industryid
WHERE
s.masterentityid = e.masterentityid
AND s.isprimarysecurity = 1
AND i.industryclassificationid = 5)),
BICS_1=bics_1.bics_name,
BICS_1_CODE=bics_1.bics_code,
BICS_2=bics_2.bics_name,
BICS_2_CODE=bics_2.bics_code,
BICS_3=bics_3.bics_name,
BICS_3_CODE=bics_3.bics_code,
BICS_4=bics_4.bics_name,
BICS_4_CODE=bics_4.bics_code,
BICS_5=bics_5.bics_name,
BICS_5_CODE=bics_5.bics_code,
BICS_6=bics_6.bics_name,
BICS_6_CODE=bics_6.bics_code,
BICS_7=bics_7.bics_name,
BICS_7_CODE=bics_7.bics_code,
e.[validfromdate]       AS ValidFromDate,
e.[validtodate]         AS ValidToDate,
[iscurrent]
FROM   [dbo].[#entity] e WITH(nolock)
JOIN [dbo].[marketstatus] ms WITH(nolock)
ON ms.marketstatusid = e.marketstatusid
JOIN [dbo].currency cr WITH(nolock)
ON cr.currencyid = e.primarycurrencyid
JOIN [dbo].exchange ex WITH(nolock)
ON ex.exchangeid = e.primaryexchangeid
LEFT JOIN [dbo].entityidmapping map WITH(nolock)
ON map.sourceentityid = e.sourceentityid
LEFT JOIN (SELECT masterentityid,
[6]  AS BICS_1_CODE,
[7]  AS BICS_2_CODE,
[8]  AS BICS_3_CODE,
[9]  AS BICS_4_CODE,
[10] AS BICS_5_CODE,
[11] AS BICS_6_CODE,
[12] AS BICS_7_CODE
FROM   (SELECT e2.masterentityid,
i.industryclassificationid,
Code1=Cast(i.code1 AS BIGINT)
FROM   dbo.#entity e2 WITH(nolock)
LEFT JOIN [dbo].[#entityindustry] ei WITH(
nolock)
ON ei.masterentityid = e2.masterentityid
AND '99991231' BETWEEN
ei.validfromdate AND
ei.validtodate
LEFT JOIN [dbo].[industry] i WITH(nolock)
ON ei.industryid = i.industryid
WHERE  e2.masterentityid = e2.masterentityid
AND i.industryclassificationid BETWEEN 6 AND 12
-- BICS level 1 to 7
AND '99991231' BETWEEN e2.validfromdate AND
e2.validtodate
--    and e2.MasterEntityId=926860 -- Abbvie Inc.
) up
PIVOT ( Avg(code1)
FOR industryclassificationid IN ( [6],
[7],
[8],
[9],
[10],
[11],
[12] ) ) AS x)
bics
ON e.masterentityid = bics.masterentityid
LEFT JOIN dbo.#vwbics bics_1
ON bics.bics_1_code = bics_1.bics_code
LEFT JOIN dbo.#vwbics bics_2
ON bics.bics_2_code = bics_2.bics_code
LEFT JOIN dbo.#vwbics bics_3
ON bics.bics_3_code = bics_3.bics_code
LEFT JOIN dbo.#vwbics bics_4
ON bics.bics_4_code = bics_4.bics_code
LEFT JOIN dbo.#vwbics bics_5
ON bics.bics_5_code = bics_5.bics_code
LEFT JOIN dbo.#vwbics bics_6
ON bics.bics_6_code = bics_6.bics_code
LEFT JOIN dbo.vwbics bics_7
ON bics.bics_7_code = bics_7.bics_code
WHERE
--@AsOfDate BETWEEN e.ValidFromDate and e.ValidToDate
---AND
( ( ms.code = 'PRIV'
AND @PrivateMarketStatus = 1 )
OR ( ms.code <> 'PRIV'
AND @PrivateMarketStatus = 0 ) )
AND e.sourceentityid = 28046509
--- 1:20 seconds
DROP TABLE #entity
DROP TABLE #entityindustry
DROP TABLE #vwbics
DROP TABLE #securityentityindustry
DROP TABLE #security

June 6, 2020 at 5:50 pm

#3761086
Reply To: performance tuning for a subquery

Also based on the execution plan hints to create indexes..I created three NONCLUSTERED index. Do you think there should be a clustered index first?

CREATE NONCLUSTERED INDEX ix1_security
ON [dbo].[#Security] ([ValidFromDate],[ValidToDate])
INCLUDE ([MasterSecurityId],[MasterEntityId],[CountryIssuedId],[CountryOfIncorporationId],[CountryOfDomicileId],[IsPrimarySecurity],[HasMultipleShares])
CREATE...

June 6, 2020 at 4:31 pm

#3761080
Reply To: performance tuning for a subquery

It was written by someone else and I am just trying to make it better. There were multiple calls to the table so I stored all data in temp tables...

June 6, 2020 at 3:39 pm

#3761071
Reply To: performance tuning for a subquery

Here are the counts on table. I do not want to hit the table again and again so I put the records in a #temp table. I do not know...

June 6, 2020 at 3:50 am

#3761004
Reply To: SSIS Error :A buffer failed while allocating 10485184 bytes.

were u ever able to resolve this?

April 15, 2020 at 1:55 am

#3742980
Reply To: SSIS: A buffer failed while allocating 10485104 bytes; The system reports 98 percent memory loaded

I am having the same issue. I changed the default buffersize to 104857600 which isthe minimum value and chnaged the DefaultBfufferMaxRows to 400 but I keep getitng the same error....

April 15, 2020 at 1:45 am

#3742979
Reply To: Setting up the identity key after creating a new table with data
let me explain my issue again:
1. table A has a key set as primary key and identity set .
2. I truncated the table
3. I removed the identity key and primary
April 12, 2020 at 9:13 pm

#3742315
Reply To: The buffer type is not valid. Make sure the Pipeline layout and all components

Hi Jeff,
thanks for your post. I have done this already. code is running fine on visual studio but it is having issue running from sql server.
has anyone else faced this...

July 27, 2019 at 5:10 am

#3666512
RE: failure of sequence conatiner

I just figured how to do this....In the sequence container I need to set the precedence constraint editor to false and select the Logical OR. I was selecting Logical AND....

March 3, 2016 at 3:04 pm

#1862945
RE: Sequence container not to fail if a task fails inside it

Can you please guide me how you achieved this? I want to redirect flow from sequence container to a task in case nay task inside sequence container fails? Exactly what...

March 3, 2016 at 2:48 pm

#1862938

Viewing 15 posts - 16 through 30 (of 30 total)