## 大魯閣常用SQL指令
1.大魯閣棒保會員共用，持有大魯閣應援/學生卡在保齡和棒壘都可以享有會員優惠。
2.持有多張卡別的顯示順序：Homereun > 應援(可能有多張，依卡號顯示) > 學生
資料庫：密碼taroko
localhost (主機，自用)
10.1.1.19 citrus會員(非即時，隔日早上6點前匯入)、taroko(POS即時消費資料)
10.1.1.17 CHEIFPOS-TRK致富寶會員
* 致富寶POS使用期間：2020.07.01~2023.04.30
* Cirtus POS使用期間：2023.05.01~
注意事項：
1. 要測試資料欄位是否是你所需，可使用以下指令，避免資料跑太久。
```
select top 1000*
```
2. ==嚴禁在localhost以外的資料庫、用select以外的語法==，避免動到資料庫資料。
3. 查詢結果可以直接全選複製到excel，並把格式調整成字串，手機號碼格式就不會跑掉了。
---
### Cirtus 資料 10.1.1.19
#### citrus.dbo.cardTbl 會員基本資料(2024/04/01後未更新)
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| --------- | ---------- | -------------------------- | ------------------------------------------------ |
| memid | 思圖會員id | "memid":"ST14491223" | |
| memberid | 思圖會員id | "memberid":"ST14491223" | |
| cardNo |會員卡號|"cardNo":"J190396057"|APP會員無卡號，以思圖id為主。J19開頭學生卡；A19開頭應援卡；A01開頭HOMERUN卡|
| ctype | 卡別 | "ctype":"APP" | APP、大魯閣應援卡、大魯閣學生卡、大魯閣HOMERUN卡 |
| activeYmd | 卡片啟用日 | "activeYmd":"20230709" | |
| checkYmd | 卡片到期日 | "checkYmd":"20991231" | |
| uName | 姓名 | "uName":"林琮凱" | |
| uSex | 性別 | "uSex":"X" | X：無法確認；F：女性；M：男性 |
| birthday | 生日 | "birthday":"20000101" | |
| mobile | 手機 | "mobile":"0981803774" | 唯一值，經過雙重驗證 |
| email | Email | "email":"123@yahoo.com.tw" | 未經過雙重驗證 |
| lastYmd | 最後消費日 | "lastYmd":"20240101" | |
| addr | 居住地 | "addr":"高雄市" | 城市名稱 |
| createYmd | 資料創建日 | "creatYmd":"20230905" | |
#### taroko.dbo.customer (即時會員資料)
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| -------- | -------- | --- | --------------------------------------------------------------- |
| CustNo | 卡號 |"CustNo":"ST00021723" |思圖卡號，只要有APP即會賦予|
| Name | 姓名 |"Name":"王大明" ||
| Birth | 生日 |"Birthday":"1984-11-09 00:00:00.000" |非必填|
| Gender | 性別 |"Gender":"M" |非必填。M男生；F女生；X未填|
| Mobile | 手機 |"Mobile":"0900000000" |唯一值|
| Email | 信箱 |"Email":"taroko.sports@gmail.com" |非必填|
#### citrus.dbo.cardCat 卡別名稱
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| -------- | -------- | --- | --------------------------------------------------------------- |
| cardCat | 卡別編號 |"cardCat":"A00302" | |
| cardName | 卡別名稱 |"cardName":"大魯閣應援卡" | A0301：大魯閣HOMERUN卡；A00302:大魯閣應援卡；A0303:大魯閣學生卡 |
#### taroko.dbo.Orders 交易訂單
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| --------- | ------------ | ------------------------------------- | ----------------- |
| StoreID | 店鋪編號 | "StoreID":"12103" | |
| OrderNO | 交易訂單編號 | "OrderNO":"P012023010100001" | 因為是流水號，訂單編號非唯一值，不同館會有同樣的編號 |
| OrderTime | 交易時間 | "OrderTime":"2023-01-01 10:54:29.000" | |
| SPrice | 交易金額 | "SPrice":"250" | 含稅金額 |
| IsDel | 交易是否取消 | "IsDel":"Y" | Y：取消；N:未取消 |
| PayType | 付款方式 | "PayType":"現金" | |
| CustNo|APP會員編號|"CustNo":"ST43353023"|思圖會員編號，只要有下載APP即會賦予一個|
| CardType| 會員卡別 | "CardType":"A0301" | A0301：大魯閣HOMERUN卡；A00302:大魯閣應援卡；A0303:大魯閣學生卡 |
| CardNo|會員編號| "CardNo":"ST03634424A0302" |ST卡號+A0302=有綁定數位付費會員卡；若只有A01、A19、J19開頭，代表尚未綁定|
| Qty | 交易數量 | "Qty":"6" | 明細項目數量 |
| CName | 姓名 | "CName":"王大明" | |
| Tel | 電話 | "Tel":"0900000000" | 唯一值 |
#### taroko.dbo.OrdersItem 交易明細
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| --------- | ------------ | ------------------------------------- | ---- |
| StoreID | 店鋪編號 | "StoreID":"12103" | |
| OrderNO | 交易訂單編號 | "OrderNO":"P012023010100001" | |
| Oty | 項目數量 |"Oty":"1"| |
| SPrice | 交易金額 | "SPrice":"250"|含稅金額|
| Gname|商品名稱|"Gname":"平日2H門票"| |
#### taroko.dbo.Store 店鋪資料
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| --------- | ------------ | ------------------------------------- | ---- |
| StoreID |店鋪編號| "StoreID":"12103"| |
| StoreName |店鋪名稱 | "StoreName":"新時代館" | 基創為12500~12599之間|
#### taroko.dbo.Goods 商品資料
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| ---------- | ------------ | -------------------------------------- | -------------------- |
| GKID | | | |
| GID | 商品代號 | "GID":"010501000372" | |
| GName | 商品名稱 | "GName":"學生代幣15枚" | |
| Price | ? | | *看起來不是商品價錢* |
| IsVaild | 商品是否有效 | "IsVaild":"Y" | |
| GType | 商品類別?| |*不知道意義*|
| UpdateDate | 更新日期 | "UpdateDate":"2023-04-06 14:16:44.630" ||
#### taroko.dbo.OrderPayway 訂單支付方式
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| -------- | ------------ | ---------------------------- | ---- |
| StoreID | 店鋪編號 | "StoreID":"12103" | |
|OrderNO |交易訂單編號 |"OrderNO":"P012023010100001" | |
| Paydesc | 支付方式 | "Paydesc":"台新刷卡離線" |常用：現金、信用卡、電子支付、動滋券 |
#### taroko.dbo.InvoiceOrders 訂單發票
| 欄位名稱 | 欄位意義 | 說明 | 備註 |
| --------- | ------------ | ------------------------------------- | ------------------------------------ |
| StoreID | 店鋪編號 | "StoreID":"12103" | |
| INVWord | 發票開頭 | "INVWord":"AK" | |
| INVNo | 發票號碼 | "INVNo":"28704041" | |
| OrderNO | 交易訂單編號 | "OrderNO":"P012023010100001" | |
| OrderTime | 交易時間 | "OrderTime":"2023-01-01 10:54:29.000" | |
| Status | 交易狀態 | "Status":"Y" | Y為正常交易，N為取消或換貨 |
| Payamt | 交易金額 | "Payamt":"500" | |
| DelReason | 取消原因 | "DelReason":"客戶換貨" |客戶取消、客戶抱怨、客戶換貨、退貨 |
| Paydesc | 支付方式 | "Paydesc":"台新刷卡離線" | 常用：現金、信用卡、電子支付、動滋券 |
---
### 撈取目前有效會員資料
目的：可針對未過期會員投遞簡訊。
```
SELECT c.mobile, s.StoreName, o.StoreID, o.OrderTime, o.CustNo, o.SPrice, o.IsDel, c.cardNo,c.uSex, c.ctype, c.uName,c.birthday,DATEDIFF(year,TRY_CAST(c.birthday AS DATE),GETDATE())AS agediff,c.activeYmd,c.checkYmd,c.lastYmd
FROM taroko.dbo.Orders o
JOIN taroko.dbo.Store s ON o.StoreID=s.StoreID
JOIN citrus.dbo.cardTbl c ON o.CustNo=c.memberid
WHERE convert(varchar(10), o.OrderTime, 112 ) between '20230501' and '20240409' ###選取期間
AND len(o.CustNo) !=0 and o.IsDel='N'
AND o.StoreID between 12500 and 12599
AND DATEDIFF(day,GETDATE(),TRY_CAST(c.checkYmd AS DATE)) >=0 ###篩選未過期會員
AND s.StoreName = '中華館' ###篩館別
```
### 撈取訂單資料
目的：撈取會員消費紀錄
```
SELECT DISTINCT c.mobile,o.OrderNO,o.OrderTime,o.SPrice,o.CardType,o.StoreID,s.StoreName,CC.cardName
FROM taroko.dbo.Orders o
JOIN citrus.dbo.cardTbl c on o.CustNo = c.memberid
JOIN taroko.dbo.Store s on o.StoreID = s.StoreID
JOIN citrus.dbo.cardCat CC on o.CardType = CC.cardCat
WHERE convert(varchar(10), o.OrderTime, 112 ) between '20230601' and '20230630'
AND len(o.CustNo) !=0 and o.IsDel='N'
AND o.StoreID between 12500 and 12599
```
### 計算購買品項
目的：計算特定時段內，消費者購買某品項的總數量
```
SELECT Gname, SPrice, COUNT(Gname) AS TotalCount, SUM(SPrice) AS TotalSum
FROM taroko.dbo.OrdersItem
WHERE CONVERT(varchar(10), CreateDate, 112) BETWEEN '20230801' AND '20230831'
AND IsDel = 'N'
AND StoreID BETWEEN 12500 AND 12599
GROUP BY Gname, SPrice;
```
```
SELECT
oi.Gname,
COUNT(*) AS SaleCount
FROM
taroko.dbo.Orders o
JOIN
taroko.dbo.OrdersItem oi ON o.StoreID = oi.StoreID AND o.OrderNO = oi.OrderNO
WHERE
oi.Gname IN ('113年年中慶套票3組', '113年年中慶套票2組', '巨城-113年年中慶套票3組', '113年年中慶電子套票3組', '113年年中慶電子套票2組', '會員30枚', '會員80枚', '學生代幣15枚','學生代幣15枚(虛擬代幣)','會員30枚(虛擬代幣)')
AND o.CardType = 'A0303'
AND CONVERT(varchar(10),OrderTime,112) between '20240701' AND '20240722'
AND o.IsDel = 'N'
GROUP BY
oi.Gname;
```
### 計算平均客單價
```
SELECT
AVG(SPrice) AS avgprice
FROM
taroko.dbo.Orders o
WHERE
o.CardType = 'A0303'
AND CONVERT(varchar(10),OrderTime,112) between '20240701' AND '20240722'
AND o.IsDel = 'N'
AND SPrice != 0
```
### 青春動滋券使用會員
目的：撈取使用會員名單、卡別、金額
```
SELECT StoreName,OrderTime,SPrice,CardType,c.Name,c.Mobile
FROM taroko.dbo.Orders o
JOIN taroko.dbo.Store s ON s.StoreID=o.StoreID
JOIN taroko.dbo.Customer c ON c.Mobile=o.Tel
JOIN taroko.dbo.OrdersPayway op ON op.StoreID=o.StoreID AND op.OrderNO = o.OrderNO
WHERE o.StoreID between 12500 and 12599
AND CONVERT(varchar(10),OrderTime,112) between '20240101' and '20240714'
AND o.IsDel = 'N'
AND op.Paydesc = '動滋券'
AND CardType IN ('A0301','A0302','A0303')
```
### 滿額發票數量
目的：mapping消費滿額會員名單，做簡訊提醒用
```
SELECT *
FROM taroko.dbo.InvoiceOrders io
JOIN taroko.dbo.Orders o ON o.OrderNO = io.OrderNO AND o.StoreID = io.StoreID
WHERE io.StoreID between 12500 and 12599
AND io.Status ='Y'
AND Payamt >= 500 #輸發票金額
AND CONVERT(varchar(10),io.OrderTime,112) between '20240701' and '20240820' #發票日期區間
AND o.CardType in ('A0301','A0302','A0303'); #卡別
```
Sign in with Wallet
Connect another wallet