January 4, 2017 at 10:54 pm
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.
January 5, 2017 at 2:04 am
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
January 5, 2017 at 2:54 pm
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, |||||||||
January 5, 2017 at 10:16 pm
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?
January 5, 2017 at 10:51 pm
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
January 8, 2017 at 2:24 pm
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
January 8, 2017 at 2:40 pm
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
January 10, 2017 at 3:23 pm
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
January 11, 2017 at 1:56 am
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
January 11, 2017 at 6:32 pm
Apologies for the bad format, attached is the DDL & DML Statements.
January 11, 2017 at 10:16 pm
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