Conditional statements within select for strings

Question

at the moment I query my database to find all interactions within a certain date parameter. I also join that table with my InteractionAttendees then join it by TargetGroups table. InteractionAttendees tells me who attended the interaction, TargetGroup tells me about the person, if they are part of a target group such as aboriginal or francophone.

I'm having trouble setting my Aboriginal and Francophone columns. I want to set it to be true if there was any attendees that are in the TargetGroup Table.

select CONVERT(date, getdate()) as ActivityDate, 
       CASE when Type = 0 Then 'General' 
       when Type = 1 Then 'Activity' 
       else 'Task' End as Type, 

       CASE when Indepth = 0 Then 'False' else 'True' End as Indepth, 

       Subject,

       Comments,

       'false' as Aboriginal,
       'false' as FrancoPhone,
       'false' as Female,
       'false' as Youth,
       'false' as Other

from Sarnia.dbo.Interactions as X 
full outer join sarnia.dbo.InteractionAttendees as Y on X.Id = Y.Interaction_Id
full outer join Sarnia.dbo.TargetGroups as Z on Y.Person_Id = Z.PersonId
where ActivityDate >= '2015-07-01' and ActivityDate <= '2015-09-30' 
group by ActivityDate, Type, Indepth, Created, subject, comments

for example

Interaction Table
Id
 1

Interaction Attendee Table
Id    InteractionId    PersonId
1          1               5
2          1              10

TargetGroups Table
Id    PersonId    TargetValue
1        5           Aboriginal
2       10           Francophone

So my resulted table would be

Activity Date    Aboriginal    Francophone
     ----           True            True

May I ask how do I properly populate my target group columns.

Anup Agrawal · Accepted Answer · 2015-09-30 21:36:00Z

1

You can update your query something like this: Pivot in the old fashioned way. Based on your previous questions I am assuming it is a sql server DB.

   MAX(IIF(TargetValue = 'Aboriginal', 'True', 'False')) as Aboriginal,
   MAX(IIF(TargetValue = 'FrancoPhone', 'True', 'False')) as FrancoPhone

If you have SQL Server 2008 or older you can use something like this

MAX(CASE WHEN TargetValue = 'Aboriginal' THEN 'True' ELSE 'False' END) as Aboriginal,
MAX(CASE WHEN TargetValue = 'FrancoPhone' THEN 'True' ELSE 'False' END) as FrancoPhone

answered Sep 30, 2015 at 21:36

Anup Agrawal

6,6891 gold badge30 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hogan · Accepted Answer · 2015-09-30 22:42:01Z

Here is an example of how to do this with a CTE. Note, I added this to the example sql you gave, but that sql has many errors.

with igroup as
(
   SELECT IID,
         CASE WHEN ACOUNT > 0 THEN true ELSE false END AS Aboriginal,
         CASE WHEN FCOUNT > 0 THEN true ELSE false END AS Francophone
   FROM (

     SELECT InteractionId as IID,
          SUM(CASE WHEN TargetValue = 'Aboriginal' THEN 1 ELSE 0 END) as ACOUNT,
          SUM(CASE WHEN TargetValue = 'FrancoPhone' THEN 1 ELSE 0 END) as FCOUNT
     FROM sarnia.dbo.InteractionAttendees A
     LEFT JOIN sarnia.dbo.TargetGroups as G on A.Person_Id = G.PersonId
     GROUP BY InteractionId
   ) X
)
select CONVERT(date, getdate()) as ActivityDate, 
       CASE when Type = 0 Then 'General' 
       when Type = 1 Then 'Activity' 
       else 'Task' End as Type, 

       CASE when Indepth = 0 Then 'False' else 'True' End as Indepth, 

       Subject,

       Comments,

       igroup.Aboriginal,
       igroup.FrancoPhone,

from Sarnia.dbo.Interactions as X 
full outer join sarnia.dbo.InteractionAttendees as Y on X.Id = Y.Interaction_Id
join igroup on X.Id = igroup.IID
where ActivityDate >= '2015-07-01' and ActivityDate <= '2015-09-30' 
group by ActivityDate, Type, Indepth, Created, subject, comments

Collectives™ on Stack Overflow

Conditional statements within select for strings

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related