Thanks Qiuyun Yu.
The sql PowerBI generates is
SELECT
TOP (1000001) [t1].[FinWeekofMonth],[t1].[FinMonthName],[t2].[mediatype],[t2].[ttlname],[t2].[sitecode],SUM([t2].[InsertNetCost])
AS [a0]
FROM
(
((
SELECT
insertbk.adnumbr,
insertbk.ttlcode, -- product/publication code
insertbk.dateins, -- date of apperance
case when insertbk.chargfl = 'Y' then ratecard.cpi else 0 end as InsertNetCost, -- net cost of insert
classes.classnm,
titles.mediatype,
titles.ttlname,
titles.sitecode
FROM
SalesWorks.dbo.adtrans
INNER JOIN insertbk ON adtrans.adnumbr = insertbk.adnumbr
INNER JOIN titles ON titles.ttlcode = insertbk.ttlcode
INNER JOIN classes ON insertbk.iadclass = classes.adclass
INNER JOIN ratecard ON ratecard.adnumbr = insertbk.adnumbr AND ratecard.ttlcode = insertbk.ttlcode AND ratecard.instnum = insertbk.instnum
LEFT OUTER JOIN custacct ON custacct.custid = adtrans.custid
WHERE
month(insertbk.dateins) >= MONTH(GETDATE())-2 and month(insertbk.dateins) <= MONTH(GETDATE())+1 and
YEAR(insertbk.dateins) >= YEAR(GETDATE()) and
insertbk.iadclass >= 1 and insertbk.iadclass <= 15000 and -- class number range
ratecard.soldfor = 1 and -- look for what advert was sold for, ratecard or agreed cost (Overide cost)
insertbk.actvflg <> 'M' and -- exclude killed inserts .... NOTE some inserts can be killed but still charged !
insertbk.actvflg <> 'K' -- exclude killed inserts ... NOTE some inserts can be killed but still charged !
) AS [t2]
left outer join
(SELECT
-- Order Header Data
category.category,
adtrans.adnumbr, -- advert URN
userdefs.userfnam,
userdefs.usersnam,
adtrans.adsourc as SourceCode -- advertising source code (renamed from "adoper" as this was misleading adtrans.adoper is correct value)
FROM
SalesWorks.dbo.adtrans
LEFT OUTER JOIN custacct ON custacct.custid = adtrans.custid
INNER JOIN category ON adtrans.adcatgy = category.adcatgy
INNER JOIN userdefs ON adtrans.adsourc = userdefs.adoper
WHERE adtrans.adnumbr in
(select insertbk.adnumbr from insertbk
INNER JOIN titles ON titles.ttlcode = insertbk.ttlcode
INNER JOIN ratecard ON ratecard.adnumbr = insertbk.adnumbr AND ratecard.ttlcode = insertbk.ttlcode AND ratecard.instnum = insertbk.instnum
WHERE
month(insertbk.dateins) >= MONTH(GETDATE())-2 and month(insertbk.dateins) <= MONTH(GETDATE())+1 and
YEAR(insertbk.dateins) >= YEAR(GETDATE()) and
insertbk.iadclass >= 1 and insertbk.iadclass <= 15000 and -- class number range
ratecard.soldfor = 1 and -- look for what advert was sold for, ratecard or agreed cost (Overide cost)
insertbk.actvflg <> 'M' and -- exclude killed inserts .... NOTE some inserts can be killed but still charged !
insertbk.actvflg <> 'K' -- exclude killed inserts ... NOTE some inserts can be killed but still charged !
)) AS [t0] on
(
[t2].[adnumbr] = [t0].[adnumbr]
)
)
left outer join
(
SELECT [Date],
CAST([Fin Year] as BIGINT)
AS [Fin Year],[FinWeekofMonth],[TIMEFRAME],[FinMonthName]
FROM
(
(SELECT [Date]
,[Index]
,[YearID]
,[Fin Year]
,[FinWeek Number]
,[FinMonthID]
,[FinMonthNo]
,[FinYear/Month]
,[FinWeekofMonth]
, CASE WHEN Calendar$.FinMonthName=DATENAME(m,DATEADD(m,0,GETDATE())) THEN 'This Month' WHEN Calendar$.FinMonthName=DATENAME(m,DATEADD(m,-1,GETDATE())) THEN 'Last Month' WHEN Calendar$.FinMonthName=DATENAME(m,DATEADD(m,1,GETDATE())) THEN 'Next Month' end as "TIMEFRAME"
, CASE WHEN Calendar$.FinMonthName=DATENAME(m,DATEADD(m,0,GETDATE())) THEN '1' WHEN Calendar$.FinMonthName=DATENAME(m,DATEADD(m,-1,GETDATE())) THEN '2' WHEN Calendar$.FinMonthName=DATENAME(m,DATEADD(m,1,GETDATE())) THEN '3' end as "TIMEorder"
,[FinMonthName]
FROM [Salesworks].[dbo].[Calendar$] WHERE [Calendar$].[Date] IS NOT NULL and YEAR([Calendar$].[Date]) >= '2016')
)
AS [t1]
)
AS [t1] on
(
[t2].[dateins] = [t1].[Date]
)
)
WHERE
(
(
(
[t0].[category] = 'Recruitment'
)
AND
(
[t1].[Fin Year] = 2017
)
)
AND
(
[t1].[TIMEFRAME] = 'Next Month'
)
)
GROUP BY [t1].[FinWeekofMonth],[t1].[FinMonthName],[t2].[mediatype],[t2].[ttlname],[t2].[sitecode]
