SQL 2016 need help with a select statement to check totals for an invoice

bstewartmcskms · July 25, 2018, 2:04pm

This is my code:
select
'ARBH' as RecordType,
' ' as ARCo,
Cast(dateadd(day,
-datepart(day, ar.Activity_Date) + 1, ar.Activity_Date) as smalldatetime)
as Mth, --- need to transform to 1st day of month
' ' as BatchID,
' ' as BatchSeq,
' ' as ARTrans,
' ' as ARSource,
'ARBH' as TransType,
case
when ar.Activity_Type = 'Invoice'
then 'I'
else
case
when ar.Activity_Type = 'Cash receipt'
then 'P'
else
case
when ar.Activity_Type = 'Adjustment'
then 'A'
else 'R' -- Release Retainage
end
end
end as ARTransType,
' ' as CustGroup,
ar.Customer as Customer, -- Will need to be translated
'NET 30' as PayTerms, -- Defaulted per the business
case
when len(ar.Invoice) > 10
then substring(ar.Invoice,(Len(ar.Invoice) -10), 10)
else substring(ar.Invoice,1,10)
end as ARInvoice, -- Will need to decide on invoices over 10 char - answered in 7/24 meeting
ar.Description as ARDescription, -- Will need to decide on invoices over 30 char
ar.Reference as CustRef,
'1' as RecType, -- Defaulted per Ben F.
' ' as JCCo,
ar.Contract as ARContract, -- Will need to be translated
Cast(ar.Activity_Date as smalldatetime) as TransDate,
Cast(ar.Due_Date as smalldatetime) as DueDate,
' ' as DiscDate,
Cast(ar.Activity_Date as smalldatetime) as AppliedMth,
' ' as AppliedTrans,
' ' as ReasonCode,
' ' as Notes,
' ' as RecID

from dbo.ARM_MASTER__CUSTOMER c

join dbo.ARA_ACTIVITY__ACTIVITY ar on ar.Customer = c.Customer
join dbo.JCM_MASTER__JOB j on ar.Job = j.Job

Where substring(ar.Customer,1,2) = '50'
and c.Status = 'Active'

or
substring(ar.Customer,1,2) = '50'
and j.Status <> 'Closed'

The activity table has multiple entries per invoice. In my where statement I need to add a test for the sum of all records for a single invoice to not be 0 if it's not on an open job. I'm still pretty new to SQL. Any help would be very much appreciated.

Thanks!

bitsmed · July 25, 2018, 2:57pm

Please provide:

table definitions in the form of create statements
representative sample data in the form of insert statements
expected result from the sample data you provide
name and version of your database engine

bstewartmcskms · July 25, 2018, 3:28pm

SQL Server 2017 database

CREATE TABLE [dbo].[ARA_ACTIVITY__STATUS](
[_Id] [uniqueidentifier] NOT NULL,
[_RowVersion] [timestamp] NOT NULL,
[Customer] nvarchar NULL,
[Status_Type] nvarchar NULL,
[Status_Date] datetime2 NULL,
[Status_Seq] [int] NULL,
[Status] nvarchar NULL,
[Invoice] nvarchar NULL,
[Draw] nvarchar NULL,
[Contract] nvarchar NULL,
[Contract_Item] nvarchar NULL,
[Job] nvarchar NULL,
[Extra] nvarchar NULL,
[Cost_Code] nvarchar NULL,
[Due_Date] datetime2 NULL,
[Amount] [decimal](28, 10) NULL,
[Finance_Charge] [decimal](28, 10) NULL,
[Cash_Receipt] [decimal](28, 10) NULL,
[Adjustment] [decimal](28, 10) NULL,
[Retainage_Held] [decimal](28, 10) NULL,
[Retainage_Billed] [decimal](28, 10) NULL,
[Reserved] [decimal](28, 10) NULL,
[Outstanding_Amount] [decimal](28, 10) NULL,
[Outstanding_Finance_Charge_Amount] [decimal](28, 10) NULL,
[BL_Invoice] [bit] NULL,
[BL_Invoice_Changed] [bit] NULL,
[SM_Invoice] [bit] NULL,
[Generation_Date] datetime2 NULL,
[Generation_Seq] [int] NULL,
[Operator_Stamp] nvarchar NULL,
[Date_Stamp] datetime2 NULL,
[Time_Stamp] time NULL,
[Row_ID] nvarchar NULL,
[Row_Version] nvarchar NULL,
[Status_Notes] nvarchar NULL,
[Status_File_Links] nvarchar NULL,
[_AttachmentsCount] [int] NOT NULL,
CONSTRAINT [PK:dbo.ARA_ACTIVITY__STATUS] PRIMARY KEY

CREATE TABLE [dbo].[JCM_MASTER__JOB](
[_Id] [uniqueidentifier] NOT NULL,
[_RowVersion] [timestamp] NOT NULL,
[Job] nvarchar NULL,
[Description] nvarchar NULL,
[Address_1] nvarchar NULL,
[Address_2] nvarchar NULL,
[City] nvarchar NULL,
[State] nvarchar NULL,
[ZIP_Code] nvarchar NULL,
[Type] nvarchar NULL,
[Size] [bigint] NULL,
[Unit_Description] nvarchar NULL,
[Security_ID] nvarchar NULL,
[Site_Phone] nvarchar NULL,
[Fax_Phone] nvarchar NULL,
[Misc_Phone] nvarchar NULL,
[Project_Manager] nvarchar NULL,
[Misc_Title_1] nvarchar NULL,
[Misc_Title_2] nvarchar NULL,
[Misc_Title_3] nvarchar NULL,
[Misc_Title_4] nvarchar NULL,
[Project_Manager_Email] nvarchar NULL,
[Superintendent_Email] nvarchar NULL,
[User_Def_Key_1] nvarchar NULL,
[User_Def_Key_2] nvarchar NULL,
[Status] nvarchar NULL,
[Use_PJ_Change_Management] [bit] NULL,
[Extras] nvarchar NULL,
[Estimated_Start_Date] datetime2 NULL,
[Estimated_Comp_Date] datetime2 NULL,
[Revised_Start_Date] datetime2 NULL,
[Revised_Comp_Date] datetime2 NULL,
[Actual_Start_Date] datetime2 NULL,
[Actual_Complete_Date] datetime2 NULL,
[Last_Cost_Update] datetime2 NULL,
[Checklist_1] [bit] NULL,
[Checklist_2] [bit] NULL,
[Checklist_3] [bit] NULL,
[Checklist_4] [bit] NULL,
[Checklist_5] [bit] NULL,
[Checklist_6] [bit] NULL,
[Checklist_7] [bit] NULL,
[Checklist_8] [bit] NULL,
[Checklist_9] [bit] NULL,
[Checklist_10] [bit] NULL,
[Checklist_11] [bit] NULL,
[Checklist_12] [bit] NULL,
[AR_Customer] nvarchar NULL,
[Job_Tax_Group] nvarchar NULL,
[Billing_Method] nvarchar NULL,
[Billing_Level] nvarchar NULL,
[Revenue_Account] nvarchar NULL,
[Retainage_Percent] [decimal](28, 10) NULL,
[Exempt_from_Finance_Charge] [bit] NULL,
[Finance_Charge_Type] nvarchar NULL,
[Finance_Charge_Percentage] [decimal](28, 10) NULL,
[Finance_Charge_Flat_Rate] [decimal](28, 10) NULL,
[Scope_of_Work] nvarchar NULL,
[Daily_Entry_Control] nvarchar NULL,
[Workers_Comp_Group] nvarchar NULL,
[Burden_Percent] [decimal](28, 10) NULL,
[Work_State] nvarchar NULL,
[Work_Local] nvarchar NULL,
[Default_Revenue_Code] nvarchar NULL,
[Certified_Project] nvarchar NULL,
[On_Hold] [bit] NULL,
[Authorization] nvarchar NULL,
[Bank_Account] nvarchar NULL,
[Labor_Tax_Group] nvarchar NULL,
[Material_Tax_Group] nvarchar NULL,
[Subcontract_Tax_Group] nvarchar NULL,
[Equipment_Tax_Group] nvarchar NULL,
[Overhead_Tax_Group] nvarchar NULL,
[Other_Tax_Group] nvarchar NULL,
[Cost_Account_Group] nvarchar NULL,
[Cost_Account_Prefix] nvarchar NULL,
[Cost_Account] nvarchar NULL,
[Cost_of_Sales_Account] nvarchar NULL,
[Full_Revenue_Account] nvarchar NULL,
[Revenue_Recog_Method] nvarchar NULL,
[Produce_Vendor_Type_1_Lien_Waiver] [bit] NULL,
[Use_Vendor_Type_1_Commitment_LW_Amount] [bit] NULL,
[Min_Vendor_Type_1_Commitment_LW_Amount] [decimal](28, 10) NULL,
[Min_Vendor_Type_1_LW_Amount] [decimal](28, 10) NULL,
[Req_Vendor_Type_1_Certified_Rpts] [bit] NULL,
[Req_Vendor_Type_1_Master_Approval] [bit] NULL,
[Produce_Vendor_Type_2_Lien_Waiver] [bit] NULL,
[Use_Vendor_Type_2_Commitment_LW_Amount] [bit] NULL,
[Min_Vendor_Type_2_Commitment_LW_Amount] [decimal](28, 10) NULL,
[Min_Vendor_Type_2_LW_Amount] [decimal](28, 10) NULL,
[Req_Vendor_Type_2_Certified_Rpts] [bit] NULL,
[Req_Vendor_Type_2_Master_Approval] [bit] NULL,
[Produce_Vendor_Type_3_Lien_Waiver] [bit] NULL,
[Use_Vendor_Type_3_Commitment_LW_Amount] [bit] NULL,
[Min_Vendor_Type_3_Commitment_LW_Amount] [decimal](28, 10) NULL,
[Min_Vendor_Type_3_LW_Amount] [decimal](28, 10) NULL,
[Req_Vendor_Type_3_Certified_Rpts] [bit] NULL,
[Req_Vendor_Type_3_Master_Approval] [bit] NULL,
[Produce_Vendor_Type_4_Lien_Waiver] [bit] NULL,
[Use_Vendor_Type_4_Commitment_LW_Amount] [bit] NULL,
[Min_Vendor_Type_4_Commitment_LW_Amount] [decimal](28, 10) NULL,
[Min_Vendor_Type_4_LW_Amount] [decimal](28, 10) NULL,
[Req_Vendor_Type_4_Certified_Rpts] [bit] NULL,
[Req_Vendor_Type_4_Master_Approval] [bit] NULL,
[Last_Day_of_Month] [tinyint] NULL,
[LW_Conditional_Thru_Date] nvarchar NULL,
[LW_Uncond_Thru_Date] nvarchar NULL,
[Jurisdiction] nvarchar NULL,
[Cert_Rpt_Week_Ending_Day] nvarchar NULL,
[Master_Cmplnc_Tolerance] [decimal](28, 10) NULL,
[Customer] nvarchar NULL,
[Customer_Name] nvarchar NULL,
[Customer_Address_1] nvarchar NULL,
[Customer_Address_2] nvarchar NULL,
[Customer_City] nvarchar NULL,
[Customer_State] nvarchar NULL,
[Customer_ZIP_Code] nvarchar NULL,
[Customer_Contact_1] nvarchar NULL,
[Customer_Phone_1] nvarchar NULL,
[Customer_FAX] nvarchar NULL,
[Bill_to_Address_1] nvarchar NULL,
[Bill_to_Address_2] nvarchar NULL,
[Bill_to_city] nvarchar NULL,
[Bill_to_state] nvarchar NULL,
[Bill_to_ZIP_Code] nvarchar NULL,
[Customer_Contact_2] nvarchar NULL,
[Customer_Phone_2] nvarchar NULL,
[Owner_Contract_ID] nvarchar NULL,
[Contract_Date] datetime2 NULL,
[Contract_Retainage_Percent] [decimal](28, 10) NULL,
[Contract_Type] nvarchar NULL,
[Original_Contract_Amount] [decimal](28, 10) NULL,
[Potential_CO_Contract_Changes] [decimal](28, 10) NULL,
[CO_Request_Contract_Changes] [decimal](28, 10) NULL,
[Verbal_Okay_Contract_Changes] [decimal](28, 10) NULL,
[JTD_Aprvd_Contract_Chgs] [decimal](28, 10) NULL,
[Revised_Contract_Amount] [decimal](28, 10) NULL,
[YTD_Aprvd_Contract_Chgs] [decimal](28, 10) NULL,
[QTD_Aprvd_Contract_Chgs] [decimal](28, 10) NULL,
[MTD_Aprvd_Contract_Chgs] [decimal](28, 10) NULL,
[LM_Aprvd_Contract_Chgs] [decimal](28, 10) NULL,
[NM_Aprvd_Contract_Chgs] [decimal](28, 10) NULL,
[JTD_Work_Billed] [decimal](28, 10) NULL,
[YTD_Work_Billed] [decimal](28, 10) NULL,
[QTD_Work_Billed] [decimal](28, 10) NULL,
[MTD_Work_Billed] [decimal](28, 10) NULL,
[LM_Work_Billed] [decimal](28, 10) NULL,
[NM_Work_Billed] [decimal](28, 10) NULL,
[JTD_Retainage_Held] [decimal](28, 10) NULL,
[YTD_Retainage_Held] [decimal](28, 10) NULL,
[QTD_Retainage_Held] [decimal](28, 10) NULL,
[MTD_Retainage_Held] [decimal](28, 10) NULL,
[LM_Retainage_Held] [decimal](28, 10) NULL,
[NM_Retainage_Held] [decimal](28, 10) NULL,
[JTD_Payments] [decimal](28, 10) NULL,
[YTD_Payments] [decimal](28, 10) NULL,
[QTD_Payments] [decimal](28, 10) NULL,
[MTD_Payments] [decimal](28, 10) NULL,
[LM_Payments] [decimal](28, 10) NULL,
[NM_Payments] [decimal](28, 10) NULL,
[JTD_Adjustment] [decimal](28, 10) NULL,
[YTD_Adjustment] [decimal](28, 10) NULL,
[QTD_Adjustment] [decimal](28, 10) NULL,
[MTD_Adjustment] [decimal](28, 10) NULL,
[LM_Adjustment] [decimal](28, 10) NULL,
[NM_Adjustment] [decimal](28, 10) NULL,
[Receivable_Balance] [decimal](28, 10) NULL,
[Last_Draw] nvarchar NULL,
[Orig_Estimate_Finalized] [bit] NULL,
[Original_Estimate] [decimal](28, 10) NULL,
[Pending_Estimate_Changes] [decimal](28, 10) NULL,
[JTD_Aprvd_Estimate_Chgs] [decimal](28, 10) NULL,
[Total_Estimate] [decimal](28, 10) NULL,
[YTD_Aprvd_Estimate_Chgs] [decimal](28, 10) NULL,
[QTD_Aprvd_Estimate_Chgs] [decimal](28, 10) NULL,
[MTD_Aprvd_Estimate_Chgs] [decimal](28, 10) NULL,
[LM_Aprvd_Estimate_Chgs] [decimal](28, 10) NULL,
[NM_Aprvd_Estimate_Chgs] [decimal](28, 10) NULL,
[Original_Commitment] [decimal](28, 10) NULL,
[Pending_Commitment_Changes] [decimal](28, 10) NULL,
[Approved_Commitment_Changes] [decimal](28, 10) NULL,
[Revised_Commitment] [decimal](28, 10) NULL,
[Commitment_Invoiced] [decimal](28, 10) NULL,
[JTD_Cost] [decimal](28, 10) NULL,
[YTD_Cost] [decimal](28, 10) NULL,
[QTD_Cost] [decimal](28, 10) NULL,
[MTD_Cost] [decimal](28, 10) NULL,
[LM_Cost] [decimal](28, 10) NULL,
[NM_Cost] [decimal](28, 10) NULL,
[WTD_Cost] [decimal](28, 10) NULL,
[LW_Cost] [decimal](28, 10) NULL,
[Tax_on_JTD_Cost] [decimal](28, 10) NULL,
[Tax_on_YTD_Cost] [decimal](28, 10) NULL,
[Tax_on_QTD_Cost] [decimal](28, 10) NULL,
[Tax_on_MTD_Cost] [decimal](28, 10) NULL,
[Tax_on_LM_Cost] [decimal](28, 10) NULL,
[Tax_on_NM_Cost] [decimal](28, 10) NULL,
[Tax_on_WTD_Cost] [decimal](28, 10) NULL,
[Tax_on_LW_Cost] [decimal](28, 10) NULL,
[JTD_Dollars_Paid] [decimal](28, 10) NULL,
[YTD_Dollars_Paid] [decimal](28, 10) NULL,
[QTD_Dollars_Paid] [decimal](28, 10) NULL,
[MTD_Dollars_Paid] [decimal](28, 10) NULL,
[LM_Dollars_Paid] [decimal](28, 10) NULL,
[NM_Dollars_Paid] [decimal](28, 10) NULL,
[WTD_Dollars_Paid] [decimal](28, 10) NULL,
[LW_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_JTD_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_YTD_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_QTD_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_MTD_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_LM_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_NM_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_WTD_Dollars_Pd] [decimal](28, 10) NULL,
[Tax_on_LW_Dollars_Paid] [decimal](28, 10) NULL,
[Total_Tax_Amount] [decimal](28, 10) NULL,
[Total_Labor_Estimate] [decimal](28, 10) NULL,
[Total_Labor_Units_Est] [decimal](28, 10) NULL,
[Total_Material_Estimate] [decimal](28, 10) NULL,
[Total_Subcontract_Est] [decimal](28, 10) NULL,
[Total_Equipment_Estimate] [decimal](28, 10) NULL,
[Total_Equipment_Units_Est] [decimal](28, 10) NULL,
[Total_Overhead_Estimate] [decimal](28, 10) NULL,
[Total_Other_Estimate] [decimal](28, 10) NULL,
[Total_Labor_Commitment] [decimal](28, 10) NULL,
[Total_Material_Commitment] [decimal](28, 10) NULL,
[Total_Sub_Commitment] [decimal](28, 10) NULL,
[Total_Equipment_Commitment] [decimal](28, 10) NULL,
[Total_Overhead_Commitment] [decimal](28, 10) NULL,
[Total_Other_Commitment] [decimal](28, 10) NULL,
[Commitment_Invoiced__Labor] [decimal](28, 10) NULL,
[Commitment_Invoiced__Material] [decimal](28, 10) NULL,
[Commitment_Invoiced__Sub] [decimal](28, 10) NULL,
[Commitment_Invoiced__Equipment] [decimal](28, 10) NULL,
[Commitment_Invoiced__Overhead] [decimal](28, 10) NULL,
[Commitment_Invoiced__Other] [decimal](28, 10) NULL,
[JTD_Labor_Cost] [decimal](28, 10) NULL,
[JTD_Labor_Units] [decimal](28, 10) NULL,
[JTD_Material_Cost] [decimal](28, 10) NULL,
[JTD_Subcontract_Cost] [decimal](28, 10) NULL,
[JTD_Equipment_Cost] [decimal](28, 10) NULL,
[JTD_Equipment_Units] [decimal](28, 10) NULL,
[JTD_Overhead_Cost] [decimal](28, 10) NULL,
[JTD_Other_Cost] [decimal](28, 10) NULL,
[MTD_Labor_Cost] [decimal](28, 10) NULL,
[MTD_Labor_Units] [decimal](28, 10) NULL,
[MTD_Material_Cost] [decimal](28, 10) NULL,
[MTD_Subcontract_Cost] [decimal](28, 10) NULL,
[MTD_Equipment_Cost] [decimal](28, 10) NULL,
[MTD_Equipment_Units] [decimal](28, 10) NULL,
[MTD_Overhead_Cost] [decimal](28, 10) NULL,
[MTD_Other_Cost] [decimal](28, 10) NULL,
[LM_Labor_Cost] [decimal](28, 10) NULL,
[LM_Labor_Units] [decimal](28, 10) NULL,
[LM_Material_Cost] [decimal](28, 10) NULL,
[LM_Subcontract_Cost] [decimal](28, 10) NULL,
[LM_Equipment_Cost] [decimal](28, 10) NULL,
[LM_Equipment_Units] [decimal](28, 10) NULL,
[LM_Overhead_Cost] [decimal](28, 10) NULL,
[LM_Other_Cost] [decimal](28, 10) NULL,
[NM_Labor_Cost] [decimal](28, 10) NULL,
[NM_Labor_Units] [decimal](28, 10) NULL,
[NM_Material_Cost] [decimal](28, 10) NULL,
[NM_Subcontract_Cost] [decimal](28, 10) NULL,
[NM_Equipment_Cost] [decimal](28, 10) NULL,
[NM_Equipment_Units] [decimal](28, 10) NULL,
[NM_Overhead_Cost] [decimal](28, 10) NULL,
[NM_Other_Cost] [decimal](28, 10) NULL,
[WTD_Labor_Cost] [decimal](28, 10) NULL,
[WTD_Labor_Units] [decimal](28, 10) NULL,
[WTD_Equipment_Cost] [decimal](28, 10) NULL,
[WTD_Equipment_Units] [decimal](28, 10) NULL,
[JTD_Labor_Dollars_Paid] [decimal](28, 10) NULL,
[JTD_Material_Dollars_Paid] [decimal](28, 10) NULL,
[JTD_Sub_Dollars_Paid] [decimal](28, 10) NULL,
[JTD_Equipment_Dollars_Paid] [decimal](28, 10) NULL,
[JTD_Overhead_Dollars_Paid] [decimal](28, 10) NULL,
[JTD_Other_Dollars_Paid] [decimal](28, 10) NULL,
[Date_of_Last_Report] datetime2 NULL,
[Percent_Complete] [decimal](28, 10) NULL,
[Msc_Job_Amt_1] [decimal](28, 10) NULL,
[Msc_Job_Amt_2] [decimal](28, 10) NULL,
[Msc_Job_Amt_3] [decimal](28, 10) NULL,
[Stored_Material] [decimal](28, 10) NULL,
[Cost_At_Comp] [decimal](28, 10) NULL,
[Cost_To_Comp] [decimal](28, 10) NULL,
[Lab_Hrs_To_Comp] [decimal](28, 10) NULL,
[Msc_CC_Amt_5] [decimal](28, 10) NULL,
[Msc_CC_Amt_6] [decimal](28, 10) NULL,
[Msc_Cat_Amt_1] [decimal](28, 10) NULL,
[Msc_Cat_Amt_2] [decimal](28, 10) NULL,
[Msc_Cat_Amt_3] [decimal](28, 10) NULL,
[Msc_Cat_Amt_4] [decimal](28, 10) NULL,
[Custom_Total_1_Total] [decimal](28, 10) NULL,
[Custom_Total_2_Total] [decimal](28, 10) NULL,
[Custom_Total_3_Total] [decimal](28, 10) NULL,
[Custom_Total_4_Total] [decimal](28, 10) NULL,
[Custom_Total_5_Total] [decimal](28, 10) NULL,
[Custom_Total_6_Total] [decimal](28, 10) NULL,
[Last_Custom_Total_Update] datetime2 NULL,
[Extras_Orig_Contract_Amount] [decimal](28, 10) NULL,
[Extras_Potential_CO_Contract_Changes] [decimal](28, 10) NULL,
[Extras_CO_Request_Contract_Changes] [decimal](28, 10) NULL,
[Extras_Verbal_Okay_Contract_Changes] [decimal](28, 10) NULL,
[Extras_JTD_Aprvd_Cntrc_Chgs] [decimal](28, 10) NULL,
[Extras_Revised_Contract_Amt] [decimal](28, 10) NULL,
[Extras_YTD_Aprvd_Cntrc_Chgs] [decimal](28, 10) NULL,
[Extras_QTD_Aprvd_Cntrc_Chgs] [decimal](28, 10) NULL,
[Extras_MTD_Aprvd_Cntrc_Chgs] [decimal](28, 10) NULL,
[Extras_LM_Aprvd_Cntrc_Chgs] [decimal](28, 10) NULL,
[Extras_NM_Aprvd_Cntrc_Chgs] [decimal](28, 10) NULL,
[Extras_JTD_Work_Billed] [decimal](28, 10) NULL,
[Extras_YTD_Work_Billed] [decimal](28, 10) NULL,
[Extras_QTD_Work_Billed] [decimal](28, 10) NULL,
[Extras_MTD_Work_Billed] [decimal](28, 10) NULL,
[Extras_LM_Work_Billed] [decimal](28, 10) NULL,
[Extras_NM_Work_Billed] [decimal](28, 10) NULL,
[Extras_JTD_Retainage_Held] [decimal](28, 10) NULL,
[Extras_YTD_Retainage_Held] [decimal](28, 10) NULL,
[Extras_QTD_Retainage_Held] [decimal](28, 10) NULL,
[Extras_MTD_Retainage_Held] [decimal](28, 10) NULL,
[Extras_LM_Retainage_Held] [decimal](28, 10) NULL,
[Extras_NM_Retainage_Held] [decimal](28, 10) NULL,
[Extras_JTD_Payments] [decimal](28, 10) NULL,
[Extras_YTD_Payments] [decimal](28, 10) NULL,
[Extras_QTD_Payments] [decimal](28, 10) NULL,
[Extras_MTD_Payments] [decimal](28, 10) NULL,
[Extras_LM_Payments] [decimal](28, 10) NULL,
[Extras_NM_Payments] [decimal](28, 10) NULL,
[Extras_JTD_Adjustment] [decimal](28, 10) NULL,
[Extras_YTD_Adjustment] [decimal](28, 10) NULL,
[Extras_QTD_Adjustment] [decimal](28, 10) NULL,
[Extras_MTD_Adjustment] [decimal](28, 10) NULL,
[Extras_LM_Adjustment] [decimal](28, 10) NULL,
[Extras_NM_Adjustment] [decimal](28, 10) NULL,
[Extras_Billing_Balance] [decimal](28, 10) NULL,
[Extras_Original_Estimate] [decimal](28, 10) NULL,
[Extras_Pndng_Estimate_Chgs] [decimal](28, 10) NULL,
[Extras_JTD_Aprvd_Est_Chgs] [decimal](28, 10) NULL,
[Extras_Total_Estimate] [decimal](28, 10) NULL,
[Extras_YTD_Aprvd_Est_Chgs] [decimal](28, 10) NULL,
[Extras_QTD_Aprvd_Est_Chgs] [decimal](28, 10) NULL,
[Extras_MTD_Aprvd_Est_Chgs] [decimal](28, 10) NULL,
[Extras_LM_Aprvd_Est_Chgs] [decimal](28, 10) NULL,
[Extras_NM_Aprvd_Est_Chgs] [decimal](28, 10) NULL,
[Extra_Original_Commitment] [decimal](28, 10) NULL,
[Extra_Pending_Commitment_Changes] [decimal](28, 10) NULL,
[Extra_Approved_Commitment_Changes] [decimal](28, 10) NULL,
[Extra_Revised_Commitment] [decimal](28, 10) NULL,
[Extra_Commitment_Invoiced] [decimal](28, 10) NULL,
[Extras_JTD_Cost] [decimal](28, 10) NULL,
[Extras_YTD_Cost] [decimal](28, 10) NULL,
[Extras_QTD_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Cst] [decimal](28, 10) NULL,
[Extras_LM_Cost] [decimal](28, 10) NULL,
[Extras_NM_Cost] [decimal](28, 10) NULL,
[Extras_WTD_Cost] [decimal](28, 10) NULL,
[Extras_LW_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_JTD_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_YTD_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_QTD_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_MTD_Cst] [decimal](28, 10) NULL,
[Tax_on_Extras_LM_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_NM_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_WTD_Cost] [decimal](28, 10) NULL,
[Tax_on_Extras_LW_Cost] [decimal](28, 10) NULL,
[Extras_JTD_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_YTD_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_QTD_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_MTD_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_LM_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_NM_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_WTD_Dollars_Paid] [decimal](28, 10) NULL,
[Extras_LW_Dollars_Paid] [decimal](28, 10) NULL,
[Tax_on_Extras_JTD_Dollrs_Pd] [decimal](28, 10) NULL,
[Tax_on_Extras_YTD_Dollrs_Pd] [decimal](28, 10) NULL,
[Tax_on_Extras_QTD_Dollrs_Pd] [decimal](28, 10) NULL,
[Tax_on_Extras_MTD_Dollrs_Pd] [decimal](28, 10) NULL,
[Tax_on_Extras_LM_Dollrs_Pd] [decimal](28, 10) NULL,
[Tax_on_Extras_NM_Dollrs_Pd] [decimal](28, 10) NULL,
[Tax_on_Extras_WTD_DP] [decimal](28, 10) NULL,
[Tax_on_Extras_LW_DP] [decimal](28, 10) NULL,
[Extras_Total_Tax_Amount] [decimal](28, 10) NULL,
[Extras_Total_Labor_Estimate] [decimal](28, 10) NULL,
[Extras_Total_Labor_Unts_Est] [decimal](28, 10) NULL,
[Extras_Total_Material_Est] [decimal](28, 10) NULL,
[Extra_Total_Sub_Estimate] [decimal](28, 10) NULL,
[Extra_Total_Equipment_Estimate] [decimal](28, 10) NULL,
[Extra_Total_Equipment_Units_Est] [decimal](28, 10) NULL,
[Extra_Total_Overhead_Est] [decimal](28, 10) NULL,
[Extra_Total_Other_Estimate] [decimal](28, 10) NULL,
[Extra_Total_Labor_Commitment] [decimal](28, 10) NULL,
[Extra_Total_Mat_Commitment] [decimal](28, 10) NULL,
[Extra_Total_Sub_Commitment] [decimal](28, 10) NULL,
[Extra_Total_Equipment_Commitment] [decimal](28, 10) NULL,
[Extra_Total_Overhead_Commitment] [decimal](28, 10) NULL,
[Extra_Total_Other_Commitment] [decimal](28, 10) NULL,
[Extra_Commitment_Inv__Labor] [decimal](28, 10) NULL,
[Extra_Commitment_Inv__Material] [decimal](28, 10) NULL,
[Extra_Commitment_Inv__Sub] [decimal](28, 10) NULL,
[Extra_Commitment_Inv__Equipment] [decimal](28, 10) NULL,
[Extra_Commitment_Inv__Overhead] [decimal](28, 10) NULL,
[Extra_Commitment_Inv__Other] [decimal](28, 10) NULL,
[Extras_JTD_Labor_Cost] [decimal](28, 10) NULL,
[Extras_JTD_Labor_Units] [decimal](28, 10) NULL,
[Extras_JTD_Material_Cost] [decimal](28, 10) NULL,
[Extras_JTD_Sub_Cost] [decimal](28, 10) NULL,
[Extras_JTD_Equipment_Cost] [decimal](28, 10) NULL,
[Extras_JTD_Equipment_Units] [decimal](28, 10) NULL,
[Extras_JTD_Overhead_Cost] [decimal](28, 10) NULL,
[Extras_JTD_Other_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Labor_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Labor_Units] [decimal](28, 10) NULL,
[Extras_MTD_Material_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Sub_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Equipment_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Equipment_Units] [decimal](28, 10) NULL,
[Extras_MTD_Overhead_Cost] [decimal](28, 10) NULL,
[Extras_MTD_Other_Cost] [decimal](28, 10) NULL,
[Extras_LM_Labor_Cost] [decimal](28, 10) NULL,
[Extras_LM_Labor_Units] [decimal](28, 10) NULL,
[Extras_LM_Material_Cost] [decimal](28, 10) NULL,
[Extras_LM_Sub_Cost] [decimal](28, 10) NULL,
[Extras_LM_Equipment_Cost] [decimal](28, 10) NULL,
[Extras_LM_Equipment_Units] [decimal](28, 10) NULL,
[Extras_LM_Overhead_Cost] [decimal](28, 10) NULL,
[Extras_LM_Other_Cost] [decimal](28, 10) NULL,
[Extras_NM_Labor_Cost] [decimal](28, 10) NULL,
[Extras_NM_Labor_Units] [decimal](28, 10) NULL,
[Extras_NM_Material_Cost] [decimal](28, 10) NULL,
[Extras_NM_Sub_Cost] [decimal](28, 10) NULL,
[Extras_NM_Equipment_Cost] [decimal](28, 10) NULL,
[Extras_NM_Equipment_Units] [decimal](28, 10) NULL,
[Extras_NM_Overhead_Cost] [decimal](28, 10) NULL,
[Extras_NM_Other_Cost] [decimal](28, 10) NULL,
[Extras_WTD_Labor_Cost] [decimal](28, 10) NULL,
[Extras_WTD_Labor_Units] [decimal](28, 10) NULL,
[Extras_WTD_Equipment_Cost] [decimal](28, 10) NULL,
[Extras_WTD_Equipment_Units] [decimal](28, 10) NULL,
[Extras_JTD_Labor_Dllrs_Paid] [decimal](28, 10) NULL,
[Extras_JTD_Mat_Dllrs_Paid] [decimal](28, 10) NULL,
[Extras_JTD_Sub_Dllrs_Paid] [decimal](28, 10) NULL,
[Extras_JTD_Equipment_Dllrs_Paid] [decimal](28, 10) NULL,
[Extras_JTD_Ovrhd_Dllrs_Paid] [decimal](28, 10) NULL,
[Extras_JTD_Other_Dllrs_Paid] [decimal](28, 10) NULL,
[Msc_Extra_Amt_1] [decimal](28, 10) NULL,
[Msc_Extra_Amt_2] [decimal](28, 10) NULL,
[Msc_Extra_Amt_3] [decimal](28, 10) NULL,
[Extras_Stored_Material] [decimal](28, 10) NULL,
[Extras_Cost_At_Comp] [decimal](28, 10) NULL,
[Extras_Cost_To_Comp] [decimal](28, 10) NULL,
[Extras_Lab_Hrs_To_Comp] [decimal](28, 10) NULL,
[Extras_Msc_CC_Amt_5] [decimal](28, 10) NULL,
[Extras_Msc_CC_Amt_6] [decimal](28, 10) NULL,
[Extras_Msc_Cat_Amt_1] [decimal](28, 10) NULL,
[Extras_Msc_Cat_Amt_2] [decimal](28, 10) NULL,
[Extras_Msc_Cat_Amt_3] [decimal](28, 10) NULL,
[Extras_Msc_Cat_Amt_4] [decimal](28, 10) NULL,
[Extras_Custom_Total_1_Total] [decimal](28, 10) NULL,
[Extras_Custom_Total_2_Total] [decimal](28, 10) NULL,
[Extras_Custom_Total_3_Total] [decimal](28, 10) NULL,
[Extras_Custom_Total_4_Total] [decimal](28, 10) NULL,
[Extras_Custom_Total_5_Total] [decimal](28, 10) NULL,
[Extras_Custom_Total_6_Total] [decimal](28, 10) NULL,
[Operator_Stamp] nvarchar NULL,
[Date_Stamp] datetime2 NULL,
[Time_Stamp] time NULL,
[Row_ID] nvarchar NULL,
[Row_Version] nvarchar NULL,
[Job_Notes] nvarchar NULL,
[Job_File_Links] nvarchar NULL,
[_AttachmentsCount] [int] NOT NULL,
CONSTRAINT [PK:dbo.JCM_MASTER__JOB] PRIMARY KEY CLUSTERED

Sample output data - this invoice is being selected and shouldn't be. Right now I am using the export tool in SSMS to export to a flat file:
ARBH| |2014-01-01 00:00:00| | | | |ARBH|I| |5079001|NET 30|I-14022-01|7900 Interstate Bldg.||1| |RC50-14-022|2014-01-09 00:00:00|2014-01-09 00:00:00| |2014-01-09 00:00:00| | | | |1
ARBH| |2014-04-01 00:00:00| | | | |ARBH|P| |5079001|NET 30|I-14022-01|7900 Interstate||1| |RC50-14-022|2014-04-07 00:00:00|2014-01-09 00:00:00| |2014-04-07 00:00:00| | | | |2
ARBL| |2014-01-01 00:00:00| | | | |7900 Interstate Bldg.| | | | |35791.0000000000|0.00|0.00|0.00|.0000000000|0.00|0.00|0.00| |1| | | | | | | |2014-01-01 00:00:00| |.0000000000|I-14022-01|1
ARBL| |2014-04-01 00:00:00| | | | |7900 Interstate| | | | |-35791.0000000000|0.00|0.00|0.00|.0000000000|0.00|0.00|0.00| |1| | | | | | | |2014-04-01 00:00:00| |.0000000000|I-14022-01|2

The two detail items sum to 0

Sample input data:
_Id _RowVersion Customer Status_Type Status_Date Status_Seq Actvty_Seq Activity_Type Activity_Status Invoice Draw Invoice_Date Due_Date Activity_Date Cash_Receipt_Type Cash_Receipt Bank_Account Deposit_Date Deposit_Id Deposit_Item Adjustment_Activity Adjustment_Date Type_Adjusted Adjustment_TypeNumber Adjustment_Type Adjustment Contract Contract_Item Job Extra Cost_Code Reference Description Amount Finance_Charge_Amount Retainage_Held Retainage_Billed Reserved Operator_Stamp Date_Stamp Time_Stamp Row_ID Row_Version Activity_Notes Activity_File_Links _AttachmentsCount
2890BDD1-D1E6-E711-90F1-005056B86C05 0x000000000029B6F9 5079001 Invoice 2014-01-09 00:00:00.0000000 1 1 Invoice Posted I-14022-01 7900 Interstate 2014-01-09 00:00:00.0000000 2014-01-09 00:00:00.0000000 2014-01-09 00:00:00.0000000 Not Used NULL 0 1 NULL Not Used 999 Not Used RC50-14-022 1 RC-50-14022 7900 Interstate Bldg. 35791.0000000000 0.0000000000 0.0000000000 0.0000000000 0.0000000000 Ashley 2014-02-18 00:00:00.0000000 12:29:19.0000000 2890bdd1-d1e6-e711-90f1-005056b86c05 1513917451 NULL NULL 0
2990BDD1-D1E6-E711-90F1-005056B86C05 0x000000000029B6FA 5079001 Invoice 2014-01-09 00:00:00.0000000 1 2 Cash receipt Posted I-14022-01 7900 Interstate 2014-01-09 00:00:00.0000000 2014-01-09 00:00:00.0000000 2014-04-07 00:00:00.0000000 Invoice cash receipt 1015 SP/AP 2014-04-07 00:00:00.0000000 1 1 0 NULL Not Used 999 Not Used RC50-14-022 1 RC-50-14022 7900 Interstate -35791.0000000000 0.0000000000 0.0000000000 0.0000000000 0.0000000000 JOAN 2014-04-07 00:00:00.0000000 16:34:57.0000000 2990bdd1-d1e6-e711-90f1-005056b86c05 1513917451 NULL NULL 0

bitsmed · July 25, 2018, 4:52pm

Is table dbo.ARA_ACTIVITY__ACTIVITY in your query the same as dbo.ARA_ACTIVITY__STATUS from your create statement?
Missing table definition (create statement) for dbo.ARM_MASTER__CUSTOMER.

Please provide:

representative sample data for all three tables in the form of insert statements
expected result from the sample data you provide

bstewartmcskms · July 26, 2018, 2:54pm

Sorry I hit the wrong table. Be careful what you ask for Thank you for your help. I have to split this up into 2 replies - it's too long.

CREATE TABLE [dbo].[ARA_ACTIVITY__ACTIVITY](
[_Id] [uniqueidentifier] NOT NULL,
[_RowVersion] [timestamp] NOT NULL,
[Customer] nvarchar NULL,
[Status_Type] nvarchar NULL,
[Status_Date] datetime2 NULL,
[Status_Seq] [int] NULL,
[Actvty_Seq] [int] NULL,
[Activity_Type] nvarchar NULL,
[Activity_Status] nvarchar NULL,
[Invoice] nvarchar NULL,
[Draw] nvarchar NULL,
[Invoice_Date] datetime2 NULL,
[Due_Date] datetime2 NULL,
[Activity_Date] datetime2 NULL,
[Cash_Receipt_Type] nvarchar NULL,
[Cash_Receipt] nvarchar NULL,
[Bank_Account] nvarchar NULL,
[Deposit_Date] datetime2 NULL,
[Deposit_Id] nvarchar NULL,
[Deposit_Item] [int] NULL,
[Adjustment_Activity] [bit] NULL,
[Adjustment_Date] datetime2 NULL,
[Type_Adjusted] nvarchar NULL,
[Adjustment_TypeNumber] [int] NULL,
[Adjustment_Type] nvarchar NULL,
[Adjustment] nvarchar NULL,
[Contract] nvarchar NULL,
[Contract_Item] nvarchar NULL,
[Job] nvarchar NULL,
[Extra] nvarchar NULL,
[Cost_Code] nvarchar NULL,
[Reference] nvarchar NULL,
[Description] nvarchar NULL,
[Amount] [decimal](28, 10) NULL,
[Finance_Charge_Amount] [decimal](28, 10) NULL,
[Retainage_Held] [decimal](28, 10) NULL,
[Retainage_Billed] [decimal](28, 10) NULL,
[Reserved] [decimal](28, 10) NULL,
[Operator_Stamp] nvarchar NULL,
[Date_Stamp] datetime2 NULL,
[Time_Stamp] time NULL,
[Row_ID] nvarchar NULL,
[Row_Version] nvarchar NULL,
[Activity_Notes] nvarchar NULL,
[Activity_File_Links] nvarchar NULL,
[_AttachmentsCount] [int] NOT NULL,
CONSTRAINT [PK:dbo.ARA_ACTIVITY__ACTIVITY] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[ARM_MASTER__CUSTOMER](
[_Id] [uniqueidentifier] NOT NULL,
[_RowVersion] [timestamp] NOT NULL,
[Customer] nvarchar NULL,
[Name] nvarchar NULL,
[Customer_Type] nvarchar NULL,
[Trade] nvarchar NULL,
[Key_1] nvarchar NULL,
[Key_2] nvarchar NULL,
[Address_1] nvarchar NULL,
[Address_2] nvarchar NULL,
[Address_3] nvarchar NULL,
[Address_4] nvarchar NULL,
[City] nvarchar NULL,
[State] nvarchar NULL,
[ZIP_Code] nvarchar NULL,
[Telephone] nvarchar NULL,
[Fax] nvarchar NULL,
[Email_Address] nvarchar NULL,
[Date_Established] datetime2 NULL,
[Status] nvarchar NULL,
[GL_Prefix] nvarchar NULL,
[Days_Before_Due] [int] NULL,
[Send_Statement] [bit] NULL,
[PO_Required] [bit] NULL,
[Credit_Limit] [decimal](28, 10) NULL,
[Credit_Rating] nvarchar NULL,
[Contact_1] nvarchar NULL,
[Contact_2] nvarchar NULL,
[Contact_3] nvarchar NULL,
[Contact_4] nvarchar NULL,
[Contact_5] nvarchar NULL,
[Contact_6] nvarchar NULL,
[Contact_7] nvarchar NULL,
[Contact_8] nvarchar NULL,
[Contact_9] nvarchar NULL,
[Contact_10] nvarchar NULL,
[Billing_Address_1] nvarchar NULL,
[Billing_Address_2] nvarchar NULL,
[Billing_Address_3] nvarchar NULL,
[Billing_Address_4] nvarchar NULL,
[Billing_City] nvarchar NULL,
[Billing_State] nvarchar NULL,
[Billing_ZIP_Code] nvarchar NULL,
[Billing_Email_Address] nvarchar NULL,
[Billing_Contact] nvarchar NULL,
[Invoice_Format] nvarchar NULL,
[SM_Invoice_Format] nvarchar NULL,
[Billing_Freq] nvarchar NULL,
[Retainage] [decimal](28, 10) NULL,
[Auto_Text] nvarchar NULL,
[Last_Auto_Number] [bigint] NULL,
[Exempt_from_Finance_Charge] [bit] NULL,
[Finance_Charge_Rate_Type] nvarchar NULL,
[Finance_Charge_Percentage_Rate] [decimal](28, 10) NULL,
[Finance_Charge_Flat_Rate] [decimal](28, 10) NULL,
[Labor_Rate_Table] nvarchar NULL,
[Material_Rate_Table] nvarchar NULL,
[Subcontract_Rate_Table] nvarchar NULL,
[Equipment_Rate_Table] nvarchar NULL,
[Overhead_Rate_Table] nvarchar NULL,
[Other_Rate_Table] nvarchar NULL,
[Markup_Table] nvarchar NULL,
[Addon_Table] nvarchar NULL,
[Discount] [decimal](28, 10) NULL,
[Total_Billed_Exempt_Status] nvarchar NULL,
[Tax_Group] nvarchar NULL,
[Labor_Exempt_Status] nvarchar NULL,
[Material_Exempt_Status] nvarchar NULL,
[Subcontract_Exempt_Status] nvarchar NULL,
[Equipment_Exempt_Status] nvarchar NULL,
[Overhead_Exempt_Status] nvarchar NULL,
[Other_Exempt_Status] nvarchar NULL,
[Resale_Cert] nvarchar NULL,
[ABN] nvarchar NULL,
[Checklist_1] [bit] NULL,
[Checklist_1_Date] datetime2 NULL,
[Checklist_2] [bit] NULL,
[Checklist_2_Date] datetime2 NULL,
[Checklist_3] [bit] NULL,
[Checklist_3_Date] datetime2 NULL,
[Checklist_4] [bit] NULL,
[Checklist_4_Date] datetime2 NULL,
[Checklist_5] [bit] NULL,
[Checklist_5_Date] datetime2 NULL,
[Checklist_6] [bit] NULL,
[Checklist_6_Date] datetime2 NULL,
[Checklist_7] [bit] NULL,
[Checklist_7_Date] datetime2 NULL,
[Checklist_8] [bit] NULL,
[Checklist_8_Date] datetime2 NULL,
[Checklist_9] [bit] NULL,
[Checklist_9_Date] datetime2 NULL,
[Checklist_10] [bit] NULL,
[Checklist_10_Date] datetime2 NULL,
[Text_1] nvarchar NULL,
[Text_2] nvarchar NULL,
[Text_3] nvarchar NULL,
[Text_4] nvarchar NULL,
[Text_5] nvarchar NULL,
[Text_6] nvarchar NULL,
[AB_Company_ID] nvarchar NULL,
[Operator_Stamp] nvarchar NULL,
[Date_Stamp] datetime2 NULL,
[Time_Stamp] time NULL,
[Row_ID] nvarchar NULL,
[Row_Version] nvarchar NULL,
[Customer_Notes] nvarchar NULL,
[Customer_File_Links] nvarchar NULL,
[_AttachmentsCount] [int] NOT NULL,
CONSTRAINT [PK:dbo.ARM_MASTER__CUSTOMER] PRIMARY KEY CLUSTERED

INSERT INTO [dbo].[ARA_ACTIVITY__ACTIVITY]
([_Id]
,[Customer]
,[Status_Type]
,[Status_Date]
,[Status_Seq]
,[Actvty_Seq]
,[Activity_Type]
,[Activity_Status]
,[Invoice]
,[Draw]
,[Invoice_Date]
,[Due_Date]
,[Activity_Date]
,[Cash_Receipt_Type]
,[Cash_Receipt]
,[Bank_Account]
,[Deposit_Date]
,[Deposit_Id]
,[Deposit_Item]
,[Adjustment_Activity]
,[Adjustment_Date]
,[Type_Adjusted]
,[Adjustment_TypeNumber]
,[Adjustment_Type]
,[Adjustment]
,[Contract]
,[Contract_Item]
,[Job]
,[Extra]
,[Cost_Code]
,[Reference]
,[Description]
,[Amount]
,[Finance_Charge_Amount]
,[Retainage_Held]
,[Retainage_Billed]
,[Reserved]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Row_ID]
,[Row_Version]
,[Activity_Notes]
,[Activity_File_Links]
,[_AttachmentsCount])
VALUES
(<'2F5984C7-CD2E-E811-8039-005056B86C71', uniqueidentifier,>
,<'60BIL03', nvarchar(15),>
,<'Invoice', nvarchar(25),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'1', int,>
,<'1', int,>
,<'Invoice', nvarchar(25),>
,<'Posted', nvarchar(25),>
,<'I-18829-01', nvarchar(20),>
,<'Deposit', nvarchar(20),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'Invoice cash receipt', nvarchar(25),>
,<' ', nvarchar(20),>
,<' ', nvarchar(15),>
,<NULL, datetime2(7),>
,<' ', nvarchar(15),>
,<'0', int,>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'Not Used', nvarchar(25),>
,<'999', int,>
,<'Not Used', nvarchar(25),>
,<' ', nvarchar(20),>
,<'ZC60-18-829', nvarchar(16),>
,<'1', nvarchar(15),>
,<'ZC-60-18829', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(17),>
,<' ', nvarchar(20),>
,<'Deposit - Brad Bilut', nvarchar(35),>
,<'12825.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'JOAN', nvarchar(25),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'15:10:55.0000000', time(7),>
,<'2f5984c7-cd2e-e811-8039-005056b86c71', nvarchar(41),>
,<'1521832255', nvarchar(25),>
,<NULL, nvarchar(max),>
,<NULL, nvarchar(max),>
,<'0', int,>)
GO
INSERT INTO [dbo].[ARA_ACTIVITY__ACTIVITY]
([_Id]
,[Customer]
,[Status_Type]
,[Status_Date]
,[Status_Seq]
,[Actvty_Seq]
,[Activity_Type]
,[Activity_Status]
,[Invoice]
,[Draw]
,[Invoice_Date]
,[Due_Date]
,[Activity_Date]
,[Cash_Receipt_Type]
,[Cash_Receipt]
,[Bank_Account]
,[Deposit_Date]
,[Deposit_Id]
,[Deposit_Item]
,[Adjustment_Activity]
,[Adjustment_Date]
,[Type_Adjusted]
,[Adjustment_TypeNumber]
,[Adjustment_Type]
,[Adjustment]
,[Contract]
,[Contract_Item]
,[Job]
,[Extra]
,[Cost_Code]
,[Reference]
,[Description]
,[Amount]
,[Finance_Charge_Amount]
,[Retainage_Held]
,[Retainage_Billed]
,[Reserved]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Row_ID]
,[Row_Version]
,[Activity_Notes]
,[Activity_File_Links]
,[_AttachmentsCount])
VALUES
(<'EE196812-CE2E-E811-8039-005056B86C71', uniqueidentifier,>
,<'60BIL03', nvarchar(15),>
,<'Invoice', nvarchar(25),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'1', int,>
,<'2', int,>
,<'Cash receipt', nvarchar(25),>
,<'Posted', nvarchar(25),>
,<'I-18829-01', nvarchar(20),>
,<'Deposit', nvarchar(20),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'Invoice cash receipt', nvarchar(25),>
,<'wire', nvarchar(20),>
,<'SZ/AP', nvarchar(15),>
,<'2018-03-22 00:00:00.0000000', datetime2(7),>
,<'2', nvarchar(15),>
,<'1', int,>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'Not Used', nvarchar(25),>
,<'999', int,>
,<'Not Used, nvarchar(25),>
,<' ', nvarchar(20),>
,<'ZC60-18-829', nvarchar(16),>
,<'1', nvarchar(15),>
,<'ZC-60-18829', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(17),>
,<' ', nvarchar(20),>
,<'Brad Bilut - Deposit', nvarchar(35),>
,<'-12825.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'JOAN', nvarchar(25),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'15:12:14.0000000', time(7),>
,<'ee196812-ce2e-e811-8039-005056b86c71', nvarchar(41),>
,<'1521832334', nvarchar(25),>
,<NULL, nvarchar(max),>
,<NULL, nvarchar(max),>
,<'0', int,>)
GO

bstewartmcskms · July 26, 2018, 2:55pm

Part 2
INSERT INTO [dbo].[ARM_MASTER__CUSTOMER]
([_Id]
,[Customer]
,[Name]
,[Customer_Type]
,[Trade]
,[Key_1]
,[Key_2]
,[Address_1]
,[Address_2]
,[Address_3]
,[Address_4]
,[City]
,[State]
,[ZIP_Code]
,[Telephone]
,[Fax]
,[Email_Address]
,[Date_Established]
,[Status]
,[GL_Prefix]
,[Days_Before_Due]
,[Send_Statement]
,[PO_Required]
,[Credit_Limit]
,[Credit_Rating]
,[Contact_1]
,[Contact_2]
,[Contact_3]
,[Contact_4]
,[Contact_5]
,[Contact_6]
,[Contact_7]
,[Contact_8]
,[Contact_9]
,[Contact_10]
,[Billing_Address_1]
,[Billing_Address_2]
,[Billing_Address_3]
,[Billing_Address_4]
,[Billing_City]
,[Billing_State]
,[Billing_ZIP_Code]
,[Billing_Email_Address]
,[Billing_Contact]
,[Invoice_Format]
,[SM_Invoice_Format]
,[Billing_Freq]
,[Retainage]
,[Auto_Text]
,[Last_Auto_Number]
,[Exempt_from_Finance_Charge]
,[Finance_Charge_Rate_Type]
,[Finance_Charge_Percentage_Rate]
,[Finance_Charge_Flat_Rate]
,[Labor_Rate_Table]
,[Material_Rate_Table]
,[Subcontract_Rate_Table]
,[Equipment_Rate_Table]
,[Overhead_Rate_Table]
,[Other_Rate_Table]
,[Markup_Table]
,[Addon_Table]
,[Discount]
,[Total_Billed_Exempt_Status]
,[Tax_Group]
,[Labor_Exempt_Status]
,[Material_Exempt_Status]
,[Subcontract_Exempt_Status]
,[Equipment_Exempt_Status]
,[Overhead_Exempt_Status]
,[Other_Exempt_Status]
,[Resale_Cert]
,[ABN]
,[Checklist_1]
,[Checklist_1_Date]
,[Checklist_2]
,[Checklist_2_Date]
,[Checklist_3]
,[Checklist_3_Date]
,[Checklist_4]
,[Checklist_4_Date]
,[Checklist_5]
,[Checklist_5_Date]
,[Checklist_6]
,[Checklist_6_Date]
,[Checklist_7]
,[Checklist_7_Date]
,[Checklist_8]
,[Checklist_8_Date]
,[Checklist_9]
,[Checklist_9_Date]
,[Checklist_10]
,[Checklist_10_Date]
,[Text_1]
,[Text_2]
,[Text_3]
,[Text_4]
,[Text_5]
,[Text_6]
,[AB_Company_ID]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Row_ID]
,[Row_Version]
,[Customer_Notes]
,[Customer_File_Links]
,[_AttachmentsCount])
VALUES
(<'6C95DC93-CC2E-E811-8039-005056B86C71', uniqueidentifier,>
,<'60BIL03', nvarchar(15),>
,<'Brenda Stewart', nvarchar(55),>
,<'Standard', nvarchar(25),>
,<'None', nvarchar(25),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<'23808 E Johnson St', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<'Broken Arrow', nvarchar(35),>
,<'OK', nvarchar(9),>
,<'74014', nvarchar(15),>
,<'(918)878-3402', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(55),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'Active', nvarchar(25),>
,<' ', nvarchar(27),>
,<'0', int,>
,<'1', bit,>
,<'0', bit,>
,<'0.0000000000', decimal(28,10),>
,<'None', nvarchar(25),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(9),>
,<' ', nvarchar(15),>
,<' ', nvarchar(55),>
,<' ', nvarchar(20),>
,<' ', nvarchar(30),>
,<' ', nvarchar(260),>
,<'None', nvarchar(25),>
,<'0.0000000000', decimal(28,10),>
,<'60BIL03', nvarchar(15),>
,<'0', bigint,>
,<'0', bit,>
,<'None', nvarchar(25),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<'0.0000000000', decimal(28,10),>
,<'Taxable', nvarchar(25),>
,<' ', nvarchar(11),>
,<'Taxable', nvarchar(25),>
,<'Taxable', nvarchar(25),>
,<'Taxable', nvarchar(25),>
,<'Taxable', nvarchar(25),>
,<'Taxable', nvarchar(25),>
,<'Taxable', nvarchar(25),>
,<' ', nvarchar(25),>
,<' ', nvarchar(25),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<'0', bit,>
,<NULL, datetime2(7),>
,<' ', nvarchar(1005),>
,<' ', nvarchar(1005),>
,<' ', nvarchar(1005),>
,<' ', nvarchar(1005),>
,<' ', nvarchar(1005),>
,<' ', nvarchar(1005),>
,<'046F2057-F696-48F9-A3FA-08449FE64E20', nvarchar(41),>
,<'JOAN, nvarchar(25),>
,<'2018-03-23 00:00:00.0000000', datetime2(7),>
,<'15:02:21.0000000', time(7),>
,<'6c95dc93-cc2e-e811-8039-005056b86c71', nvarchar(41),>
,<'1521831741', nvarchar(25),>
,<NULL, nvarchar(max),>
,<NULL, nvarchar(max),>
,<'0', int,>)
GO

bstewartmcskms · July 26, 2018, 2:56pm

Part 3
INSERT INTO [dbo].[JCM_MASTER__JOB]
([_Id]
,[Job]
,[Description]
,[Address_1]
,[Address_2]
,[City]
,[State]
,[ZIP_Code]
,[Type]
,[Size]
,[Unit_Description]
,[Security_ID]
,[Site_Phone]
,[Fax_Phone]
,[Misc_Phone]
,[Project_Manager]
,[Misc_Title_1]
,[Misc_Title_2]
,[Misc_Title_3]
,[Misc_Title_4]
,[Project_Manager_Email]
,[Superintendent_Email]
,[User_Def_Key_1]
,[User_Def_Key_2]
,[Status]
,[Use_PJ_Change_Management]
,[Extras]
,[Estimated_Start_Date]
,[Estimated_Comp_Date]
,[Revised_Start_Date]
,[Revised_Comp_Date]
,[Actual_Start_Date]
,[Actual_Complete_Date]
,[Last_Cost_Update]
,[Checklist_1]
,[Checklist_2]
,[Checklist_3]
,[Checklist_4]
,[Checklist_5]
,[Checklist_6]
,[Checklist_7]
,[Checklist_8]
,[Checklist_9]
,[Checklist_10]
,[Checklist_11]
,[Checklist_12]
,[AR_Customer]
,[Job_Tax_Group]
,[Billing_Method]
,[Billing_Level]
,[Revenue_Account]
,[Retainage_Percent]
,[Exempt_from_Finance_Charge]
,[Finance_Charge_Type]
,[Finance_Charge_Percentage]
,[Finance_Charge_Flat_Rate]
,[Scope_of_Work]
,[Daily_Entry_Control]
,[Workers_Comp_Group]
,[Burden_Percent]
,[Work_State]
,[Work_Local]
,[Default_Revenue_Code]
,[Certified_Project]
,[On_Hold]
,[Authorization]
,[Bank_Account]
,[Labor_Tax_Group]
,[Material_Tax_Group]
,[Subcontract_Tax_Group]
,[Equipment_Tax_Group]
,[Overhead_Tax_Group]
,[Other_Tax_Group]
,[Cost_Account_Group]
,[Cost_Account_Prefix]
,[Cost_Account]
,[Cost_of_Sales_Account]
,[Full_Revenue_Account]
,[Revenue_Recog_Method]
,[Produce_Vendor_Type_1_Lien_Waiver]
,[Use_Vendor_Type_1_Commitment_LW_Amount]
,[Min_Vendor_Type_1_Commitment_LW_Amount]
,[Min_Vendor_Type_1_LW_Amount]
,[Req_Vendor_Type_1_Certified_Rpts]
,[Req_Vendor_Type_1_Master_Approval]
,[Produce_Vendor_Type_2_Lien_Waiver]
,[Use_Vendor_Type_2_Commitment_LW_Amount]
,[Min_Vendor_Type_2_Commitment_LW_Amount]
,[Min_Vendor_Type_2_LW_Amount]
,[Req_Vendor_Type_2_Certified_Rpts]
,[Req_Vendor_Type_2_Master_Approval]
,[Produce_Vendor_Type_3_Lien_Waiver]
,[Use_Vendor_Type_3_Commitment_LW_Amount]
,[Min_Vendor_Type_3_Commitment_LW_Amount]
,[Min_Vendor_Type_3_LW_Amount]
,[Req_Vendor_Type_3_Certified_Rpts]
,[Req_Vendor_Type_3_Master_Approval]
,[Produce_Vendor_Type_4_Lien_Waiver]
,[Use_Vendor_Type_4_Commitment_LW_Amount]
,[Min_Vendor_Type_4_Commitment_LW_Amount]
,[Min_Vendor_Type_4_LW_Amount]
,[Req_Vendor_Type_4_Certified_Rpts]
,[Req_Vendor_Type_4_Master_Approval]
,[Last_Day_of_Month]
,[LW_Conditional_Thru_Date]
,[LW_Uncond_Thru_Date]
,[Jurisdiction]
,[Cert_Rpt_Week_Ending_Day]
,[Master_Cmplnc_Tolerance]
,[Customer]
,[Customer_Name]
,[Customer_Address_1]
,[Customer_Address_2]
,[Customer_City]
,[Customer_State]
,[Customer_ZIP_Code]
,[Customer_Contact_1]
,[Customer_Phone_1]
,[Customer_FAX]
,[Bill_to_Address_1]
,[Bill_to_Address_2]
,[Bill_to_city]
,[Bill_to_state]
,[Bill_to_ZIP_Code]
,[Customer_Contact_2]
,[Customer_Phone_2]
,[Owner_Contract_ID]
,[Contract_Date]
,[Contract_Retainage_Percent]
,[Contract_Type]
,[Original_Contract_Amount]
,[Potential_CO_Contract_Changes]
,[CO_Request_Contract_Changes]
,[Verbal_Okay_Contract_Changes]
,[JTD_Aprvd_Contract_Chgs]
,[Revised_Contract_Amount]
,[YTD_Aprvd_Contract_Chgs]
,[QTD_Aprvd_Contract_Chgs]
,[MTD_Aprvd_Contract_Chgs]
,[LM_Aprvd_Contract_Chgs]
,[NM_Aprvd_Contract_Chgs]
,[JTD_Work_Billed]
,[YTD_Work_Billed]
,[QTD_Work_Billed]
,[MTD_Work_Billed]
,[LM_Work_Billed]
,[NM_Work_Billed]
,[JTD_Retainage_Held]
,[YTD_Retainage_Held]
,[QTD_Retainage_Held]
,[MTD_Retainage_Held]
,[LM_Retainage_Held]
,[NM_Retainage_Held]
,[JTD_Payments]
,[YTD_Payments]
,[QTD_Payments]
,[MTD_Payments]
,[LM_Payments]
,[NM_Payments]
,[JTD_Adjustment]
,[YTD_Adjustment]
,[QTD_Adjustment]
,[MTD_Adjustment]
,[LM_Adjustment]
,[NM_Adjustment]
,[Receivable_Balance]
,[Last_Draw]
,[Orig_Estimate_Finalized]
,[Original_Estimate]
,[Pending_Estimate_Changes]
,[JTD_Aprvd_Estimate_Chgs]
,[Total_Estimate]
,[YTD_Aprvd_Estimate_Chgs]
,[QTD_Aprvd_Estimate_Chgs]
,[MTD_Aprvd_Estimate_Chgs]
,[LM_Aprvd_Estimate_Chgs]
,[NM_Aprvd_Estimate_Chgs]
,[Original_Commitment]
,[Pending_Commitment_Changes]
,[Approved_Commitment_Changes]
,[Revised_Commitment]
,[Commitment_Invoiced]
,[JTD_Cost]
,[YTD_Cost]
,[QTD_Cost]
,[MTD_Cost]
,[LM_Cost]
,[NM_Cost]
,[WTD_Cost]
,[LW_Cost]
,[Tax_on_JTD_Cost]
,[Tax_on_YTD_Cost]
,[Tax_on_QTD_Cost]
,[Tax_on_MTD_Cost]
,[Tax_on_LM_Cost]
,[Tax_on_NM_Cost]
,[Tax_on_WTD_Cost]
,[Tax_on_LW_Cost]
,[JTD_Dollars_Paid]
,[YTD_Dollars_Paid]
,[QTD_Dollars_Paid]
,[MTD_Dollars_Paid]
,[LM_Dollars_Paid]
,[NM_Dollars_Paid]
,[WTD_Dollars_Paid]
,[LW_Dollars_Paid]
,[Tax_on_JTD_Dollars_Paid]
,[Tax_on_YTD_Dollars_Paid]
,[Tax_on_QTD_Dollars_Paid]
,[Tax_on_MTD_Dollars_Paid]
,[Tax_on_LM_Dollars_Paid]
,[Tax_on_NM_Dollars_Paid]
,[Tax_on_WTD_Dollars_Pd]
,[Tax_on_LW_Dollars_Paid]
,[Total_Tax_Amount]
,[Total_Labor_Estimate]
,[Total_Labor_Units_Est]
,[Total_Material_Estimate]
,[Total_Subcontract_Est]
,[Total_Equipment_Estimate]
,[Total_Equipment_Units_Est]
,[Total_Overhead_Estimate]
,[Total_Other_Estimate]
,[Total_Labor_Commitment]
,[Total_Material_Commitment]
,[Total_Sub_Commitment]
,[Total_Equipment_Commitment]
,[Total_Overhead_Commitment]
,[Total_Other_Commitment]
,[Commitment_Invoiced__Labor]
,[Commitment_Invoiced__Material]
,[Commitment_Invoiced__Sub]
,[Commitment_Invoiced__Equipment]
,[Commitment_Invoiced__Overhead]
,[Commitment_Invoiced__Other]
,[JTD_Labor_Cost]
,[JTD_Labor_Units]
,[JTD_Material_Cost]
,[JTD_Subcontract_Cost]
,[JTD_Equipment_Cost]
,[JTD_Equipment_Units]
,[JTD_Overhead_Cost]
,[JTD_Other_Cost]
,[MTD_Labor_Cost]
,[MTD_Labor_Units]
,[MTD_Material_Cost]
,[MTD_Subcontract_Cost]
,[MTD_Equipment_Cost]
,[MTD_Equipment_Units]
,[MTD_Overhead_Cost]
,[MTD_Other_Cost]
,[LM_Labor_Cost]
,[LM_Labor_Units]
,[LM_Material_Cost]
,[LM_Subcontract_Cost]
,[LM_Equipment_Cost]
,[LM_Equipment_Units]
,[LM_Overhead_Cost]
,[LM_Other_Cost]
,[NM_Labor_Cost]
,[NM_Labor_Units]
,[NM_Material_Cost]
,[NM_Subcontract_Cost]
,[NM_Equipment_Cost]
,[NM_Equipment_Units]
,[NM_Overhead_Cost]
,[NM_Other_Cost]
,[WTD_Labor_Cost]
,[WTD_Labor_Units]
,[WTD_Equipment_Cost]
,[WTD_Equipment_Units]
,[JTD_Labor_Dollars_Paid]
,[JTD_Material_Dollars_Paid]
,[JTD_Sub_Dollars_Paid]
,[JTD_Equipment_Dollars_Paid]
,[JTD_Overhead_Dollars_Paid]
,[JTD_Other_Dollars_Paid]
,[Date_of_Last_Report]
,[Percent_Complete]
,[Msc_Job_Amt_1]
,[Msc_Job_Amt_2]
,[Msc_Job_Amt_3]
,[Stored_Material]
,[Cost_At_Comp]
,[Cost_To_Comp]
,[Lab_Hrs_To_Comp]
,[Msc_CC_Amt_5]
,[Msc_CC_Amt_6]
,[Msc_Cat_Amt_1]
,[Msc_Cat_Amt_2]
,[Msc_Cat_Amt_3]
,[Msc_Cat_Amt_4]
,[Custom_Total_1_Total]
,[Custom_Total_2_Total]
,[Custom_Total_3_Total]
,[Custom_Total_4_Total]
,[Custom_Total_5_Total]
,[Custom_Total_6_Total]
,[Last_Custom_Total_Update]
,[Extras_Orig_Contract_Amount]
,[Extras_Potential_CO_Contract_Changes]
,[Extras_CO_Request_Contract_Changes]
,[Extras_Verbal_Okay_Contract_Changes]
,[Extras_JTD_Aprvd_Cntrc_Chgs]
,[Extras_Revised_Contract_Amt]
,[Extras_YTD_Aprvd_Cntrc_Chgs]
,[Extras_QTD_Aprvd_Cntrc_Chgs]
,[Extras_MTD_Aprvd_Cntrc_Chgs]
,[Extras_LM_Aprvd_Cntrc_Chgs]
,[Extras_NM_Aprvd_Cntrc_Chgs]
,[Extras_JTD_Work_Billed]
,[Extras_YTD_Work_Billed]
,[Extras_QTD_Work_Billed]
,[Extras_MTD_Work_Billed]
,[Extras_LM_Work_Billed]
,[Extras_NM_Work_Billed]
,[Extras_JTD_Retainage_Held]
,[Extras_YTD_Retainage_Held]
,[Extras_QTD_Retainage_Held]
,[Extras_MTD_Retainage_Held]
,[Extras_LM_Retainage_Held]
,[Extras_NM_Retainage_Held]
,[Extras_JTD_Payments]
,[Extras_YTD_Payments]
,[Extras_QTD_Payments]
,[Extras_MTD_Payments]
,[Extras_LM_Payments]
,[Extras_NM_Payments]
,[Extras_JTD_Adjustment]
,[Extras_YTD_Adjustment]
,[Extras_QTD_Adjustment]
,[Extras_MTD_Adjustment]
,[Extras_LM_Adjustment]
,[Extras_NM_Adjustment]
,[Extras_Billing_Balance]
,[Extras_Original_Estimate]
,[Extras_Pndng_Estimate_Chgs]
,[Extras_JTD_Aprvd_Est_Chgs]
,[Extras_Total_Estimate]
,[Extras_YTD_Aprvd_Est_Chgs]
,[Extras_QTD_Aprvd_Est_Chgs]
,[Extras_MTD_Aprvd_Est_Chgs]
,[Extras_LM_Aprvd_Est_Chgs]
,[Extras_NM_Aprvd_Est_Chgs]
,[Extra_Original_Commitment]
,[Extra_Pending_Commitment_Changes]
,[Extra_Approved_Commitment_Changes]
,[Extra_Revised_Commitment]
,[Extra_Commitment_Invoiced]
,[Extras_JTD_Cost]
,[Extras_YTD_Cost]
,[Extras_QTD_Cost]
,[Extras_MTD_Cst]
,[Extras_LM_Cost]
,[Extras_NM_Cost]
,[Extras_WTD_Cost]
,[Extras_LW_Cost]
,[Tax_on_Extras_JTD_Cost]
,[Tax_on_Extras_YTD_Cost]
,[Tax_on_Extras_QTD_Cost]
,[Tax_on_Extras_MTD_Cst]
,[Tax_on_Extras_LM_Cost]
,[Tax_on_Extras_NM_Cost]
,[Tax_on_Extras_WTD_Cost]
,[Tax_on_Extras_LW_Cost]
,[Extras_JTD_Dollars_Paid]
,[Extras_YTD_Dollars_Paid]
,[Extras_QTD_Dollars_Paid]
,[Extras_MTD_Dollars_Paid]
,[Extras_LM_Dollars_Paid]
,[Extras_NM_Dollars_Paid]
,[Extras_WTD_Dollars_Paid]
,[Extras_LW_Dollars_Paid]
,[Tax_on_Extras_JTD_Dollrs_Pd]
,[Tax_on_Extras_YTD_Dollrs_Pd]
,[Tax_on_Extras_QTD_Dollrs_Pd]
,[Tax_on_Extras_MTD_Dollrs_Pd]
,[Tax_on_Extras_LM_Dollrs_Pd]
,[Tax_on_Extras_NM_Dollrs_Pd]
,[Tax_on_Extras_WTD_DP]
,[Tax_on_Extras_LW_DP]
,[Extras_Total_Tax_Amount]
,[Extras_Total_Labor_Estimate]
,[Extras_Total_Labor_Unts_Est]
,[Extras_Total_Material_Est]
,[Extra_Total_Sub_Estimate]
,[Extra_Total_Equipment_Estimate]
,[Extra_Total_Equipment_Units_Est]
,[Extra_Total_Overhead_Est]
,[Extra_Total_Other_Estimate]
,[Extra_Total_Labor_Commitment]
,[Extra_Total_Mat_Commitment]
,[Extra_Total_Sub_Commitment]
,[Extra_Total_Equipment_Commitment]
,[Extra_Total_Overhead_Commitment]
,[Extra_Total_Other_Commitment]
,[Extra_Commitment_Inv__Labor]
,[Extra_Commitment_Inv__Material]
,[Extra_Commitment_Inv__Sub]
,[Extra_Commitment_Inv__Equipment]
,[Extra_Commitment_Inv__Overhead]
,[Extra_Commitment_Inv__Other]
,[Extras_JTD_Labor_Cost]
,[Extras_JTD_Labor_Units]
,[Extras_JTD_Material_Cost]
,[Extras_JTD_Sub_Cost]
,[Extras_JTD_Equipment_Cost]
,[Extras_JTD_Equipment_Units]
,[Extras_JTD_Overhead_Cost]
,[Extras_JTD_Other_Cost]
,[Extras_MTD_Labor_Cost]
,[Extras_MTD_Labor_Units]
,[Extras_MTD_Material_Cost]
,[Extras_MTD_Sub_Cost]
,[Extras_MTD_Equipment_Cost]
,[Extras_MTD_Equipment_Units]
,[Extras_MTD_Overhead_Cost]
,[Extras_MTD_Other_Cost]
,[Extras_LM_Labor_Cost]
,[Extras_LM_Labor_Units]
,[Extras_LM_Material_Cost]
,[Extras_LM_Sub_Cost]
,[Extras_LM_Equipment_Cost]
,[Extras_LM_Equipment_Units]
,[Extras_LM_Overhead_Cost]
,[Extras_LM_Other_Cost]
,[Extras_NM_Labor_Cost]
,[Extras_NM_Labor_Units]
,[Extras_NM_Material_Cost]
,[Extras_NM_Sub_Cost]
,[Extras_NM_Equipment_Cost]
,[Extras_NM_Equipment_Units]
,[Extras_NM_Overhead_Cost]
,[Extras_NM_Other_Cost]
,[Extras_WTD_Labor_Cost]
,[Extras_WTD_Labor_Units]
,[Extras_WTD_Equipment_Cost]
,[Extras_WTD_Equipment_Units]
,[Extras_JTD_Labor_Dllrs_Paid]
,[Extras_JTD_Mat_Dllrs_Paid]
,[Extras_JTD_Sub_Dllrs_Paid]
,[Extras_JTD_Equipment_Dllrs_Paid]
,[Extras_JTD_Ovrhd_Dllrs_Paid]
,[Extras_JTD_Other_Dllrs_Paid]
,[Msc_Extra_Amt_1]
,[Msc_Extra_Amt_2]
,[Msc_Extra_Amt_3]
,[Extras_Stored_Material]
,[Extras_Cost_At_Comp]
,[Extras_Cost_To_Comp]
,[Extras_Lab_Hrs_To_Comp]
,[Extras_Msc_CC_Amt_5]
,[Extras_Msc_CC_Amt_6]
,[Extras_Msc_Cat_Amt_1]
,[Extras_Msc_Cat_Amt_2]
,[Extras_Msc_Cat_Amt_3]
,[Extras_Msc_Cat_Amt_4]
,[Extras_Custom_Total_1_Total]
,[Extras_Custom_Total_2_Total]
,[Extras_Custom_Total_3_Total]
,[Extras_Custom_Total_4_Total]
,[Extras_Custom_Total_5_Total]
,[Extras_Custom_Total_6_Total]
,[Operator_Stamp]
,[Date_Stamp]
,[Time_Stamp]
,[Row_ID]
,[Row_Version]
,[Job_Notes]
,[Job_File_Links]
,[_AttachmentsCount])

bstewartmcskms · July 26, 2018, 2:57pm

Part 4
VALUES
(<'8AAB74EA-8E69-E811-90F7-005056B86C05', uniqueidentifier,>
,<'ZC-60-18829', nvarchar(15),>
,<'Brad Bilut # 1502', nvarchar(35),>
,<'23808 E Johnson St', nvarchar(35),>
,<' ', nvarchar(35),>
,<'Broken Arrow', nvarchar(20),>
,<'OK', nvarchar(9),>
,<'74014', nvarchar(15),>
,<' ', nvarchar(20),>
,<'0', bigint,>
,<' ', nvarchar(11),>
,<' ', nvarchar(43),>
,<'(918)878-3402', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<'Dustin Nolen', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<'Dustin Nolen', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(55),>
,<' ', nvarchar(55),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<'Closed', nvarchar(25),>
,<'1', bit,>
,<'Not Tracking Sub Jbs', nvarchar(25),>
,<NULL, datetime2(7),>
,<NULL, datetime2(7),>
,<NULL, datetime2(7),>
,<NULL, datetime2(7),>
,<'2018-03-30 00:00:00.0000000', datetime2(7),>
,<NULL, datetime2(7),>
,<'2018-05-03 00:00:00.0000000', datetime2(7),>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'60BIL03', nvarchar(15),>
,<' ', nvarchar(11),>
,<'Use Contracts', nvarchar(25),>
,<'Job', nvarchar(25),>
,<' ', nvarchar(20),>
,<'0.0000000000', decimal(28,10),>
,<'0', bit,>
,<'None', nvarchar(25),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<' ', nvarchar(1005),>
,<'Allow Daily Enry', nvarchar(25),>
,<' ', nvarchar(11),>
,<'0.0000000000', decimal(28,10),>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(17),>
,<' ', nvarchar(26),>
,<'0', bit,>
,<' ', nvarchar(15),>
,<' ', nvarchar(15),>
,<' ', nvarchar(11),>
,<' ', nvarchar(11),>
,<' ', nvarchar(11),>
,<' ', nvarchar(11),>
,<' ', nvarchar(11),>
,<' ', nvarchar(11),>
,<'Group A', nvarchar(25),>
,<'60-60-10', nvarchar(27),>
,<' ', nvarchar(30),>
,<' ', nvarchar(30),>
,<' ', nvarchar(30),>
,<'Percent complete', nvarchar(25),>
,<'0', bit,>
,<'0', bit,>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'0', bit,>
,<'00.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0', bit,>
,<'0', bit,>
,<'29', tinyint,>
,<'Last Day of Month', nvarchar(25),>
,<'Payment Date', nvarchar(25),>
,<'Not Specified', nvarchar(25),>
,<'Friday', nvarchar(25),>
,<'0.0000000000', decimal(28,10),>
,<' ', nvarchar(15),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(20),>
,<' ', nvarchar(9),>
,<' ', nvarchar(15),>
,<' ', nvarchar(35),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<' ', nvarchar(35),>
,<' ', nvarchar(35),>
,<' ', nvarchar(20),>
,<' ', nvarchar(9),>
,<' ', nvarchar(15),>
,<' ', nvarchar(35),>
,<' ', nvarchar(20),>
,<' ', nvarchar(20),>
,<NULL, datetime2(7),>
,<'0.0000000000', decimal(28,10),>
,<' ', nvarchar(20),>
,<'24600.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'12825.0000000000', decimal(28,10),>
,<'12825.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'12825.0000000000', decimal(28,10),>
,<'12825.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'Deposit', nvarchar(20),>
,<'0', bit,>
,<'18063.7800000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<JTD_Aprvd_Estimate_Chgs, decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'1184.2100000000', decimal(28,10),>
,<'1184.2100000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'1047.2100000000', decimal(28,10),>
,<'13.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'1184.2100000000', decimal(28,10),>
,<'1184.2100000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'563.8900000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'1049.3000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'4216.0000000000', decimal(28,10),>
,<'124.0000000000', decimal(28,10),>
,<'12362.7800000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'1485.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'959.5200000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'224.6900000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'959.5200000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'224.6900000000', decimal(28,10),>
,<NULL, datetime2(7),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<NULL, datetime2(7),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<'0.0000000000', decimal(28,10),>
,<Sam, nvarchar(25),>
,<'2018-07-03 00:00:00.0000000', datetime2(7),>
,<'13:00:37.0000000', time(7),>
,<'8aab74ea-8e69-e811-90f7-005056b86c05', nvarchar(41),>
,<'1530637237', nvarchar(25),>
,<NULL, nvarchar(max),>
,<NULL, nvarchar(max),>
,<'0', int,>)
GO

bstewartmcskms · July 26, 2018, 3:01pm

Expected result is 0 rows returned

bitsmed · July 26, 2018, 7:47pm

Your create statements lack size specifier on all nvarchar fields.
Also, your insert statements doesn't work (at least on my system MSSQL 2017).

Please make sure your statements work, so we can concentrate on solving your issue, rather than spending time building sample data.