SELECT CorePageName AS ArticleName ,
T.WidgetTypeName ,
WidgetPageName ,
ZonePosition ,
CoreCreated ,
CoreFirstPublish
FROM ( SELECT *
FROM ( SELECT w.* ,
( SELECT c.CorePageTypeName
FROM dbo.CorePage c
WHERE WidgetPageID = c.CoreID
) WidgetTypeName ,
( SELECT TOP 1
cpvw.CorePageName
FROM dbo.CorePageVersion cpvw
WHERE WidgetPageID = cpvw.CorePageID
) WidgetPageName ,
cp.CoreID ,
cpv.CoreVersionID ,
'ArticleBottomWidget' AS ZonePosition ,
cp.CoreGuid ,
( SELECT cr.CoreGuid
FROM dbo.CorePage cr
WHERE cr.CoreID = w.WidgetPageID
) WidgetGuid ,
cpv.CorePageName ,
cp.CoreCreated ,
cpv.CoreFirstPublish
FROM dbo.ArticleBottomWidget w
INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
WHERE cp.CoreRemoteSite = 'AmeliaCore'
) AS tmp1
INNER JOIN ( SELECT cpv.CorePageID ,
MAX(cpv.CoreVersionID) AS LastVersionID
FROM dbo.CorePageVersion cpv
WHERE cpv.CoreStatus=4
GROUP BY cpv.CorePageID
) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
UNION ALL
SELECT *
FROM ( SELECT w.* ,
( SELECT c.CorePageTypeName
FROM dbo.CorePage c
WHERE WidgetPageID = c.CoreID
) WidgetTypeName ,
( SELECT TOP 1
cpvw.CorePageName
FROM dbo.CorePageVersion cpvw
WHERE WidgetPageID = cpvw.CorePageID
) WidgetPageName ,
cp.CoreID ,
cpv.CoreVersionID ,
'ArticleRightWidget' AS ZonePosition ,
cp.CoreGuid ,
( SELECT cr.CoreGuid
FROM dbo.CorePage cr
WHERE cr.CoreID = w.WidgetPageID
) WidgetGuid ,
cpv.CorePageName ,
cp.CoreCreated ,
cpv.CoreFirstPublish
FROM dbo.ArticleRightWidget w
INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
WHERE cp.CoreRemoteSite = 'AmeliaCore'
) AS tmp1
INNER JOIN ( SELECT cpv.CorePageID ,
MAX(cpv.CoreVersionID) AS LastVersionID
FROM dbo.CorePageVersion cpv
WHERE cpv.CoreStatus=4
GROUP BY cpv.CorePageID
) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
UNION ALL
SELECT *
FROM ( SELECT w.* ,
( SELECT c.CorePageTypeName
FROM dbo.CorePage c
WHERE WidgetPageID = c.CoreID
) WidgetTypeName ,
( SELECT TOP 1
cpvw.CorePageName
FROM dbo.CorePageVersion cpvw
WHERE WidgetPageID = cpvw.CorePageID
) WidgetPageName ,
cp.CoreID ,
cpv.CoreVersionID ,
'ArticleTopWidget' AS ZonePosition ,
cp.CoreGuid ,
( SELECT cr.CoreGuid
FROM dbo.CorePage cr
WHERE cr.CoreID = w.WidgetPageID
) WidgetGuid ,
cpv.CorePageName ,
cp.CoreCreated ,
cpv.CoreFirstPublish
FROM dbo.ArticleTopWidget w
INNER JOIN dbo.CorePageVersion cpv ON w.CoreArticleVersionID = cpv.CoreVersionID
INNER JOIN dbo.CorePage cp ON cpv.CorePageID = cp.CoreID
WHERE cp.CoreRemoteSite = 'AmeliaCore'
) AS tmp1
INNER JOIN ( SELECT cpv.CorePageID ,
MAX(cpv.CoreVersionID) AS LastVersionID
FROM dbo.CorePageVersion cpv
WHERE cpv.CoreStatus=4
GROUP BY cpv.CorePageID
) AS tmp2 ON tmp1.CoreID = tmp2.CorePageID AND tmp1.CoreVersionID = tmp2.LastVersionID
) AS T
WHERE T.WidgetTypeName IN ('[Widget] Shop image gallery widget','[Widget] Factbox','[Widget] Html','[Widget] Quick poll widget','[Widget] Quiz widget')
--IS NOT NULL --AND T.CorePageID < 110683 --(T.CorePageID>=113351 AND T.CorePageID<=110683000)
ORDER BY T.CorePageID