SQL, How to do aggregation on extracting field and range band field?

hi Experts,

1) I want to extract numeric , then do aggregation on [promo code]. I.E. I want to extract numeric from [promo code] = BWL0416A or BWL0416 to 0416, then do the aggregation of [quantity'], [number of applications] based on 0416.

2) I want to add a range band on [response score], then do the aggregation of [quantity'], [number of applications] based on the range band.

The query will be similar like

select 
     newpromocode, 
     CBD,
     CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN '0-500'  
          WHEN RESPONSESCORE BETWEEN 500 AND 1000  THEN '500-1000' END AS RESPONSESCORE,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN QUANTITY END ) AS QUANTITY,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 500 AND 1000 THEN QUANTITY END ) AS QUANTITY,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN numberofapplications END ) AS numberofapplications,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 500 AND 1000 THEN numberofapplications END ) AS numberofapplications
FROM  
    (
      SELECT
      extractnumeric AS newpromocode,
      CBD,
     responsescore,
     sum(case when EXTRACT NUMERIC (PROMOCODE) THEN NUMERIC THEN QUANTITY END )AS QUANTITY,
     sum(case when EXTRACT NUMERIC (PROMOCODE) THEN NUMERIC THEN numberofapplications END )AS numberofapplications
     from applications
     ) A

Open in new window

For "EXTRACT NUMERIC (PROMOCODE) ......" I do not know what the extract query is suitable for this case, so just made it up. Sorry for the complication.

Please find the attachment for the original data and result table.

Thank you,
Capture.PNG

It is possible to do with some simple cross apply magic:

declare @t table (t varchar(20));

insert into @t values('BWL0416A'),('0462'),('BWL0417');

select t, a.a, b.b
from @t
cross apply(select CASE WHEN PATINDEX('%[0-9]%', t) > 0 THEN SUBSTRING(t, PATINDEX('%[0-9]%', t), LEN(t)) ELSE t END a) a
cross apply(select CASE WHEN PATINDEX('%[^0-9]%', a.a) > 0 THEN SUBSTRING(a.a, 1, PATINDEX('%[^0-9]%', a.a)-1) ELSE a.a END b) b

Open in new window

Applicable to your case the query will be:

select 
     newpromocode, 
     CBD,
     CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN '0-500'  
          WHEN RESPONSESCORE BETWEEN 500 AND 1000  THEN '500-1000' END AS RESPONSESCORE,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN QUANTITY END ) AS QUANTITY,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 500 AND 1000 THEN QUANTITY END ) AS QUANTITY,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 0 AND 500 THEN numberofapplications END ) AS numberofapplications,
     SUM(CASE WHEN RESPONSESCORE BETWEEN 500 AND 1000 THEN numberofapplications END ) AS numberofapplications
FROM  
    (
      SELECT
      extractnumeric AS newpromocode,
      CBD,
     responsescore,
     sum(case when IsNumeric(b.b)=1 THEN CAST(b.b as INT) ELSE QUANTITY END )AS QUANTITY,
     sum(case when IsNumeric(b.b)=1 THEN CAST(b.b as INT) ELSE numberofapplications END )AS numberofapplications
     from applications
cross apply(select CASE WHEN PATINDEX('%[0-9]%', PROMOCODE) > 0 THEN SUBSTRING(PROMOCODE, PATINDEX('%[0-9]%', PROMOCODE), LEN(PROMOCODE)) ELSE PROMOCODE END a) a
cross apply(select CASE WHEN PATINDEX('%[^0-9]%', a.a) > 0 THEN SUBSTRING(a.a, 1, PATINDEX('%[^0-9]%', a.a)-1) ELSE a.a END b) b
     ) A

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 25

chaauCommented: 2017-03-07

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

tanj1035Author Commented: 2017-03-13

Thanks for your help;.

SQL, How to do aggregation on extracting field and range band field?

Who is Participating?