SELECT dbo.ComFlow.ComFlowCode, dbo.ComFlow.ComCode, dbo.ComFlow.CustCode, dbo.ComFlow.DepartCode, dbo.ComFlow.SaleCode, dbo.ComFlow.EmpCode, dbo.ComFlow.Quantity * dbo.Commodity.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy, dbo.ComFlow.FlowDate) + '-' + DATENAME(mm, dbo.ComFlow.FlowDate) + '-' + DATENAME(dd, dbo.ComFlow.FlowDate)) AS FlowDate, dbo.ComFlow.SalType, dbo.Employee.DepartCode AS DepartIn, dbo.Sale.DepartCode AS DepartOut, dbo.ComFlow.Quantity * dbo.Commodity.TradePrice * dbo.Commodity.Discount / 100 AS Total, dbo.Department.GrpCode AS GrpCodeIn, Department1.GrpCode AS GrpCodeOutFROM dbo.ComFlow INNER JOIN dbo.Customer ON dbo.ComFlow.SalType IN (N'促銷', N'流向退貨', N'多級流向') AND dbo.ComFlow.CustCode = dbo.Customer.CustCode INNER JOIN dbo.CustomerRelation ON dbo.ComFlow.ComCode = dbo.CustomerRelation.ComCode AND dbo.CustomerRelation.CustCode = dbo.Customer.CustCode INNER JOIN dbo.Employee ON dbo.CustomerRelation.EmpCode = dbo.Employee.EmpCode INNER JOIN dbo.Sale ON dbo.ComFlow.SaleCode = dbo.Sale.SaleCode INNER JOIN dbo.Department ON dbo.Department.DepartCode = dbo.Employee.DepartCode INNER JOIN dbo.Department AS Department1 ON Department1.DepartCode = dbo.Sale.DepartCode AND dbo.Department.GrpCode <> Department1.GrpCode INNER JOIN dbo.Commodity ON dbo.ComFlow.ComCode = dbo.Commodity.ComCodeWHERE (NOT (dbo.ComFlow.SalType = N'流向退貨')) OR (NOT (dbo.Customer.Type = N'醫藥公司'))UNION ALLSELECT ComFlow_1.ComFlowCode, ComFlow_1.ComCode, ComFlow_1.CustCode, ComFlow_1.DepartCode, ComFlow_1.SaleCode, ComFlow_1.EmpCode, ComFlow_1.Quantity * Commodity_1.ConvertRate AS Quantity, CONVERT(datetime, DATENAME(yyyy, ComFlow_1.FlowDate) + '-' + DATENAME(mm, ComFlow_1.FlowDate) + '-' + DATENAME(dd, ComFlow_1.FlowDate)) AS FlowDate, ComFlow_1.SalType, Employee_1.DepartCode AS DepartIn, Sale_1.DepartCode AS DepartOut, ComFlow_1.Quantity * Commodity_1.TradePrice * Commodity_1.Discount / 100 AS Total, Department_1.GrpCode AS GrpCodeIn, Department1.GrpCode AS GrpCodeOutFROM dbo.ComFlow AS ComFlow_1 INNER JOIN dbo.Customer AS Customer_1 ON ComFlow_1.SalType IN (N'自然流向', N'自然流向退貨') AND ComFlow_1.OutCustCode = Customer_1.CustCode INNER JOIN dbo.CustomerRelation AS CustomerRelation_1 ON ComFlow_1.ComCode = CustomerRelation_1.ComCode AND CustomerRelation_1.CustCode = Customer_1.CustCode INNER JOIN dbo.Employee AS Employee_1 ON CustomerRelation_1.EmpCode = Employee_1.EmpCode INNER JOIN dbo.Sale AS Sale_1 ON ComFlow_1.SaleCode = Sale_1.SaleCode INNER JOIN dbo.Department AS Department_1 ON Department_1.DepartCode = Employee_1.DepartCode INNER JOIN dbo.Department AS Department1 ON Department1.DepartCode = Sale_1.DepartCode AND Department_1.GrpCode <> Department1.GrpCode INNER JOIN dbo.Commodity AS Commodity_1 ON ComFlow_1.ComCode = Commodity_1.ComCodeWHERE (NOT (ComFlow_1.SalType = N'流向退貨')) OR (NOT (Customer_1.Type = N'醫藥公司')) |
