I need to create a query that will show all the cells from the table below which exceed 80%.
Here is the query I was given (quite long). I am a beginner and this query is too advanced for me.
I need to add a statement which will retrieve the Channel name and column name as well as the %number > 80%, all in one row.
Thank you.
| dbxquery connection=BDMS shortnames=t query="select *
from (
select
p.ZoneTargetAreaName DMA,
p.ZoneTargetAreaChannel Channel,
cast(cast(sum(case when p.NumberSTBRanAd = 0 and p.NumberSTBNotRanAd = 0 and p.ExternalStatusCode = 1 then 1 else 0 end) / cast(count(p.PlacementID) as decimal) *100 as decimal(18,2)) as varchar(6))+'%' percents
from placements p
inner join AiredBreaks ab on p.BreakID = ab.BreakID
where ExternalStatusCode in (1,2,12)
and ab.ViewsrvID = '1P' -- using only primary server
and (
(ab.AiredTime >= dateadd(hour,5,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,5,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('ATL','BLW','BOS','BTM','CBA','CHR','CLB','CLE','CNC','DET','DTN','FMN','GBO','GDP','GSP','HLB','HTF','IDY','JAX','KNX','LSV','LXT','MIA','NLK','NYC','ORL','PBG','PHL','RAL','RMP','RNK','TPA','WBS','WDC','WPM')) or
(ab.AiredTime >= dateadd(hour,6,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,6,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('AST','BHM','CHG','CSD','DFW','DSO','HOU','HTS','JKS','KTY','LRK','MBP','MMS','MSP','MWK','NOL','NSH','OKC','OMH','PDC','SAN','SFM','STL','TLS','WTA')) or
(ab.AiredTime >= dateadd(hour,7,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,7,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('ABQ','CSP','DEN','SLC','PHX','TUS')) or
(ab.AiredTime >= dateadd(hour,8,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))-1 and ab.AiredTime < dateadd(hour,8,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) and p.Zonetargetareaname in ('FAT','LAS','LAX','PRT','SAC','SDC','SEA','SFO','SKN')) )
group by p.ZoneTargetAreaName, p.ZoneTargetAreaChannel
) as P
pivot (
min(percents) for P.DMA in (ATL,BLW,BOS,BTM,CBA,CHR,CLB,CLE,CNC,DET,DTN,FMN,GBO,GDP,GSP,HLB,HTF,IDY,JAX,KNX,LSV,LXT,MIA,NLK,NYC,ORL,PBG,PHL,RAL,RMP,RNK,TPA,WBS,WDC,WPM,AST,BHM,CSD,CHG,DFW,DSO,HOU,HTS,JKS,KTY,LRK,MBP,MMS,MSP,MWK,NOL,NSH,OKC,OMH,PDC,SAN,SFM,STL,TLS,WTA,ABQ,CSP,DEN,PHX,SLC,TUS,FAT,LAS,LAX,PRT,SAC,SDC,SEA,SFO,SKN)
) as PIV"
| fields - _raw _time
| fillnull value=NULL ATL BLW BOS BTM CBA CHR CLB CLE CNC DET DTN FMN GBO GDP GSP HLB HTF IDY JAX KNX LSV LXT MIA NLK NYC ORL PBG PHL RAL RMP RNK TPA WBS WDC WPM AST BHM CSD CHG DFW DSO HOU HTS JKS KTY LRK MBP MMS MSP MWK NOL NSH OKC OMH PDC SAN SFM STL TLS WTA ABQ CSP DEN PHX SLC TUS FAT LAS LAX PRT SAC SDC SEA SFO SKN
| table Channel ATL BLW BOS BTM CBA CHR CLB CLE CNC DET DTN FMN GBO GDP GSP HLB HTF IDY JAX KNX LSV LXT MIA NLK NYC ORL PBG PHL RAL RMP RNK TPA WBS WDC WPM AST BHM CSD CHG DFW DSO HOU HTS JKS KTY LRK MBP MMS MSP MWK NOL NSH OKC OMH PDC SAN SFM STL TLS WTA ABQ CSP DEN PHX SLC TUS FAT LAS LAX PRT SAC SDC SEA SFO SKN
... View more