导数据

1.皮老师总办数据

SELECT
    b.NamePath,
    YEAR (A .QSDate) AS '签约年份',
    -- sum(a.BldArea) as '签约面积'
    --sum(a.HtTotal - a.AjTotal) as '按揭首付'
    --sum(a.HtTotal) as '合同总价'
    SUM (A .AjTotal) AS '按揭总价' --sum(a.BldArea) as '签约面积'
    --count(a.RoomGUID)
FROM
    s_Contract A
JOIN myBusinessUnit b ON A .BUGUID = b.BUGUID
WHERE
    1 = 1
AND A .Status = '激活'
AND YEAR (A .QSDate) > '2013'
AND YEAR (A .QSDate) < '2016'
AND A .PayformType != '一次性付款'
AND AjTotal < A .HtTotal
GROUP BY
    b.NamePath,
    YEAR (A .QSDate)
ORDER BY
    b.NamePath,
    YEAR (A .QSDate);

SELECT
    b.NamePath,
    CONVERT (VARCHAR(7), A .QSDate, 120) AS '签约月份',
    SUM (A .HtTotal - A .AjTotal) AS '合同金额' --sum(a.BldArea) as '签约面积'
    -- count(a.RoomGUID)
FROM
    s_Contract A
JOIN myBusinessUnit b ON A .BUGUID = b.BUGUID
WHERE
    1 = 1
AND A .Status = '激活'
AND YEAR (A .QSDate) > '2015'
AND YEAR (A .QSDate) < '2018'
AND A .PayformType != '一次性付款'
GROUP BY
    b.NamePath,
    CONVERT (VARCHAR(7), A .QSDate, 120)
ORDER BY
    b.NamePath,
    CONVERT (VARCHAR(7), A .QSDate, 120);

 

2.黎森上海数据

-- 地产
SELECT * FROM p_Project where BUGUID = 'AF86FA9E-65DF-47F6-9C35-C3CD17ED8869'  and ProjName like '%朗润%';

select b.ProjName,count(a.RoomGUID) from p_Room a join p_Project b on a.ProjGUID = b.ProjGUID
where ProjCode in ('SHAREA_SHGS.SHCSHY',
'SHAREA_SHGS.SHCSHY.SHJFC',
'SHAREA_SHGS.SHCSHY.SHCH2',
'SHAREA_SHGS.SHCSHY.SHCH1',
'SHAREA_SHGS.SHCSHY.SHCH1S',
'SHAREA_SHGS.SHCHXY.XDDQ',
'SHAREA_SHGS.SHCHXY.1QSP',
'SHAREA_SHGS.SHCHXY.1QCW',
'SHAREA_SHGS.SHCHXY.XDXQ',
'SHAREA_SHGS.SHCHXY.WKQBG',
'SHAREA_SHGS.SHCHXY',
'SHAREA_SHGS.SHCHXY.SHCHXY1',
'SHAREA_SHGS.SHCHXY.XDXQZY',
'SHAREA_SHGS.SHLRY.SHLRY1',
'SHAREA_SHGS.SHLRY',
'SHAREA_SHGS.SHLRY.LRYSP',
'SHAREA_SHGS.SHLRY.LRYCK')
GROUP BY b.ProjName

-- 物业


SELECT * from ECIF_PROJECT a where a.CREATE_SYSTEM_CD = 03
and a.CITY_CD = 310000
and a.PROJECT_NAME like '%城市花园%' 

--城市花园 ：8D25CB8CA2BB45CF8C65BE112D8877B9
--城花新园：7226C8F9FE7F4CB297FECB2D9DDA3AC3
--上海朗润园(朗润园)：D28DF0D4AC6F4C39A48A4E01C97465E0;
select d.PROJECT_NAME,e.CODE_DESC,count(a.ECIF_CUST_ID) from ECIF_INDIVIDUAL a join ECIF_CUSTROOMRELA b on a.ECIF_CUST_ID = b.ECIF_CUST_ID
join ECIF_ROOM c on  b.room_id = c.SRC_ROOM_ID
join ECIF_PROJECT d on c.project_id = d.SRC_PROJECT_ID
join ECIF_CODEDESC e on e.CODE_VALUE = b.CUST_ROOM_RELA_CD
where d.SRC_PROJECT_ID in ('8D25CB8CA2BB45CF8C65BE112D8877B9',
'7226C8F9FE7F4CB297FECB2D9DDA3AC3',
'D28DF0D4AC6F4C39A48A4E01C97465E0')
GROUP BY d.PROJECT_NAME,e.CODE_DESC
ORDER BY d.PROJECT_NAME,e.CODE_DESC;

 3-----调整

-- 月份
SELECT
	CONVERT (VARCHAR(7), a.QSDate, 120) AS '签约月份',
	-- sum(a.BldArea) as '签约面积'
	SUM (a.HtTotal - a.AjTotal) AS '按揭首付',
	--sum(a.HtTotal) as '合同总价',
	SUM (a.AjTotal) AS '按揭总价' --um(a.BldArea) as '签约面积',
	--count(a.RoomGUID)
FROM
	s_Contract a
JOIN myBusinessUnit b ON a.BUGUID = b.BUGUID
WHERE
	1 = 1
AND a.Status = '激活'
AND --year(a.QSDate)  > '2015'
--CONVERT(varchar(7), a.QSDate, 120) > '2016-11' and
CONVERT (VARCHAR(7), a.QSDate, 120) > '2012-12'
AND a.PayformType != '一次性付款'
AND AjTotal < a.HtTotal
AND a.HtTotal < 100000000
GROUP BY
	CONVERT (VARCHAR(7), a.QSDate, 120)
ORDER BY
	CONVERT (VARCHAR(7), a.QSDate, 120);


--城市
SELECT
	b.NamePath,
	a.BUGUID,
	CONVERT (VARCHAR(7), a.QSDate, 120) AS '签约月份',
	--sum(a.BldArea) as '签约面积'
	--sum(a.HtTotal - a.AjTotal) as '按揭首付'
	--sum(a.HtTotal) as '合同总价'
	--sum(a.AjTotal) as '按揭总价'
	--sum(a.BldArea) as '签约面积'
	COUNT (a.RoomGUID)
FROM
	s_Contract a
JOIN myBusinessUnit b ON a.BUGUID = b.BUGUID
WHERE
	1 = 1
AND a.Status = '激活'
AND --year(a.QSDate)  > '2015'
--CONVERT(varchar(7), a.QSDate, 120) > '2016-11' and
CONVERT (VARCHAR(7), a.QSDate, 120) IN ('2017-05')
AND a.PayformType = '一次性付款'
AND AjTotal < a.HtTotal
AND a.HtTotal < 100000000
GROUP BY
	a.BUGUID,
	b.NamePath,
	CONVERT (VARCHAR(7), a.QSDate, 120)
ORDER BY
	b.NamePath,
	CONVERT (VARCHAR(7), a.QSDate, 120);

4-集团汇总

-- 按月的到访量
select  CONVERT (VARCHAR(7), a.CreatonDate, 120) AS 'mon',count(OppGUID) from  s_CstReceive a
GROUP BY CONVERT (VARCHAR(7), a.CreatonDate, 120)
ORDER BY CONVERT (VARCHAR(7), a.CreatonDate, 120);
-- 按月的签约套数;面积;金额;
select  CONVERT (VARCHAR(7), b.QSDate, 120) AS 'mon',count(ContractGUID) as '套数',SUM(b.Total) as '总价',sum(BldArea) as '面积',
SUM(b.Total)/sum(BldArea) as '每平单价' from  s_Contract b
where 1=1 and 
b.Status = '激活'
--and CONVERT (VARCHAR(7), b.QSDate, 120) = '2017-03'
GROUP BY CONVERT (VARCHAR(7), b.QSDate, 120)
ORDER BY CONVERT (VARCHAR(7), b.QSDate, 120);
-- 按回款月份统计的回款周期
SELECT 
--T1.NamePath,
--t1.mon as '认购日期',
T1.endgetin as '回款完成月',
sum(T1.[date])/COUNT(T1.[date]) as  '回款周期',COUNT(T1.[date]) as '回款完成笔数',
sum(T1.hka) as '回款总金额'
FROM
(SELECT    bu1.NamePath AS NamePath,
p1.projname,a.TradeGUID,a.hkAmount as 'hka',
CONVERT (VARCHAR(7), c.QSDate, 120) AS 'mon',
(select MIN(CONVERT (VARCHAR(7), b.GetDate, 120)) from s_Getin b where b.SaleGUID = a.TradeGUID) as 'beggetin' ,
(select max(CONVERT (VARCHAR(7), b.GetDate, 120)) from s_Getin b where b.SaleGUID = a.TradeGUID) as 'endgetin' ,
 datediff( day,(select MIN(b.GetDate) from s_Getin b where b.SaleGUID = a.TradeGUID),(select max(b.GetDate) from s_Getin b where b.SaleGUID = a.TradeGUID)) as 'date'

from s_Trade a 
JOIN s_Order c on c.TradeGUID = a.TradeGUID
LEFT JOIN p_project p2 ON c.projguid = p2.projguid
LEFT JOIN p_project p1 ON p2.ParentCode = p1.ProjCode
LEFT JOIN myBusinessUnit bu1 ON bu1.buguid = p2.buguid
where 1=1 
and a.hkAmount = c.Total 
AND (select MIN(b.GetDate) from s_Getin b where b.SaleGUID = a.TradeGUID) !=
(select max(b.GetDate) from s_Getin b where b.SaleGUID = a.TradeGUID)
--AND bu1.BUGUID = 'AF86FA9E-65DF-47F6-9C35-C3CD17ED8869'
) T1
GROUP BY 
--T1.NamePath,
--t1.mon
T1.endgetin
ORDER BY 
T1.endgetin 
--sum(T1.[date])/COUNT(T1.[date])
--and a.JzAmount = c.Total
-- 按回款月份统计的回款金额
--