0

I am trying to write a script that will combine specific survey results based on the demographs used in the survey. I have been able to write a script that adds demographs together to create a new demograph e.g. M18+ and F18+ = A18+ which works perfectly however when trying to create a script that will create a demograph range e.g. A18-24, i am encountering some issues i cannot resolve.

My first script that adds two demographs together is as follows:

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

This script works fine. It adds two demographs together and inserts new rows of data, creating a sum of the audience for each Com, Survey, Wk, and then Time. See the sample data set below where the addition of M18+ and F18+ has created a new record called A18+

Let me start with a sample data set: (Disclaimer: the live database has MANY rows for each demograph)

com | surveyid | demo | wk | time | audience
-------------------------
 1 | LO2017    | A18+ | 1  | 300  |   4.7
 1 | LO2017    | F18+ | 1  | 300  |   1.9
 1 | LO2017    | M18+ | 1  | 300  |   2.8
 1 | LO2017    | A25+ | 1  | 300  |   2.3
 1 | LO2017    | A18+ | 2  | 100  |   3.7
 1 | LO2017    | F18+ | 2  | 100  |   1.9
 1 | LO2017    | M18+ | 2  | 100  |   2.8
 1 | LO2017    | A25+ | 2  | 100  |   4.3

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

It is this second script that i need to get working and have spent hours trying to resolve without much luck. Please let me know if i can provide any more information to help you help me!

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
  • What are your expected results? – Erik Darling Mar 14 '17 at 1:01
  • @sp_BlitzErik Good question. I have added this into the question! – L.Moy Mar 14 '17 at 1:10
  • As far as I understand time = sum(time) and wk is not necessary right? – McNets Mar 14 '17 at 14:26
0

I'll start after you has generated #SurveyTemp table.

I'm not sure to understand your expected result, take a look at your table ordered by demo:

SELECT   * 
FROM     @table_survey  
WHERE    demo IN ('A18+','A25+') 
ORDER BY com, survey_id, demo, wk;

com | survey_id | demo | wk | time | audience | 
--: | :-------- | :--- | -: | ---: | :------- | 
  1 | LO2017    | A18+ |  1 |  300 | 4.7      | 
  1 | LO2017    | A18+ |  2 |  100 | 3.7      | 
  1 | LO2017    | A25+ |  1 |  300 | 2.3      | 
  1 | LO2017    | A25+ |  2 |  100 | 4.3      |

There are 2 records for each demo. If you want to add new record as a result of some operation between these two records, you should decide if you want to group by wk and time (that I think it's the correct way), or not.

In your insert command:

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, 
)

You're grouping by Com, Survey, Wk, Time, but you're calculating overall audience of all demo records, (without considering wk and time):

(select sum(aud) from table_survey where demo = 'A18+')
(select sum(aud) from table_survey where demo = 'A25+')

IMHO, at least wk should be added to the new records, and you should decide what to do with time.

My proposed solution: GROUP BY WK AND TIME

SELECT   Com, survey_id, 'A1824', wk, time,
         (SELECT sum(audience) FROM @table_survey 
          WHERE demo = 'A18+' AND wk = t1.wk AND time = t1.time)
          - 
         (SELECT sum(audience) FROM @table_survey 
          WHERE demo = 'A25+' AND wk = t1.wk AND time = t1.time) AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id, Wk, time;

NOTE: I've added wk and time to calculate sum(audience) for every demo.

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
  1 | LO2017    | A1824            |  1 |  300 | 2.4     
  1 | LO2017    | A1824            |  2 |  100 | -0.6    

As you can see, first record match your expected result.

Without group by wk and time

SELECT   Com, survey_id, 'A1824', 0 AS wk,
         avg(time) as time,
         (SELECT sum(audience) FROM @table_survey WHERE demo = 'A18+')
          - 
         (SELECT sum(audience) FROM @table_survey WHERE demo = 'A25+') AS audience
FROM     @table_survey t1
WHERE    survey_id = 'LO2017'
GROUP BY Com, survey_id;

This is the result:

Com | survey_id | (No column name) | wk | time | audience
--: | :-------- | :--------------- | -: | ---: | :-------
  1 | LO2017    | A1824            |  0 |  200 | 1.8     

NOTE: You don't need to INSERT & UPDATE, it can be done on a single INSERT operation.

Check it: dbfiddle here

  • I... wow. That got it! Thank you so much for your well thought out and detailed response! Can i paypal you a beer?! haha. – L.Moy Mar 14 '17 at 23:17
  • A Tequila shot will be enough!! – McNets Mar 14 '17 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.