I have all theses below tables. i want to merge all the tables (Increment_Sales, Increment_Returns,Incremental_Non_Etp_Sales,Incremental_Non_Etp_Returns,)
I want other tables(Increment_Receiving_temp1, Incremental_Receiving_Non_Etp,Increment_Receiving_temp2,Incremental_Receiving_Non_Etp_2) to be separated from following above tables (Increment_Sales, Increment_Returns,Incremental_Non_Etp_Sales,Incremental_Non_Etp_Returns,)
Load
Warehouse as SAPCode,
InvoiceNumber as [inv no],
Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceQuantity as quantity_sold,
SalesPrice as [sales price],
LocalAmount as [inv value],
if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
VatAmount as [tax amount];
SQL SELECT *
FROM ETPEAS.dbo.CashOrderTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;
Increment_Returns:
LOAD
Warehouse as SAPCode,
SalesReturnNumber as [return no],
Date#(CreateDate, 'YYYYMMDD') as Sold_Date,
month(Date#(CreateDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(CreateDate, 'YYYYMMDD')) as Sold_Year,
replace(ReturnItemNumber,' ','') as itemnumber,
left(ReturnItemNumber,3) as [Season Code],
ReturnQuantity as quantity_returns,
SalesPrice as [sales price],
-LocalAmount as [inv value],
if ( LocalAmount <500, '<500', if ( LocalAmount <800, '500-799',if ( LocalAmount <1200, '800-1199',if ( LocalAmount <1600,'1200-1599',if ( LocalAmount <2500,'1600-2499',if ( LocalAmount <3500,'2500-3499',if ( LocalAmount <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket];
SQL SELECT *
FROM ETPEAS.dbo.SalesReturnTrn
WHERE InvoiceType = '31' and (CreateDate >= 20130101 and CreateDate <= '$(currentdate)') ;
//store POS_Master_final into ;
Incremental_Non_Etp_Sales:
Load
Warehouse as SAPCode,
InvoiceNumber as [inv no],
Date#(SalesDate, 'YYYYMMDD') as Sold_Date,
month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
//'N_ETP SALE' as SaleType,
SaleQuantity as quantity_sold,
NetValue as [inv value],
MRPValue as [sales price],
if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
TaxAmount as [tax amount];
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId = WH.Warehouse
where SD.Doc_Type = 'ZIKE' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;
Incremental_Non_Etp_Returns:
Load
Warehouse as SAPCode,
InvoiceNumber as [return no],
Date#(SalesDate, 'YYYYMMDD') as Sold_Date,
month(Date#(SalesDate, 'YYYYMMDD')) as Sold_Month,
year(Date#(SalesDate, 'YYYYMMDD')) as Sold_Year,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
//'N_ETP SALE' as SaleType,
SaleQuantity as quantity_returns,
-NetValue as [inv value],
MRPValue as [sales price],
if ( NetValue <500, '<500', if ( NetValue <800, '500-799',if ( NetValue <1200, '800-1199',if ( NetValue <1600,'1200-1599',if ( NetValue <2500,'1600-2499',if ( NetValue <3500,'2500-3499',if ( NetValue <5000,'3500-4999','>=5000'))))))) as [Inv val Bucket],
-TaxAmount as [tax amount];
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity, SD.NetValue,SD.MRPValue,SD.TaxAmount
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'ZIKR' and SD.SalesDate >= 20130101 and SD.SalesDate <= '$(currentdate)' ;
I want below tables (all) should be kept separate from above tables
Increment_Receiving_temp1:
LOAD
Warehouse as SAPCode,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
Date#(CurrTransactionDate, 'YYYYMMDD') as Received_Date,
ReceivingNumber as [rec number],
ReceivedQuantity as quantity_received;
SQL SELECT *
FROM ETPEAS.dbo.DOLine
WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;
Incremental_Receiving_Non_Etp:
Load
ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,
Date#(SalesDate, 'YYYYMMDD') as Received_Date,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceNumber as [rec number],
SaleQuantity as quantity_received
;
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'GR' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101;
Increment_Receiving_temp2:
LOAD
Warehouse as ETPCode,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
Date#(CurrTransactionDate, 'YYYYMMDD') as Received_Date,
-ReceivedQuantity as quantity_received;
SQL SELECT *
FROM ETPEAS.dbo.DOLine
WHERE(CurrTransactionDate >= 20130101 and CurrTransactionDate <= '$(currentdate)') ;
Incremental_Receiving_Non_Etp_2:
Load
ApplyMap('SAPCODE_ETPCodeMapping',NCode,'') as ETPCode,
Date#(SalesDate, 'YYYYMMDD') as Received_Date,
replace(ItemNumber,' ','') as itemnumber,
left(ItemNumber,3) as [Season Code],
InvoiceNumber as [rec number],
-SaleQuantity as quantity_received
;
SQl Select WH.GoodsSender as NCode, SD.InvoiceNumber, SD.SalesDate,SD.ItemNumber,
SD.SaleQuantity
FROM SAP_Link.dbo.Sales_DataNonETP_BIReports SD
INNER JOIN ETPEAS.dbo.warehouse_sap_bireport WH
ON SD.CustomerId=WH.Warehouse
where SD.Doc_Type = 'STN' and SD.SalesDate <= '$(currentdate)' and SD.SalesDate >= 20130101;
