SELECT yy_code AS visit_area, RTRIM
((SELECT description
FROM pls..yingye_base
WHERE yy_code = A.yy_code)) AS 营业所, COUNT(*) AS visit_count,
DATEDIFF(Day, '2007/01/21', '2007/02/20') + 1 -
(SELECT COUNT(*)
FROM PUNCH..AM13
WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20') - 1 AS visit_days,
(SELECT COUNT(*) 'tempcolsaleddd'
FROM (SELECT slsman
FROM crm_visit
WHERE 1 = 1 AND CONVERT(char(10), visit_date, 111) >= '2007/01/21' AND
CONVERT(char(10), visit_date, 111) <= '2007/02/20' AND
yy_code = A.yy_code AND slsman IN
(SELECT slsman
FROM pls..yingye_slsman
WHERE haspart = 0)
GROUP BY slsman) CCCCDDDDD) AS '业务员数',
(SELECT COUNT(*) 'tempcol'
FROM (SELECT yy_code, slsman, visit_date
FROM crm_visit
WHERE 1 = 1 AND CONVERT(char(10), visit_date, 111) >= '2007/01/21' AND
CONVERT(char(10), visit_date, 111) <= '2007/02/20' AND
yy_code = A.yy_code AND visit_date NOT IN
(SELECT riqi
FROM PUNCH..AM13
WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20') AND
slsman IN
(SELECT slsman
FROM pls..yingye_slsman
WHERE haspart = 0)
GROUP BY yy_code, slsman, visit_date) ddddd) AS 实际反馈数
FROM crm_visit A
WHERE (1 = 1) AND (CONVERT(char(10), visit_date, 111) >= '2007/01/21') AND
(CONVERT(char(10), visit_date, 111) <= '2007/02/20') AND (visit_date NOT IN
(SELECT riqi
FROM PUNCH..AM13
WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20')) AND (slsman IN
(SELECT slsman
FROM pls..yingye_slsman
WHERE haspart = 0))
GROUP BY yy_code
ORDER BY yy_code
((SELECT description
FROM pls..yingye_base
WHERE yy_code = A.yy_code)) AS 营业所, COUNT(*) AS visit_count,
DATEDIFF(Day, '2007/01/21', '2007/02/20') + 1 -
(SELECT COUNT(*)
FROM PUNCH..AM13
WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20') - 1 AS visit_days,
(SELECT COUNT(*) 'tempcolsaleddd'
FROM (SELECT slsman
FROM crm_visit
WHERE 1 = 1 AND CONVERT(char(10), visit_date, 111) >= '2007/01/21' AND
CONVERT(char(10), visit_date, 111) <= '2007/02/20' AND
yy_code = A.yy_code AND slsman IN
(SELECT slsman
FROM pls..yingye_slsman
WHERE haspart = 0)
GROUP BY slsman) CCCCDDDDD) AS '业务员数',
(SELECT COUNT(*) 'tempcol'
FROM (SELECT yy_code, slsman, visit_date
FROM crm_visit
WHERE 1 = 1 AND CONVERT(char(10), visit_date, 111) >= '2007/01/21' AND
CONVERT(char(10), visit_date, 111) <= '2007/02/20' AND
yy_code = A.yy_code AND visit_date NOT IN
(SELECT riqi
FROM PUNCH..AM13
WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20') AND
slsman IN
(SELECT slsman
FROM pls..yingye_slsman
WHERE haspart = 0)
GROUP BY yy_code, slsman, visit_date) ddddd) AS 实际反馈数
FROM crm_visit A
WHERE (1 = 1) AND (CONVERT(char(10), visit_date, 111) >= '2007/01/21') AND
(CONVERT(char(10), visit_date, 111) <= '2007/02/20') AND (visit_date NOT IN
(SELECT riqi
FROM PUNCH..AM13
WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20')) AND (slsman IN
(SELECT slsman
FROM pls..yingye_slsman
WHERE haspart = 0))
GROUP BY yy_code
ORDER BY yy_code
SELECT
visit_area = A.yy_code,
营业所 = RTRIM(B.description),
visit_count = COUNT(*),
visit_days = DATEDIFF(Day,'2007/01/21','2007/02/20')+1 -(SELECT COUNT(*) FROM PUNCH..AM13 WHERE riqi >= '2007/01/21' AND riqi <= '2007/02/20') - 1,
业务员数 =
(
(SELECT COUNT(DISTINCT(slsman) FROM crm_visit WHERE 1=1
AND CONVERT(char(10), visit_date, 111) >= '2007/01/21'
AND CONVERT(char(10), visit_date, 111) <= '2007/02/20'
AND yy_code = A.yy_code
AND EXISTS(SELECT 1 FROM pls..yingye_slsman WHERE haspart = 0 AND slsman=crm_visit.slsman)
) ,
实际反馈数 =
(
SELECT count(DISTINCT yy_code, slsman, visit_date) FROM crm_visit WHERE 1=1
AND CONVERT(char(10), visit_date, 111) >= '2007/01/21'
AND CONVERT(char(10), visit_date, 111) <= '2007/02/20'
AND yy_code = A.yy_code
AND NOT EXISTS
(
SELECT 1 FROM PUNCH..AM13 AA INNER JOIN pls..yingye_slsman BB
ON
AA.slsman =BB.slsman
and AA.riqi=crm_visit.visit_date
AND AA.riqi >= '2007/01/21'
AND AA.riqi <= '2007/02/20'
AND BB.haspart = 0
)
)
FROM
crm_visit A LEFT OUTER JOIN pls..yingye_base B ON A.yy_code=B.yy_code
WHERE (1 = 1)
AND CONVERT(char(10), A.visit_date, 111) >= '2007/01/21'
AND CONVERT(char(10), A.visit_date, 111) <= '2007/02/20'
AND NOT EXISTS
(
SELECT 1 FROM PUNCH..AM13
WHERE riqi >= '2007/01/21'
AND riqi <= '2007/02/20'
AND riqi=A.visit_date
)
AND EXISTS
(SELECT 1 FROM pls..yingye_slsman WHERE haspart = 0 AND slsman=A.slsman)