# MYSQL
###### tags: `working note` `sql`


[各式各樣的GETDATE()時間格式轉換CONVERT](https://www.dotblogs.com.tw/kevinya/2014/09/05/146474)

###### 日銷售

SELECT DISTINCT table7.Shop_Name  as 'packageStore',table6.Brand_Name as 'packageBrand',table8.Name as 'salePerson',table9.Name as 'sharePerson',RIGHT(CONVERT(varchar, table2.Start_Time, 111),5) as 'packageStartDate', RIGHT(CONVERT(varchar, table2.End_Time, 111),5) as'packageEndDate',RIGHT(CONVERT(varchar, table1.Create_Time, 111),5) as 'orderDate',table1.Order_ID as 'orderId',table1.Package_ID as'packageId',table2.Name as 'packageName',CONCAT(table3.Product_Name,'x',table3.QTY) as 'packageItem',table2.Amount as 'packagePrice',table1.QTY as 'orderQantity', table4.Total_Amount  as 'orderAmount',CASE
            WHEN table1.Delivery = '1'
               THEN N'宅配'
               ELSE N'自取'
       END as 'deliveryType' , table1.Receiver as 'orderReceiver',table1.Create_Time,table2.Start_Time,table2.End_Time,table2.Shoppe_ID
FROM [Regular_Sec].[dbo].[rcum_order] AS table1
JOIN Regular_Sec.dbo.rcum_product_package AS table2 ON table1.Package_ID = table2.Package_ID
JOIN  Regular_Sec.dbo.rcum_package_detail AS table3 ON table3.Package_ID = table1.Package_ID
JOIN  Regular_Sec.dbo.rcum_order_pay AS table4 ON table4.Order_ID = table1.Order_ID
JOIN  Regular_Sec.dbo.rcum_user_event_log AS table5 ON (table5.Trace_ID = (SELECT Trace_ID FROM  Regular_Sec.dbo.rcum_user_event_log AS event1 WHERE (event1.Point_Key = table1.Order_ID AND event1.Code='10007') AND (table5.Code='10002' OR table5.code='10001') ))
JOIN  Regular_Sec.dbo.rcum_brand AS table6 ON table6.Brand_ID = table2.Brand_ID
JOIN  Regular_Sec.dbo.rcum_shop AS table7 ON table2.Shoppe_ID LIKE CONCAT('%',table7.Store_ID,'%') 
JOIN  Regular_Sec.dbo.member AS table8 ON table2.Create_ID = table8.Member_ID 
JOIN  Regular_Sec.dbo.member AS table9 ON  table5.User_ID collate Chinese_Taiwan_Stroke_CI_AS  = table9.Member_ID 
WHERE (RIGHT(CONVERT(varchar, table1.Create_Time, 111),5) = '06/29') AND (table2.Shoppe_ID LIKE CONCAT('SK00003', '%'))
ORDER BY table2.Shoppe_ID ASC