偉特 CTI 會員檢視表

example.sql

-- 按照消費金額金額排序營養師妍竹(20131005)在活動A中一年內有購買小商品分類"激力小麥蛋白"(Code=103)的客戶。
SELECT * FROM View_CTI_Member
WHERE
 orderDate >= '20150105'
 AND goodsCategoryCode = '103'
 AND empId = '20131005'
ORDER BY orderSubTotal DESC

View_CTI_Member.sql

WITH TMP_CCS_OrderDetails AS ( -- 訂單以及商品相關資料
	SELECT 
		SUM(CCS_OrderDetails.SubTotal) AS orderSubTotal, -- 訂單小計總和
		POS_Member.SerNo AS memberSerNo, -- 會員編號(用來Group)
		MAX(CCS_OrderIndex.OrderDate) AS orderDate, -- 訂單時間(Group 後取最後一筆的時間)
		MAX(PIS_Goods.Code) goodsCode, -- 商品代碼
    MAX(PIS_GoodsLittleCategory.Name) AS goodsCategoryName, -- 商品小分類名稱
    MAX(PIS_GoodsLittleCategory.Code) AS goodsCategoryCode -- 商品小分類代碼
	FROM 
		CCS_OrderDetails 
		LEFT JOIN CCS_OrderIndex ON CCS_OrderIndex.SerNo = CCS_OrderDetails.IndexSerNo 
		LEFT JOIN POS_Member ON CCS_OrderIndex.MemberSerNo = POS_Member.SerNo
		LEFT JOIN PIS_Goods ON CCS_OrderDetails.GoodsSerNo = PIS_Goods.SerNo
		LEFT JOIN PIS_GoodsLittleCategory ON PIS_GoodsLittleCategory.SerNo = PIS_Goods.SmallCategorySerNo
	WHERE
		-- DOCMT000000000000000153376: CallCenter訂單, -- DOCMT000000000000000175784: 內部通路 Nutrimate官網
		CCS_OrderIndex.DocumentSerNo IN ('DOCMT000000000000000153376', 'DOCMT000000000000000175784') 
		AND CCS_OrderIndex.Status = 1 -- 狀態 1.正常 0資料未完整 -1取消訂單 -2停止出貨
		AND CCS_OrderDetails.SubTotal <> 0
	GROUP BY
		POS_Member.SerNo, 
		CCS_OrderIndex.MemberSerNo,
		PIS_Goods.Code,
    PIS_GoodsLittleCategory.Name,
    PIS_GoodsLittleCategory.Code
),

TMP_PIS_Member AS ( -- 會員相關資料
	SELECT     
		POS_Member.SerNo AS _memberSerNo, -- 會員編號
		POS_Member.Code AS memberId, -- 會員客代
		POS_Member.Status AS memberStatus, -- 會員卡狀態   (0 : 停用  1: 正常)
		POS_Member.Name AS memberName, -- 會員姓名
		CASE POS_Member.Sex WHEN '1' THEN '1' WHEN '0' THEN '2' ELSE ' ' END AS memberSex, -- 會員性別
		POS_Member.Birthday AS memberBirthday, -- 會員生日
    CASE WHEN POS_Member.Birthday IS NULL THEN 0 ELSE CONVERT(int,ROUND(DATEDIFF(hour,POS_Member.Birthday,GETDATE())/8766.0,0)) END AS memberAge, -- 會員年齡
		POS_Member.HomeTel AS memberHomeTel, -- 會員家裡電話
		POS_Member.OfficeTel AS memberOfficeTel, -- 會員公司電話
		POS_Member.CellPhone AS memberMobil, -- 會員手機
		(SELECT TOP 1 BonusAfter FROM DCS_BonusLog WHERE MemberSerNoStr = POS_Member.SerNo ORDER BY BonusLogSerNo DESC) AS memberBonus, -- 會員現有紅利
		POS_Member.E_Mail AS memberEmail, -- 會員Email
		CCS_ShoppingBehaviorBrief.FirstTraxDate AS shoppingFirstbuyDate, -- 首購日 
		CCS_ShoppingBehaviorBrief.BusinessDate AS shoppintManageDate,  -- 會員經營日
		CCS_ShoppingBehaviorBrief.LastConsumeDate AS shoppintLastConsumeDate, -- 最後消費日
		CCS_ShoppingBehaviorBrief.TotalConsume AS shoppingTotalConsume, -- 總消費金額(若為0 或 NULL 表示沒有購物紀錄)
		HRS_Employee.Code AS empId, -- 員工id
		HRS_Employee.name as empName -- 員工姓名
	FROM CCS_ShoppingBehaviorBrief 
		INNER JOIN POS_Member 
		INNER JOIN CCS_CRMFields 
			ON POS_Member.SerNo = CCS_CRMFields.MemberSerNoStr 
			ON CCS_ShoppingBehaviorBrief.MemberSerNoStr = POS_Member.SerNo 
		INNER JOIN dbo.HRS_Employee ON CCS_CRMFields.ExploitSerNoStr = HRS_Employee.SerNo 
  --WHERE POS_Member.Status = 1  -- (0 : 停用  1: 正常)
)

SELECT 
	TMP_CCS_OrderDetails.*,
	TMP_PIS_Member.*
FROM
	TMP_PIS_Member WITH (NOLOCK)
	LEFT JOIN TMP_CCS_OrderDetails ON TMP_CCS_OrderDetails.memberSerNo = TMP_PIS_Member._memberSerNo