-2

I'm running a contest that lists all videos watched by school teachers, however if one of the videos they watch is a 'stem' video, I want their name to be listed twice so that they can be put into the drawing twice.
Basically I have a case when clause in there so that if a video contains the word 'stem' in the title, the final column of my table says 'Stem' else '0'. In the situation where the final column says stem, I want to repeat that entire row. I hope that makes sense. This is what I have so far;

SELECT *
FROM (
SELECT District, 
       SchoolName, 
       PersonnelId, 
       Name, 
       EmailAddress, 
       BeganViewingDate, 
       CASE WHEN SecondsCompleted >= SeventyFivePercent 
            THEN ContentName 
            ELSE '0' END as 'ContentName',
       SegmentLengthInSeconds, 
       SecondsCompleted,
       CASE WHEN contentname LIKE '%Stem%' 
            THEN 'Stem' 
            ELSE '0' END As 'Stem'
FROM (

SELECT vp.PersonnelId,
       cp.EmailAddress, 
       CONCAT(cp.LastName, ', ', cp.FirstName) as Name, 
       vp.BeganViewingDate, 
       vp.SecondsCompleted, 
       c.ContentId, 
       c.ContentName, 
       c.SegmentLengthInSeconds, 
       ROUND(SegmentLengthInSeconds*0.75) as SeventyFivePercent,     
       cv.SchoolName, 
       cv.District
FROM PD360v2.ViewingProgress as vp
JOIN PD360v2.Content as c on vp.ContentId = c.ContentId
JOIN PD360v2.ClientPersonnel as cp on vp.PersonnelId = cp.PersonnelId
JOIN PD360v2.ClientView as cv on cp.ClientId = cv.Id
WHERE vp.BeganViewingDate BETWEEN '2016-02-02 08:00:00' AND '2016-02-09 07:59:00'
AND cv.NcesDistrictId IN (List Of districts here)

) as a

ORDER BY PersonnelId) as b
WHERE ContentName not like 0
ORDER BY District, SchoolName, Name;
Aidandebradney
  • 25
  • 4

1 Answers1

0

i just brief the sql, you should add column you want by urself

Select vp.name,'0' from PD360v2.ViewingProgress as vp
Where vp.BeganViewingDate between '2016-02-02 08:00:00' and '2016-02-09 07:59:00' And cv.NcesDistrictId in (List Of districts here)
Union all
Select vp.name,'Stem' from PD360v2.ViewingProgress as vp
join PD360v2.Content as c on vp.contentid=c.contentid
Where vp.BeganViewingDate between '2016-02-02 08:00:00' and '2016-02-09 07:59:00' And cv.NcesDistrictId in (List Of districts here) and contentname like '%Stem%'
kiro
  • 111
  • 1
  • 4