Return to Question

create table #SurveyTemp(
            Com nchar(6),
            Survey nvarchar(50),
            Demo nchar(50),
            Wk int,
            Time int,
            Aud decimal(18,8)
            )
insert into #SurveyTemp (Com, Survey, Demo, Wk, Time, Aud)
 
select Com, Survey, 'A18+', Wk, Time, sum(Aud)
from table_survey
where survey = 'LO2017'
and 
 demograph like 'F18+'
or
 surveyid = 'LO2017'
and 
 demograph like 'M18+'
group by Com, survey, Wk, Time

insert into table_survey
select temp.Com, temp.Survey, temp.Demo, temp.Wk, temp.Time, temp.Aud
from #SurveyTemp temp

drop table #SurveyTemp

What i need to write now is a script that creates a range. Notice how in the table above there is a A18+ and a A25+. Please bear in mind that in my live table there will be MULTIPLE rows for all demographs.

In this example, i will be using the above table. So, i need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

In the script below, i am trying to create a range called 'A1824' but i am either getting a "subquery returned more than 1 row" error OR it is inserting the wrong data into the temp table where the sum in AudOne and AudTwo is the SAME for every single Wk and Time.

create table #SurveyTemp(
            Com nchar(6),
            Survey nvarchar(50),
            Demo nchar(50),
            Wk int,
            Time int,
            Aud decimal(18,8),
            AudOne decimal(18,8),
            AudTwo decimal(18,8)
            )
insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(
 select Com, Survey, 'A1824', Wk, Time, 0.0, 
(select sum(aud) from table_survey where demo = 'A18+'), 
(select sum(aud) from table_survey where demo = 'A25+')
from table_survey 
where surveyid = 'LO2017'
group by Com, survey, Wk, Time, 
)

update #SurveyTemp
set Aud = AudOne - AudTwo

insert into table_survey
select temp.Com, temp.survey, temp.demo, temp.Wk, temp.Time, temp.aud
from #SurveyTemp temp

drop table #SurveyTemp

create table #SurveyTemp 
(
    Com nchar(6),
    Survey nvarchar(50),
    Demo nchar(50),
    Wk int,
    Time int,
    Aud decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, Time, Aud)
select   Com, Survey, 'A18+', Wk, Time, sum(Aud)
from     table_survey
where    survey = 'LO2017'
and      demograph like 'F18+'
or       surveyid = 'LO2017'
and      demograph like 'M18+'
group by Com, survey, Wk, Time:

insert into table_survey
select temp.Com, temp.Survey, temp.Demo, temp.Wk, temp.Time, temp.Aud
from   #SurveyTemp temp

drop table #SurveyTemp

What I need to write now is a script that creates a range. Notice how in the table above there is a A18+ and a A25+. Please bear in mind that in my live table there will be MULTIPLE rows for all demographs.

In this example, I will be using the above table. So, I need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

In the script below, I am trying to create a range called 'A1824' but I am either getting a "subquery returned more than 1 row" error OR it is inserting the wrong data into the temp table where the sum in AudOne and AudTwo is the SAME for every single Wk and Time.

create table #SurveyTemp 
(
    Com nchar(6),
    Survey nvarchar(50),
    Demo nchar(50),
    Wk int,
    Time int,
    Aud decimal(18,8),
    AudOne decimal(18,8),
    AudTwo decimal(18,8)
);

insert into #SurveyTemp (Com, Survey, Demo, Wk, TimeBlock, Aud, AudOne, AudTwo)
(select   Com, Survey, 'A1824', Wk, Time, 0.0, 
          (select sum(aud) from table_survey where demo = 'A18+'), 
          (select sum(aud) from table_survey where demo = 'A25+')
 from     table_survey 
 where    surveyid = 'LO2017'
 group by Com, survey, Wk, Time, 
)

update #SurveyTemp
set Aud = AudOne - AudTwo;

insert into table_survey
select temp.Com, temp.survey, temp.demo, temp.Wk, temp.Time, temp.aud
from   #SurveyTemp temp

drop table #SurveyTemp

In this example, i will be using the above table. So, i need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, sum A18+ and A25+ THEN subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

In this example, i will be using the above table. So, i need to create a similar script that will sum the audience for two demographs and then subtract one from the other to create a range. For example, subtract the A25+ data from A18+ to create a range called A1824. I hope that makes sense.

Edit: My expected results would be something like the following:

com | surveyid | demo  | wk | time | audience
-------------------------
 1  | LO2017   | A1824 | 1  | 300  |   2.4

But there would be multiple rows where there are multiple 'wk' and 'time' entries. E.g.:

com | surveyid | demo  | wk | time | audience
-------------------------
 1  | LO2017   | A1824 | 1  | 100  |   2.4
 1  | LO2017   | A1824 | 1  | 200  |   3.7
 1  | LO2017   | A1824 | 2  | 100  |   2.1
 1  | LO2017   | A1824 | 2  | 200  |   6.2