declare @trannumber INT =4784 declare @trandate DATE = '2018-02-03' declare @registerid INT = 40 SELECT TOP (1) t.TranDate , t.TranNumber , CASE t.OperatMode WHEN 1 THEN 'true' ELSE 'false' END AS 'TrainingModeFlag' , t.AccountNo AS 'CustomerID' , t.SubAccount AS 'SubKey' , t.StoreNumber AS 'RetailStoreID' , t.RegisterID AS 'WorkstationID' , t.RegisterID AS 'TillID' , t.SoldToAdd1 AS 'Address' , --may be calculate field t.SoldToAdd2 , t.SoldToCity AS 'City' , t.SoldToState AS 'State' , t.SoldToZip AS 'PostalCode' , 'USA' AS 'CountryCode' , --calculate field t.SoldBusName AS 'Name' , t.ShipToAdd1 , t.ShipToAdd2 , --needed for neighborhood tag in Delivery method t.ShipToCity , t.ShipToState , t.ShipToZip , t.ShipBusName , '' AS 'CustomerType' , 'true' AS 'BusinessCustomerFlag' , t.PhoneNumber AS 'Telephone' , t.TranNumber AS 'SequenceNumber' , CONVERT(DATETIME, t.TranDate, 0) + CONVERT(DATETIME, t.StartTime, 0) AS 'BeginDateTime' , CONVERT(DATETIME, t.TranDate, 0) + CONVERT(DATETIME, t.EndTime, 0) AS 'EndDateTime' , CONVERT(DATE, t.TranDate, 0) AS 'BusinessDayDate' , CONVERT(DATE, t.SystemDate, 0) AS 'CreateDate' , t.EmployeeNo AS 'OperatorID' , t.InvLines AS 'TransactionCount' , t.TxnTotal AS 'TranTotal' , t.TaxAmount AS 'TaxAmount' , t.TypeOfSale AS 'TranTotalTypeOfSale' , t.InvoiceNo , t.PostVoidTran , t.CostOfGoods --------------------------------------------------------------------------------------------------------------------------------------------------------- , t.LoyaltyCard --------------------------------------------------------------------------------------------------------------------------------------------------------- , t.RecType --------------------------------------------------------------------------------------------------------------------------------------------------------- , t.ReferenceNo__3 --------------------------------------------------------------------------------------------------------------------------------------------------------- , t.DocNumber AS 'TranTotalDocNumber' , t.RegisterID AS 'TranTotalRegisterID' , t.ShipMidInit AS 'TranTotalShipMidInit' , t.RecType AS 'TranTotalRecType' --, ISNULL(NULLIF(LTRIM(RTRIM(t.ShipFirstName + ' ' + ShipLastName)),''),' ') AS 'NameDP' , t.SystemDate AS 'TranTotalSystemDate' , t.TranNumber as 'TranTotalTranNumberTest' , t.TranDate as 'TranTotalTranDateTest' , t.RegisterID as 'TranTotalRegisterIDTest' , t.AccountNo AS 'TranTotalAccountNoAccountNumber' INTO #ARTSSaleDemandTest FROM dbo.TranTotal t WHERE t.TranNumber = @trannumber AND t.TranDate = @trandate AND t.RegisterID = @registerid select 'TranTotal', * from dbo.TranTotal t WHERE t.TranNumber = @trannumber AND t.TranDate = @trandate AND t.RegisterID = @registerid SELECT TOP (1) REPLICATE('0',11-LEN(RTRIM(h.CustomerID))) + RTRIM(h.CustomerID) AS 'CustomerID' , h.TrainingModeFlag , h.RetailStoreID , h.WorkstationID , h.TillID , h.SequenceNumber , h.BeginDateTime , h.EndDateTime , h.BusinessDayDate , h.OperatorID , oh.CreateDate , oh.LastMntDate , h.TransactionCount , tto.RecType , tto.TypeOfSale , tto.PostVoidTOS , tto.PostVoidTran , tto.AppliedDep , od.ExtendedAmt , od.SKU , od.SKUDesc , od.OrderStatus , oh.OrderTotal , od.ClassNo , od.DeptNo , oh.OrderType , oh.CopySource , oh.EmployeeNo , oh.OutsideSlpn , tdet.PickTake , h.TranNumber , h.TranDate , tt.TenderCode , tt.MagStripe , tt.CardExpire , td.MiscNumber , tt.CreditAuthCode , oh.PrintedTotal , h.SubKey , h.Address , tto.SoldToAdd2 , h.City , h.State , h.PostalCode , h.CountryCode , h.Name , tto.PONumber , tto.ShipToAdd1 , tto.ShipToAdd2 , tto.ShipToCity , tto.ShipToState , tto.ShipToZip , tto.ShipBusName , tto.InvoiceNo , h.CustomerType , h.BusinessCustomerFlag , h.Telephone , h.TranTotal , h.TaxAmount , tto.Deposits , oh.FRegTaxTotal , oh.RegTaxTotal , oh.OrderNumber , tto.CostOfGoods --INTO #ARTSCreditTransDemand FROM #ARTSSaleDemandTest h LEFT JOIN dbo.Customer c ON h.CustomerID = c.CustomerID LEFT JOIN dbo.TranTender tt ON h.TranNumber = tt.TranNumber and h.TranDate = tt.TranDate and h.Tillid = tt.RegisterId LEFT JOIN dbo.TranTendData td ON tt.TranNumber = td.TranNumber and td.TranDate = tt.TranDate and tt.RegisterId = td.RegisterId AND td.SigSequence = tt.SigSequence INNER JOIN dbo.TranTotal tto ON tto.TranNumber = h.TranNumber and tto.TranDate = h.TranDate and tto.RegisterId = h.Tillid LEFT JOIN dbo.TranDetail tdet ON h.TranNumber = tdet.TranNumber and td.TranDate = tdet.TranDate and h.Tillid = tdet.RegisterId LEFT JOIN dbo.OrderHeader oh ON (tdet.DocNumber = oh.OrderNumber and tdet.TypeOfSale NOT IN (34,36,44,46)) OR (tto.DocNumber = oh.OrderNumber and tto.TypeOfSale IN (34,36,44,46)) LEFT JOIN dbo.OrderDetail od ON oh.OrderNumber = od.OrderNumber LEFT JOIN dbo.TranTendData dat ON dat.RegisterID = tt.RegisterID AND dat.TranNumber = tt.TranNumber AND dat.TranDate = tt.TranDate AND dat.SigSequence = tt.SigSequence AND ((dat.[Sequence] = 2 AND -- MSI Tender 3 len(dat.MiscNumber) = 9 AND dat.MiscNumber NOT LIKE '%001') OR (dat.[Sequence] = 3 AND -- MSI Tender 4 len(dat.MiscNumber) = 9 AND dat.MiscNumber NOT LIKE '%001')) WHERE (c.Type = 'D' AND c.CustTypeNo NOT IN (10, 11)) OR -- Clientes AR que no son de Credinomina (c.CustTypeNo IN (10,11)) OR -- Clientes de Credinomina (tt.TenderCode IN (3,4,18, 19) AND SUBSTRING(td.MiscNumber,0,6) NOT IN ('600973', '854860', '881105') AND dat.MiscNumber IS NOT NULL) OR (SUBSTRING(td.MiscNumber,0,6) IN ('600973', '854860', '881105', '520021')) OR (tto.TypeOfSale = 52) OR -- Pago a Tarjeta de Credito (tto.TypeOfSale = 88) OR -- Post Voids (tto.TypeOfSale IN (34, 35, 36) AND oh.OrderType = 'S') OR -- Ordenes Especiales AR (tto.TypeOfSale IN (44, 45, 46) AND oh.OrderType = 'S' AND oh.CopySource = 'A') OR -- Apartados (tdet.MiscAcct = 6) -- Accesorios Financieros ORDER BY h.SequenceNumber ASC select * from #ARTSSaleDemandTest drop table #ARTSSaleDemandTest