Header to Many rows

  • Hi All,

    I am trying to format data with a header row made up of two data sets VCR\CCR (joined on cust num & cost centre) to a many lines data set BTP. The BTP lines can occur more than once per cost centre record.

    Current Format:

    VCR|cust num 312346|32332|dddd|wwwww|

    CCR|cust num 312346|costcentre 11111|cccc|vvvvv|bbbbb|22122|5555|

    BPT|cust num 312346|costcentre 11111|Mrs Smith|dddd|32332|ccccc|qqqqqq

    VCR|cust num 312346|32332|dddd|wwwww|

    CCR|cust num 312346|costcentre 11111|cccc|vvvvv|bbbbb|22122|5555|

    BPT|cust num 312346|costcentre 11111|MR Swan|dddd|32332|ccccc|qqqqqq

    VCR|cust num 312346|32332|dddd|wwwww|

    CCR|cust num 312346|costcentre 11111|cccc|vvvvv|bbbbb|22122|5555|

    BPT|cust num 312346|costcentre 11111|MR Rogers|dddd|32332|ccccc|qqqqqq

    Required Format:

    VCR|cust num 312346|32332|dddd|wwwww|

    CCR|cust num 312346|costcentre 11111|cccc|vvvvv|bbbbb|22122|5555|

    BPT|cust num 312346|costcentre 11111|Mrs Smith|dddd|32332|ccccc|qqqqqq

    BPT|cust num 312346|costcentre 11111|MR Swan|dddd|32332|ccccc|qqqqqq

    BPT|cust num 312346|costcentre 11111|MR Rogers|dddd|32332|ccccc|qqqqqq

    The lines should occur more than once for each cost centre, but they should be grouped\joined to the HEADER CCR row.

    ;WITH CTEVCR AS (

    SELECT DISTINCT

    [VCRRecType],

    [VCRCustNo],

    [VCRCollationFlag],

    [VCRPrintFlag],

    [VCRNotUsed],

    [VCRImageFlag],

    [VCRTradingName],

    [VCRContactName],

    [VCRAddress1],

    [VCRAddress2],

    [VCRAddress3],

    [VCRAddress4],

    [VCROfficeFlag],

    [VCRGrandTotalPeriod],

    [VCRGrandTotalCosttoDate],

    [VCRBillFromDate],

    [VCRBillToDate],

    [VCRReprintFlag],

    [VCRemailAddress],

    CHAR(13)+CHAR(10) + CCRRecType as [CCRRecType],

    CCRCustno,

    CCRCostCentre,

    CCRBPSubtotalFuel,

    CCRSBPubTotalRepairs,

    CCRBPSubTotalLease,

    CCRBPSubtotaltoll,

    CCRBPSubTotalRUCRego,

    CCRBPSubTotalOtherExp,

    CCRBPSubtotalAccident,

    CCRBPSubToalMgmt,

    CCRBPSubTotal,

    CCRCTDSubtotalFuelOil,

    CCRCTDSubtotalRepairsandMaint,

    CCRCTDSubtotalLeaseCharges,

    CCRCTDSubtotalTollRoadCharges,

    CCRCTDSubtotalRUCandRego,

    CCRCTDSubtotalOtherExpenses,

    CCRCTDSubtotalAccidentInsurance,

    CCRCTDSubtotalMgmtCharges,

    CCRCTDSubtotalTotal,

    CCRTransactionFees

    FROM [dbo].[VehicleAnalysisVCR] R

    JOIN [dbo].[VehicleAnalysisCCR] C

    ON R.VCRCustNo = C.CCRCustno and R.VCRCostCentre = C.CCRCostCentre

    )

    ,

    CTEBTP AS (SELECT

    RecType as [RecType]

    ,custno

    ,CostCentre

    ,ModelYear

    ,VehicleDesc

    ,DriverName

    ,Odometer

    ,[Span(Kms)]

    ,[Fuel/Oil]

    ,[Repairs/Maintenance]

    ,LeaseCharges

    ,TollRoadCharges

    ,[RUC/Rego]

    ,OtherExpenses

    ,AccidentInsurance

    ,ManagementCharges

    ,Total

    ,regnno

    ,CopyofTotal

    FROM [dbo].[VehicleAnalysisBPT] B

    JOIN [dbo].[VehicleAnalysisCCR] C

    ON B.CostCentre = c.CCRCostCentre)

    SELECT R.[VCRRecType],R.[VCRCustNo],R.[VCRCollationFlag],R.[VCRPrintFlag],R.[VCRNotUsed],R.[VCRImageFlag],R.[VCRTradingName],R.[VCRContactName],R.[VCRAddress1],R.[VCRAddress2],R.[VCRAddress3],R.[VCRAddress4],R.[VCROfficeFlag],R.[VCRGrandTotalPeriod],R.[VCRGrandTotalCosttoDate],

    R.[VCRBillFromDate],R.[VCRBillToDate],R.[VCRReprintFlag],R.[VCRemailAddress],

    R.[CCRRecType],R.CCRCustno,R.CCRCostCentre,R.CCRBPSubtotalFuel,R.CCRSBPubTotalRepairs,R.CCRBPSubTotalLease,R.CCRBPSubtotaltoll,R.CCRBPSubTotalRUCRego,R.CCRBPSubTotalOtherExp,R.CCRBPSubtotalAccident,R.CCRBPSubToalMgmt,R.CCRBPSubTotal,R.CCRCTDSubtotalFuelOil,R.CCRCTDSubtotalRepairsandMaint,R.CCRCTDSubtotalLeaseCharges,R.CCRCTDSubtotalTollRoadCharges,R.CCRCTDSubtotalRUCandRego,

    R.CCRCTDSubtotalOtherExpenses,R.CCRCTDSubtotalAccidentInsurance,R.CCRCTDSubtotalMgmtCharges,R.CCRCTDSubtotalTotal,R.CCRTransactionFees

    ,CHAR(13)+CHAR(10) + B.RecType as [RecType],B.custno,B.CostCentre,B.ModelYear,B.VehicleDesc,B.DriverName,B.Odometer,B.[Span(Kms)],B.[Fuel/Oil],B.[Repairs/Maintenance],B.LeaseCharges,B.TollRoadCharges,B.[RUC/Rego],B.OtherExpenses,B.AccidentInsurance

    ,B.ManagementCharges,B.Total,B.regnno

    FROM CTEVCR R

    FULL OUTER JOIN CTEBTP B

    ON R.CCRCostCentre = B.CostCentre

    Hope this make sense and cheers for any assistance.

  • Can you provide DDL and DLM. Your sample data has a varying amount of columns. You can't have a table which has different numbers of columns for different rows, SQL doesn't work like that.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • DDL is below and some sample data is attached, the required format is pipe delimited | | | | etc...

    CREATE TABLE [dbo].[VehicleAnalysisVCR](

    [VCRRecType] [varchar](3) NULL,

    [VCRCustNo] [int] NULL,

    [VCRCostCentre] [varchar](max) NULL,

    [VCRCollationFlag] [varchar](2) NULL,

    [VCRPrintFlag] [varchar](10) NULL,

    [VCRNotUsed] [varchar](1) NULL,

    [VCRImageFlag] [varchar](2) NULL,

    [VCRTradingName] [varchar](60) NULL,

    [VCRContactName] [varchar](40) NULL,

    [VCRAddress1] [varchar](100) NULL,

    [VCRAddress2] [varchar](100) NULL,

    [VCRAddress3] [varchar](100) NULL,

    [VCRAddress4] [varchar](100) NULL,

    [VCROfficeFlag] [varchar](1) NULL,

    [VCRGrandTotalPeriod] [money] NULL,

    [VCRGrandTotalCosttoDate] [float] NULL,

    [VCRBillFromDate] [varchar](1) NULL,

    [VCRBillToDate] [varchar](1) NULL,

    [VCRReprintFlag] [varchar](1) NULL,

    [VCRemailAddress] [varchar](8000) NULL,

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    CREATE TABLE [dbo].[VehicleAnalysisCCR](

    [CCRRecType] [varchar](3) NULL,

    [CCRCustno] [int] NULL,

    [CCRCostCentre] [varchar](100) NULL,

    [CCRBPSubtotalFuel] [money] NULL,

    [CCRSBPubTotalRepairs] [money] NULL,

    [CCRBPSubTotalLease] [money] NULL,

    [CCRBPSubtotaltoll] [money] NULL,

    [CCRBPSubTotalRUCRego] [money] NULL,

    [CCRBPSubTotalOtherExp] [money] NULL,

    [CCRBPSubtotalAccident] [money] NULL,

    [CCRBPSubToalMgmt] [money] NULL,

    [CCRBPSubTotal] [money] NULL,

    [CCRCTDSubtotalFuelOil] [float] NULL,

    [CCRCTDSubtotalRepairsandMaint] [float] NULL,

    [CCRCTDSubtotalLeaseCharges] [float] NULL,

    [CCRCTDSubtotalTollRoadCharges] [float] NULL,

    [CCRCTDSubtotalRUCandRego] [float] NULL,

    [CCRCTDSubtotalOtherExpenses] [float] NULL,

    [CCRCTDSubtotalAccidentInsurance] [float] NULL,

    [CCRCTDSubtotalMgmtCharges] [float] NULL,

    [CCRCTDSubtotalTotal] [float] NULL,

    [CCRTransactionFees] [float] NULL,

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[VehicleAnalysisBPT](

    [RecType] [varchar](3) NULL,

    [DossierNo] [int] NULL,

    [CostCentre] [varchar](100) NULL,

    [custno] [int] NULL,

    [ModelYear] [int] NULL,

    [VehicleDesc] [varchar](100) NULL,

    [DriverName] [varchar](100) NULL,

    [Odometer] [bigint] NULL,

    [Span(Kms)] [bigint] NULL,

    [Fuel/Oil] [money] NULL,

    [Repairs/Maintenance] [money] NULL,

    [LeaseCharges] [money] NULL,

    [TollRoadCharges] [money] NULL,

    [RUC/Rego] [money] NULL,

    [OtherExpenses] [money] NULL,

    [AccidentInsurance] [money] NULL,

    [ManagementCharges] [money] NULL,

    [Total] [money] NULL,

    [regnno] [varchar](6) NULL,

    [CopyofTotal] [varchar](50) NULL,

    [Parent] [int] NULL,

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[VehicleAnalysisCTD](

    [RecType] [varchar](3) NULL,

    [DossierNo] [int] NULL,

    [CostCentre] [varchar](100) NULL,

    [custno] [int] NULL,

    [ModelYear] [int] NULL,

    [VehicleDesc] [varchar](100) NULL,

    [DriverName] [varchar](100) NULL,

    [Odometer] [bigint] NULL,

    [Span(Kms)] [bigint] NULL,

    [Fuel/Oil] [money] NULL,

    [Repairs/Maintenance] [money] NULL,

    [LeaseCharges] [money] NULL,

    [TollRoadCharges] [money] NULL,

    [RUC/Rego] [money] NULL,

    [OtherExpenses] [money] NULL,

    [AccidentInsurance] [money] NULL,

    [ManagementCharges] [money] NULL,

    [regnno] [varchar](6) NULL,

    [Total] [money] NULL,

    [TotalCentsPerKm] [money] NULL,

    [LitresPerVehicle] [int] NULL,

    [LitresNationalAverage] [int] NULL,

    [CO2OutputByQty] [int] NULL,

    [TotalKmsToDate] [bigint] NULL,

    [CopyofTotal] [varchar](50) NULL,

    [Parent] [int] NULL

    ) ON [PRIMARY]

    Format of the file is like this;

    1 VCR: Customer Number, ||||||||||||

    1 CCR: Cost Centre, |||||||||| (joined to VCR on customer number)

    Many BPT: Cost Centre Monthly Period, ||||||||||

    Many CTD: Cost Centre All, |||||||||

  • I have made a header table called [VehicleAnalysis_Header] which holds the VCR\CCR records with unique primary key.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[VehicleAnalysis_Header](

    [Head_ID] [int] IDENTITY(1,1) NOT NULL,

    [VCRRecType] [varchar](3) NULL,

    [VCRCustNo] [int] NULL,

    [VCRCollationFlag] [varchar](2) NULL,

    [VCRPrintFlag] [varchar](10) NULL,

    [VCRNotUsed] [varchar](1) NULL,

    [VCRImageFlag] [varchar](2) NULL,

    [VCRTradingName] [varchar](60) NULL,

    [VCRContactName] [varchar](40) NULL,

    [VCRAddress1] [varchar](100) NULL,

    [VCRAddress2] [varchar](100) NULL,

    [VCRAddress3] [varchar](100) NULL,

    [VCRAddress4] [varchar](100) NULL,

    [VCROfficeFlag] [varchar](1) NULL,

    [VCRGrandTotalPeriod] [money] NULL,

    [VCRGrandTotalCosttoDate] [float] NULL,

    [VCRBillFromDate] [varchar](1) NULL,

    [VCRBillToDate] [varchar](1) NULL,

    [VCRReprintFlag] [varchar](1) NULL,

    [VCRemailAddress] [varchar](8000) NULL,

    [CCRRecType] [varchar](3) NULL,

    [CCRCustno] [int] NULL,

    [CCRCostCentre] [varchar](100) NULL,

    [CCRBPSubtotalFuel] [money] NULL,

    [CCRSBPubTotalRepairs] [money] NULL,

    [CCRBPSubTotalLease] [money] NULL,

    [CCRBPSubtotaltoll] [money] NULL,

    [CCRBPSubTotalRUCRego] [money] NULL,

    [CCRBPSubTotalOtherExp] [money] NULL,

    [CCRBPSubtotalAccident] [money] NULL,

    [CCRBPSubToalMgmt] [money] NULL,

    [CCRBPSubTotal] [money] NULL,

    [CCRCTDSubtotalFuelOil] [float] NULL,

    [CCRCTDSubtotalRepairsandMaint] [float] NULL,

    [CCRCTDSubtotalLeaseCharges] [float] NULL,

    [CCRCTDSubtotalTollRoadCharges] [float] NULL,

    [CCRCTDSubtotalRUCandRego] [float] NULL,

    [CCRCTDSubtotalOtherExpenses] [float] NULL,

    [CCRCTDSubtotalAccidentInsurance] [float] NULL,

    [CCRCTDSubtotalMgmtCharges] [float] NULL,

    [CCRCTDSubtotalTotal] [float] NULL,

    [CCRTransactionFees] [float] NULL,

    PRIMARY KEY CLUSTERED

    (

    [Head_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

    UNIQUE NONCLUSTERED

    (

    [Head_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    Which looks like this;

    looks like this;

    VCR|302563|AC|N| |FS|TE WANANGA O AOTEAROA|Smith Smith|PO BOX 999|TE zzzzz||3840|Y|9412.40|109304268.2| | |N|email, email, email|CCR|20350-10100-3450|7244.80|0.00|0.00|0.00|0.00|0.00|0.00|940.00|8184.80|13072.2|0|0|0|0|0|0|1718.4|8583.36|0|

    This has enabled me to use row_number for the header row like this;

    ;WITH CTEVCR([VCRRecType],[VCRCustNo],[VCRCollationFlag],[VCRPrintFlag],[VCRNotUsed],[VCRImageFlag],[VCRTradingName],[VCRContactName],[VCRAddress1],[VCRAddress2],[VCRAddress3],[VCRAddress4],[VCROfficeFlag],[VCRGrandTotalPeriod],

    [VCRGrandTotalCosttoDate],[VCRBillFromDate],[VCRBillToDate],[VCRReprintFlag],[VCRemailAddress],

    CCRRecType,CCRCostCentre,CCRBPSubtotalFuel,CCRSBPubTotalRepairs,CCRBPSubTotalLease, CCRBPSubtotaltoll,CCRBPSubTotalRUCRego,CCRBPSubTotalOtherExp,CCRBPSubtotalAccident,CCRBPSubToalMgmt,CCRBPSubTotal,CCRCTDSubtotalFuelOil,CCRCTDSubtotalRepairsandMaint,

    CCRCTDSubtotalLeaseCharges,CCRCTDSubtotalTollRoadCharges,CCRCTDSubtotalRUCandRego,CCRCTDSubtotalOtherExpenses,CCRCTDSubtotalAccidentInsurance,CCRCTDSubtotalMgmtCharges,CCRCTDSubtotalTotal,CCRTransactionFees,

    RecType,regnno,ModelYear,VehicleDesc,DriverName,Odometer,[Span(Kms)],[Fuel/Oil],[Repairs/Maintenance],LeaseCharges,TollRoadCharges,[RUC/Rego],OtherExpenses,AccidentInsurance,ManagementCharges,Total,Rowid

    /*[dRecType],dregnno,dmodelyear,dvehicledesc,ddrivername,dodometer,[dSpan(Kms)],[dFuel/Oil],[dRepairs/Maintenance],dLeaseCharges,[dTollRoadCharges],[dRUC/Rego],dOtherExpenses,[dAccidentInsurance],[dManagementCharges],

    [dTotal],[dTotalCentsPerKm],[dLitresPerVehicle],[dLitresNationalAverage],[dCO2OutputByQty],[dTotalKmsToDate]*/ )

    AS(

    SELECT DISTINCT

    [VCRRecType],

    [VCRCustNo],

    [VCRCollationFlag],

    [VCRPrintFlag],

    [VCRNotUsed],

    [VCRImageFlag],

    [VCRTradingName],

    [VCRContactName],

    [VCRAddress1],

    [VCRAddress2],

    [VCRAddress3],

    [VCRAddress4],

    [VCROfficeFlag],

    [VCRGrandTotalPeriod],

    [VCRGrandTotalCosttoDate],

    [VCRBillFromDate],

    [VCRBillToDate],

    [VCRReprintFlag],

    [VCRemailAddress],

    CHAR(13)+CHAR(10)+ CCRRecType,

    --CCRCustno,

    CCRCostCentre,

    CCRBPSubtotalFuel,

    CCRSBPubTotalRepairs,

    CCRBPSubTotalLease,

    CCRBPSubtotaltoll,

    CCRBPSubTotalRUCRego,

    CCRBPSubTotalOtherExp,

    CCRBPSubtotalAccident,

    CCRBPSubToalMgmt,

    CCRBPSubTotal,

    CCRCTDSubtotalFuelOil,

    CCRCTDSubtotalRepairsandMaint,

    CCRCTDSubtotalLeaseCharges,

    CCRCTDSubtotalTollRoadCharges,

    CCRCTDSubtotalRUCandRego,

    CCRCTDSubtotalOtherExpenses,

    CCRCTDSubtotalAccidentInsurance,

    CCRCTDSubtotalMgmtCharges,

    CCRCTDSubtotalTotal,

    CCRTransactionFees,

    CHAR(13)+CHAR(10) + B.RecType,

    B.regnno,

    --custno,

    --CostCentre,

    B.ModelYear,

    B.VehicleDesc,

    B.DriverName,

    B.Odometer,

    B.[Span(Kms)],

    B.[Fuel/Oil],

    B.[Repairs/Maintenance],

    B.LeaseCharges,

    B.TollRoadCharges,

    B.[RUC/Rego],

    B.OtherExpenses,

    B.AccidentInsurance,

    B.ManagementCharges,

    B.Total,

    ROW_NUMBER() OVER ( PARTITION BY Head_id ORDER BY head_id ASC ) as Rowid

    --CHAR(13)+CHAR(10) + d.[RecType],

    --d.regnno,

    ----d.CostCentre,

    ----d.custno,

    --d.modelyear,

    --d.vehicledesc,

    --d.drivername,

    --d.odometer,

    --d.[Span(Kms)],

    --d.[Fuel/Oil],

    --d.[Repairs/Maintenance],

    --d.LeaseCharges,

    --d.[TollRoadCharges],

    --d.[RUC/Rego],

    --d.OtherExpenses,

    --d.[AccidentInsurance],

    --d.[ManagementCharges],

    --d.[Total],

    --d.[TotalCentsPerKm],

    --d.[LitresPerVehicle],

    --d.[LitresNationalAverage],

    --d.[CO2OutputByQty],

    --d.[TotalKmsToDate]

    FROM [dbo].[VehicleAnalysis_Header] H

    JOIN [dbo].[VehicleAnalysisBPT] B

    ON H.VCRCustNo = b.custno and H.CCRCostCentre = B.CostCentre

    JOIN [dbo].[VehicleAnalysisCTD] D

    ON H.VCRCustno = D.custno and H.CCRCostCentre = D.CostCentre

    WHERE VCRCustNo != 0

    )

    This produces the data exactly right without the CTD set which is commented out of the code above;

    VCR ||||

    CCR ||||

    BPT ||||

    BPT |||||

    BPT ||||||

    --Sample data correct without CTD data set

    VCR302563ACN FSTE WANANGA O AOTEAROAJOHN SMITHPO BOX 999TE AWAMUTU3840Y9412.4109304268.2 Nemail email email

    CCR20350-10100-34507244.80000009408184.813072.20000001718.48583.360

    BPTJSQ6312016TOYOTA RAV4 (540W)2.5L 6AT GX AWD ATLesley Joyce633102897.920000007523649.92

    VCR302563ACN FSTE WANANGA O AOTEAROAJOHN SMITHPO BOX 999TE AWAMUTU3840Y9949.6131165121.8 Nemail email email

    CCR00000-91260-34506771.600000018808651.6527290.5670530.960491.1623844146.04717030469.2449838.840

    BPTGBK4652009HYUNDAI i30 ELITE 2.0PET AUTO WAGONJade Strother1318550368.68000000188556.68

    0000000000000000000000

    BPTGBK4652009HYUNDAI i30 ELITE 2.0PET AUTO WAGONJade Strother1318550368.68000000188556.68

    0000000000000000000000

    BPTGBN4802008HYUNDAI i30 ELITE 2.0PET AUTO HATCHJade Strother673690308.48000000188496.48

    0000000000000000000000

    BPTGBN4802008HYUNDAI i30 ELITE 2.0PET AUTO HATCHJade Strother673690308.48000000188496.48

    VCR302563ACN FSTE WANANGA O AOTEAROAJOHN SMITHPO BOX 999TE AWAMUTU3840Y10542.8153025975.5 Nemail email email

    CCR00000-92000-34508227.60000009409167.6225118.48844.605307429.4001071293483.80

    BPTHLN1102012HYUNDAI ELANTRA 1.8 AUTO SEDANPool Vehicle47506013164.1600283.52000300816483.2

    as soon as I add the CTD data set back in it becomes incorrectly mixed in with the BTP data like this;

    VCR |||||

    CCR |||||

    CTD |||||

    BTP |||||

    CTD |||||

    BTP |||||

    CTD |||||

    when it should be;

    VCR ||||

    CCR ||||

    BTP ||||

    BTP ||||

    BTP ||||

    CTD ||||

    CTD ||||

    CTD ||||

    VCR ||||

    CCR |||

    BTP ||||

    BTP ||||

    CTD ||||

    CTD |||||

    Any suggestions?

  • Since your query does not have an ORDER BY clause, you are not guaranteed of any specific order.

    Perhaps adding the following to the end of your query will help

    ORDER BY H.VCRCustno,

    H.CCRCostCentre,

    CASE

    WHEN VCRRecType = 'VCR' THEN 1

    WHEN CCRRecType = 'CCR' THEN 2

    WHEN B.RecType = 'BPT' THEN 3

    WHEN D.RecType = 'CTD' THEN 4

    ELSE 5

    END

  • That sample text file really isn't, for lack of a better words, digestible. Looking at your DDL you've got 4 tables, but have combined your Sample data into one table.

    You should really be providing INSERT statements.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Ok, so I spent a little time and attempted to turn the OP's sample data into something "usable", which gave me this (which doesn't work):

    USE TESTDB;

    GO

    INSERT INTO VehicleAnalysis_Header

    VALUES('2563','CC','P',NULL,'FS','TE WANANGA O AOTEAROA','Karen Barrett','Mike Lewis','PO Box 6076','Urlich','Hamilton',NULL,'3245','N','FG','783569','64778920','20161006','20161103','N','aaaa@aaa.com');

    GO

    INSERT INTO VehicleAnalysisCCR

    VALUES('20310-10100-3450','389488','230321','0','9443','72644','1043','0','80630','783569','32446745','14908126','0','93819','10366267','24164','3301650','3638149','64778920','3080',NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2003','FORD TRANSIT TD MED 4DOO','mike smith','220914','0','0','0','0','434','0','0','0','0','434',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN NAVARA','Jane Smith','221861','3131','26393','178139','0','0','17554','0','0','2350','224436',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','112373','0','3549','0','0','0','0','0','0','2350','5899',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2009','NISSAN WINGROAD','Jane Smith','121035','0','0','0','0','0','0','0','0','2350','2350',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2010','NISSAN WINGROAD','mike smith','92716','1627','9208','0','0','0','0','0','0','2350','11558',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2010','NISSAN WINGROAD','Jane Smith','134219','0','0','0','0','0','0','0','0','2350','2350',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','73799','0','0','0','0','0','0','0','0','2350','2350',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','89344','0','0','0','0','0','0','0','0','2350','2350',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','68196','0','3397','0','0','0','0','0','0','2350','5747',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','92465','1942','14763','0','0','0','0','0','0','2350','17113',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','80549','3693','15337','0','0','0','0','0','0','2350','17687',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','TOYOTA HILUX 4WD D/CAB','Jane Smith','165337','1437','11034','997','0','834','0','0','0','2350','15215',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','mike smith','142780','1071','12769','0','0','0','0','0','0','2350','15119',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','Jane Smith','83411','1059','10608','0','0','0','0','0','0','2350','12958',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','mike smith','80052','543','5471','0','0','0','0','0','0','2350','7821',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','88262','927','7825','0','0','0','55090','0','0','2350','65265',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','FORD TRANSIT 12 SEAT MINI BUS','mike smith','47199','0','0','0','0','0','0','0','0','2350','2350',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','TOYOTA HILUX 4WD D/CAB','Jane Smith','113158','1023','9987','0','0','0','0','0','0','2350','12337',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','FORD TRANSIT 12 SEAT MINI BUS','mike smith','45610','979','6533','0','0','434','0','0','0','2350','9317',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','FORD TRANSIT 12 SEAT MINI BUS','Jane Smith','124750','2932','20329','17626','0','0','0','0','0','2350','40305',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','mike smith','61438','2995','29810','0','0','1520','0','1043','0','2350','34723',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','Jane Smith','52010','1987','16141','0','0','0','0','0','0','2350','18491',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','mike smith','38827','888','3571','0','0','0','0','0','0','2350','5921',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','Jane Smith','44166','2640','26666','5319','0','1268','0','0','0','2350','35603',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','mike smith','49870','1816','20051','28240','0','0','0','0','0','2350','50641',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','Jane Smith','47449','1197','4800','0','0','0','0','0','0','2350','7150',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','mike smith','51100','1347','34117','0','0','2851','0','0','0','2350','39318',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','Jane Smith','95853','1691','17830','0','0','834','0','0','0','2350','21014',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI ELANTRA','mike smith','62399','0','21829','0','0','417','0','0','0','2350','24596',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','9343','879','6575','0','0','0','0','0','0','2350','8925',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','mike smith','22241','823','6326','0','0','200','0','0','0','2350','8876',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','13309','1022','12612','0','0','0','0','0','0','2350','14962',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2016','FORD TRANSIT 460E','mike smith','5768','2278','18370','0','0','0','0','0','0','2350','20720',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2016','TOYOTA COROLLA GX CVT','Jane Smith','3342','2075','13587','0','0','651','0','0','0','2350','16588',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2003','FORD TRANSIT TD MED 4DOO','mike smith','220914','0','2677136','3062929','0','1756','1224869','0','218057','257164','7441911','3368','1192','0','6870','220914',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN NAVARA','Jane Smith','221861','0','2674659','1928513','0','2663','1307664','0','339504','187458','6440461','2902','1183','0','6850','221861',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','112373','0','1465279','984099','0','2426','732474','0','47300','181022','3412600','3036','1210','0','3548','112373',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2009','NISSAN WINGROAD','Jane Smith','121035','0','1517256','524476','0','1251','160713','0','179245','168481','2551422','2108','776','0','2150','121035',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2010','NISSAN WINGROAD','mike smith','92716','0','1182320','450763','0','904','117381','0','90761','147044','1989173','2145','760','0','1614','92716',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2010','NISSAN WINGROAD','Jane Smith','134219','0','1722876','307476','0','470','117381','0','261332','148071','2557606','1905','769','0','2364','134219',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','73799','0','984142','593430','0','7430','483591','0','256050','136566','2461209','3335','1105','0','2127','73799',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','89344','0','1091033','474497','0','1322','588245','0','404350','135986','2695433','3016','1056','0','2462','89344',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','68196','0','873579','305811','0','617','495765','0','260327','134276','2070375','3035','1084','0','1928','68196',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','92465','0','1210186','622906','0','1051','587045','0','370207','135959','2927354','3165','1137','0','2743','92465',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','80549','0','1005013','606440','0','870','491418','0','242900','134997','2481638','3080','1117','0','2347','80549',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','TOYOTA HILUX 4WD D/CAB','Jane Smith','165337','0','1904381','1047335','0','9191','939657','0','173914','130896','4205374','2543','1032','0','4453','165337',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','mike smith','142780','0','1901437','447692','0','470','98949','0','0','129591','2578139','1805','808','0','2641','142780',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','Jane Smith','83411','0','1148333','249521','0','1719','99751','0','0','126196','1625520','1948','836','0','1597','83411',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','mike smith','80052','0','1144595','270057','0','2785','99751','0','0','126760','1643948','2053','849','0','1557','80052',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2012','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','88262','0','1021722','524150','0','400','578766','0','0','117650','2242688','2540','1071','0','2468','88262',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2012','FORD TRANSIT 12 SEAT MINI BUS','mike smith','47199','0','508989','130106','0','470','381084','0','0','116123','1136772','2408','985','0','1214','47199',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2012','TOYOTA HILUX 4WD D/CAB','Jane Smith','113158','0','1042171','584628','0','3657','681875','0','86957','110440','2509728','2217','937','0','2767','113158',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2013','FORD TRANSIT 12 SEAT MINI BUS','mike smith','45610','0','449268','174927','0','1339','353767','0','0','100500','1079801','2367','1031','0','1227','45610',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2013','FORD TRANSIT 12 SEAT MINI BUS','Jane Smith','124750','0','457555','500864','0','0','344046','0','0','54809','1357274','1087','978','0','3186','124750',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','mike smith','61438','0','928589','234931','0','2633','50041','19992','74308','93110','1403604','2284','977','0','1374','61438',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','Jane Smith','52010','0','593281','113502','0','600','50041','0','0','91470','848894','1632','750','0','893','52010',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','mike smith','38827','0','445080','50899','0','1868','37147','0','0','75400','610394','1572','779','0','693','38827',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','Jane Smith','44166','0','507156','89081','0','3119','37147','0','0','75940','712443','1613','790','0','799','44166',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','mike smith','49870','0','566580','72421','0','2936','37147','0','0','76180','755264','1514','771','0','880','49870',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','Jane Smith','47449','0','504377','72160','0','0','37147','0','57000','75740','746424','1573','704','0','764','47449',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','mike smith','51100','0','577525','110752','0','17676','37147','0','43478','77480','864058','1690','779','0','911','51100',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI I30 GD HATCH','Jane Smith','95853','0','1101355','227221','0','13540','37147','0','0','79020','1458283','1521','773','0','1697','95853',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI ELANTRA','mike smith','62399','0','805192','87394','0','7954','19836','4172','195960','67110','1187618','1903','896','0','1280','62399',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','9343','0','71325','19912','0','0','7772','0','0','35790','134799','1442','563','0','120','9343',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','mike smith','22241','0','179149','18927','0','1834','7772','0','0','36630','244312','1098','592','0','302','22241',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','13309','0','112007','20306','0','217','7772','0','0','36190','176492','1326','630','0','192','13309',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2016','FORD TRANSIT 460E','Jane Smith','5768','0','45870','0','0','0','115959','0','0','23740','185569','3217','1194','0','179','5768',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2016','TOYOTA COROLLA GX CVT','mike smith','3342','0','27329','0','0','651','0','0','0','14360','42340','1266','611','0','47','3342',NULL);

    GO

    INSERT INTO VehicleAnalysisVCR

    VALUES('REGONO','CC','P',NULL,'FS','TE WANANGA O AOTEAROA','Jane Smith','PO Box 151','Te Awamutu',NULL,NULL,'3840','N','FG','122852','1377364','20161006','20161103','N',NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisCCR

    VALUES('REGONO','53506','64146','0','200','0','0','0','5000','122852','902848','191882','0','6386','27608','0','145000','103640','1377364','300',NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI ELANTRA','mike smith','66045','1965','36098','64146','0','200','0','0','0','2350','102794',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','24841','2113','17408','0','0','0','0','0','0','2350','19758',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2014','HYUNDAI ELANTRA','mike smith','66045','0','700187','172815','0','3901','19836','0','145000','66870','1108609','1678','743','0','1124','aaaa@aaa.com',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','24841','0','202661','19067','0','2485','7772','0','0','36770','268755','1081','610','0','347','24841',NULL);

    GO

    INSERT INTO VehicleAnalysisVCR

    VALUES('2563','CC','P',NULL,'FS','TE WANANGA O AOTEAROA','Jane Smith','PO Box 1191','Rotorua',NULL,NULL,'3040','N','FG','1130321','82335869','20161006','20161103','N',NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisCCR

    VALUES('30310-10100-3450','652432','224845','0','13322','133952','0','0','105770','1130321','38826499','21459929','0','269609','14441163','27705','2907958','4403006','82335869','4720',NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2002','FORD TRANSIT 12 SEATER MINI BU','mike smith','236644','2919','23832','38467','0','200','0','0','0','2350','64849',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2003','FORD TRANSIT 14 SEATER MINI BU','Jane Smith','283663','428','4819','23454','0','0','0','0','0','2350','30623',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2004','FORD TRANIST 14 SEATER MINI BU','mike smith','189070','1356','10271','-5403','0','0','55090','0','0','2350','62308',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','Jane Smith','100359','678','6395','1497','0','0','0','0','0','2350','10242',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','FORD TRANSIT 12 SEATER MINI BU','mike smith','172489','4471','52230','10113','0','0','799','0','0','2350','65492',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','111224','884','17177','0','0','0','0','0','0','2350','19527',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','168360','657','5963','0','0','0','0','0','0','2350','8313',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','Jane Smith','201169','2247','24852','0','0','0','0','0','0','2350','27202',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','110685','2223','19502','65670','0','0','55090','0','0','2350','142612',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','Jane Smith','106874','0','2459','0','0','0','0','0','0','2350','4809',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2009','FORD TRANSIT 12 SEATER MINI BU','mike smith','177821','1462','10876','0','0','0','15201','0','0','2350','28427',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2010','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','105633','1734','19876','0','0','0','0','0','0','2350','22226',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','mike smith','96410','3234','35407','0','0','0','0','0','0','2350','37757',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','Jane Smith','98295','1030','9142','0','0','851','0','0','0','2350','12343',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','FORD TRANSIT 12 SEATER MINI BU','mike smith','92845','863','7094','0','0','417','0','0','0','2350','9861',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','HYUNDAI i30 S/WAGON','Jane Smith','75086','2230','24284','0','0','800','0','0','0','2350','27434',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','HYUNDAI i30 S/WAGON','mike smith','67744','1036','11876','0','0','0','0','0','0','2350','14226',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','HYUNDAI I30 GD HATCH','Jane Smith','84596','1746','24888','0','0','0','0','0','0','2350','27238',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','NISSAN X-TRAIL ST-L CVT','mike smith','118762','1686','20887','0','0','434','0','0','0','2350','23671',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','NISSAN X-TRAIL ST-L CVT','Jane Smith','127299','0','18270','0','0','0','0','0','0','2350','20620',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','FORD TRANSIT 12 SEAT MINI BUS','mike smith','67998','1756','13568','997','0','1034','0','0','0','2350','17949',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2012','HYUNDAI I30 S/WAGON','Jane Smith','61362','2957','24568','997','0','1719','7772','0','0','2350','37406',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','mike smith','71198','2930','23910','0','0','0','0','0','0','2350','26260',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','Jane Smith','67012','1638','15662','0','0','834','0','0','0','2350','18846',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','mike smith','64208','1765','20467','0','0','434','0','0','0','2350','23251',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','Jane Smith','65408','2316','18893','25455','0','868','0','0','0','2350','47566',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','HYUNDAI I30 S/WAGON','mike smith','53770','1209','13612','0','0','0','0','0','0','2350','15962',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2013','FORD TRANSIT 12 SEAT MINI BUS','Jane Smith','67087','1398','10753','0','0','434','0','0','0','2350','13537',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI ELANTRA','mike smith','52816','2224','15662','39000','0','486','0','0','0','2350','57498',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 S/WAGON','Jane Smith','40935','898','8743','0','0','634','0','0','0','2350','11727',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 S/WAGON','mike smith','45839','2021','17093','0','0','217','0','0','0','2350','19660',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 S/WAGON','Jane Smith','42747','558','8913','24598','0','0','0','0','0','2350','35861',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','HYUNDAI I30 S/WAGON','mike smith','56448','1069','11488','0','0','217','0','0','0','2350','14055',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2014','TOYOTA HILUX 4WD','Jane Smith','85774','2447','12233','0','0','1919','0','0','0','2350','16502',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','mike smith','11063','0','10966','0','0','868','0','0','0','2350','14184',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','Jane Smith','29982','1175','9125','0','0','0','0','0','0','2350','11475',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','TOYOTA COROLLA GX CVT','mike smith','11899','0','13464','0','0','0','0','0','0','2350','15814',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','FORD TRANSIT 460E','Jane Smith','11072','840','5550','0','0','0','0','0','0','2350','7900',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','FORD TRANSIT 460E','mike smith','11599','0','4939','0','0','0','0','0','0','2350','7289',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2015','FORD TRANSIT 460E','Jane Smith','10964','1562','12313','0','0','956','0','0','0','2350','15619',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2016','FORD TRANSIT 460E','mike smith','10523','825','5679','0','0','0','0','0','0','2350','8029',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2016','TOYOTA COROLLA','Jane Smith','3457','787','7433','0','0','0','0','0','0','2350','9783',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisBPT

    VALUES('REGONO','2016','TOYOTA COROLLA GX CVT','mike smith','7725','2205','17298','0','0','0','0','0','0','2350','19648',NULL,NULL,NULL,NULL,NULL,NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2002','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','236644','0','1351466','1973002','0','2817','889950','2645','86957','185228','4492065','1898','602','0','3719','236644',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2003','FORD TRANSIT 14 SEATER MINI BU','mike smith','283663','0','2902868','3129074','0','817','1450127','13398','0','262253','7758537','2735','1093','0','8091','283663',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2004','FORD TRANIST 14 SEATER MINI BU','Jane Smith','189070','0','2247051','2738663','0','3906','1195899','0','90000','253002','6528521','3452','1146','0','5652','189070',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','100359','0','1335157','622826','0','3137','733622','0','110435','181356','2986533','2975','1218','0','3191','100359',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','172489','0','993361','1484553','0','434','559880','0','0','69470','3107698','1801','1168','0','2736','172489',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','FORD TRANSIT 12 SEATER MINI BU','mike smith','111224','0','354252','261790','0','3472','344880','0','43478','65830','1073702','965','1092','0','1212','111224',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','Jane Smith','168360','0','2290154','737281','0','5774','989353','0','372125','185417','4580104','2720','1231','0','5407','168360',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','201169','0','2896117','1025484','0','1519','1187277','0','0','185694','5296091','2632','1253','0','6577','201169',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','Jane Smith','110685','0','1498214','977385','0','635','786031','0','331170','181225','3774660','3410','1270','0','3668','110685',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2008','NISSAN URVAN MINIBUS','mike smith','106874','0','1335149','673029','0','0','730143','11662','260846','180215','3191044','2985','1168','0','3257','106874',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2009','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','177821','0','1910789','1133513','0','217','987692','0','268000','160704','4460915','2508','958','0','4444','177821',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2010','FORD TRANSIT 12 SEATER MINI BU','mike smith','105633','0','1291990','923750','0','2555','665957','0','157457','139277','3180986','3011','1109','0','3056','105633',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','FORD TRANSIT 12 SEATER MINI BU','Jane Smith','96410','0','1219443','638710','0','3472','612510','0','181751','138650','2794536','2898','1115','0','2806','96410',NULL);

    GO

    INSERT INTO VehicleAnalysisCTD

    VALUES('REGONO','2011','HYUNDAI i30 S/WAGON','mike smith','98295','0','1359731','284817','0','27252','98949','0','0','130160','1900909','1933','842','0','1895','98295',NULL);

    GO

    This results in the following errors (which doesn't surprise me, as the BPT table ModelYear and VehicleDesc are column 5 and 6 yet are 2 and 3 in the sample data):

    Msg 213, Level 16, State 1, Line 4

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 8

    Column name or number of supplied values does not match table definition.

    Msg 8152, Level 16, State 14, Line 12

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 16

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 20

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 24

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 28

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 32

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 36

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 40

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 44

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 48

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 52

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 56

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 60

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 64

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 68

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 72

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 76

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 80

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 84

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 88

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 92

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 96

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 100

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 104

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 108

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 112

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 116

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 120

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 124

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 128

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 132

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 136

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 140

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 144

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 213, Level 16, State 1, Line 148

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 152

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 156

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 160

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 164

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 168

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 172

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 176

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 180

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 184

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 188

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 192

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 196

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 200

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 204

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 208

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 212

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 216

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 220

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 224

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 228

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 232

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 236

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 240

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 244

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 248

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 252

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 256

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 260

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 264

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 268

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 272

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 276

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 280

    Column name or number of supplied values does not match table definition.

    Msg 208, Level 16, State 1, Line 284

    Invalid object name 'VehicleAnalysisVCR'.

    Msg 213, Level 16, State 1, Line 288

    Column name or number of supplied values does not match table definition.

    Msg 8152, Level 16, State 14, Line 292

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 296

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 213, Level 16, State 1, Line 300

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 304

    Column name or number of supplied values does not match table definition.

    Msg 208, Level 16, State 1, Line 308

    Invalid object name 'VehicleAnalysisVCR'.

    Msg 213, Level 16, State 1, Line 312

    Column name or number of supplied values does not match table definition.

    Msg 8152, Level 16, State 14, Line 316

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 320

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 324

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 328

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 332

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 336

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 340

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 344

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 348

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 352

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 356

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 360

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 364

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 368

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 372

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 376

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 380

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 384

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 388

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 392

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 396

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 400

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 404

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 408

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 412

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 416

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 420

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 424

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 428

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 432

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 436

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 440

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 444

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 448

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 452

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 456

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 460

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 464

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 468

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 472

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 476

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 480

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 8152, Level 16, State 14, Line 484

    String or binary data would be truncated.

    The statement has been terminated.

    Msg 213, Level 16, State 1, Line 488

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 492

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 496

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 500

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 504

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 508

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 512

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 516

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 520

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 524

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 528

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 532

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 536

    Column name or number of supplied values does not match table definition.

    Msg 213, Level 16, State 1, Line 540

    Column name or number of supplied values does not match table definition.

    Can you provide some valid INSERT statements?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Insert statements;

    INSERT INTO [dbo].[VehicleAnalysisVCR]

    ([VCRRecType]

    ,[VCRCustNo]

    ,[VCRCostCentre]

    ,[VCRCollationFlag]

    ,[VCRPrintFlag]

    ,[VCRNotUsed]

    ,[VCRImageFlag]

    ,[VCRTradingName]

    ,[VCRContactName]

    ,[VCRAddress1]

    ,[VCRAddress2]

    ,[VCRAddress3]

    ,[VCRAddress4]

    ,[VCROfficeFlag]

    ,[VCRGrandTotalPeriod]

    ,[VCRGrandTotalCosttoDate]

    ,[VCRBillFromDate]

    ,[VCRBillToDate]

    ,[VCRReprintFlag]

    ,[VCRemailAddress]

    ,[Parent])

    VALUES

    (<VCRRecType, varchar(3),>

    ,<VCRCustNo, int,>

    ,<VCRCostCentre, varchar(max),>

    ,<VCRCollationFlag, varchar(2),>

    ,<VCRPrintFlag, varchar(10),>

    ,<VCRNotUsed, varchar(1),>

    ,<VCRImageFlag, varchar(2),>

    ,<VCRTradingName, varchar(60),>

    ,<VCRContactName, varchar(40),>

    ,<VCRAddress1, varchar(100),>

    ,<VCRAddress2, varchar(100),>

    ,<VCRAddress3, varchar(100),>

    ,<VCRAddress4, varchar(100),>

    ,<VCROfficeFlag, varchar(1),>

    ,<VCRGrandTotalPeriod, money,>

    ,<VCRGrandTotalCosttoDate, float,>

    ,<VCRBillFromDate, varchar(1),>

    ,<VCRBillToDate, varchar(1),>

    ,<VCRReprintFlag, varchar(1),>

    ,<VCRemailAddress, varchar(8000),>

    ,<Parent, int,>)

    GO

    INSERT INTO [dbo].[VehicleAnalysisCCR]

    ([CCRRecType]

    ,[CCRCustno]

    ,[CCRCostCentre]

    ,[CCRBPSubtotalFuel]

    ,[CCRSBPubTotalRepairs]

    ,[CCRBPSubTotalLease]

    ,[CCRBPSubtotaltoll]

    ,[CCRBPSubTotalRUCRego]

    ,[CCRBPSubTotalOtherExp]

    ,[CCRBPSubtotalAccident]

    ,[CCRBPSubToalMgmt]

    ,[CCRBPSubTotal]

    ,[CCRCTDSubtotalFuelOil]

    ,[CCRCTDSubtotalRepairsandMaint]

    ,[CCRCTDSubtotalLeaseCharges]

    ,[CCRCTDSubtotalTollRoadCharges]

    ,[CCRCTDSubtotalRUCandRego]

    ,[CCRCTDSubtotalOtherExpenses]

    ,[CCRCTDSubtotalAccidentInsurance]

    ,[CCRCTDSubtotalMgmtCharges]

    ,[CCRCTDSubtotalTotal]

    ,[CCRTransactionFees]

    ,[Parent])

    VALUES

    (<CCRRecType, varchar(3),>

    ,<CCRCustno, int,>

    ,<CCRCostCentre, varchar(100),>

    ,<CCRBPSubtotalFuel, money,>

    ,<CCRSBPubTotalRepairs, money,>

    ,<CCRBPSubTotalLease, money,>

    ,<CCRBPSubtotaltoll, money,>

    ,<CCRBPSubTotalRUCRego, money,>

    ,<CCRBPSubTotalOtherExp, money,>

    ,<CCRBPSubtotalAccident, money,>

    ,<CCRBPSubToalMgmt, money,>

    ,<CCRBPSubTotal, money,>

    ,<CCRCTDSubtotalFuelOil, float,>

    ,<CCRCTDSubtotalRepairsandMaint, float,>

    ,<CCRCTDSubtotalLeaseCharges, float,>

    ,<CCRCTDSubtotalTollRoadCharges, float,>

    ,<CCRCTDSubtotalRUCandRego, float,>

    ,<CCRCTDSubtotalOtherExpenses, float,>

    ,<CCRCTDSubtotalAccidentInsurance, float,>

    ,<CCRCTDSubtotalMgmtCharges, float,>

    ,<CCRCTDSubtotalTotal, float,>

    ,<CCRTransactionFees, float,>

    ,<Parent, int,>)

    GO

    INSERT INTO [dbo].[VehicleAnalysisBPT]

    ([DossierNo]

    ,[CostCentre]

    ,[RecType]

    ,[ModelYear]

    ,[VehicleDesc]

    ,[DriverName]

    ,[Odometer]

    ,[Span(Kms)]

    ,[Fuel/Oil]

    ,[Repairs/Maintenance]

    ,[LeaseCharges]

    ,[TollRoadCharges]

    ,[RUC/Rego]

    ,[OtherExpenses]

    ,[AccidentInsurance]

    ,[ManagementCharges]

    ,[Total]

    ,[regnno]

    ,[CopyofTotal]

    ,[Parent]

    ,[custno])

    VALUES

    (<DossierNo, int,>

    ,<CostCentre, varchar(100),>

    ,<RecType, varchar(3),>

    ,<ModelYear, int,>

    ,<VehicleDesc, varchar(100),>

    ,<DriverName, varchar(100),>

    ,<Odometer, bigint,>

    ,<Span(Kms), bigint,>

    ,<Fuel/Oil, money,>

    ,<Repairs/Maintenance, money,>

    ,<LeaseCharges, money,>

    ,<TollRoadCharges, money,>

    ,<RUC/Rego, money,>

    ,<OtherExpenses, money,>

    ,<AccidentInsurance, money,>

    ,<ManagementCharges, money,>

    ,<Total, money,>

    ,<regnno, varchar(6),>

    ,<CopyofTotal, varchar(50),>

    ,<Parent, int,>

    ,<custno, int,>)

    GO

    INSERT INTO [dbo].[VehicleAnalysisCTD]

    ([DossierNo]

    ,[CostCentre]

    ,[RecType]

    ,[ModelYear]

    ,[VehicleDesc]

    ,[DriverName]

    ,[Odometer]

    ,[Span(Kms)]

    ,[Fuel/Oil]

    ,[Repairs/Maintenance]

    ,[LeaseCharges]

    ,[TollRoadCharges]

    ,[RUC/Rego]

    ,[OtherExpenses]

    ,[AccidentInsurance]

    ,[ManagementCharges]

    ,[regnno]

    ,[Total]

    ,[TotalCentsPerKm]

    ,[LitresPerVehicle]

    ,[LitresNationalAverage]

    ,[CO2OutputByQty]

    ,[TotalKmsToDate]

    ,[CopyofTotal]

    ,[Parent]

    ,[custno])

    VALUES

    (<DossierNo, int,>

    ,<CostCentre, varchar(100),>

    ,<RecType, varchar(3),>

    ,<ModelYear, int,>

    ,<VehicleDesc, varchar(100),>

    ,<DriverName, varchar(100),>

    ,<Odometer, bigint,>

    ,<Span(Kms), bigint,>

    ,<Fuel/Oil, money,>

    ,<Repairs/Maintenance, money,>

    ,<LeaseCharges, money,>

    ,<TollRoadCharges, money,>

    ,<RUC/Rego, money,>

    ,<OtherExpenses, money,>

    ,<AccidentInsurance, money,>

    ,<ManagementCharges, money,>

    ,<regnno, varchar(6),>

    ,<Total, money,>

    ,<TotalCentsPerKm, money,>

    ,<LitresPerVehicle, int,>

    ,<LitresNationalAverage, int,>

    ,<CO2OutputByQty, int,>

    ,<TotalKmsToDate, bigint,>

    ,<CopyofTotal, varchar(50),>

    ,<Parent, int,>

    ,<custno, int,>)

    GO

    Attached is sample data

  • ringovski (1/10/2017)


    Insert statements;

    INSERT INTO [dbo].[VehicleAnalysisVCR]

    ([VCRRecType]

    ,[VCRCustNo]

    ,[VCRCostCentre]

    ,[VCRCollationFlag]

    ,[VCRPrintFlag]

    ,[VCRNotUsed]

    ,[VCRImageFlag]

    ,[VCRTradingName]

    ,[VCRContactName]

    ,[VCRAddress1]

    ,[VCRAddress2]

    ,[VCRAddress3]

    ,[VCRAddress4]

    ,[VCROfficeFlag]

    ,[VCRGrandTotalPeriod]

    ,[VCRGrandTotalCosttoDate]

    ,[VCRBillFromDate]

    ,[VCRBillToDate]

    ,[VCRReprintFlag]

    ,[VCRemailAddress]

    ,[Parent])

    VALUES

    (<VCRRecType, varchar(3),>

    ,<VCRCustNo, int,>

    ,<VCRCostCentre, varchar(max),>

    ,<VCRCollationFlag, varchar(2),>

    ,<VCRPrintFlag, varchar(10),>

    ,<VCRNotUsed, varchar(1),>

    ,<VCRImageFlag, varchar(2),>

    ,<VCRTradingName, varchar(60),>

    ,<VCRContactName, varchar(40),>

    ,<VCRAddress1, varchar(100),>

    ,<VCRAddress2, varchar(100),>

    ,<VCRAddress3, varchar(100),>

    ,<VCRAddress4, varchar(100),>

    ,<VCROfficeFlag, varchar(1),>

    ,<VCRGrandTotalPeriod, money,>

    ,<VCRGrandTotalCosttoDate, float,>

    ,<VCRBillFromDate, varchar(1),>

    ,<VCRBillToDate, varchar(1),>

    ,<VCRReprintFlag, varchar(1),>

    ,<VCRemailAddress, varchar(8000),>

    ,<Parent, int,>)

    GO

    INSERT INTO [dbo].[VehicleAnalysisCCR]

    ([CCRRecType]

    ,[CCRCustno]

    ,[CCRCostCentre]

    ,[CCRBPSubtotalFuel]

    ,[CCRSBPubTotalRepairs]

    ,[CCRBPSubTotalLease]

    ,[CCRBPSubtotaltoll]

    ,[CCRBPSubTotalRUCRego]

    ,[CCRBPSubTotalOtherExp]

    ,[CCRBPSubtotalAccident]

    ,[CCRBPSubToalMgmt]

    ,[CCRBPSubTotal]

    ,[CCRCTDSubtotalFuelOil]

    ,[CCRCTDSubtotalRepairsandMaint]

    ,[CCRCTDSubtotalLeaseCharges]

    ,[CCRCTDSubtotalTollRoadCharges]

    ,[CCRCTDSubtotalRUCandRego]

    ,[CCRCTDSubtotalOtherExpenses]

    ,[CCRCTDSubtotalAccidentInsurance]

    ,[CCRCTDSubtotalMgmtCharges]

    ,[CCRCTDSubtotalTotal]

    ,[CCRTransactionFees]

    ,[Parent])

    VALUES

    (<CCRRecType, varchar(3),>

    ,<CCRCustno, int,>

    ,<CCRCostCentre, varchar(100),>

    ,<CCRBPSubtotalFuel, money,>

    ,<CCRSBPubTotalRepairs, money,>

    ,<CCRBPSubTotalLease, money,>

    ,<CCRBPSubtotaltoll, money,>

    ,<CCRBPSubTotalRUCRego, money,>

    ,<CCRBPSubTotalOtherExp, money,>

    ,<CCRBPSubtotalAccident, money,>

    ,<CCRBPSubToalMgmt, money,>

    ,<CCRBPSubTotal, money,>

    ,<CCRCTDSubtotalFuelOil, float,>

    ,<CCRCTDSubtotalRepairsandMaint, float,>

    ,<CCRCTDSubtotalLeaseCharges, float,>

    ,<CCRCTDSubtotalTollRoadCharges, float,>

    ,<CCRCTDSubtotalRUCandRego, float,>

    ,<CCRCTDSubtotalOtherExpenses, float,>

    ,<CCRCTDSubtotalAccidentInsurance, float,>

    ,<CCRCTDSubtotalMgmtCharges, float,>

    ,<CCRCTDSubtotalTotal, float,>

    ,<CCRTransactionFees, float,>

    ,<Parent, int,>)

    GO

    INSERT INTO [dbo].[VehicleAnalysisBPT]

    ([DossierNo]

    ,[CostCentre]

    ,[RecType]

    ,[ModelYear]

    ,[VehicleDesc]

    ,[DriverName]

    ,[Odometer]

    ,[Span(Kms)]

    ,[Fuel/Oil]

    ,[Repairs/Maintenance]

    ,[LeaseCharges]

    ,[TollRoadCharges]

    ,[RUC/Rego]

    ,[OtherExpenses]

    ,[AccidentInsurance]

    ,[ManagementCharges]

    ,[Total]

    ,[regnno]

    ,[CopyofTotal]

    ,[Parent]

    ,[custno])

    VALUES

    (<DossierNo, int,>

    ,<CostCentre, varchar(100),>

    ,<RecType, varchar(3),>

    ,<ModelYear, int,>

    ,<VehicleDesc, varchar(100),>

    ,<DriverName, varchar(100),>

    ,<Odometer, bigint,>

    ,<Span(Kms), bigint,>

    ,<Fuel/Oil, money,>

    ,<Repairs/Maintenance, money,>

    ,<LeaseCharges, money,>

    ,<TollRoadCharges, money,>

    ,<RUC/Rego, money,>

    ,<OtherExpenses, money,>

    ,<AccidentInsurance, money,>

    ,<ManagementCharges, money,>

    ,<Total, money,>

    ,<regnno, varchar(6),>

    ,<CopyofTotal, varchar(50),>

    ,<Parent, int,>

    ,<custno, int,>)

    GO

    INSERT INTO [dbo].[VehicleAnalysisCTD]

    ([DossierNo]

    ,[CostCentre]

    ,[RecType]

    ,[ModelYear]

    ,[VehicleDesc]

    ,[DriverName]

    ,[Odometer]

    ,[Span(Kms)]

    ,[Fuel/Oil]

    ,[Repairs/Maintenance]

    ,[LeaseCharges]

    ,[TollRoadCharges]

    ,[RUC/Rego]

    ,[OtherExpenses]

    ,[AccidentInsurance]

    ,[ManagementCharges]

    ,[regnno]

    ,[Total]

    ,[TotalCentsPerKm]

    ,[LitresPerVehicle]

    ,[LitresNationalAverage]

    ,[CO2OutputByQty]

    ,[TotalKmsToDate]

    ,[CopyofTotal]

    ,[Parent]

    ,[custno])

    VALUES

    (<DossierNo, int,>

    ,<CostCentre, varchar(100),>

    ,<RecType, varchar(3),>

    ,<ModelYear, int,>

    ,<VehicleDesc, varchar(100),>

    ,<DriverName, varchar(100),>

    ,<Odometer, bigint,>

    ,<Span(Kms), bigint,>

    ,<Fuel/Oil, money,>

    ,<Repairs/Maintenance, money,>

    ,<LeaseCharges, money,>

    ,<TollRoadCharges, money,>

    ,<RUC/Rego, money,>

    ,<OtherExpenses, money,>

    ,<AccidentInsurance, money,>

    ,<ManagementCharges, money,>

    ,<regnno, varchar(6),>

    ,<Total, money,>

    ,<TotalCentsPerKm, money,>

    ,<LitresPerVehicle, int,>

    ,<LitresNationalAverage, int,>

    ,<CO2OutputByQty, int,>

    ,<TotalKmsToDate, bigint,>

    ,<CopyofTotal, varchar(50),>

    ,<Parent, int,>

    ,<custno, int,>)

    GO

    Attached is sample data

    We need to Sample data in a digestible/usable format. Please provide them in INSERT statements.

    If you're unsure how, have a look at the link in my signature.

    Thanks

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Apologies for the bad format, attached is the DDL & DML Statements.

  • I finally managed to resolve the required format issue.

    I built two tables one for the headers row VCR\CCR and one for lines BTP\CTD. If you are interested they were like this;

    Header Table

    Head_ID1, VCR|||||||||||||CCR||||||||||||||||||

    Head_ID2, VCR|||||||||||||CCR||||||||||||||||||

    Lines Table

    Line_id1, BTP|||||||||||||

    Line_id2, BTP|||||||||||||

    Line_id3, BTP|||||||||||||

    Line_id4, BTP|||||||||||||

    Line_id5, BTP|||||||||||||

    etc...

    Line_id201, CTD|||||||||||||

    Line_id202, CTD|||||||||||||

    Line_id203, CTD|||||||||||||

    etc...

    Then I used the following code and it works correctly.

    ;WITH CTEHead([VCRRecType],[VCRCustNo],[VCRCollationFlag],[VCRPrintFlag],[VCRNotUsed],[VCRImageFlag],[VCRTradingName],[VCRContactName],[VCRAddress1],[VCRAddress2],[VCRAddress3],[VCRAddress4],[VCRPostCode],[VCROfficeFlag],[CardPlanID],[VCRGrandTotalPeriod],

    [VCRGrandTotalCosttoDate],[VCRBillFromDate],[VCRBillToDate],[VCRReprintFlag],[VCRemailAddress],

    CCRRecType,CCRCostCentre,CCRBPSubtotalFuel,CCRSBPubTotalRepairs,CCRBPSubTotalLease, CCRBPSubtotaltoll,CCRBPSubTotalRUCRego,CCRBPSubTotalOtherExp,CCRBPSubtotalAccident,CCRBPSubToalMgmt,CCRBPSubTotal,CCRCTDSubtotalFuelOil,CCRCTDSubtotalRepairsandMaint,

    CCRCTDSubtotalLeaseCharges,CCRCTDSubtotalTollRoadCharges,CCRCTDSubtotalRUCandRego,CCRCTDSubtotalOtherExpenses,CCRCTDSubtotalAccidentInsurance,CCRCTDSubtotalMgmtCharges,CCRCTDSubtotalTotal,CCRTransactionFees,Rowid,

    [dRecType],dregono,dmodelyear,dvehicledesc,ddrivername,dodometer,[dSpan(Kms)],[dFuel/Oil],[dRepairs/Maintenance],dLeaseCharges,[dTollRoadCharges],[dRUC/Rego],dOtherExpenses,[dAccidentInsurance],[dManagementCharges],

    [dTotal],[dTotalCentsPerKm],[dLitresPerVehicle],[dLitresNationalAverage],[dCO2OutputByQty],[dTotalKmsToDate],subrow )

    AS(

    SELECT DISTINCT

    [VCRRecType],

    [VCRCustNo],

    [VCRCollationFlag],

    [VCRPrintFlag],

    [VCRNotUsed],

    [VCRImageFlag],

    [VCRTradingName],

    [VCRContactName],

    [VCRAddress1],

    [VCRAddress2],

    [VCRAddress3],

    [VCRAddress4],

    '' [PostCode],

    [VCROfficeFlag],

    '' [CardPlanID],

    [VCRGrandTotalPeriod],

    [VCRGrandTotalCosttoDate],

    [VCRBillFromDate],

    [VCRBillToDate],

    [VCRReprintFlag],

    [VCRemailAddress],

    CHAR(13)+CHAR(10)+ CCRRecType as [CCRRecType],

    --CCRCustno,

    CCRCostCentre,

    CCRBPSubtotalFuel,

    CCRSBPubTotalRepairs,

    CCRBPSubTotalLease,

    CCRBPSubtotaltoll,

    CCRBPSubTotalRUCRego,

    CCRBPSubTotalOtherExp,

    CCRBPSubtotalAccident,

    CCRBPSubToalMgmt,

    CCRBPSubTotal,

    CCRCTDSubtotalFuelOil,

    CCRCTDSubtotalRepairsandMaint,

    CCRCTDSubtotalLeaseCharges,

    CCRCTDSubtotalTollRoadCharges,

    CCRCTDSubtotalRUCandRego,

    CCRCTDSubtotalOtherExpenses,

    CCRCTDSubtotalAccidentInsurance,

    CCRCTDSubtotalMgmtCharges,

    CCRCTDSubtotalTotal,

    CCRTransactionFees,

    ROW_NUMBER() OVER ( PARTITION BY Head_id ORDER BY head_id,lines_id ASC ) as Rowid,

    CHAR(13)+CHAR(10) + d.[RecType] as [LRecType],

    d.regnno,

    --d.CostCentre,

    --d.custno,

    d.modelyear,

    d.vehicledesc,

    d.drivername,

    d.odometer,

    d.[Span(Kms)],

    d.[Fuel/Oil],

    d.[Repairs/Maintenance],

    d.LeaseCharges,

    d.[TollRoadCharges],

    d.[RUC/Rego],

    d.OtherExpenses,

    d.[AccidentInsurance],

    d.[ManagementCharges],

    d.[Total],

    d.[TotalCentsPerKm],

    d.[LitresPerVehicle],

    d.[LitresNationalAverage],

    d.[CO2OutputByQty],

    d.[TotalKmsToDate],

    d.subrow

    FROM [dbo].[VehicleAnalysis_Header] H

    JOIN [dbo].[VehicleAnalysisLines] d

    ON H.VCRCustNo = d.custno and H.CCRCostCentre = d.CostCentre

    )

    SELECT

    CASE WHEN RowId = 1 THEN [VCRRecType]

    ELSE '' END AS [VCRRecType]

    ,CASE WHEN RowId = 1 THEN [VCRCustNo]

    ELSE '' END AS [VCRCustNo]

    ,CASE WHEN RowId = 1 THEN [VCRCollationFlag]

    ELSE '' END AS [VCRCollationFlag]

    ,CASE WHEN RowId = 1 THEN [VCRPrintFlag]

    ELSE '' END AS [VCRPrintFlag]

    ,CASE WHEN RowId = 1 THEN [VCRNotUsed]

    ELSE '' END AS [VCRNotUsed]

    ,CASE WHEN RowId = 1 THEN [VCRImageFlag]

    ELSE '' END AS [VCRImageFlag]

    ,CASE WHEN RowId = 1 THEN [VCRTradingName]

    ELSE '' END AS [VCRTradingName]

    ,CASE WHEN RowId = 1 THEN [VCRContactName]

    ELSE '' END AS [VCRContactName]

    ,CASE WHEN RowId = 1 THEN [VCRAddress1]

    ELSE '' END AS [VCRAddress1]

    ,CASE WHEN RowId = 1 THEN [VCRAddress2]

    ELSE '' END AS [VCRAddress2]

    ,CASE WHEN RowId = 1 THEN [VCRAddress3]

    ELSE '' END AS [VCRAddress3]

    ,CASE WHEN RowId = 1 THEN [VCRAddress4]

    ELSE '' END AS [VCRAddress4]

    ,CASE WHEN RowId = 1 THEN [VCRPostCode]

    ELSE '' END AS [VCRPostCode]

    ,CASE WHEN RowId = 1 THEN [VCROfficeFlag]

    ELSE '' END AS [VCROfficeFlag]

    ,CASE WHEN RowId = 1 THEN [CardPlanID]

    ELSE '' END AS [CardPlanId]

    ,CASE WHEN RowId = 1 THEN [VCRGrandTotalPeriod]

    ELSE '' END AS [VCRGrandTotalPeriod]

    ,CASE WHEN RowId = 1 THEN [VCRGrandTotalCosttoDate]

    ELSE '' END AS [VCRGrandTotalCosttoDate]

    ,CASE WHEN RowId = 1 THEN [VCRBillFromDate]

    ELSE '' END AS [VCRBillFromDate]

    ,CASE WHEN RowId = 1 THEN [VCRBillToDate]

    ELSE '' END AS [VCRBillToDate]

    ,CASE WHEN RowId = 1 THEN [VCRReprintFlag]

    ELSE '' END AS [VCRReprintFlag]

    ,CASE WHEN RowId = 1 THEN [VCRemailAddress]

    ELSE '' END AS [VCRemailAddress]

    ,CASE WHEN RowId = 1 THEN [CCRRecType]

    ELSE '' END AS [CCRRecType]

    ,CASE WHEN RowId = 1 THEN CCRCostCentre

    ELSE '' END AS CCRCostCentre

    ,CASE WHEN RowId = 1 THEN CCRBPSubtotalFuel

    ELSE '' END AS CCRBPSubtotalFuel

    ,CASE WHEN RowId = 1 THEN CCRSBPubTotalRepairs

    ELSE '' END AS CCRSBPubTotalRepairs

    ,CASE WHEN RowId = 1 THEN CCRBPSubTotalLease

    ELSE '' END AS CCCRBPSubTotalLease

    ,CASE WHEN RowId = 1 THEN CCRBPSubtotaltoll

    ELSE '' END AS CCRBPSubtotaltoll

    ,CASE WHEN RowId = 1 THEN CCRBPSubTotalRUCRego

    ELSE '' END AS CCRBPSubTotalRUCRego

    ,CASE WHEN RowId = 1 THEN CCRBPSubTotalOtherExp

    ELSE '' END AS CCRBPSubTotalOtherExp

    ,CASE WHEN RowId = 1 THEN CCRBPSubtotalAccident

    ELSE '' END AS CCRBPSubtotalAccident

    ,CASE WHEN RowId = 1 THEN CCRBPSubToalMgmt

    ELSE '' END AS CCRBPSubToalMgmt

    ,CASE WHEN RowId = 1 THEN CCRBPSubTotal

    ELSE '' END AS CCRBPSubToalMgmt

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalFuelOil

    ELSE '' END AS CCRCTDSubtotalFuelOil

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalRepairsandMaint

    ELSE '' END AS CCRCTDSubtotalFuelOil

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalLeaseCharges

    ELSE '' END AS CCRCTDSubtotalFuelOil

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalTollRoadCharges

    ELSE '' END AS CCRCTDSubtotalTollRoadCharges

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalRUCandRego

    ELSE '' END AS CCRCTDSubtotalRUCandRego

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalOtherExpenses

    ELSE '' END AS CCRCTDSubtotalOtherExpenses

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalAccidentInsurance

    ELSE '' END AS CCRCTDSubtotalAccidentInsurance

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalMgmtCharges

    ELSE '' END AS CCRCTDSubtotalMgmtCharges

    ,CASE WHEN RowId = 1 THEN CCRCTDSubtotalTotal

    ELSE '' END AS CCRCTDSubtotalTotal

    ,CASE WHEN RowId = 1 THEN CCRTransactionFees

    ELSE '' END AS CCRTransactionFees

    -- ,rowid

    ,[dRecType]

    ,dregono

    ,dmodelyear

    ,dvehicledesc

    ,ddrivername

    ,dodometer

    ,[dSpan(Kms)]

    ,[dFuel/Oil]

    ,[dRepairs/Maintenance]

    ,dLeaseCharges

    ,[dTollRoadCharges]

    ,[dRUC/Rego]

    ,dOtherExpenses

    ,[dAccidentInsurance]

    ,[dManagementCharges]

    ,[dTotal]

    ,[dTotalCentsPerKm]

    ,[dLitresPerVehicle]

    ,[dLitresNationalAverage]

    ,[dCO2OutputByQty]

    ,[dTotalKmsToDate]

    --,[subrow]

    FROM CTEHEAD H

    Thanks for all the replies.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply