Need advice on how to rewrite SP

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)

Need advice on how to rewrite SP

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-11-17 : 11:12:44

I'm working on an SP that inserts records into a summary table. Each field for the select is a subquery with it's own conditions for that field in the summary table. The issue that came up is that it hit the 256 tables per query limit in SQL Server...so it was given to me to rewrite. It's not that there are 256 different tables being joined together. It's the same 4 or 5 tables being used in each subquery with it's own set of conditions for that particular summation. I'm having trouble thinking of where to start on this. Has anyone had to deal with anything like this? I can post the SP if you like.

Thanks,

Van

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 11:22:04

without posting your query, i'm not able to suggest much. post your current query or post some sample data and illustrate what you're trying to achieve with a sample output

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-11-17 : 12:05:51

I understand. It's a pretty big query and all. My first thought is to take the tables being used and insert the records into a temp table (denormalized) and then call that temp table in each subselect. What do you think? Just remember, I didn't write this.

insert monthlydashboard
SELECT
(Select 'SMART CHECKING') as SmartChecking,
(SELECT count(distinct AccountNumber)
from cycleprocess
where CAST(productcode as varchar(5)) in (select trancondition from atecondition where columnname = 'SmartChecking')

-- SMART CHECKING
and accountstatuscode <> 'C'
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalAccountsSmartNEW,
(SELECT count(distinct AccountNumber)
from cycleprocess
where CAST(productcode as varchar(5)) in (select trancondition from atecondition where columnname = 'SmartChecking')

-- SMART CHECKING
and accountstatuscode <> 'C'
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalAccountsSmartEXISTING,
(SELECT round(sum(isnull(dailybalance,0)),2)
from dailybalance
where accountnumber in (select accountnumber from cycleprocess where CAST(productcode as varchar(5)) in (select trancondition from atecondition where columnname = 'SmartChecking')-- SMART CHECKING
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalbalancesSmartNEW,
(SELECT round(sum(isnull(dailybalance,0)),2)
from dailybalance
where accountnumber in (select accountnumber from cycleprocess where productcode in ('19','21')-- SMART CHECKING
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalbalancesSmartEXISTING,
(select sum(accruedinterestlow)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numdebitcardcount')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numdirectdeposits')
or custusercode like '%N%'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and accountnumber in (select distinct accountnumber from cycleprocess
where productcode in ('19','21'))
--waive flag so that user will get high interest
and (t1.USerFlag not like '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding)
having sum(t1.accruedinterestLow) > 0.01) as AccruedInterestLowSmartEXISTING,
(select sum(accruedinterestlow)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numdebitcardcount')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numdirectdeposits')

or custusercode like '%N%'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and accountnumber in (select distinct accountnumber from cycleprocess
where productcode in ('19','21'))
--waive flag so that user will get high interest
and (t1.USerFlag not like '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding)
having sum(t1.accruedinterestLow) > 0.01) as AccruedInterestLowSmartNEW,
---------accruedinterest high SMART
(select sum(accruedinteresthigh)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) >= 0.01) as AccruedInterestHighSmartNEW,
(select sum(accruedinteresthigh)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess
where productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) >= 0.01) as AccruedInterestHighSmartEXISTING,
----------------------------------------
(select sum(foreignatmfees) from foreignatmfeesPAID
where accountnumber in (select accountnumber from cycleprocess where productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7)
and CycleDate >= @CycleDateBeginning AND CycleDate <= @CycleDateEnding) as NumberofATMRefundsSmartNEW,
(select sum(foreignatmfees) from foreignatmfeesPAID
where accountnumber in (select accountnumber from cycleprocess where productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7)
and CycleDate > @CycleDateBeginning AND CycleDate <= @CycleDateEnding) as NumberofATMRefundsSmartEXISTING,
(select count(distinct accountnumber)
from cycleprocess t1
where t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and t1.accountnumber in (select distinct t2.accountnumber from cycleprocess t2
where t2.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22')) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t2.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t2.dteLastUpdated > @CycleDateBeginning AND t2.dteLastUpdated <= @CycleDateEnding
and t2.accountnumber not in
(select distinct accountnumber from cycleprocess where
trancode in ('72') or trancode in ('81') or trancode in ('14')
and dtelastupdated > @CycleDateBeginning and dtelastupdated <=@CycleDateEnding))
or t1.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t1.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding
and t1.accountnumber in (select distinct accountnumber from carddepositcount
where dtelastupdated > @CycleDateBeginning and dteLastUpdated <= @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numServiceFeeDirectDeposits')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numDirectDeposits')
or custusercode like '%N%'))) as countsvcchargessuperNEW,
(select count(distinct accountnumber)
from cycleprocess t1
where t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.accountnumber in (select distinct t2.accountnumber from cycleprocess t2
where t2.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22')) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t2.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t2.dteLastUpdated > @CycleDateBeginning AND t2.dteLastUpdated <= @CycleDateEnding
and t2.accountnumber not in (select distinct accountnumber from cycleprocess where
trancode in ('72') or trancode in ('81') or trancode in ('14')
and dtelastupdated > @CycleDateBeginning and dtelastupdated <=@CycleDateEnding))

or t1.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t1.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding
and t1.accountnumber in (select distinct accountnumber from carddepositcount
where dtelastupdated > @CycleDateBeginning and dteLastUpdated <= @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numServiceFeeDirectDeposits')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numDirectDeposits')
or custusercode like '%N%'))) as countsvcchargessuperEXISTING,
(select sum(numdebitcardcount) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7)
and accountnumber in (select distinct accountnumber from cycleprocess where productcode in ('19','21'))) as DebitCardCountSmartNEW,
(select sum(numdebitcardcount) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select distinct accountnumber from cycleprocess where productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess
where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7)) as DebitCardCountSmartEXISTING,
(select sum(numdirectdeposits) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7
and trancode in ('14'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('19','21'))) as DirectdepositCountSmartNEW,
(select sum(numdirectdeposits) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7
and trancode in ('14'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('19','21'))) as DirectdepositCountSmartEXISTING,
(select count(cnt) from (select sum(numdirectdeposits) cnt from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7
and trancode in ('14'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('19','21'))
group by accountnumber) t
where cnt > 0) as DirectdepositAccountCountSmartNEW,
(select count(cnt) from (select sum(numdirectdeposits) cnt from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7
and trancode in ('14'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('19','21'))
group by accountnumber) t
where cnt > 0) as DirectdepositAccountCountSmartEXISTING,
(select isnull(sum(tranamount),0) from (select cd.accountnumber, tranamount
from carddepositcount cd join
cycleprocess cp on cd.accountnumber = cp.accountnumber
where cd.dteLastUpdated > @CycleDateBeginning AND cd.dteLastUpdated <= @CycleDateEnding
and DATEDIFF(dd, cp.dateaccountopened, cp.dteLastUpdated)<= 7
and cp.trancode in ('14') and cp.productcode in ('19','21')) t) as DirectDepositSumSMARTNEW,
(select isnull(sum(tranamount),0) from (select cd.accountnumber, tranamount
from carddepositcount cd join
cycleprocess cp on cd.accountnumber = cp.accountnumber
where cd.dteLastUpdated > @CycleDateBeginning AND cd.dteLastUpdated <= @CycleDateEnding
and DATEDIFF(dd, cp.dateaccountopened, cp.dteLastUpdated)> 7
and cp.trancode in ('14') and cp.productcode in ('19','21')) t) as DirectDepositSumSMARTEXISTING,
(SELECT count(DISTINCT AccountNumber)
FROM dbo.CycleProcess
WHERE (DateAccountOpened IS NOT NULL)
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) < 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and ProductCode in ('19','21')) as NewAccountsThisPeriodSMART,
isnull((select isnull(count(accountnumber),0)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) >= 0.01),0) as CountQualifiedSmartNEW,
isnull((select count(accountnumber)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess
where productcode in ('19','21'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) >= 0.01),0) as CountQualifiedSmartEXISTING,
(select sum(NSFItemNum) + sum(ODItemNum)
from cycleprocess
where productcode in ('19','21')
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as NSFODChargesSMARTNEW,
(select sum(NSFItemNum) + sum(ODItemNum)
from cycleprocess
where productcode in ('19','21')
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as NSFODChargesSMARTEXISTING,
-------------SUPERSMART--------------------------------------------------------------
(Select 'SUPER SMART CHECKING') as SuperSmartChecking,
(SELECT count(distinct AccountNumber)
from cycleprocess
where productcode in ('20','22')
-- SMART CHECKING
and accountstatuscode <> 'C'
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as totalAccountsSuperNEW,
(SELECT count(distinct AccountNumber)
from cycleprocess
where productcode in ('20','22')
-- SMART CHECKING
and accountstatuscode <> 'C'
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalAccountsSuperEXISTING,
(SELECT round(sum(isnull(dailybalance,0)),2)
from dailybalance
where accountnumber in (select accountnumber from cycleprocess where productcode in ('20','22')-- SMART CHECKING
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7 and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalbalancesSuperNEW,
(SELECT round(sum(isnull(dailybalance,0)),2)
from dailybalance
where accountnumber in (select accountnumber from cycleprocess where productcode in ('20','22')-- SMART CHECKING
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7 and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)) as totalbalancesSuperEXISTING,
(select sum(accruedinterestlow)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numdebitcardcount')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numdirectdeposits')
or custusercode like '%N%'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and accountnumber in (select distinct accountnumber from cycleprocess
where productcode in ('20','22'))
--waive flag so that user will get high interest
and (t1.USerFlag not like '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding)
having sum(t1.accruedinterestLow) > 0.01) as AccruedInterestLowSuperEXISTING,
(select sum(accruedinterestlow)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numdebitcardcount')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numdirectdeposits')
or custusercode like '%N%'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and accountnumber in (select distinct accountnumber from cycleprocess where productcode in ('20','22'))
--waive flag so that user will get high interest
and (t1.USerFlag not like '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding)
having sum(t1.accruedinterestLow) >= 0.01) as AccruedInterestLowSuperNEW,
---------accruedinterest high SMART
(select sum(accruedinteresthigh)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess where productcode in ('20','22'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) > 0.01) as AccruedInterestHighSuperNEW,
(select sum(accruedinteresthigh)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess where productcode in ('20','22'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7))
having sum(t1.accruedinterestHigh) > 0.01) as AccruedInterestHighSuperEXISTING,
------------------------------------------
(select sum(foreignatmfees) from foreignatmfeesPAID where
accountnumber in (select accountnumber from cycleprocess where productcode in ('20','22'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7)
and CycleDate > @CycleDateBeginning AND CycleDate <= @CycleDateEnding) as NumberofATMRefundsSuperNEW,
(select sum(foreignatmfees) from foreignatmfeesPAID where
accountnumber in (select accountnumber from cycleprocess where productcode in ('20','22'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7)
and CycleDate > @CycleDateBeginning AND CycleDate <= @CycleDateEnding) as NumberofATMRefundsSuperEXISTING,
(select sum(numdebitcardcount) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7)
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))) as DebitCardCountSuperNEW,
(select sum(numdebitcardcount) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7)
) as DebitCardCountSuperEXISTING,
(select sum(numdirectdeposits) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7)
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))) as DirectdepositCountSuperNEW,
(select sum(numdirectdeposits) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7)
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))) as DirectdepositCountSuperEXISTING,
(select count(cnt) from (select sum(numdirectdeposits) cnt from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)<= 7)
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))
group by accountnumber) t
where cnt > 0) as DirectdepositAccountCountSuperNEW,
(select count(cnt) from (select sum(numdirectdeposits) cnt from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated)> 7)
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))
group by accountnumber) t
where cnt > 0) as DirectdepositAccountCountSuperEXISTING,
(select isnull(sum(tranamount),0) from (select cd.accountnumber, tranamount
from carddepositcount cd join
cycleprocess cp on cd.accountnumber = cp.accountnumber
where cd.dteLastUpdated > @CycleDateBeginning AND cd.dteLastUpdated <= @CycleDateEnding
and DATEDIFF(dd, cp.dateaccountopened, cp.dteLastUpdated)<= 7
and cp.trancode in ('14') and cp.productcode in ('20','22')) t) as DirectDepositSumSUPERNEW,
(select isnull(sum(tranamount),0) from (select cd.accountnumber, tranamount
from carddepositcount cd join
cycleprocess cp on cd.accountnumber = cp.accountnumber
where cd.dteLastUpdated > @CycleDateBeginning AND cd.dteLastUpdated <= @CycleDateEnding
and DATEDIFF(dd, cp.dateaccountopened, cp.dteLastUpdated)> 7
and cp.trancode in ('14') and cp.productcode in ('20','22')) t) as DirectDepositSumSUPEREXISTING,
(SELECT count(DISTINCT AccountNumber)
FROM dbo.CycleProcess
WHERE (DateAccountOpened IS NOT NULL)
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and ProductCode in ('20','22')) as NewAccountsThisPeriodSUPERSMART,
isnull((select count(accountnumber)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) > 0.01),0) as CountQualifyingSUPERSMARTNEW,
isnull((select count(accountnumber)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and accountnumber in (select distinct accountnumber from cycleprocess where
productcode in ('20','22'))
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
--waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
having sum(t1.accruedinterestHigh) > 0.01),0) as CountQualifyingSUPERSMARTEXISTING,
(select sum(NSFItemNum) + sum(ODItemNum)
from cycleprocess
where productcode in ('20','22')
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as NSFODChargesSUPERNEW,
(select sum(NSFItemNum) + sum(ODItemNum)
from cycleprocess
where productcode in ('20','22')
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as NSFODChargesSUPEREXISTING,
(select @CycleDateBeginning) as cycledate,
----------------------SUMMARY FOR NEW AND EXISTING---------------------------------------------------
(SELECT count(DISTINCT AccountNumber)
FROM dbo.CycleProcess
WHERE (DateAccountOpened IS NOT NULL)
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and ProductCode in ('19','20','21','22')) as totalAccountsSmartandSuperNEW,
(SELECT count( DISTINCT AccountNumber)
FROM dbo.CycleProcess
WHERE (DateAccountOpened IS NOT NULL)
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and ProductCode in ('19','20','21','22')) as totalAccountsSmartandSuperEXISTING,
(SELECT round(sum(isnull(dailybalance,0)),2)
from dailybalance
where accountnumber in ( select accountnumber from cycleprocess where productcode in ('19','20','21','22'
)-- SUPER SMART CHECKING
and
(DateAccountOpened IS NOT NULL)
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7 and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as totalbalancesSmartandSuperEXISTING,
(SELECT round(sum(isnull(dailybalance,0)),2)
from dailybalance
where accountnumber in ( select accountnumber from cycleprocess where productcode in ('19','20','21','22'
)-- SUPER SMART CHECKING
and
(DateAccountOpened IS NOT NULL)
and DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7 and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) as totalbalancesSmartandSuperNEW,
(select
sum(accruedinterestlow)
from accruedinterest t1
where
t1.accountnumber in
(select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) <
(select trancondition from atecondition where columnname = 'numdebitcardcount')
or sum(numDirectDeposits) <
(select trancondition from atecondition where columnname = 'numdirectdeposits')
or custusercode like '%N%') )
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
--waive flag so that user will get high interest
and (t1.USerFlag not like '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding)
having sum(t1.accruedinterestLow) > 0.01) as AccruedInterestLowSuperSmartNEW,
(select
sum(accruedinterestlow)
from accruedinterest t1
where
t1.accountnumber in
(select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) <
(select trancondition from atecondition where columnname = 'numdebitcardcount')
or sum(numDirectDeposits) <
(select trancondition from atecondition where columnname = 'numdirectdeposits')
or custusercode like '%N%'))
and (dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
--waive flag so that user will get high interest
and (t1.USerFlag not like '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding)
having sum(t1.accruedinterestLow) > 0.01) as AccruedInterestLowSuperSmartEXISTING,
---------accruedinterest high SMART
(select sum(accruedinteresthigh)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
----waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding)
having sum(t1.accruedinterestHigh) > 0.01) as AccruedInterestHighSuperSmartNEW,
(select sum(accruedinteresthigh)
from accruedinterest t1
where t1.accountnumber in (select distinct accountnumber from carddepositcount
where dteLastUpdated between @CycleDateBeginning and @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) >= (select trancondition from atecondition where columnname = 'numDebitCardCount')
and sum(numDirectDeposits) >= (select trancondition from atecondition where columnname = 'numDirectDeposits')
and custusercode not like '%N%'))

and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
--waive flag so that user will get high interest
or (t1.USerFlag LIKE '%Y%' and t1.dteLastUpdated > @CycleDateBeginning AND t1.dtelastupdated <= @CycleDateEnding)
having sum(t1.accruedinterestHigh) > 0.01) as AccruedInterestHighSuperSmartEXISTING,
-- ------------------------------------------
(select sum(foreignatmfees) from foreignatmfeesPAID where
CycleDate > @CycleDateBeginning AND CycleDate <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
as NumberofATMRefundsSuperSmartNEW,
(select sum(foreignatmfees) from foreignatmfeesPAID where
CycleDate > @CycleDateBeginning AND CycleDate <= @CycleDateEnding
and
accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7))
as NumberofATMRefundsSuperSmartEXISTING,
(select sum(numdebitcardcount) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding and
accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
as DebitCardCountSuperSmartNEW,
(select sum(numdebitcardcount) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding and
accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7))
as DebitCardCountSuperSmartEXISTING,
(select sum(numdirectdeposits) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7))
as DirectdepositCountSuperSmartNEW,
(select sum(numdirectdeposits) from carddepositcount
where dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding
and accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7))
as DirectdepositCountSuperSmartEXISTING,
--service charge stuuf

(select count(distinct
accountnumber)
from

cycleprocess t1

where

t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)

and t1.accountnumber in

(
select distinct t2.accountnumber from cycleprocess t2
where t2.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart

and t2.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver

and t2.dteLastUpdated > @CycleDateBeginning AND t2.dteLastUpdated <= @CycleDateEnding

and t2.accountnumber not in

(select distinct accountnumber from cycleprocess where

trancode in ('72') or trancode in ('81') or trancode in ('14')

and dtelastupdated > @CycleDateBeginning and dtelastupdated <=@CycleDateEnding)

)

or
t1.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart

and t1.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver

and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)

and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding

and t1.accountnumber in
(select distinct accountnumber from carddepositcount
where dtelastupdated > @CycleDateBeginning and dteLastUpdated <= @CycleDateEnding
group by accountnumber,custusercode

having (sum(numDebitCardCount) <
--
(select trancondition from atecondition where columnname = 'numServiceFeeDirectDeposits')

or
sum(numDirectDeposits) <
--
(select trancondition from atecondition where columnname = 'numDirectDeposits')

or

custusercode like '%N%'

)

)

) as countsvcchargessuperEXISTING,

(select count(distinct
accountnumber)
from

cycleprocess t1

where

t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding)

and t1.accountnumber in

(
select distinct t2.accountnumber from cycleprocess t2
where t2.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart

and t2.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver

and t2.dteLastUpdated > @CycleDateBeginning AND t2.dteLastUpdated <= @CycleDateEnding

and t2.accountnumber not in

(select distinct accountnumber from cycleprocess where

trancode in ('72') or trancode in ('81') or trancode in ('14')

and dtelastupdated > @CycleDateBeginning and dtelastupdated <=@CycleDateEnding)

)

or
t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7
and dteLastUpdated > @CycleDateBeginning AND dteLastUpdated <= @CycleDateEnding) and

t1.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart

and t1.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver

and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding

and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)

and t1.accountnumber in
(select distinct accountnumber from carddepositcount
where dtelastupdated > @CycleDateBeginning and dteLastUpdated <= @CycleDateEnding
group by accountnumber,custusercode

having (sum(numDebitCardCount) <
--
(select trancondition from atecondition where columnname = 'numServiceFeeDirectDeposits')

or
sum(numDirectDeposits) <
--
(select trancondition from atecondition where columnname = 'numDirectDeposits')

or

custusercode like '%N%'

)

)

) as countsvcchargessuperNEW,
(select isnull(sum(tranamount),0)
from
cycleprocess t1
where
t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and t1.accountnumber in (select distinct t2.accountnumber from cycleprocess t2
where t2.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t2.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t2.dteLastUpdated > @CycleDateBeginning AND t2.dteLastUpdated <= @CycleDateEnding
and t2.accountnumber not in (select distinct accountnumber from cycleprocess where
trancode in ('72') or trancode in ('81') or trancode in ('14')
and dtelastupdated > @CycleDateBeginning and dtelastupdated <=@CycleDateEnding))
or
t1.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t1.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) <= 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding
and t1.accountnumber in (select distinct accountnumber from carddepositcount
where dtelastupdated > @CycleDateBeginning and dteLastUpdated <= @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numServiceFeeDirectDeposits')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numDirectDeposits')
or custusercode like '%N%'))) as SUMsvcchargessuperNew,
(select isnull(sum(tranamount),0)
from
cycleprocess t1
where
t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.accountnumber in (select distinct t2.accountnumber from cycleprocess t2
where t2.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t2.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t2.dteLastUpdated > @CycleDateBeginning AND t2.dteLastUpdated <= @CycleDateEnding
and t2.accountnumber not in (select distinct accountnumber from cycleprocess where
trancode in ('72') or trancode in ('81') or trancode in ('14')
and dtelastupdated > @CycleDateBeginning and dtelastupdated <=@CycleDateEnding))
or
t1.ProductCode in (Select producttype from producttypequalifier where producttype in ('20','22') ) -- Super Smart Checking -ToDO Must be changed for new Product Codes for Super Smart
and t1.SvcChargeCode NOT LIKE '%W%' -- Service Charge Code Waiver
and t1.accountnumber in (select accountnumber from cycleprocess where DATEDIFF(dd, dateaccountopened, dteLastUpdated) > 7)
and t1.dteLastUpdated > @CycleDateBeginning AND t1.dteLastUpdated <= @CycleDateEnding
and t1.accountnumber in (select distinct accountnumber from carddepositcount
where dtelastupdated > @CycleDateBeginning and dteLastUpdated <= @CycleDateEnding
group by accountnumber,custusercode
having (sum(numDebitCardCount) < (select trancondition from atecondition where columnname = 'numServiceFeeDirectDeposits')
or sum(numDirectDeposits) < (select trancondition from atecondition where columnname = 'numDirectDeposits')
or custusercode like '%N%'))) as SUMsvcchargessuperEXISTING

END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 12:17:03

ah...never realised it will be such a mess. can you post some data and explain what you reqmnet is?

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-11-17 : 12:25:15

Well, I'm still trying to wrap my head around what all is needed (just started a new job). Basically there is a monthlydashboard table that has one record per month in it and this sp inserts into it...summarizing the data (counts and sums). It's complex because each field is a subselect with it's own unique conditions on the 4-5 tables used. I'm still developing the idea of trying to put all the data frim the 4-5 tables into a temp table and using the temp table instead.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-11-17 : 12:35:51

I think I figured out an easier way. I will just break each subselect out and make it an update rather than having them all in one big query.

Ceemack
Starting Member

3 Posts

Posted - 2008-11-17 : 13:11:10

It's hard to tell for sure, as that code is somewhat tough to read, but this might be a job for a CASE statement. Each of the subqueries would become a CASE statement...WHEN all of these conditions are met, THEN the value of the case statement is this. You'd probably have to SUM each of the case statements and group by month.

In cases where you have to do counts--say, the number of accounts in a particular category, use "1" as the value in the THEN clause and SUM the case statement to get a count.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 13:25:11

Something like

SELECT MAX(CASE .... WHEN ... THEN ... END) ,
MAX(CASE .... WHEN ... THEN ... END)
FROM Table1
INNER JOIN Table2 AS t2 ON ...
INNER JOIN Table3 AS t3 ON ...
INNER JOIN Table4 AS t4 ON ...
GROUP BY ...

E 12°55'05.63"
N 56°04'39.26"

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-11-17 : 13:55:27

You might be served better to create several table valued functions for each subquery to returning necessary rows from each. They will be much easier to maintain and read.

SELECT
FROM dbo.func1([@param1], [@param2], ... , [@paramN]) A
INNER JOIN dbo.func1([@param1], [@param2], ... , [@paramN]) B
ON A.[column] = B.column]

-- so forth for the remaining subqueries

Then you can do inserts and updates as necessary. Breaking these out into units will be way more efficient and friendly for maintenance instead of clutering a SP with this and may create better optimization plans

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-11-17 : 14:53:25

Great ideas guys. Thanks. I'm not sure why the CASE solution didn't come to me but hey it was a long morning...

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-11-17 : 15:35:31

After looking at this I am thinking of another you may consider to cleanup this mess. To make it more manageable incase you need to add values or remove values

CREATE TABLE DynamicValues (
ID int
Key1 varchar(20)
Key2 varchar(20)
Value varchar(10)
Active bit
)

INSERT INTO DynamicValue ( 1, 'trancode', 'smartcheck', 14, 1 )
INSERT INTO DynamicValue ( 2, 'trancode', 'smartcheck', 72, 1 )
INSERT INTO DynamicValue ( 1, 'productcode', 'smartcheck', 20, 1 )
INSERT INTO DynamicValue ( 2, 'productcode', 'smartcheck', 21, 1 )

This will make some of the repetitive in statements easier to deal with plus you can turn off old values and/or add new ones without disturbing the queries once you clean up this mess.

--- IN (select value from dbo.DynamicValue where [key1] = '' and [key2] = '' and [active] = 1)

As long as Key1 will be columnname and Key2 will be unique to your give query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources