Make Your Microsoft Dynamics Investment Count & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.
Become a Premium Member and unlock a new, free course in leading technologies each month.
SELECT
auditors.auditorID
,auditors.fName
, COUNT(DISTINCT case when claims.dateon >='20140928' AND claims.dateon < '20141028' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30
, SUM(case when claims.dateon >= '20140928' AND claims.dateon < '20141028' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30
FROM auditors
INNER JOIN claims ON claims.auditorID = auditors.auditorID
INNER JOIN entries ON claims.rID = entries.rid
WHERE claims.status = 'closed'
AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')
GROUP BY
auditors.auditorID
, auditors.fname
ORDER BY auditors.fName
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
FROM auditors
LEFT JOIN claims
ON claims.auditorID = auditors.auditorID
AND claims.status = 'closed'
AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')
LEFT JOIN entries
ON claims.rID = entries.rid
SELECT
auditors.auditorID
, auditors.fName
, COUNT(DISTINCT CASE
WHEN claims.dateon >= '20140928' AND
claims.dateon < '20141028' AND
entries.errorCode NOT IN ('DP', 'RB', 'WP', 'PE') THEN claims.rID
END) AS rTotal30
, SUM(CASE
WHEN claims.dateon >= '20140928' AND
claims.dateon < '20141028' AND
entries.errorCode NOT IN ('DP', 'RB', 'WP', 'PE') THEN entries.refundDue
ELSE 0.0
END) AS rate30
FROM auditors
LEFT JOIN claims
ON claims.auditorID = auditors.auditorID
AND claims.status = 'closed'
AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')
LEFT JOIN entries
ON claims.rID = entries.rid
GROUP BY
auditors.auditorID
, auditors.fname
ORDER BY
auditors.fName
SELECT
auditors.auditorID
, auditors.fName
, COUNT(DISTINCT claims.rID) AS rTotal30
, SUM(entries.refundDue) AS rate30
FROM auditors
LEFT JOIN claims
ON claims.auditorID = auditors.auditorID
AND claims.status = 'closed'
AND (claims.dateon >= '20140928' AND claims.dateon < '20141028')
LEFT JOIN entries
ON claims.rID = entries.rid
AND entries.errorCode NOT IN ('DP', 'RB', 'WP', 'PE')
GROUP BY
auditors.auditorID
, auditors.fname
ORDER BY
auditors.fName
SELECT
auditors.auditorID
, auditors.fName
, COUNT(DISTINCT case when claims.dateon >='20140914' AND claims.dateon < '20141029' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30
, SUM(case when claims.dateon >= '20140914' AND claims.dateon < '20141029' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30
, COUNT(DISTINCT case when claims.dateon >='20140914' AND claims.dateon < '20141029' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal30
, SUM(case when claims.dateon >= '20140914' AND claims.dateon < '20141029' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment30
, COUNT(DISTINCT case when claims.dateon >='20140731' AND claims.dateon < '20140913' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal60
, SUM(case when claims.dateon >= '20140731' AND claims.dateon < '20140913' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate60
, COUNT(DISTINCT case when claims.dateon >='20140731' AND claims.dateon < '20140913' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal60
, SUM(case when claims.dateon >='20140731' AND claims.dateon < '20140913' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment60
, COUNT(DISTINCT case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal90
, SUM(case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate90
, COUNT(DISTINCT case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal90
, SUM(case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment90
, COUNT(DISTINCT case when claims.dateon >='20140502' AND claims.dateon < '20140615' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal120
, SUM(case when claims.dateon >= '20140502' AND claims.dateon < '20140615' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate120
, COUNT(DISTINCT case when claims.dateon >='20140502' AND claims.dateon < '20140615' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal120
, SUM(case when claims.dateon >='20140502' AND claims.dateon < '20140615' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment120
FROM auditors
LEFT JOIN claims ON claims.auditorID = auditors.auditorID AND claims.status = 'closed' AND (claims.dateon >= '20140502' AND claims.dateon < '20141029')
LEFT JOIN entries ON claims.rID = entries.rid
GROUP BY
auditors.auditorID
, auditors.fname
ORDER BY auditors.fName
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.