daily pastebin goal
85%
SHARE
TWEET

Untitled

a guest Feb 13th, 2018 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT d.INVOICE_NO, d.part_no, d.PART_ORDER_QTY, d.PART_SHIPPED_QTY, d.INV_ALLOC_ORIG_DOC, d.INV_ALLOC_BR_QTY_HIST, d.INV_ALLOC_QTY_HIST, pc.PopCode, pm.SOURCE_CODE, d.BSIT_STORE, I.CUST_NO, C.Sales_type
  2.   FROM [BSIT_ERA_RAW_DATA].[dbo].[INVOICE_ERA_LINE_DTLS] D
  3.   LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[INVOICE] I ON D.INVOICE_NO = I.INVOICE_NO
  4.   LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[PROD_MASTER] PM ON D.PART_NO = PM.PARTS_MASTER_KEY AND PM.BSIT_STORE = 'STORE01'
  5.   LEFT JOIN [MICQIF_PROD].[dbo].[PopCodes] PC ON right(d.PART_NO,len(d.part_no)-2) COLLATE SQL_Latin1_General_CP1_CI_AS = PC.ItemNo COLLATE SQL_Latin1_General_CP1_CI_AS
  6.   LEFT JOIN [BSIT_ERA_RAW_DATA].[dbo].[CUSTOMER] C ON I.CUST_NO = C.CUSTOMER_ID AND C.BSIT_STORE = 'STORE01'
  7.   WHERE D.PART_ISSUE_DATE >= CONVERT(DATE,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1 , 0))
  8.   AND D.PART_ISSUE_DATE <= CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)
  9.   AND I.INVOICE_CLOSED_DATE <= CONVERT(varchar(10), DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) , -1), 120)
  10.  
  11.   AND D.INV_ALLOC_ORIG_DOC IS NULL
  12.   AND D.INVOICE_NO NOT LIKE 'CM%'
  13.   AND D.PART_NO IS NOT NULL
  14.   and d.part_no like 'cp%'
  15.   and INVOICE_VOID_DATE IS NULL
  16.   AND I.CUST_NO NOT IN ('90102','90103','90104','90105','90201','90203','90204','90205','90401','90402','90403','90405','90501','90502','90503','90504','90301','90302','90304','90305')
  17.    
  18. CONVERT(DATE,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1 , 0))
RAW Paste Data
Top