Tuesday, September 8, 2015

sql

SELECT     PF.ProductFeeId, PF.FeeItemType, ST.ServiceTypeName, FI.FeeItemName, FI.FeeDescription,
                      CASE WHEN PF.AmountType = 'Percent' THEN dbo.fnFormatPercentage(MIN(Amount), 2) ELSE dbo.fnFormatCurrency(MIN(Amount), 2) END AS Amount, PF.FeeId,FI.SequenceNumber
FROM         dbo.tblProductFees AS PF INNER JOIN
                      dbo.tblFeeItems AS FI ON PF.FeeId = FI.FeeId LEFT OUTER JOIN
                      dbo.ServiceType AS ST ON PF.ServiceTypeID = ST.ServiceTypeID
WHERE     (PF.ProductId = 250)
GROUP BY PF.ProductFeeId, PF.FeeItemType, ST.ServiceTypeName, FI.FeeItemName, FI.FeeDescription, PF.AmountType, PF.FeeId,FI.SequenceNumber
order by Case when PF.feeitemtype='Implementation Fees' then 1
when PF.feeitemtype='Core Recurring Fees' then 2
when PF.feeitemtype='Optional Core Fees' then 3
when PF.feeitemtype='Transaction Fees' then 4
when PF.feeitemtype='Brokerage Window' then 5
Else 6 End,FI.SequenceNumber
///////////////////////////////////////////////////////
SELECT CompanyID,CompanyName,Count(*) AS test FROM vw_ServiceProvider  WHERE CompanyTypeiD=3 and CompanyID in(7016,10797,4694,10607,11372)
Group by CompanyID,CompanyName
order by Case when CompanyName='Mid Atlantic Trust Company' then 1
when CompanyName='Matrix Trust Company' then 2
when CompanyName='Pershing LLC' then 3
when CompanyName='Charles Schwab Trust Company' then 4
Else 5 End
///////////////////////////////////////////////////
SELECT 0 AS CompanyID,'' AS CompanyName
UNION
SELECT DISTINCT CompanyID,CompanyName FROM vw_ServiceProvider  WHERE CompanyTypeiD=3 and CompanyID in(10797,4694,10607,11372)
////////////////////////////////////////////////////////


No comments:

Post a Comment