✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
select e.value, d.title
from external_map e,
document d
where e.doc_id = d.id
and e.value like '%itos%'
select e.value, d.title
from external map e,
document d
where e.doc_id = d.id
and e.value like 'Memorial%'
-- Account names for Licensees
-- Used to identify licensees from the FTP logs
-- ---------------------------------------------
-- SELECT u.doc_id, u.value, l.value
SELECT (convert(nvarchar(10),u.doc_id) + ':' + u.value + ':' + l.value)
FROM query_term u
JOIN query_term l
ON u.doc_id = l.doc_id
AND u.path = '/Licensee/FtpInformation/UserName'
WHERE l.path = '/Licensee/LicenseeInformation' +
'/LicenseeNameInformation' +
'/OfficialName/Name'
-- Account names for Licensees
-- Used to identify licensees from the FTP logs
-- ---------------------------------------------
-- SELECT u.doc_id, u.value, l.value
SELECT (convert(nvarchar(10),u.doc_id) + ':' + u.value + ':' + l.value)
FROM query_term u
JOIN query_term l
ON u.doc_id = l.doc_id
AND u.path = '/Licensee/FtpInformation/UserName'
WHERE l.path = '/Licensee/LicenseeInformation' +
'/LicenseeNameInformation' +
'/OfficialName/Name'
order by l.value
-- This query selects the image media files published
-- between two dates.
-- You must adjust the dates for a specific time frame.
-- Note:
-- The date needs to be specified three times:
-- a) FromDate b) ToDate c) FromDate
-- This report delivers the same result as the ad-hoc
-- ICRDB Pub Numbers - Images published this year
-- ----------------------------------------------------
SELECT ppd.doc_id, d.title, min(pp.started) AS started
FROM pub_proc_doc ppd
JOIN pub_proc pp
ON pp.id = ppd.pub_proc
Join document d
ON d.id = ppd.doc_id
JOIN doc_type dt
ON d.doc_type = dt.id
AND dt.name = 'Media'
JOIN query_term_pub q
ON q.doc_id = d.id
AND q.path = '/Media/PhysicalMedia/ImageData/ImageEncoding'
WHERE pp.started between '2011-01-01' and dateadd(DAY, 1, '2011-12-15')
AND pp.pub_subset LIKE 'Push_%%'
AND pp.status = 'Success'
AND NOT EXISTS (SELECT 'x'
FROM pub_proc_doc a
JOIN pub_proc b
ON b.id = a.pub_proc
WHERE started < '2011-01-01'
AND pub_subset LIKE 'Push_%%'
AND status = 'Success'
AND a.doc_id = ppd.doc_id
)
GROUP BY ppd.doc_id, d.title
ORDER BY ppd.doc_id
-- This query selects the image media files published
-- between two dates.
-- You must adjust the dates for a specific time frame.
-- Note:
-- The date needs to be specified three times:
-- a) FromDate b) ToDate c) FromDate
-- This report delivers the same result as the ad-hoc
-- ICRDB Pub Numbers - Images published this year
-- ----------------------------------------------------
SELECT ppd.doc_id, d.title, min(pp.started) AS started
FROM pub_proc_doc ppd
JOIN pub_proc pp
ON pp.id = ppd.pub_proc
Join document d
ON d.id = ppd.doc_id
JOIN doc_type dt
ON d.doc_type = dt.id
AND dt.name = 'Media'
JOIN query_term_pub q
ON q.doc_id = d.id
AND q.path = '/Media/PhysicalMedia/ImageData/ImageEncoding'
WHERE pp.started between '2011-01-01' and dateadd(DAY, 1, '2015-05-05')
AND pp.pub_subset LIKE 'Push_%%'
AND pp.status = 'Success'
AND NOT EXISTS (SELECT 'x'
FROM pub_proc_doc a
JOIN pub_proc b
ON b.id = a.pub_proc
WHERE started < '2011-01-01'
AND pub_subset LIKE 'Push_%%'
AND status = 'Success'
AND a.doc_id = ppd.doc_id
)
GROUP BY ppd.doc_id, d.title
ORDER BY ppd.doc_id
SELECT n.doc_id, n.value AS "Name",
b.value AS "Contact", e.value AS "Email"
FROM query_term n
JOIN query_term t
ON t.doc_id = n.doc_id
AND t.path = '/Licensee/LicenseeInformation' +
'/LicenseeStatus'
JOIN query_term b
ON b.doc_id = n.doc_id
AND b.path = '/Licensee/ContactPersons' +
'/ContactPerson/ContactName'
JOIN query_term ct
ON ct.doc_id = n.doc_id
AND ct.path = '/Licensee/ContactPersons' +
'/ContactPerson/ContactType'
AND left(ct.node_loc, 8) = left(b.node_loc, 8)
LEFT OUTER JOIN query_term p
ON p.doc_id = b.doc_id
AND p.path = '/Licensee/ContactPersons' +
'/ContactPerson/ContactDetail' +
'/Phone'
AND left(p.node_loc, 8) = left(b.node_loc, 8)
LEFT OUTER JOIN query_term e
ON e.doc_id = b.doc_id
AND e.path = '/Licensee/ContactPersons' +
'/ContactPerson/ContactDetail' +
'/Email'
AND left(e.node_loc, 8) = left(b.node_loc, 8)
WHERE n.path = '/Licensee/LicenseeInformation' +
'/LicenseeNameInformation' +
'/OfficialName/Name'
and t.value in ('Production', 'Test')
ORDER BY n.value, ct.value
SELECT d.id --AS "CDR ID", d.title AS "Doc title",
-- d.first_pub AS "Date First Published"
FROM document d
JOIN doc_type t
ON t.id = d.doc_type
WHERE t.name = 'Media'
AND d.first_pub IS NOT NULL
and d.active_status = 'A'
and d.title not like '%MP3%'
order by d.id
✓
✓
+
✓
+
+
+
+
+
+
+