SQL Command - JackHu88/Comm GitHub Wiki

Copy data

Insert into [RebatePaymentTestDB].[dbo].[RebateDetail]
 ([SequenceNo]
      ,[PDCreatedOn]
      ,[PDAuthor]
      ,[EffectiveDate]
)
 SELECT [SequenceNo],
	  [PDCreatedOn]
      ,[PDAuthor]
      ,[EffectiveDate]
 FROM [RebatePayment].[dbo].[RebateDetail]

payment status

select n.id, n.sequenceNo,n.rebateYear,n.rebatePeriod,n.customerNumber,n.status,n.divisionCode,h.remark,n.confirmDiffAmount,
SUM(ISNULL(CASE WHEN ISNUMERIC(d.totalAmount) = 0 THEN NULL ELSE CAST(d.totalAmount as decimal(18,2)) END,0)) as invoiceAmount
From [dbo].[csr_rebate_payment_notification] n 
left join (select *, ROW_NUMBER() OVER(PARTITION BY paymentId ORDER BY optDate DESC) AS rn FROM [dbo].[csr_rebate_payment_history]) h  
 on n.id = h.paymentId and h.optStatus=2 and h.rn = 1
left join csr_rebate_payment_accessories a  
on a.paymentId = n.id 
left join csr_rebate_invoice_recognize r 
on a.id = r.accessoryId and a.paymentId = r.paymentId 
left join csr_rebate_invoice_info d 
on r.id = d.recognizeId 
and r.accessoryId = d.accessoryId
Where (n.status != 4 or n.status is null) and a.status = 1 and a.type = 0 and r.status = 1 and d.latestRecognizeFlag = '1' and d.resultCode='200'
group by  n.id, n.sequenceNo,n.rebateYear,n.rebatePeriod,n.customerNumber,n.status,n.divisionCode,h.remark,n.confirmDiffAmount

invoice amount

select b.id, a.id as accessoryId, b.sequenceNo, b.rebateYear, b.customerNumber,
b.customerName, b.rebateAmount, b.prePDRNo, 
d.sellerName, d.sellerCode, d.purchaserName,d.purchaserCode,d.totalAmount, d.taxRate ,d.scarletLetterInvoiceNumber,d.companyCode
from csr_rebate_payment_accessories a 
left join csr_rebate_payment_notification b 
on a.paymentId = b.id
left join csr_rebate_invoice_recognize c
on a.id = c.accessoryId
and a.paymentId = c.paymentId
left join csr_rebate_invoice_info d
on c.id = d.recognizeId
and c.accessoryId = d.accessoryId
where a.status = 1
and a.type = 0
and c.status = 1
and d.latestRecognizeFlag = '1'  and d.resultCode='200'
order by b.id asc;

get order amount

SELECT STUFF((SELECT distinct ',' + [Order_UID] FROM [DW_APAC].[INT].[v_CustomerOrder_CN_Rebate_Payment_List_Check]
where (Customer_UID='15255' 
and Customer_PO LIKE'SD-003042%')
or (Customer_UID='3488' and Customer_PO LIKE'SD-003042%')
FOR XML PATH('')),1,1,'') AS Order_UID

get order amount

select abs(SUM(Order_Amount_PC)) as total from INT.v_CustomerOrder_CN_Rebate_Payment_List_Check 
where Order_Amount_PC < 0 and Order_UID in ('894778','894777')

GET ORDER NUMBER

SELECT STUFF((SELECT distinct ',' + [Order_UID] FROM [INT].[v_CustomerOrder_CN_Rebate_Payment_List_Check]
WHERE 
([Customer_UID]='15255' and [Customer_PO] like 'SD-003042EP%'  and Order_Type='C')
or 
([Head_Message1]='15255' and Customer_PO like 'SD-003042EP%'  and Order_Type='D')
FOR XML PATH('')),1,1,'') AS Order_UID

Filing

create table SP_Filing_Box(
	BoxID nvarchar(50) NOT NULL PRIMARY KEY,
	Capacity nvarchar(50),
	CartonID nvarchar(50),
	Created datetime,
	Modified datetime,
	CreatedBy nvarchar(50)
)

alter table dbo.SP_Filing_Output add RackID nvarchar(50)

ALTER TABLE dbo.SP_SKUHistory DROP COLUMN Special_Request

alter table dbo.DashBoard_TeamRoom_Migration ALTER COLUMN F_Status nvarchar(100) NULL 


INSERT INTO dbo.SP_Filing(ReportID,Location,Status,BoxID,Comment,Operator,Created,Modified)
VALUES('@ReportID'
    ,@Location
      ,'@Status'
      ,'@BoxID'
      ,'@Comment'
      ,'@Operator'
      ,GETDATE()
      ,GETDATE())      
      
Update dbo.SP_Filing
SET Status='@Status',BoxID='@BoxID',Comment='@Comment',Operator='@Operator',Modified=GETDATE()
WHERE ReportID='@ReportID'


INSERT INTO dbo.SP_Filing_Box(BoxID,Capacity,Created,Modified,CreatedBy)
VALUES('@BoxID'
      ,'@Capacity'
      ,GETDATE()
      ,GETDATE(),
      '@CreatedBy')
      
Update dbo.SP_Filing_Box
SET CartonID='@CartonID',Modified=GETDATE()
WHERE BoxID='@BoxID'


INSERT INTO dbo.SP_Filing_Carton(CartonID,Capacity,Location,Created,Modified)
VALUES('@CartonID'
      ,'10'
      ,'@Location'
      ,GETDATE()
      ,GETDATE())
      
Update dbo.SP_Filing_Carton
SET RackID='@RackID',Modified=GETDATE()
WHERE CartonID='@CartonID'


SELECT TOP 10000 Filing.ReportID
      ,Filing.ReportKey
      ,Filing.ReportName
      ,Filing.EmployeeID
      ,Filing.EmployeeName
      ,Filing.CompanyCodeName
      ,Filing.CompanyCode
 ,Filing.Location
      ,Filing.Status
      ,Filing.Operator
      ,Filing.Location
      ,Filing.BoxID     
      ,ISNULL(Box.CartonID,'') AS CartonID
      ,ISNULL(Carton.RackID,'') AS RackID
  FROM ([Eform_Data_Output].[dbo].[SP_Filing] as Filing
  LEFT JOIN [Eform_Data_Output].[dbo].[SP_Filing_Box] as Box
  ON Filing.BoxID=Box.BoxID)
  LEFT JOIN [Eform_Data_Output].[dbo].[SP_Filing_Carton] as Carton
  ON Box.CartonID=Carton.CartonID
 WHERE (Filing.Operator = '@Operator' or '' = '@Operator') and (Filing.Status = '@Status' or '' = '@Status') and (Filing.Created >= '@StartDate' or '' = '@StartDate') and (Filing.Created <= '@EndDate' or '' = '@EndDate')