Alternate to the second SELECT SQL

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

SQL Server 2005 Forums

Transact-SQL (2005)

Alternate to the second SELECT SQL

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-09-07 : 13:41:01

Hi,

I need to improve the code to not have teh second select. The only difference between the two selects is a filter. But I do need to conditionally check whether the table is populated.

I am not sure what else can I use intead of second select which repeats all columns.

DECLARE @SessionID nchar(38)
DECLARE @ReportID smallint
SET @SessionID = dbo.GFS_fxGetSessionID() --'GFS__Sep 1 2010 1:40PM4900.388864'
SET @ReportID = 1 --641
DECLARE @FromDate datetime
DECLARE @ToDate datetime
SELECT @FromDate = '08/01/10'
SELECT @ToDate = '08/31/10'
--declare @NewSessionID nvarchar(38)
--Some Constants
DECLARE @Status nvarchar(100)
DECLARE @Type nvarchar(100)
DECLARE @Warnings nvarchar(100)
DECLARE @ShowZeroCashFlow bit
DECLARE @ShowClosingMethodDetail bit

--These are for the TransactionSumary view
SET @Status = 'All'
SET @Type = 'All'
SET @Warnings = ''
SET @ShowZeroCashFlow = 0
SET @ShowClosingMethodDetail = 1
--Some Constants

DECLARE @UseSelectedOption bit
DECLARE @Server nvarchar(20)
DECLARE @CustBool1 bit
SET @CustBool1 = 1

--SET @CustBool1 = 0
--declare @CustBool1 bit

--GOCHECKFUND UDF
declare @GoCheckFund table (PshipId smallint, GoCheckFundCode varchar(100))
insert @GoCheckFund
select
PshipId, convert(varchar(100),UDFValue)
from
dbo.GFS_FN_GetUDFValues('Fund', 'GOCheckFund')
-- end GoCheckFund udf

--FundLiveDate UDF
DECLARE @FundLiveDate TABLE(pshipid nvarchar(100), fundLiveDate nvarchar(100))
insert into @FundLiveDate

SELECT Pshipinfo.Pshipid,convert(varchar,UDFValue)
FROM UserDefinedField INNER JOIN UserDefinedFieldItem ON UserDefinedField.UDFItemID = UserDefinedFieldItem.UDFItemID
inner join pshipinfo on UserDefinedField.pshipid=Pshipinfo.pshipid
where UserDefinedField.UDFLevel = 'Fund' AND UDFCode = 'FundLiveDate'

-- end FundLiveDate udf

SET @Server = (select [server] from PartnerDB)

SET @UseSelectedOption = @CustBool1
if @UseSelectedOption = 1 and not @Server like 'YGN%'
begin
SELECT IsNull(FundStructCode, 'None') FundStructCode,
IsNull(FundStructName, 'Stand Alone Fund') FundStructName,
psi.PshipCode FundCode,
psi.PshipName FundName,
IsNull(ic.ClassCode, 'None') ClassCode,
IsNull(ic.ClassName, 'No Classes in Fund') ClassName,
IsNull(cc.CurrencyCode, psc.CurrencyCode) CurrencyCode,
IsNull(cc.CurrencyDescription, psc.CurrencyDescription) CurrencyDescription,
IsNull(CONVERT(nvarchar(100), StartDate, 101), 'No Capital') StartDate,
CASE WHEN IsMaster = 1 AND IsFeeder = 1 THEN 'Master/Feeder'
WHEN IsMaster = 1 THEN 'Master'
WHEN IsFeeder = 1 THEN 'Feeder'
ELSE 'Stand Alone'
END LegalEntityType,
CASE WHEN MCMSEnabled = 1 THEN 'Series'
WHEN Equalization = 1 THEN 'Equalization'
ELSE 'Onshore'
END AccountingType,
IsNull(CONVERT(nvarchar(100), IsNull(cfs.Tier6Rate, pfs.Tier6Rate) * 100) + '%', 'No ManFee Default') ManagmentFee,

psi.ShareDecimals ShareDecimals,
CASE WHEN udf.UDFValue > 0 THEN udf.UDFValue ELSE psi.NAVPerShareDecimals END NAVPerShareDecimals,

CASE WHEN psi.PIF = 0 THEN 'Exclude from PIF'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100), ISNULL(pic.PIFPayout,psi.PIFPayout) * 100) + '%'
WHEN pic.ExcludePIF Is Not NULL AND pic.ExcludePIF = 0 THEN 'Exclude from PIF'
ELSE CONVERT(nvarchar(100), IsNull(pic.PIFPayout, psi.PIFPayout) * 100) + '%'
END PIFDefault,
CASE WHEN psi.PIF = 0 THEN 'No Hurdle'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100), IsNull(pic.PIFHurdle, psi.PIFHurdle) * 100) + '%'
WHEN pic.ExcludePIF Is Not NULL AND pic.ExcludePIF = 0 THEN 'No Hurdle'
WHEN IsNull(pic.UseHW, psi.UseHW) = 0 THEN 'No Hurdle'
ELSE CONVERT(nvarchar(100), IsNull(pic.PIFHurdle, psi.PIFHurdle) * 100) + '%'
END Hurdle,
CASE WHEN pic.FullyRestricted IS NULL THEN 'Set for each Investor'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100),pic.FullyRestricted)
ELSE CASE WHEN pic.FullyRestricted = 1 THEN 'Y' ELSE 'N' END
END FullyRestricted,
CASE WHEN pic.PartiallyRestricted IS NULL THEN 'Set for each Investor'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100),pic.PartiallyRestricted)
ELSE CASE WHEN pic.PartiallyRestricted = 1 THEN 'Y' ELSE 'N' END
END PartiallyRestricted,
CASE WHEN AuthorizedShareCapital = 0 THEN 'None'
ELSE
CASE WHEN AuthorizedShareCapitalFundLevel= 1 THEN 'Fund'ELSE 'Class' END END AuthorizedSharesReporting,
BaseNAV BaseAssetValue,
EnforceCurrencyOutperform EnforceCurrencyOutperform,
psi.CalculatePerfectHedgeRedistribution PerfectHedgerRedistribution,
FrictionMethod FrictionMethod,
ic.MinSubs MinimumSubscription,
ic.MaxSubs MaximumSubscription,
ic.FrontEndFeePercent FrontEndFeePercent,
ic.AdditionalMinSubs AdditionalMinimum,
ic.MinReds MinimumRedemption,
ic.MaxReds MaximumRedemption,
ic.HoldBackPercent HoldBackPercent,
ic.RedemptionFrequency RedemptionFrequency,
ic.RedemptionNoticePenaltyPct NoticePenaltyPercent,
ic.RedemptionNoticePeriod NoticePeriod,
ic.LockupPeriodPenaltyPct LockupPenaltyPercent,
ic.Lockup LockupPeriod,
ic.RedemptionGate RedemptionGate,
FL.fundLiveDate FundLiveDate,
GC.GoCheckFundCode GoCheckFund,
psi.AssetsUnderMgmt IncludeInAUM
FROM
--fAxpDVTransactionSummary(@SessionID, @ReportID, @FromDate, @ToDate, @Status, @Type, @Warnings, @ShowZeroCashFlow, @ShowClosingMethodDetail) TS
pshipInfo psi
LEFT JOIN FundStructures FundStructures
ON psi.FundStructID = FundStructures.FundStructID
LEFT JOIN fAxpDVTransactionSummary(@SessionID, @ReportID, @FromDate, @ToDate, @Status, @Type, @Warnings, @ShowZeroCashFlow, @ShowClosingMethodDetail) TS
ON psi.pshipid=TS.pshipid
LEFT JOIN InvestorClass ic
ON psi.PshipID = ic.PshipID
LEFT JOIN Currency cc
ON ic.CurrencyID = cc.CurrencyID
INNER JOIN Currency psc
ON psi.CurrencyID = psc.CurrencyID
LEFT JOIN (SELECT PshipID, InvestorClassID, MIN(EffectiveCapitalDate) StartDate
FROM CapitalAccountView
GROUP BY PshipID, InvestorClassID) StartDates
ON psi.PshipID = StartDates.PshipID
AND IsNull(ic.ClassID, -1) = StartDates.InvestorClassID
LEFT JOIN FeeClassInfo fci
ON ic.PshipID = fci.PshipID
AND ic.ClassID = fci.ClassID
AND 'ManFee' = fci.ItemCode
LEFT JOIN FeeSchedules cfs
ON fci.PshipID = cfs.PshipID
AND fci.SchedCode = cfs.SchedCode
LEFT JOIN FeeSettings fs
ON psi.PshipID = fs.PshipID
AND 'ManFee' = fs.ItemCode
LEFT JOIN FeeSchedules pfs
ON fs.PshipID = pfs.PshipID
AND fs.DefaultSchedCode = pfs.SchedCode
LEFT JOIN UserDefinedField udf
ON psi.PShipID = udf.PshipID
LEFT JOIN UserDefinedFieldItem udfi
ON udfi.UDFItemID = udf.UDFItemID
LEFT JOIN PeriodicInvestorClass pic
ON psi.PshipId = pic.PshipId
and psi.CurrentPeriod = pic.Period
and ic.ClassID = pic.ClassID
LEFT JOIN Subscriptions s
ON s.pshipid=TS.pshipid
LEFT JOIN Redemptions r
ON r.pshipid=TS.pshipid
LEFT JOIN @GOCHECKFUND GC
ON psi.pshipid=GC.pshipid
LEFT JOIN @FundLiveDate FL
ON psi.pshipid=FL.pshipid
WHERE psi.PshipCode like '%BCT20%'
ORDER BY FundCode, FundStructCode, ClassCode
end
else
begin
SELECT IsNull(FundStructCode, 'None') FundStructCode,
IsNull(FundStructName, 'Stand Alone Fund') FundStructName,
psi.PshipCode FundCode,
psi.PshipName FundName,
IsNull(ic.ClassCode, 'None') ClassCode,
IsNull(ic.ClassName, 'No Classes in Fund') ClassName,
IsNull(cc.CurrencyCode, psc.CurrencyCode) CurrencyCode,
IsNull(cc.CurrencyDescription, psc.CurrencyDescription) CurrencyDescription,
IsNull(CONVERT(nvarchar(100), StartDate, 101), 'No Capital') StartDate,
CASE WHEN IsMaster = 1 AND IsFeeder = 1 THEN 'Master/Feeder'
WHEN IsMaster = 1 THEN 'Master'
WHEN IsFeeder = 1 THEN 'Feeder'
ELSE 'Stand Alone'
END LegalEntityType,
CASE WHEN MCMSEnabled = 1 THEN 'Series'
WHEN Equalization = 1 THEN 'Equalization'
ELSE 'Onshore'
END AccountingType,
IsNull(CONVERT(nvarchar(100), IsNull(cfs.Tier6Rate, pfs.Tier6Rate) * 100) + '%', 'No ManFee Default') ManagmentFee,

psi.ShareDecimals ShareDecimals,
CASE WHEN udf.UDFValue > 0 THEN udf.UDFValue ELSE psi.NAVPerShareDecimals END NAVPerShareDecimals,

CASE WHEN psi.PIF = 0 THEN 'Exclude from PIF'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100), ISNULL(pic.PIFPayout,psi.PIFPayout) * 100) + '%'
WHEN pic.ExcludePIF Is Not NULL AND pic.ExcludePIF = 0 THEN 'Exclude from PIF'
ELSE CONVERT(nvarchar(100), IsNull(pic.PIFPayout, psi.PIFPayout) * 100) + '%'
END PIFDefault,
CASE WHEN psi.PIF = 0 THEN 'No Hurdle'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100), IsNull(pic.PIFHurdle, psi.PIFHurdle) * 100) + '%'
WHEN pic.ExcludePIF Is Not NULL AND pic.ExcludePIF = 0 THEN 'No Hurdle'
WHEN IsNull(pic.UseHW, psi.UseHW) = 0 THEN 'No Hurdle'
ELSE CONVERT(nvarchar(100), IsNull(pic.PIFHurdle, psi.PIFHurdle) * 100) + '%'
END Hurdle,
CASE WHEN pic.FullyRestricted IS NULL THEN 'Set for each Investor'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100),pic.FullyRestricted)
ELSE CASE WHEN pic.FullyRestricted = 1 THEN 'Y' ELSE 'N' END
END FullyRestricted,
CASE WHEN pic.PartiallyRestricted IS NULL THEN 'Set for each Investor'
WHEN pic.ClassID is not null THEN CONVERT(nvarchar(100),pic.PartiallyRestricted)
ELSE CASE WHEN pic.PartiallyRestricted = 1 THEN 'Y' ELSE 'N' END
END PartiallyRestricted,
CASE WHEN AuthorizedShareCapital = 0 THEN 'None'
ELSE
CASE WHEN AuthorizedShareCapitalFundLevel= 1 THEN 'Fund'ELSE 'Class' END END AuthorizedSharesReporting,
BaseNAV BaseAssetValue,
EnforceCurrencyOutperform EnforceCurrencyOutperform,
psi.CalculatePerfectHedgeRedistribution PerfectHedgerRedistribution,
FrictionMethod FrictionMethod,
ic.MinSubs MinimumSubscription,
ic.MaxSubs MaximumSubscription,
ic.FrontEndFeePercent FrontEndFeePercent,
ic.AdditionalMinSubs AdditionalMinimum,
ic.MinReds MinimumRedemption,
ic.MaxReds MaximumRedemption,
ic.HoldBackPercent HoldBackPercent,
ic.RedemptionFrequency RedemptionFrequency,
ic.RedemptionNoticePenaltyPct NoticePenaltyPercent,
ic.RedemptionNoticePeriod NoticePeriod,
ic.LockupPeriodPenaltyPct LockupPenaltyPercent,
ic.Lockup LockupPeriod,
ic.RedemptionGate RedemptionGate,
FL.fundLiveDate FundLiveDate,
GC.GoCheckFundCode GoCheckFund,
psi.AssetsUnderMgmt IncludeInAUM
FROM
--fAxpDVTransactionSummary(@SessionID, @ReportID, @FromDate, @ToDate, @Status, @Type, @Warnings, @ShowZeroCashFlow, @ShowClosingMethodDetail) TS
pshipInfo psi
LEFT JOIN FundStructures FundStructures
ON psi.FundStructID = FundStructures.FundStructID
LEFT JOIN fAxpDVTransactionSummary(@SessionID, @ReportID, @FromDate, @ToDate, @Status, @Type, @Warnings, @ShowZeroCashFlow, @ShowClosingMethodDetail) TS
ON psi.pshipid=TS.pshipid
LEFT JOIN InvestorClass ic
ON psi.PshipID = ic.PshipID
LEFT JOIN Currency cc
ON ic.CurrencyID = cc.CurrencyID
INNER JOIN Currency psc
ON psi.CurrencyID = psc.CurrencyID
LEFT JOIN (SELECT PshipID, InvestorClassID, MIN(EffectiveCapitalDate) StartDate
FROM CapitalAccountView
GROUP BY PshipID, InvestorClassID) StartDates
ON psi.PshipID = StartDates.PshipID
AND IsNull(ic.ClassID, -1) = StartDates.InvestorClassID
LEFT JOIN FeeClassInfo fci
ON ic.PshipID = fci.PshipID
AND ic.ClassID = fci.ClassID
AND 'ManFee' = fci.ItemCode
LEFT JOIN FeeSchedules cfs
ON fci.PshipID = cfs.PshipID
AND fci.SchedCode = cfs.SchedCode
LEFT JOIN FeeSettings fs
ON psi.PshipID = fs.PshipID
AND 'ManFee' = fs.ItemCode
LEFT JOIN FeeSchedules pfs
ON fs.PshipID = pfs.PshipID
AND fs.DefaultSchedCode = pfs.SchedCode
LEFT JOIN UserDefinedField udf
ON psi.PShipID = udf.PshipID
LEFT JOIN UserDefinedFieldItem udfi
ON udfi.UDFItemID = udf.UDFItemID
LEFT JOIN PeriodicInvestorClass pic
ON psi.PshipId = pic.PshipId
and psi.CurrentPeriod = pic.Period
and ic.ClassID = pic.ClassID
LEFT JOIN Subscriptions s
ON s.pshipid=TS.pshipid
LEFT JOIN Redemptions r
ON r.pshipid=TS.pshipid
LEFT JOIN @GOCHECKFUND GC
ON psi.pshipid=GC.pshipid
LEFT JOIN @FundLiveDate FL
ON psi.pshipid=FL.pshipid
LEFT JOIN SelectedCapitalAccounts scap
ON psi.

Where psi.pshipid in (select distinct pshipid from SelectedCapitalAccounts where sessionid = @Sessionid and reportid = @reportid)
ORDER BY FundCode, FundStructCode, ClassCode

end

Thanks in advance
sqlnovice123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-07 : 14:38:28

Maybe you should edit your post to add formatting and colors to indicate where the problem is. Right now your post is too confusing for anyone to answer.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-09-07 : 14:56:49

Oops apologies.

I managed to get an idea to restrict the code to single based. I am gettign a syntax error in the below where clause:

Where PshipInfo.pshipID in (case when @custbool1 = 1 then PshipInfo.pshipID else select distinct pshipid from SelectedCapitalAccounts where sessionid = @Sessionid and reportid = @reportid end)

DECLARE @Status nvarchar(100)
DECLARE @Type nvarchar(100)
DECLARE @Warnings nvarchar(100)
DECLARE @ShowZeroCashFlow bit
DECLARE @ShowClosingMethodDetail bit

DECLARE @UseSelectedOption bit
DECLARE @Server nvarchar(20)

SET @Server = (select [server] from PartnerDB)

SET @UseSelectedOption = @CustBool1

declare @GoCheckFund table (PshipId smallint, GoCheckFundCode varchar(100))
insert @GoCheckFund
select
PshipId, convert(varchar(100),UDFValue)
from
dbo.GFS_FN_GetUDFValues('Fund', 'GOCheckFund')

DECLARE @FundLiveDate TABLE(pshipid nvarchar(100), fundLiveDate nvarchar(100))
insert into @FundLiveDate

SELECT Pshipinfo.Pshipid,convert(varchar,UDFValue)
FROM UserDefinedField INNER JOIN UserDefinedFieldItem ON UserDefinedField.UDFItemID = UserDefinedFieldItem.UDFItemID
inner join pshipinfo on UserDefinedField.pshipid=Pshipinfo.pshipid
where UserDefinedField.UDFLevel = 'Fund' AND UDFCode = 'FundLiveDate'

if @UseSelectedOption = 1 and not @Server like 'YGN%'
begin
SELECT IsNull(FundStructCode, 'None') FundStructCode,
IsNull(FundStructName, 'Stand Alone Fund') FundStructName,
PshipCode FundCode,
PshipName FundName,
IsNull(ClassCode, 'None') ClassCode, IsNull(ClassName, 'No Classes in Fund') ClassName,
IsNull(ClassCurrency.CurrencyCode, PshipCurrency.CurrencyCode) CurrencyCode,
IsNull(ClassCurrency.CurrencyDescription, PshipCurrency.CurrencyDescription) CurrencyDescription,
IsNull(CONVERT(nvarchar(100), StartDate, 101), 'No Capital') StartDate,
CASE WHEN IsMaster = 1 AND IsFeeder = 1 THEN 'Master/Feeder'
WHEN IsMaster = 1 THEN 'Master'
WHEN IsFeeder = 1 THEN 'Feeder'
ELSE 'Stand Alone'
END LegalEntityType,
CASE WHEN MCMSEnabled = 1 THEN 'Series'
WHEN Equalization = 1 THEN 'Equalization'
ELSE 'Onshore'
END AccountingType,
IsNull(CONVERT(nvarchar(100), IsNull(ClassFeeSchedules.Tier6Rate, PshipFeeSchedules.Tier6Rate) * 100) + '%', 'No ManFee Default') ManagmentFee,
PshipInfo.ShareDecimals ShareDecimals,
CASE WHEN UserDefinedField.UDFValue > 0 THEN UserDefinedField.UDFValue ELSE PshipInfo.NAVPerShareDecimals END NAVPerShareDecimals,
CASE WHEN PshipInfo.PIF = 0 THEN 'Exclude from PIF'
WHEN PeriodicInvestorClass.ClassID is not null THEN CONVERT(nvarchar(100), ISNULL(pic.PIFPayout,PshipInfo.PIFPayout) * 100) + '%'
WHEN PeriodicInvestorClass.ExcludePIF Is Not NULL AND PeriodicInvestorClass.ExcludePIF = 0 THEN 'Exclude from PIF'
ELSE CONVERT(nvarchar(100), IsNull(PeriodicInvestorClass.PIFPayout, PshipInfo.PIFPayout) * 100) + '%'
END PIFDefault,
CASE WHEN PshipInfo.PIF = 0 THEN 'No Hurdle'
WHEN PeriodicInvestorClass.ClassID is not null THEN CONVERT(nvarchar(100), IsNull(PeriodicInvestorClass.PIFHurdle, PshipInfo.PIFHurdle) * 100) + '%'
WHEN PeriodicInvestorClass.ExcludePIF Is Not NULL AND PeriodicInvestorClass.ExcludePIF = 0 THEN 'No Hurdle'
WHEN IsNull(PeriodicInvestorClass.UseHW, PshipInfo.UseHW) = 0 THEN 'No Hurdle'
ELSE CONVERT(nvarchar(100), IsNull(PeriodicInvestorClass.PIFHurdle, PshipInfo.PIFHurdle) * 100) + '%'
END Hurdle,
CASE WHEN PeriodicInvestorClass.FullyRestricted IS NULL THEN 'Set for each Investor'
WHEN PeriodicInvestorClass.ClassID is not null THEN CONVERT(nvarchar(100),pic.FullyRestricted)
ELSE CASE WHEN PeriodicInvestorClass.FullyRestricted = 1 THEN 'Y' ELSE 'N' END
END FullyRestricted,
CASE WHEN PeriodicInvestorClass.PartiallyRestricted IS NULL THEN 'Set for each Investor'
WHEN PeriodicInvestorClass.ClassID is not null THEN CONVERT(nvarchar(100),PeriodicInvestorClass.PartiallyRestricted)
ELSE CASE WHEN PeriodicInvestorClass.PartiallyRestricted = 1 THEN 'Y' ELSE 'N' END
END PartiallyRestricted,
HedgeMethod,
CASE WHEN AuthorizedShareCapital = 0 THEN 'None'
ELSE
CASE WHEN AuthorizedShareCapitalFundLevel= 1 THEN 'Fund'ELSE 'Class' END END AuthorizedSharesReporting,
BaseNAV BaseAssetValue,
EnforceCurrencyOutperform EnforceCurrencyOutperform,
PshipInfo.CalculatePerfectHedgeRedistribution PerfectHedgerRedistribution,
FrictionMethod FrictionMethod,
InvestorClass.MinSubs MinimumSubscription,
InvestorClass.MaxSubs MaximumSubscription,
InvestorClass.FrontEndFeePercent FrontEndFeePercent,
InvestorClass.AdditionalMinSubs AdditionalMinimum,
InvestorClass.MinReds MinimumRedemption,
InvestorClass.MaxReds MaximumRedemption,
InvestorClass.HoldBackPercent HoldBackPercent,
InvestorClass.RedemptionFrequency RedemptionFrequency,
InvestorClass.RedemptionNoticePenaltyPct NoticePenaltyPercent,
InvestorClass.RedemptionNoticePeriod NoticePeriod,
InvestorClass.LockupPeriodPenaltyPct LockupPenaltyPercent,
InvestorClass.Lockup LockupPeriod,
InvestorClass.RedemptionGate RedemptionGate,
FL.fundLiveDate FundLiveDate,
GC.GoCheckFundCode GoCheckFund,
PshipInfo.AssetsUnderMgmt IncludeInAUM

FROM PshipInfo LEFT JOIN FundStructures ON PshipInfo.FundStructID = FundStructures.FundStructID
LEFT JOIN InvestorClass ON PshipInfo.PshipID = InvestorClass.PshipID
LEFT JOIN Currency ClassCurrency ON InvestorClass.CurrencyID = ClassCurrency.CurrencyID
INNER JOIN Currency PshipCurrency ON PshipInfo.CurrencyID = PshipCurrency.CurrencyID
LEFT JOIN (SELECT PshipID, InvestorClassID, MIN(EffectiveCapitalDate) StartDate
FROM CapitalAccountView
GROUP BY PshipID, InvestorClassID) StartDates ON PshipInfo.PshipID = StartDates.PshipID AND
IsNull(InvestorClass.ClassID, -1) = StartDates.InvestorClassID
LEFT JOIN FeeClassInfo ON InvestorClass.PshipID = FeeClassInfo.PshipID AND
InvestorClass.ClassID = FeeClassInfo.ClassID AND
'ManFee' = FeeClassInfo.ItemCode
LEFT JOIN FeeSchedules ClassFeeSchedules ON FeeClassInfo.PshipID = ClassFeeSchedules.PshipID AND
FeeClassInfo.SchedCode = ClassFeeSchedules.SchedCode
LEFT JOIN FeeSettings ON PshipInfo.PshipID = FeeSettings.PshipID AND
'ManFee' = FeeSettings.ItemCode
LEFT JOIN FeeSchedules PshipFeeSchedules ON FeeSettings.PshipID = PshipFeeSchedules.PshipID AND
FeeSettings.DefaultSchedCode = PshipFeeSchedules.SchedCode

LEFT JOIN UserDefinedField ON PshipInfo.PShipID = UserDefinedField.PshipID
LEFT JOIN UserDefinedFieldItem ON UserDefinedFieldItem.UDFItemID = UserDefinedField.UDFItemID
LEFT JOIN PeriodicInvestorClass ON PshipInfo.PshipId = PeriodicInvestorClass.PshipId
and PshipInfo.CurrentPeriod = PeriodicInvestorClass.Period
and InvestorClass.ClassID = InvestorClass.ClassID
LEFT JOIN @GOCHECKFUND GC ON PshipInfo.pshipid=GC.pshipid
LEFT JOIN @FundLiveDate FL ON PshipInfo.pshipid=FL.pshipid
Where PshipInfo.pshipID in (case when @custbool1 = 1 then PshipInfo.pshipID else select distinct pshipid from SelectedCapitalAccounts where sessionid = @Sessionid and reportid = @reportid end)

ORDER BY FundCode, FundStructCode, ClassCode

Thanks for your help.
sqlnovice123

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-09-07 : 15:00:16

I got it to work.

DECLARE @SessionID nchar(38)
DECLARE @ReportID smallint
DECLARE @CustBool1 bit
SET @SessionID = 'GFS__Sep 1 2010 1:40PM4900.388864'
SET @ReportID = 641
SET @CustBool1 = 1
select * from pshipInfo
Where PshipInfo.pshipID in
(CASE WHEN @custbool1 = 1 THEN PshipInfo.pshipID ELSE (select distinct pshipid from SelectedCapitalAccounts
where sessionid = @Sessionid and reportid = @reportid) END)

Thanks
sqlnovice123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources