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
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
Title | # Comments | Views | Activity |
---|---|---|---|
fail an Ssis 2012 package when a certain scenario occurs and send out an email and stop the job/ package. | 5 | 31 | |
TSQL - How to declare table name | 26 | 31 | |
Tsql query | 6 | 22 | |
MS SQL BCP Extra Lines Between Records | 2 | 19 |
Join the community of 500,000 technology professionals and ask your questions.