November 29, 2007 at 8:40 am
Hello again. I have a number of codes for which I need totals. This is a sample of them:
--WNENSTAT
--WNENYCFN
'WNENYCHM%'
--WSEG
--WSENFLA
--WWEST
--'WWGRTLA%'
They vary in length from 4-8 characters.
Table the data will come from looks like this:
CREATE TABLE "CorporateRevenue07" (
"Code" "varchar" (20) COLLATE Latin1_General_BIN NOT NULL ,
"ProcessDate" "smalldatetime" NOT NULL ,
"RevDate" "smalldatetime" NOT NULL ,
"Gate" "char" (2) COLLATE Latin1_General_BIN NOT NULL ,
"UnadjustedRevenue" "money" NULL ,
"Adjustments" "money" NULL ,
"CommissionableRevenue" "money" NULL ,
"RevenuePlan" "money" NULL ,
"WriteOffs" "money" NULL ,
"Baseline" "numeric"(20, 8) NULL ,
"RevType" "char" (2) COLLATE Latin1_General_BIN NOT NULL ,
"ModUser" "varchar" (5) COLLATE Latin1_General_BIN NULL ,
"ModDate" "datetime" NULL ,
"ModAction" "char" (1) COLLATE Latin1_General_BIN NULL ,
PRIMARY KEY CLUSTERED
(
"ProcessDate",
"Code",
"RevDate",
"Gate",
"RevType"
) ON "PRIMARY"
) ON "PRIMARY"
GO
Sample data:
INSERT INTO CorporateRevenue07
(Code,ProcessDate, RevDate, Gate, UnadjustedRevenue, Adjustments, CommissionableRevenue,
RevenuePlan, WriteOffs, Baseline, RevType, ModUser, ModDate, ModAction)
SELECT 'WWGRTLAMNCE','Jan 1 2007 12:00AM','Dec 1 2007 12:00AM','CF',0.00,0.00,0.00,5491.47,0.00,'0.00000000','RB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWGRTLAMNCE','Jan 1 2007 12:00AM','Dec 1 2007 12:00AM','US',0.00,0.00,0.00,247988.55,0.00,'0.00000000','RB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWGRTLAMNCE','Jan 1 2007 12:00AM','Dec 1 2007 12:00AM','US',0.00,0.00,0.00,253955.32,0.00,'0.00000000','TB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWGRTLAMNCE','Jan 1 2007 12:00AM','Dec 1 2007 12:00AM','WW',0.00,0.00,0.00,247988.55,0.00,'0.00000000','RB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWGRTLAMNCE','Jan 1 2007 12:00AM','Dec 1 2007 12:00AM','WW',0.00,0.00,0.00,253955.32,0.00,'0.00000000','TB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWNOCAL','Jan 1 2007 12:00AM','Jan 1 2006 12:00AM','WW',7791654.09,0.00,7791654.09,0.00,0.00,'0.00000000','RB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWNOCAL','Jan 1 2007 12:00AM','Jan 1 2006 12:00AM','WW',8368028.59,0.00,8368028.59,0.00,0.00,'0.00000000','TB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWNOCAL','Jan 1 2007 12:00AM','Feb 1 2006 12:00AM','WW',7890172.04,0.00,7890172.04,0.00,0.00,'0.00000000','RB','RSS','Mar 13 2007 12:36PM','I' UNION ALL
SELECT 'WWNOCAL','Jan 1 2007 12:00AM','Feb 1 2006 12:00AM','WW',8315577.21,0.00,8315577.21,0.00,0.00,'0.00000000','TB','RSS','Mar 13 2007 12:36PM','I'
I'm using this (which you probably recognize), to get the sums.
select LEFT(cr.Code,7) AS Code,
sum(cr.CommissionableRevenue) as totals,
cr.RevDate
from CorporateRevenue07 cr
where cr.ProcessDate = '9/1/07' --------Remains static for report
and cr.Code like 'WCGRPLNS%'
AND cr.RevDate between '12/31/06' and '9/2/07'
and cr.Gate = 'WW'
and cr.RevType = 'TB'
group by LEFT(cr.Code,7), cr.RevDate --characters vary, 4 to 8 -- change above also
order by cr.RevDate
Is it possible to use multiple wildcard statements, in this case Like, in a single query? How is this done?
I will need to use above (or improvement on same) to update a table that looks like this:
Select CorporateRevenue07.Code,
CorporateRevenue07.CommissionableRevenue,
CorporateRevenue07.RevDate
into #CorpRev
from smcscalc..CorporateRevenue07
Group by CorporateRevenue07.Code,
CorporateRevenue07.CommissionableRevenue,
CorporateRevenue07.ProcessDate,
CorporateRevenue07.RevDate,
CorporateRevenue07.Gate,
CorporateRevenue07.RevType
having
CorporateRevenue07.ProcessDate ='9/1/07' -- Does not change
and CorporateRevenue07.RevDate between '12/31/06' and '9/2/07' -- January - September 2007
and CorporateRevenue07.Gate = 'WW'
and CorporateRevenue07.RevType = 'TB'
and CorporateRevenue07.Code
in ('WCENTREG','WCGRPLNS', 'WCORPSEG', 'WCSSHORE', 'WCTEXOMA', 'WCTRIST', 'WISEAST',
'WISWEST', 'WMADCVA', 'WMAMARY', 'WMAPENN', 'WMAVAMD', 'WMIDAREG', 'WMWCHISB',
'WMWDHIDT', 'WMWMICH', 'WMWNOHIO', 'WMWPADEL', 'WMWREG', 'WMWSOHIO', 'WNECONN',
'WNELIOUT', 'WNEMASS1', 'WNENJN', 'WNENSTAT', 'WNENYCFN', 'WNENYCHM', 'WNEREG',
'WSEALTN', 'WSECARO', 'WSEGA', 'WSENFLA', 'WSESFLA', 'WWGRTLA', 'WWNOCAL',
'WWPACNW', 'WWREG', 'WWROCKY', 'WWSOCAL')
order by Code
November 29, 2007 at 11:34 am
Some progress.
This gives me a list of totals. Unfortunately, they don't match the above values. Possible cause?
--get Branches having MNCs according to length
select distinct BRN_CD
into #MNCs
from --
SRDM_PlanningHierarchy
where ProcessDate between '12/31/06' and '9/2/07'
and BRN_CD like 'W%'
and len(BRN_CD)<=8
order by BRN_CD
--Get Totals for each Branch
select LEFT(cr.Code,8) AS Code,
sum(cr.CommissionableRevenue) as totals,
cr.RevDate
from CorporateRevenue07 cr
where cr.ProcessDate = '9/1/07' --------Remains static for report
AND cr.RevDate between '12/31/06' and '9/2/07'
and cr.Gate = 'WW'
and cr.RevType = 'TB'
and cr.Code in (select BRN_CD from #MNCs)
group by LEFT(cr.Code,8), cr.RevDate --characters vary, 4 to 8 -- change above also
order by cr.RevDate
November 29, 2007 at 12:03 pm
Generic the answer to your question is - yes, you can use multiple LIKE statements, but in your case - you're trying to do too many things in one, which is going to hurt you in the long run. Split the processing up a little.
It's going to be easier for you to extract the values you want to a table which you can then use for your matching. Split the difficulties up. IN gets to be VERY inefficient when you start getting into large sets of data.
use something like this
create table MatchCodes(codeVal Varchar(20))
Once you have this - insert values in there as you see fit, or when you need them for your total.
something like
insert matchcodes (codeval)
select 'WNENSTAT' UNION ALL
select 'WNENYCFN' UNION ALL
select 'WSEG' UNION ALL
select 'WSENFLA' UNION ALL
select 'WWEST' UNION ALL
select
codefield
from
codetable
where
codefield like 'WNENYCHM%'
or codefield like 'WWGRTLA%'
And then just use that MatchCodes table in your WHERE clause. The stuff you currently have in the HAVING should be moved into a WHERE clause, since the where applies to pre-aggregated data, and the HAVING applies to the POST aggregated data.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply