Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Problem creating a query for a report

Author  Topic 

Paul76
Starting Member

5 Posts
Posted - 2011-04-11 : 16:22:41
First off I am a newbie so sorry if I get the terminology wrong here. I am trying to create a query for a report and I can’t figure out how to do it. The data for the report is derived from three tables unithistory, ordertable, and model. There are ten columns in the report. Columns one through three is what I want the rows grouped by which are manufacturer, date, and vehicle model year. The other seven columns are counts of different data using each grouped row. The other columns are orders, cancelled orders, shipped orders, received units, warranty repaired units, c/p repaired units, total units repaired. The problem I am running into is that the count columns (four through ten) use different date fields within two tables to make their counts. There are four different date fields used to parse the count data. I want all counts to be returned in one row per a specific date. As an example say I have 20 orders shipped on 2/22/2011, 3 cancelled orders on 2/22/2011, and 200 units received on 2/22/2011 lets assume all these transaction have the same manufacturer = “Ford” and model year = “2011”, I want counts returned on one row per Ford, 2/22/2011, and 2011. The problem once again is that different date fields are used to derive orders, cancelled orders, and received orders. Could someone point me in the right direction on how to figure this out?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-11 : 20:43:27
Paul it's very hard to offer any useful suggestions without seeing the structure of the table - columns, how the tables relate to each other etc. So if you can post the DDL for the tables and some sample data, I am sure someone will respond. Brett's page http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx has a description of how to get the DDL for the tables.

Without the DDL, I can only describe how the query would be in general terms. First, you would join the 3 tables using some common criteria. The common criteria might be a manufacturer and model number, or it might be something else. In any case, once you join the tables, you can then calculate the counts by looking at the date in each table separately.
Go to Top of Page

Paul76
Starting Member

5 Posts
Posted - 2011-04-12 : 09:33:00
I was afraid I was being a bit to vauge with my description. Below are the table schema's and sample data.

-----------orderTable----------------------------------
/*-- -Object: table [hunt].[ordertable] -------*/
CREATE TABLE [hunt].[ordertable] (
[contractNoFrom] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[orderType] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[po] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orderNo] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orderTakenBy] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orderDateTime] [datetime] NULL,
[orderStatus] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dealerNo] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelNoOriginallyRequested] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VIN] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mileage] [int] NULL,
[inServiceDate] [datetime] NULL,
[warrantyType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sanityStatus] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_ordertable_sanityStatus] DEFAULT (0) NULL,
[releaseDateTime] [datetime] NULL,
[releasedBy] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelNoShipped] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[unitNo] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shipper] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shipCost] [money] NULL,
[shipDateTime] [datetime] NULL,
[rma] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ars] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[trackingNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[coreStatus] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[complaint] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[complaintCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AudioElectronicSN] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TrafficElectronicSN] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/*-- -Object: primary key [hunt].[ordertable].[PK_ordertable] -------*/
if not exists (select * from dbo.sysobjects where id = object_id(N'[PK_ordertable]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [hunt].[ordertable] ADD
CONSTRAINT [PK_ordertable] PRIMARY KEY CLUSTERED
(
[contractNoFrom]
) ON [PRIMARY]
GO



SET XACT_ABORT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF


BEGIN TRANSACTION

DECLARE @error INT
DECLARE @ptrBinary varbinary(16)

-- --Table: [hunt].[ordertable]
-- --Insert(s)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('00000', 'INT', 'JIMS REPAIR', '98NS', 'AMYH', '2008-12-01T07:36:56.000', 'A', '45219', '04801062AE', '04801062AE', '1C4GP54G2WB596174', 110687, '2008-12-01T00:00:00.000', '0', '0', '2008-12-01T07:36:56.000', 'AMYH', 'P04801062AE', 'HNT00431576', 'kbrown', 16.00, '2008-12-01T14:13:59.000', '00000', '1ze4908hj613663845', '1Z5Ehj841345444798', '0', 'LIGHTING AND LOCKS NOT WORKING RIGHT', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000000', 'INT', '22123', 'BRENDA CHASTAIN', 'AMYH', '2007-06-27T12:55:34.000', 'A', '45017', '04686492AF', '04686492AF', '2B4GP44G0YR523655', 123400, '1999-09-30T00:00:00.000', '0', '0', '2007-06-27T12:55:34.000', 'AMYH', 'P04686492AF', 'HNT00435141', 'chelsel', 16.00, '2007-06-27T14:17:13.000', '000000', '1ze49082hj2982618', '1Z5E7hj40144289052', '0', 'CUSTOMER STATES THE HEADLIGHTS ARE INOP', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000000001', 'INT', '69819', 'MARTIN', 'kbrown', '2010-03-15T08:13:09.000', 'A', '37694', 'Z5094031AF', 'Z5094031AF', '6R639009', 147357, '2006-01-02T00:00:00.000', '0', '0', '2010-03-15T08:13:09.000', 'kbrown', 'P05094031AD', 'HNT00446922', 'CHELSEL', 11.25, '2010-03-15T13:35:08.000', '000000001', '1ZE490820613865707', '1Z5E7E840345116170', '0', 'INOP', '4 DISC 03/24/10', '', '')
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000001', 'INT', '23071', 'LONDON AUTO SAL', 'AMYH', '2007-09-21T08:01:39.000', 'A', '45017', '04602410AO', '04602410AO', '2C3AE66G44H669388', 55250, '2003-06-17T00:00:00.000', '0', '0', '2007-09-21T08:01:39.000', 'AMYH', 'P04602410AO', 'HNT00415594', 'AHALL', 16.00, '2007-09-21T12:45:51.000', '0000001', '1ZE490820613506710', '1Z5E7E840145925497', '0', 'CUSTOMER STATES THE INTERIOR LIGHTS ARE NOT WORKING', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000001', 'INT', 'INTL', 'INTL', 'kbrown', '2005-07-15T11:03:14.000', 'A', '13623', '05069131AE', '05069131AE', '1J4GL48172W238089', 11593, '2005-06-15T00:00:00.000', '1', '0', '2005-07-15T11:03:14.000', 'kbrown', '05069131AE', 'HNT00354864', 'kbrown', 0.00, '2005-07-18T09:39:21.000', '000001', '8260460493', '8766958830', '0', 'INTERNAL DEFECT,STORED ENERGY FIRING,MALFUNCTION IND LAMP ON ,CORRODED', 'BCM', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000011', 'INT', 'BRIAN', 'KATHY STENSAAS', 'AMYH', '2007-08-07T10:22:48.000', 'A', '43316', 'Z4760296AC', 'Z4760296AC', '2B3HD56G52H299140', 15532, '2007-08-07T00:00:00.000', '0', '1', '2007-08-07T10:22:48.000', 'AMYH', 'P04760296AC', 'HNT00427750', 'gbussell', 16.00, '2007-08-07T11:36:04.000', '0000011', '1ZE490820613478966', '1Z5E7E840143444804', '0', 'RADIO STATIC', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000012', 'INT', '16837', 'HUBER', 'AMYH', '2007-08-07T15:51:05.000', 'A', '62301', 'Z5269463', 'Z5269463', '2P4GH55RXRR817955', 18087, '2007-08-07T00:00:00.000', '0', '1', '2007-08-07T15:51:05.000', 'AMYH', '5269463', 'HNT00438750', 'chelsel', 16.00, '2007-08-07T16:09:42.000', '0000012', '1ZE490820613479134', '1Z5E7E840144933006', '0', 'RADIO DOES NOT WORK', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000018', 'INT', 'BRIAN', 'PETES AUTO SERV', 'SCONKLIN', '2007-08-08T14:47:17.000', 'A', '43316', 'Z6038518AJ', 'Z6038518AJ', '2B4GP54LX1R165375', 67188, '2001-01-05T00:00:00.000', '0', '0', '2007-08-08T14:47:17.000', 'SCONKLIN', 'P56038518AJ', 'HNT00437982', 'chelsel', 16.00, '2007-08-08T15:18:16.000', '0000018', '1ZE490820613479438', '1Z5E7E840145409698', '0', 'RADIO WILL NOT WORK', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000002', 'INT', 'INTL', 'INTL', 'kbrown', '2005-08-29T11:32:25.000', 'A', '13623', '56045600AJ', '56045600AJ', '3D7KU28663G720507', 135, '2005-04-29T00:00:00.000', '1', '0', '2005-08-29T11:32:25.000', 'kbrown', 'P56045600AK', 'HNT00321828', 'kbrown', 0.00, '2005-08-29T12:26:51.000', '000002', '8260461484', '8778041893', '0', 'ELECTRONIC DISPLAY DEFECT', 'EIC', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000003', 'INT', 'INTL', '1385/05', 'twalsh', '2005-10-06T10:40:33.000', 'A', '13623', '56010540AF', '56010540AF', '1J4GL48595W654426', 3585, '2005-07-26T00:00:00.000', '1', '0', '2005-10-06T10:40:33.000', 'twalsh', 'P56010540AF', 'HNT00356234', 'kbrown', 0.00, '2005-10-06T15:55:28.000', '000003', '8260459561', '8792553652', '0', 'WRONG DISPLAY', 'EIC', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000004', 'INT', 'INTL', '1574/05', 'twalsh', '2005-11-18T12:07:42.000', 'A', '13623', '56010151AH', '56010151AH', '1J4GL48KX2W257924', 43850, '2002-01-11T00:00:00.000', '0', '0', '2005-11-18T12:07:42.000', 'twalsh', 'P56010151AI', 'HNT00359094', 'kbrown', 0.00, '2005-12-01T14:41:54.000', '000004', 'NA', '7761921985', '0', 'CLUSTER WAKE UP OUTPUT LOW', 'EIC', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000005', 'INT', 'INTL', '0375/04', 'twalsh', '2005-12-08T12:53:34.000', 'A', '13623', '56038406AM', '56042498AM', '1J4GW68N0XC780066', 23596, '2005-12-07T00:00:00.000', '0', '1', '2005-12-08T12:53:34.000', 'twalsh', '56042498AM', 'USDTR534542', 'twalsh', 0.00, '2005-12-08T15:56:40.000', '000005', '7787618506', '8816397295', '0', 'INTERNAL DEFECT', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000006', 'INT', 'INTL', '0448/06', 'twalsh', '2006-07-19T14:21:29.000', 'A', '13623', '04602284AH', '04602284AH', '2B3HD56J0WH131553', 29951, '1998-02-05T00:00:00.000', '0', '0', '2006-07-19T14:21:29.000', 'twalsh', 'P04602284AH', 'HNT00364175', 'twalsh', 0.00, '2006-07-20T14:09:15.000', '000006', '7874768985', '8893980756', '0', 'INTERNAL', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000007', 'INT', 'INTL', 'INTL', 'kbrown', '2006-08-15T08:52:58.000', 'A', '13623', '04759532AJ', '04759532AJ', '2C3HC56GXYH176058', 45540, '2000-12-01T00:00:00.000', '0', '0', '2006-08-15T08:52:58.000', 'kbrown', 'P04602282AH', 'HNT00390649', 'twalsh', 0.00, '2006-08-30T11:37:22.000', '000007', '7874769324', '8909080725', '0', 'INTERNAL DEFECT', 'BCM', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('00001', 'INT', 'JAKE', 'JAKE HOLLAND', 'AMYH', '2007-10-16T09:37:25.000', 'A', '66792', 'Z6038518AF', 'Z6038518AJ', '1R339626', 110008, '2001-07-09T00:00:00.000', '0', '0', '2007-10-16T09:37:25.000', 'AMYH', 'P56038518AJ', 'HNT00420126', 'AHALL', 16.00, '2007-10-16T13:03:19.000', '00001', '1ZE490820613497034', '1Z5E7E840143396474', '0', 'NO DISPLAY-SLIDERS FOR EQ LIGHT UP AND GO OFF ALL THE TIME', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000010', 'INT', 'INTL', '1238/06', 'twalsh', '2006-11-07T13:24:00.000', 'A', '13623', '56050231AI', '05083892AC', '1J4GW58N64C326474', 5801, '2003-12-29T00:00:00.000', '1', '0', '2006-11-07T13:24:00.000', 'twalsh', '05083892AH', 'HNT00404781', 'kbrown', 0.00, '2006-11-07T14:21:53.000', '000010', '7874770481', '8935431746', '0', 'INTERNAL DEFECT', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000011', 'INT', 'INTL', 'INTL', 'twalsh', '2007-01-16T08:56:39.000', 'A', '13623', '56050231AI', '05083892AH', '1J8GW58N24C423265', 1863, '2004-12-30T00:00:00.000', '1', '0', '2007-01-16T08:56:39.000', 'twalsh', '05083892AH', 'HNT00409411', 'twalsh', 0.00, '2007-01-16T10:51:49.000', '000011', '8081878652', '8961748670', '0', 'INTERNAL DEFECT', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000111', 'INT', 'HOLLAND', 'HOLLAND', 'AMYH', '2008-04-11T11:05:10.000', 'A', '66792', 'Z6038518AJ', 'Z6038518AJ', '2C8GP64L41R339626', 110000, '2001-07-09T00:00:00.000', '0', '0', '2008-04-11T11:05:10.000', 'AMYH', 'P56038518AJ', 'HNT00420160', 'gbussell', 16.00, '2008-04-11T15:00:39.000', '0000111', '1ze490820613570150', '1Z5E7E841343084289', '0', 'NO DISPLAY SLIDERS FOR EQ FLASH ALL THE TIME', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000012', 'INT', 'INTL', '1535', 'twalsh', '2007-01-16T08:57:35.000', 'A', '13623', '56044877AC', '56044877AC', '1J4GL48516W129622', 1568, '2006-02-20T00:00:00.000', '1', '0', '2007-01-16T08:57:35.000', 'twalsh', 'P56044877AC', 'HNT00379502', 'twalsh', 0.00, '2007-01-16T10:50:11.000', '000012', '8081878630', '8961748386', '0', 'WRONG KM DISPLAY', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000013', 'INT', 'INTL', '76606', 'twalsh', '2007-01-16T08:58:16.000', 'A', '13623', '56044903AI', '56044903AI', '1D7HW48K76S599397', 3, '2006-03-30T00:00:00.000', '1', '0', '2007-01-16T08:58:16.000', 'twalsh', 'P56044903AI', 'HNT00362092', 'twalsh', 0.00, '2007-01-16T10:51:12.000', '000013', '28081878663', '8961740174', '0', 'WRONG DISPLAY', 'XXX', null, null)
INSERT INTO [hunt].[ordertable] ([contractNoFrom], [orderType], [po], [orderNo], [orderTakenBy], [orderDateTime], [orderStatus], [dealerNo], [modelNoOriginallyRequested], [modelNo], [VIN], [mileage], [inServiceDate], [warrantyType], [sanityStatus], [releaseDateTime], [releasedBy], [modelNoShipped], [unitNo], [shipper], [shipCost], [shipDateTime], [rma], [ars], [trackingNo], [coreStatus], [complaint], [complaintCode], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000063', 'INT', 'RE0063/CHRIS', 'JENKINS', 'AMYH', '2009-07-16T16:28:23.000', 'A', '41356', 'Z5064173AN', 'Z5064173AI', '7D158232', 33988, '2007-01-01T00:00:00.000', '0', '1', '2009-07-16T16:28:23.000', 'AMYH', 'P05064173AI', 'HNT00447442', 'CHELSEL', 16.00, '2009-07-16T16:33:46.000', '000063', '1ze490820613545535', '1Z5E7E840345242524', '0', 'PLUG INOP', '1 DISC 07/29/09', '', '')




COMMIT TRANSACTION

----------------modelTable----------------------------
/*-- -Object: table [hunt].[model] -------*/
CREATE TABLE [hunt].[model] (
[modelNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[modelNoOrdered] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelNoClaimed] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelNoBulletin] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[productType] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cassette] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_model_cassette] DEFAULT (0) NULL,
[CD] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_model_CD] DEFAULT (0) NULL,
[scrap] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_model_scrap] DEFAULT (0) NULL,
[superceded] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_model_superceded] DEFAULT (0) NULL,
[superceding1] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[superceding2] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[superceding3] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[superceding4] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[superceding5] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[substituted] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_model_substituted] DEFAULT (0) NULL,
[substitute1] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[substitute2] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[substitute3] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[substitute4] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[substitute5] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quantity] [int] NULL,
[manufacturer] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/*-- -Object: primary key [hunt].[model].[PK_model] -------*/
if not exists (select * from dbo.sysobjects where id = object_id(N'[PK_model]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [hunt].[model] ADD
CONSTRAINT [PK_model] PRIMARY KEY CLUSTERED
(
[modelNo]
) ON [PRIMARY]
GO



SET XACT_ABORT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF


BEGIN TRANSACTION

DECLARE @error INT
DECLARE @ptrBinary varbinary(16)

-- --Table: [hunt].[model]
-- --Insert(s)
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('', '', '', '', '', '0', '0', '0', '0', '', '', '', '', '', '0', '', '', '', '', '', 4, '')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('006-275-285', '006-275-285', '006-275-285', '006-275-285', 'R', '0', '0', '0', '0', '', '', '', '', '', '0', '', '', '', '', '', 0, 'MagnetiMarelli')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('03848576', '03848576', '03848576', '03848576', 'F', '0', '0', '0', '0', ' ', ' ', ' ', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', 0, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04048567', 'Z4048567', '4048567', '4048567', 'R', '0', '0', '0', '0', ' ', ' ', ' ', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', 0, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04222136', 'Z4222136', '4222136', '4222136', 'R', '0', '0', '0', '0', ' ', ' ', ' ', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', 0, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04222175', '04222175', '04222175', '04222175', 'F', '0', '0', '0', '0', ' ', ' ', ' ', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', 2, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04222660', '04222660', '04222660', '04222660', 'F', '0', '0', '0', '0', ' ', ' ', ' ', ' ', ' ', '0', ' ', ' ', ' ', ' ', ' ', 1, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04222768', '04222768', '4222768', '04222768', 'F', '0', '0', '0', '0', null, null, null, null, null, '1', '04222769', '04312037', '04312757', null, null, 7, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04222774', '04222774', '4222774', '04222774', 'F', '0', '0', '0', '0', null, null, null, null, null, '1', '04312751', null, null, null, null, 2, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04222800', '04222800', '4222800', '04222800', 'F', '0', '0', '0', '0', null, null, null, null, null, '1', '04312810', null, null, null, null, 14, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04234138', '04234138', '4234138', '04234138', 'F', '0', '0', '0', '0', null, null, null, null, null, '1', '04437504', null, null, null, null, 0, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04311075', '04311075', '4311075', '04311075', 'F', '0', '0', '0', '0', null, null, null, null, null, '1', '04312770', null, null, null, null, 6, 'Huntsville')
INSERT INTO [hunt].[model] ([modelNo], [modelNoOrdered], [modelNoClaimed], [modelNoBulletin], [productType], [cassette], [CD], [scrap], [superceded], [superceding1], [superceding2], [superceding3], [superceding4], [superceding5], [substituted], [substitute1], [substitute2], [substitute3], [substitute4], [substitute5], [quantity], [manufacturer]) VALUES('04311076', '04311076', '4311076', '04311076', 'F', '0', '0', '0', '0', null, null, null, null, null, '1', '04312771', null, null, null, null, 3, 'Huntsville')




COMMIT TRANSACTION

---------------------unitHistoryTable---------------------

/*-- -Object: table [hunt].[unitHistory] -------*/
CREATE TABLE [hunt].[unitHistory] (
[contractNoFrom] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[unitNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[repairCycleNo] [smallint] CONSTRAINT [DF_unitHistory_repairCycleNo] DEFAULT (0) NULL,
[receiveDateTime] [datetime] NULL,
[inspectorID] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[unitModelYear] [int] NULL,
[modelInNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelOutNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[modelNoClaimed] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[tapeNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CDNo] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[J1850CCD] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quarantineBefore] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quarantineAfter] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quarantineBeforeIn] [datetime] NULL,
[quarantineBeforeOut] [datetime] NULL,
[quarantineAfterIn] [datetime] NULL,
[quarantineAfterOut] [datetime] NULL,
[scrapAuthorize] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scrapReason] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[scrapIn] [datetime] NULL,
[scrapOut] [datetime] NULL,
[comebackCategory] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[comebackReason] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[warrantyClaimIn] [datetime] NULL,
[warrantyClaimOut] [datetime] NULL,
[warrantyClaimBy] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[waitFlag] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_unitHistory_waitFlag] DEFAULT (0) NULL,
[contractNoTo] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shipDateTime] [datetime] NULL,
[maxTestCycle] [smallint] CONSTRAINT [DF_unitHistory_maxTestCycle] DEFAULT (0) NULL,
[returnedUnused] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_unitHistory_returnedUnused] DEFAULT (0) NULL,
[superceded] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS CONSTRAINT [DF_unitHistory_superceded] DEFAULT (0) NULL,
[contractType] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FTNTF] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EDI856] [tinyint] NULL,
[EDI861] [tinyint] NULL,
[warrantyClaimStatus] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AudioElectronicSN] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TrafficElectronicSN] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/*-- -Object: primary key [hunt].[unitHistory].[PK_unitHistory] -------*/
ALTER TABLE [hunt].[unitHistory] ADD
CONSTRAINT [PK_unitHistory] PRIMARY KEY CLUSTERED
(
[contractNoFrom]
) ON [PRIMARY]
GO


/*-- -Object: index [hunt].[unitHistory].[IDX_UNITHISTORY_CONTRACTNOFROM] -------*/
CREATE INDEX [IDX_UNITHISTORY_CONTRACTNOFROM] ON [hunt].[unitHistory] ([contractNoFrom]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

/*-- -Object: index [hunt].[unitHistory].[IDX_UNITHISTORY_CONTRACTNOTO] -------*/
CREATE INDEX [IDX_UNITHISTORY_CONTRACTNOTO] ON [hunt].[unitHistory] ([contractNoTo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

/*-- -Object: index [hunt].[unitHistory].[IDX_UNITHISTORY_UNITNO] -------*/
CREATE INDEX [IDX_UNITHISTORY_UNITNO] ON [hunt].[unitHistory] ([unitNo]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO



SET XACT_ABORT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF


BEGIN TRANSACTION

DECLARE @error INT
DECLARE @ptrBinary varbinary(16)



-- --Table: [hunt].[unitHistory]
-- --Insert(s)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('00000', 'HNT00448508', 1, '2008-12-08T11:13:30.000', 'SCONKLIN', 1998, 'P04801062AD', 'P04801062AE', '04801062AD', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2008-12-15T09:52:21.000', null, null, '0', '219786', '2009-05-14T14:20:24.000', 1, '0', '0', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000000', 'HNT00437420', 1, '2007-07-11T10:14:42.000', 'SCONKLIN', 2000, 'P04686492AE', 'P04686492AF', '04686492AE', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-07-11T12:33:24.000', null, null, '0', '045997', '2007-08-06T15:54:03.000', 1, '0', '1', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000000001', 'HNT00467298', 1, '2010-03-18T08:51:49.000', 'SCONKLIN', 2005, 'P05094031AA', 'P05094031AA', '5094031AA', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2010-03-23T11:03:56.000', null, null, '0', '81058462', '2010-11-18T13:52:47.000', 1, '0', '0', null, '0', 0, 1, ' ', '', '')
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000001', 'HNT00431211', 1, '2007-09-28T12:08:53.000', 'SCONKLIN', 2004, 'P04602410AO', 'P04602410AO', '04602410AO', '', '', '', '', '', null, null, null, null, 'X', 'WATERDAMAGE', '2007-09-28T00:00:00.000', '2007-09-28T00:00:00.000', '', '', '2007-09-28T12:38:42.000', null, null, '0', null, null, 1, '0', '0', null, '0', null, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000001', 'HNT00374804', 1, '2005-12-07T14:57:03.000', 'twalsh', 2002, '05069131AD', '05069131AE', '05069131AD', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2005-12-19T14:52:25.000', '2005-12-21T14:38:27.000', 'AHALL ', '0', '92869764', '2005-12-20T10:54:33.000', 1, '0', '0', null, '0', 0, 1, 'A', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000011', 'HNT00439048', 1, '2007-08-13T11:02:30.000', 'SCONKLIN', 2002, 'P04760296AC', 'P04760296AC', '04760296AC', '', '', '', '', '', null, null, null, null, 'DHEILIG', 'SERVBULLETIN', '2007-09-10T00:00:00.000', '2007-09-10T00:00:00.000', '', '', '2007-09-10T09:25:30.000', null, null, '0', null, null, 1, '0', '0', null, '0', null, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000012', 'HNT00438682', 1, '2007-08-10T10:44:12.000', 'SCONKLIN', 2004, '04704309', '04704309', '4704309', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-08-16T12:30:49.000', null, null, '0', '113781', '2008-05-02T15:58:55.000', 1, '0', '0', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000018', 'HNT00439305', 1, '2007-08-17T12:32:02.000', 'SCONKLIN', 2001, 'P56038518AI', 'P56038518AJ', '56038518AI', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-08-24T16:23:36.000', null, null, '0', '73611', '2008-01-23T16:05:21.000', 1, '0', '1', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000002', 'HNT00303988', 1, '2005-09-20T13:29:42.000', 'twalsh', 2002, 'P56045600AJ', 'P56045600AJ', '56045618AH', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2005-09-28T09:14:00.000', '2005-10-07T08:17:46.000', 'AHALL ', '0', null, null, 1, '0', '0', null, '0', null, 1, 'A', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000003', 'HNT00374688', 1, '2005-10-27T13:56:10.000', 'twalsh', 2005, 'P56010540AF', 'P56010540AF', '56010540AG', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2005-10-27T16:38:20.000', '2006-10-02T11:59:47.000', 'SCONKLIN ', '0', 'BS93070', '2006-04-24T11:35:48.000', 1, '0', '0', null, '0', null, 1, 'A', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000004', 'HNT00374883', 1, '2006-01-25T16:17:13.000', 'twalsh', 2001, 'P56010151AH', 'P56010151AH', '56010151AH', '', '', '', '', 'HNT00374883', null, null, '2007-01-25T00:00:00.000', null, null, null, null, null, '', '', '2007-01-25T14:51:42.000', null, null, '0', null, null, 1, '0', '0', null, '0', null, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000005', 'HNT00374884', 1, '2006-01-25T16:20:42.000', 'twalsh', 2000, '56042498AM', '56042498AM', '56042498AM', '', '', '', '', '', null, null, null, null, '', 'WATERDAMAGE', '2006-04-07T00:00:00.000', '2006-04-07T00:00:00.000', '', '', '2006-04-07T09:18:53.000', null, null, '0', null, null, 1, '0', '0', null, '0', null, 1, null, null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000006', 'HNT00360640', 1, '2006-08-15T15:50:31.000', 'twalsh', 1999, 'P04602284AH', 'P04602284AH', 'EW04602284AH', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2006-08-15T16:22:48.000', null, null, '0', '95217481', '2006-10-04T10:56:31.000', 1, '0', '0', null, '0', 0, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000007', 'HNT00360706', 1, '2006-09-12T15:32:37.000', 'twalsh', 2000, 'P04759532AJ', 'P04602282AH', '04759532AJ', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2006-09-29T15:25:52.000', null, null, '0', '95190821', '2006-09-29T15:29:55.000', 1, '0', '1', null, '0', 0, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('00001', 'HNT00420126', 1, '2007-12-19T11:29:17.000', 'SCONKLIN', 2001, 'P56038518AJ', 'P56038518AJ', '56038518AJ', '', '', '', '', '', null, null, null, null, null, null, null, null, 'EXCHANGENOTUSED', '', '2007-12-19T15:49:09.000', null, null, '0', null, null, 1, '1', '0', null, '0', null, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000010', 'HNT00424484', 1, '2007-03-21T14:49:58.000', 'twalsh', 2003, '05083892AI', '05083892AH', '05083892AI', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-03-21T15:38:45.000', '2007-03-22T14:03:44.000', 'AHALL ', '0', '96704874', '2007-04-13T16:27:45.000', 1, '0', '0', null, '0', 0, 1, 'A', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000011', 'HNT00424496', 1, '2007-02-14T11:09:31.000', 'twalsh', 2004, '05083892AI', '05083892AH', '05083892AI', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-02-15T10:25:23.000', '2007-04-05T07:55:44.000', 'AHALL ', '0', '96416607', '2007-03-08T10:42:34.000', 1, '0', '0', null, '0', 0, 1, 'S', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000111', 'HNT00428588', 1, '2008-04-23T11:18:30.000', 'AMYH', 2001, 'P56038518AJ', 'P56038518AJ', '56038518AJ', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2008-04-25T12:32:04.000', null, null, '0', '061808', '2008-06-18T13:47:36.000', 1, '0', '0', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000012', 'HNT00424494', 1, '2007-02-14T11:10:15.000', 'twalsh', 2005, 'P56044877AC', 'P56044877AC', '56044877AC', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-05-14T16:16:45.000', '2007-05-15T14:22:29.000', 'AMYH ', '0', null, null, 1, '0', '0', null, '0', null, 1, 'A', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000013', 'HNT00424495', 1, '2007-02-14T11:10:58.000', 'twalsh', 2005, 'P56044903AI', 'P56044903AI', '56044903AI', '', '', '', '', '', null, null, null, null, 'NO REPAIR', 'SERVBULLETIN', '2007-02-14T00:00:00.000', '2007-02-14T00:00:00.000', '', '', '2007-02-14T12:48:24.000', '2007-02-15T14:17:58.000', 'SCONKLIN ', '0', null, null, 1, '0', '1', null, '0', null, 1, 'A', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('000063', 'HNT00447927', 1, '2009-07-24T11:34:15.000', 'SCONKLIN', 2007, 'P05064173AI', 'P05064173AI', '05064173AI', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2009-07-28T10:21:40.000', null, null, '0', '021810', '2010-02-18T13:51:37.000', 1, '0', '0', null, '0', 1, 1, ' ', '', '')
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0000631', 'HNT00447442', 1, '2009-08-24T09:55:40.000', 'SCONKLIN', 2006, 'P05064173AI', 'P05064173AI', '05064173AI', '', '', '', '', '', null, null, null, null, null, null, null, null, 'NTF', '', '2009-08-25T15:44:44.000', null, null, '0', '39849', '2010-02-25T15:47:51.000', 1, '0', '0', null, '1', 1, 1, ' ', '', '')
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0005', 'HNT00431059', 1, '2007-09-25T12:29:54.000', 'SCONKLIN', 2000, 'P04686492AE', 'P04686492AF', '04686492AE', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-09-26T16:52:14.000', null, null, '0', '208069', '2007-10-25T14:57:50.000', 1, '0', '1', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0006', 'HNT00431010', 1, '2007-09-20T12:11:58.000', 'SCONKLIN', 2002, 'P04760936AF', 'P04760936AF', '04760936af', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2007-09-20T16:53:46.000', null, null, '0', '008502', '2008-01-14T15:35:23.000', 1, '0', '0', null, '0', 1, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('00067', 'HNT00444385', 1, '2009-10-09T10:14:03.000', 'SCONKLIN', 2005, 'P05026061AA', 'P05026062AC', '05026062AC', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2009-10-13T14:07:22.000', null, null, '0', '28218', '2009-10-16T13:27:21.000', 1, '0', '0', null, '0', 1, 1, ' ', '', '')
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('001003', 'HNT00440419', 1, '2009-03-03T11:11:35.000', 'SCONKLIN', 2005, 'P05094031AA', 'P05094031AA', '5094031AA', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2009-03-10T11:54:48.000', null, null, '0', '78124375', '2009-07-31T13:09:47.000', 1, '0', '0', null, '0', 0, 1, ' ', '', '')
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('0010031', 'HNT00440418', 1, '2009-03-03T11:11:16.000', 'SCONKLIN', 2004, 'P05064010AL', 'P05091720AF', '05064010AL', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2009-03-10T15:22:00.000', null, null, '0', '040920091', '2009-04-09T16:07:20.000', 1, '0', '1', null, '0', 1, 1, ' ', '', '')
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('001076', 'HNT00438502', 1, '2007-07-26T09:37:29.000', 'SCONKLIN', 2000, 'P56010190AC', 'P56010190AC', '56010190AC', '', '', '', '', 'NO REPAIR', null, null, '2007-10-04T00:00:00.000', null, null, null, null, null, '', '', '2007-10-04T11:04:25.000', null, null, '0', null, null, 1, '0', '0', null, '0', null, 1, ' ', null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('001104', 'HNT00206049', 2, '2004-05-26T14:26:54.000', 'twalsh', 2002, 'P04748079AH', 'P04748079AH', '04748079AH', '', '', '', '', '', null, null, null, null, null, null, null, null, 'DEALERSFAULT', 'OUTPUTSTAGEBLOWN', '2004-05-27T16:03:36.000', '2004-05-28T14:48:40.000', 'lscjc ', '0', 'BS93070', '2006-05-05T12:32:25.000', 1, '0', '0', null, '0', null, 1, null, null, null)
INSERT INTO [hunt].[unitHistory] ([contractNoFrom], [unitNo], [repairCycleNo], [receiveDateTime], [inspectorID], [unitModelYear], [modelInNo], [modelOutNo], [modelNoClaimed], [tapeNo], [CDNo], [J1850CCD], [quarantineBefore], [quarantineAfter], [quarantineBeforeIn], [quarantineBeforeOut], [quarantineAfterIn], [quarantineAfterOut], [scrapAuthorize], [scrapReason], [scrapIn], [scrapOut], [comebackCategory], [comebackReason], [warrantyClaimIn], [warrantyClaimOut], [warrantyClaimBy], [waitFlag], [contractNoTo], [shipDateTime], [maxTestCycle], [returnedUnused], [superceded], [contractType], [FTNTF], [EDI856], [EDI861], [warrantyClaimStatus], [AudioElectronicSN], [TrafficElectronicSN]) VALUES('001105', 'HNT00219966', 1, '2004-06-07T13:33:01.000', 'twalsh', 2003, '05093038AC', '05093038AC', '05093038AC', '', '', '', '', '', null, null, null, null, null, null, null, null, '', '', '2004-06-09T09:58:49.000', '2004-06-10T14:40:28.000', 'AHALL ', '0', '88363235', '2004-06-16T16:39:13.000', 1, '0', '0', null, '0', 0, 1, null, null, null)





COMMIT TRANSACTION

-----------------------------------------------------------
For the count of orders the important fields are ordertable.orderDateTime, model.manufacturer, and unitHistory.unitModelYear.

For count of Cancelled orders ordertable.orderDateTime, model.manufacturer, unitHistory.unitModelYear, and orderStatus="C".

For Shipped count ordertable.shipDateTime, model.manufacturer, and unitHistory.unitModelYear.

For received count ordertable.receivedDateTime, model.manufacturer, and unitHistory.unitModelYear.

For Warranty repaired count unitHistory.warrantyClaimIn, ordertable.warrantyType="1", model.manufacturer, and unitHistory.unitModelYear.

For C/P units repaired unitHistory.warrantyClaimIn, ordertable.warrantyType="0 or 2 or 3", model.manufacturer, and unitHistory.unitModelYear.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-12 : 11:33:20
Is there a way to relate these 3 tables? For example, there is a contractNoFrom column in your hunt.model table and hunt.ordertable. So perhaps they refer to the same contractNoFrom - which means those two tables can be "joined" based on that column. However, I don't see any column that can be used to join the unitHistory table to the other two tables.

Potential columns that I thought might be related - for example modelNo column in unitHistory table and modelNoIn column in orderTable do not seem to have matching data.

Without one or more columns to match between the tables, we cannot join the tables; and if we don't have a way of joining the tables, I don't know how to generate the report that you described.
Go to Top of Page

Paul76
Starting Member

5 Posts
Posted - 2011-04-12 : 12:20:21
sunitabeck,

I screwed up once again. I was trying to shorten what I had on the post so I removed some of the INSERT INTO statments in the tables which will probably mean that you dont have a dataset which can used for testing. unitHistory table is related to ordertable by contractNoFrom and ordertable is realated to the model table by by the field modelNo. If it would help I can post script files with full data and schema on an ftp. I havent mentioned this yet but I am going to be searching the records using a date range from a date from and date to prompt.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-12 : 20:25:16
I am going to try to do this in two steps. First, I want to join the three tables and do a simple select - just to make sure that I have the syntax and joins correctly. That would be:
SELECT
   *
FROM
   hunt.unitHistory uh
   INNER JOIN hunt.ordertable ot ON
      ot.contractNoFrom = uh.contractNoFrom
   INNER JOIN hunt.model m ON
      m.modelNo = ot.modelNo

I put the join conditions as you had indicated.

In the second step, I want to enhance the query in step 1 to pick up the columns I want and the counts. That would be SOMETHING like this - I don't know enough about the business logic to be sure.
DECLARE @date DATETIME;
SET @date = '20110412';

SELECT 
   m.manufacturer,
   @date AS Date,
   uh.unitModelYear,
   COUNT
   (
      CASE WHEN ot.orderDateTime >= @date AND ot.orderDateTime < DATEADD(dd,1,@date) THEN 1 END 
   ) AS orders,
   COUNT
   (
      CASE WHEN ot.orderDateTime >= @date AND ot.orderDateTime < DATEADD(dd,1,@date)
         AND ot.orderStatus = 'CANCELLED' THEN 1 END
   ) AS CancelledOrders
   -- etc.
FROM
   hunt.unitHistory uh
   INNER JOIN hunt.ordertable ot ON
      ot.contractNoFrom = uh.contractNoFrom
   INNER JOIN hunt.model m ON
      m.modelNo = ot.modelNo
GROUP BY
   m.manufacturer,
   uh.unitModelYear


Here, I am picking one date and doing the query for that single date. You could also specify a date range.

The counts are calculated by using the case-when construct.

Finally, any column that is outside of the aggregation functions is listed in the group by clause.

Hope that makes sense - if you try it and run into difficulties, post the code and we can try to figure out what the problem might be.
Go to Top of Page

Paul76
Starting Member

5 Posts
Posted - 2011-04-13 : 08:00:20
SUNITABECK,
Thank you for your help. I will try this out and let you know the results.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-13 : 08:32:08
You are quite welcome - but what I posted is a sample/pattern/guide for how to do it. If you are able to make it work for one count, we can take that query and make it more efficient, error-proof etc. So if you run into troubles, please post back.
Go to Top of Page

Paul76
Starting Member

5 Posts
Posted - 2011-04-13 : 11:07:08
The code did not work as shown. This was not your fault it is mine. It has something to do with how the tables are joined in the second grouping of code. You have given me a good starting point to play with the code and figure it out. If I get stuck I will post the code I have as you suggested. Once again thanks for your help.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site