我有这个脚本
USE [KevinMayhewLive]
GO
/****** Object: View [dbo].[KM_SALES_DATA] Script Date: 06/27/2017 10:23:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[KM_SALES_DATA]
--Natika Edward 28/03/17
--Pull Sales Order Data
AS
SELECT
"SL_ACCOUNTS"."CUCODE" AS [Customer Code],
"ORD_DETAIL"."OD_ORDER_NUMBER" AS [Order Number],
CONVERT(VARCHAR(10),"ORD_DETAIL"."OD_DATE_PUTIN",103) AS [Date of
Purchase],
"ORD_DETAIL"."OD_STOCK_CODE" AS [Item],
"ORD_DETAIL"."OD_QTYORD" AS [Quantity Ordered],
"ORD_DETAIL"."OD_UNITCST" AS [Unit Cost],
"ORD_DETAIL"."OD_GROSS" AS [C_AMT_SALES],
"ORD_HEADER"."OH_USER2" AS [Order Origin],
"CRM_CAMPAIGN"."CMP_CODE" AS [Campaign Code]
FROM (("KevinMayhewLive"."dbo"."ORD_DETAIL" "ORD_DETAIL"
INNER JOIN "KevinMayhewLive"."dbo"."ORD_HEADER" "ORD_HEADER" ON
"ORD_DETAIL"."OD_ORDER_NUMBER"="ORD_HEADER"."OH_ORDER_NUMBER")
INNER JOIN "KevinMayhewLive"."dbo"."CRM_CAMPAIGN" "CRM_CAMPAIGN" ON
"ORD_HEADER"."OH_CAMPAIGN_LINK"="CRM_CAMPAIGN"."CMP_PRIMARY")
INNER JOIN "KevinMayhewLive"."dbo"."SL_ACCOUNTS" "SL_ACCOUNTS" ON
"ORD_HEADER"."OH_ACCOUNT"="SL_ACCOUNTS"."CUCODE"
--where CONVERT(VARCHAR(10),"ORD_DETAIL"."OD_DATE_PUTIN",103) =
CONVERT(VARCHAR(10),GETDATE(),103) --take out hyphens to run report and
bring back daily figures.
要提取销售订单数据,它还包括没有股票代码的邮资和文本行,我需要排除这些没有代码的行......有任何想法吗?
放在WHERE "ORD_DETAIL"."OD_STOCK_CODE IS NOT NULL OR "ORD_DETAIL"."OD_STOCK_CODE" <> ''
代码的末尾。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句