| Author |
Topic |
|
wembleybear
Yak Posting Veteran
93 Posts |
 Posted - 2014-06-09 : 13:00:47
|
I have a straight-forward select query to show work orders for a particular customer as below. I want to add a field value from another table, deltickitem di which contains contract records. I need to include the field di.weekchg to show the weekly hire rate, but the joined query must ensure that the both the contract number matches that in the original select and that the item number matches that in the actual select. Additionally, there is the problem that the item can appear more than once in the deltickitem table against a particular contract (if item has been off-hired and then re-hired on the same contract number) - in this case the query must select the record with the highest di.counter number, which I haven't worked out how to put in my query.This is my basic code, but I keep ending up with duplicate work order lines in my result set - any help to restructure my query to get this right would be much appreciated:Select wh.worknumber, wh.custnum, wh.contract, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg, wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost] From worksorderhdr wh Left Join inventory iv On iv.item = wh.itemcode inner Join models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1 order by wh.date_created Many thanksMartyn |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-06-09 : 13:05:13
|
| Please show us some sample data.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-09 : 17:48:50
|
| Also, what version of SQL Server are you using? |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2014-06-10 : 05:33:19
|
Below are sample results. I only want to see one line per worknumber - the one matching the itemcode number and highest counter number that relates to that itemcode (In the top example, counter records 3 & 4 do not relate to that itemcode as my query is not correct and is selecting all the contract records from the di contract table). So for DN-000108, Counter record 2 with weeklychg of £181.50 should be the one selected.I am using SQL Server 2008R2, and as I have added a couple of fields, my revised code is below as well.worknumber custnum contract counter sitename itemcode regnum name weekchg date_created task_descr Repair Cost-------------------- ------------ -------------------- ------- ------------------------------------------------------------ -------------------- -------------------- ------------------------------ --------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------DN-000108 BARRATNE 033845 1 10MTR+TRAKER/HOUGHTON LE SPRING 3006A KX60 GNJ 533-105 165.00 2011-12-07 16:52:00 MACHINE STARTS AND THEN CUTS OUT 0.00DN-000108 BARRATNE 033845 2 10MTR+TRAKER/HOUGHTON LE SPRING 3006A KX60 GNJ 533-105 181.50 2011-12-07 16:52:00 MACHINE STARTS AND THEN CUTS OUT 0.00DN-000108 BARRATNE 033845 3 10MTR+TRAKER/HOUGHTON LE SPRING 3006A KX60 GNJ 533-105 0.00 2011-12-07 16:52:00 MACHINE STARTS AND THEN CUTS OUT 0.00DN-000108 BARRATNE 033845 4 10MTR+TRAKER/HOUGHTON LE SPRING 3006A KX60 GNJ 533-105 0.00 2011-12-07 16:52:00 MACHINE STARTS AND THEN CUTS OUT 0.00DN-000109 BARRATNE 01-000180 1 THE WILLOWS, 2062A KX11 HND 535-125 170.00 2011-12-07 16:53:00 HANDBRAKE AND RADIO 0.00DN-000109 BARRATNE 01-000180 3 THE WILLOWS, 2062A KX11 HND 535-125 0.00 2011-12-07 16:53:00 HANDBRAKE AND RADIO 0.00DN-000109 BARRATNE 01-000180 2 THE WILLOWS, 2062A KX11 HND 535-125 187.00 2011-12-07 16:53:00 HANDBRAKE AND RADIO 0.00DN-000109 BARRATNE 01-000180 4 THE WILLOWS, 2062A KX11 HND 535-125 0.00 2011-12-07 16:53:00 HANDBRAKE AND RADIO Select wh.worknumber, wh.custnum, wh.contract, di.counter, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg, wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost] From worksorderhdr wh Left Join inventory iv On iv.item = wh.itemcode inner Join models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1 order by wh.date_created |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-10 : 11:34:54
|
How do you determine a non-matching row? Is it because the weekchg value is $0.00? If so, maybe this will work:SELECT *FROM ( Select wh.worknumber, wh.custnum, wh.contract, di.counter, wh.sitename, wh.itemcode, wh.regnum, m.name, di.weekchg, wh.date_created, wh.task_descr, wh.actual_labour_sale+wh.actual_parts_sale as [Repair Cost], ROW_NUMBER() OVER (PARTITION BY wh.worknumber ORDER BY di.counter DESC) AS RowNum From worksorderhdr wh Left Join inventory iv On iv.item = wh.itemcode inner Join models m On m.id = iv.model_id left join deltickitem di on di.dticket = wh.contract where wh.custnum = 'BARRATNE' and wh.rejected <> 1 and wh.charge_to_cust = 1 AND di.weekchg > $0.00 ) AS Torder by date_created |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2014-06-10 : 12:05:52
|
Not quite, these rows sometimes have values > 0 in weekchg, but the itemcode is different on these rows, so should not be included in the result set. For matching lines, in addition to matching on the itemcode, I only want the row with the highest counter number returned in the results.The problem is getting the join by contract number AND itemcode, then of those selected, returning only the recording with the highest counter number.In your query 've changed the item field to the one from the deltickhdr table so it can be seen what I mean - e.g for DN-000238 only the record with counter number 2 should be returned as 3 is actually for a completely different item (SATRAK instead of 879A) and 1 is a previous record:worknumber custnum contract counter sitename item regnum name weekchg date_created task_descr Repair Cost RowNum-------------------- ------------ -------------------- ------- ------------------------------------------------------------ -------------------------------------------------- -------------------- ------------------------------ --------------------- ----------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- --------------------DN-000108 BARRATNE 033845 2 10MTR+TRAKER/HOUGHTON LE SPRING 3006A KX60 GNJ 533-105 181.50 2011-12-07 16:52:00 MACHINE STARTS AND THEN CUTS OUT 0.00 1DN-000108 BARRATNE 033845 1 10MTR+TRAKER/HOUGHTON LE SPRING 3006A KX60 GNJ 533-105 165.00 2011-12-07 16:52:00 MACHINE STARTS AND THEN CUTS OUT 0.00 2DN-000109 BARRATNE 01-000180 2 THE WILLOWS, 2062A KX11 HND 535-125 187.00 2011-12-07 16:53:00 HANDBRAKE AND RADIO 0.00 1DN-000109 BARRATNE 01-000180 1 THE WILLOWS, 2062A KX11 HND 535-125 170.00 2011-12-07 16:53:00 HANDBRAKE AND RADIO 0.00 2DN-000227 BARRATNE 034881 2 14MT TELE/HORTON PARK/SWOP 3022A 1822A KX11 DCZ 535-140 209.00 2011-12-09 09:36:00 SITE REPORTED 06.12.115 LITRES ENGINE OIL REQUIRED. SENT DIRECT TO SITE 37.21 1DN-000227 BARRATNE 034881 1 14MT TELE/HORTON PARK/SWOP 3022A 1822A KX11 DCZ 535-140 190.00 2011-12-09 09:36:00 SITE REPORTED 06.12.115 LITRES ENGINE OIL REQUIRED. SENT DIRECT TO SITE 37.21 2DN-000238 BARRATNE 028984 3 7MTR+TRACKER/THE LEAZES, NEWCASTLE SATRAK KX59 CFN 531-70 10.00 2011-12-09 10:59:00 SITE REPORTED 06.12.11REPLACEMENT BEACON REQUIRED. SENT DIRECT TO SITE 46.76 1DN-000238 BARRATNE 028984 2 7MTR+TRACKER/THE LEAZES, NEWCASTLE 879A KX59 CFN 531-70 165.00 2011-12-09 10:59:00 SITE REPORTED 06.12.11REPLACEMENT BEACON REQUIRED. SENT DIRECT TO SITE 46.76 2DN-000238 BARRATNE 028984 1 7MTR+TRACKER/THE LEAZES, NEWCASTLE 879A KX59 CFN 531-70 130.00 2011-12-09 10:59:00 SITE REPORTED 06.12.11 |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-10 : 12:13:58
|
| You are telling us what should be picked, but now how. In your previous example you say that "for DN-000238 only the record with counter number 2 should be returned as 3 is actually for a completely different item (SATRAK instead of 879A)" Why? SATRAK has the highest Counter. So, what is the logic for picking a different item? Do you want the highest Counter for a Worknumber where there is item with a counter of 1 or just multiple of the same item code or ..?Also, it might be easier if you post sample data as requested by Tara. What you've posted is the result of your query. Post the create table and insert statements to populate the tables. Then post your expected results and we can write a queries against those tables. |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2014-06-10 : 12:31:53
|
| I'm perhaps making this more difficult than it is. A work order in our system can only contain 1 item, even if a contract has many items on it. So in my example 879A is on the work order, but SATRAK is not so I don't want that returned by the query as it is not related to the work order at all.The only fly in the ointment is that sometimes an item will be closed on a contract and then added again at a different weekchg, in which case the deltickitem table has two lines for the same item on it - the one with the higher counter number being the line in use.I will post create & insert statements for the database tables after this. |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2014-06-10 : 12:34:42
|
Contract (deltickitem) table:CREATE TABLE [dbo].[deltickitem]( [ucounter] [int] IDENTITY(1,1) NOT NULL, [dticket] [nvarchar](20) NOT NULL, [counter] [smallint] NULL, [item] [nvarchar](50) NULL, [quant] [float] NULL, [backorder] [float] NULL, [descr] [nvarchar](255) NULL, [min_chg] [money] NULL, [weekchg] [money] NULL, [monthchg] [money] NULL, [ddate] [smalldatetime] NULL, [retused] [float] NULL, [sold] [float] NULL, [prev_sold] [float] NULL, [start_rent] [smalldatetime] NULL, [stop_rent] [smalldatetime] NULL, [return_date] [smalldatetime] NULL, [orig_start_rent] [smalldatetime] NULL, [discount] [float] NULL, [taxable] [bit] NOT NULL, [discountable] [bit] NOT NULL, [groupable] [bit] NOT NULL, [itype] [nvarchar](1) NULL, [days_min] [smallint] NULL, [ret_by] [nvarchar](50) NULL, [meterstart] [float] NULL, [meterstop] [float] NULL, [hourlychg] [money] NULL, [credit_days] [smallint] NULL, [um] [nvarchar](50) NULL, [printctr] [smallint] NULL, [pricelevel] [nvarchar](2) NULL, [deldate] [smalldatetime] NULL, [origorder] [float] NULL, [projret] [smalldatetime] NULL, [promin] [money] NULL, [prorate] [money] NULL, [proadd] [money] NULL, [proxweek] [money] NULL, [proxmonth] [money] NULL, [weekend] [bit] NOT NULL, [retauth] [nvarchar](20) NULL, [last_invoice] [float] NULL, [last_invc_date] [smalldatetime] NULL, [taxcode] [nvarchar](6) NULL, [weight_amt] [float] NULL, [weight_um] [nvarchar](20) NULL, [secure_dep] [money] NULL, [job_number] [nvarchar](20) NULL, [trans_ucounter] [int] NOT NULL, [applies_to_itype] [nvarchar](1) NULL, [total_value_credit] [bit] NOT NULL, [quant_input] [bit] NOT NULL, [printprice] [bit] NOT NULL, [planned_item] [nvarchar](50) NULL, [grid_id] [nvarchar](20) NULL, [alloc_date] [datetime] NULL, [alloc_customer] [nvarchar](12) NULL, [alloc_job] [nvarchar](50) NULL, [applies_to_ucounter] [int] NULL, [planned_quant] [float] NULL, [actual_quant] [float] NULL, [actual_item] [nvarchar](20) NULL, [alloc_ucounter] [int] NULL, [parent_ucounter] [int] NULL, [generate_credit] [bit] NOT NULL, [inv_value_to_date] [money] NULL, [item_contents_id] [int] NULL, [offshore_ticket] [nvarchar](20) NULL, [platform_id] [int] NULL, [status] [nvarchar](60) NULL, [dw_level] [smallint] NULL, [amended] [smallint] NULL, [exchanged] [bit] NULL, [exchange_ucounter] [int] NULL, [reason_id] [int] NULL, [alloc_ticket] [nvarchar](20) NULL, [meter_uom] [nvarchar](50) NULL, [reduced_onshore_ucounter] [int] NULL, [line_type] [smallint] NULL, [per_item] [bit] NULL, [misc_chg_qty] [float] NULL, [repair1_qty] [float] NULL, [repair2_qty] [float] NULL, [status_code] [smallint] NULL, [generate_final_invoice] [bit] NOT NULL, [booking_start] [smalldatetime] NULL, [booking_end] [smalldatetime] NULL, [uninvoiced_net] [money] NULL, [uninvoiced_vat] [money] NULL, [invoiced_net] [money] NULL, [invoiced_vat] [money] NULL, [dw1_percent] [float] NULL, [dw2_percent] [float] NULL, [rehire] [bit] NOT NULL, [stage] [smallint] NULL, [suspend] [bit] NOT NULL, [projected_dw_net] [money] NULL, [projected_dw_vat] [money] NULL, [adjust_days] [float] NULL, [misc_chg_added_on_return] [tinyint] NULL, [return_depot] [nvarchar](3) NULL, [transport_distance_id] [int] NULL, [transport_charge] [money] NULL, [transport_charge_min] [bit] NOT NULL, [no_delivery_charge] [bit] NOT NULL, [no_collection_charge] [bit] NOT NULL, [supervisor] [nvarchar](128) NULL, [supervisor_auth_reason_id] [int] NULL, [supervisor_auth_comment] [nvarchar](50) NULL, [fully_invoiced] [bit] NOT NULL, [charge_from_spec] [int] NULL, [meter_invoiced_value] [money] NULL, [meter_at_last_invoice] [float] NULL, [meter_inv_start_date] [smalldatetime] NULL, [meter_date] [smalldatetime] NULL, [reopened] [bit] NOT NULL, [proj_stop_date] [smalldatetime] NULL, [local_tax_code] [nvarchar](6) NULL, [uninvoiced_local_tax] [money] NULL, [invoiced_local_tax] [money] NULL, [projected_dw_local_tax] [money] NULL, [source_depot] [nvarchar](3) NULL, [post_disc_meter_invoiced_value] [money] NULL, [meter_invoiced_vat] [money] NULL, [meter_invoiced_local_tax] [money] NULL, [invoiced_dw1_net] [money] NULL, [invoiced_dw1_vat] [money] NULL, [invoiced_dw1_local_tax] [money] NULL, [invoiced_dw2_net] [money] NULL, [invoiced_dw2_vat] [money] NULL, [invoiced_dw2_local_tax] [money] NULL, [pre_termination_quant] [float] NULL, [lower_day] [smallint] NULL, [upper_day] [smallint] NULL, [cdw_rate] [money] NULL, [meter_allowance] [float] NULL, [price_grid_line_id] [int] NULL, [invoiced_cdw_pre_disc] [money] NULL, [printed] [bit] NOT NULL, [vehicle] [bit] NOT NULL, [regnum] [nvarchar](20) NULL, [swapped] [bit] NOT NULL, [swap_ucounter] [int] NULL, [misc_chg_id] [int] NULL, [projected_weekly_rate] [money] NULL, [days_in_projected_weekly_rate] [tinyint] NULL, [accessory] [bit] NOT NULL, [merchant1_id] [int] NULL, [merchant2_id] [int] NULL, [merchant3_id] [int] NULL, [serial_number] [nvarchar](35) NULL, [barcode_rfid] [nvarchar](20) NULL, [cost_price] [money] NULL, [cost_hire] [money] NULL, [prices_changed] [bit] NOT NULL, [misc_chg_line_type] [smallint] NULL, [pre_disc_uninvoiced_net] [money] NOT NULL, [meterstartoffset] [float] NULL, [meterstopoffset] [float] NULL, [meteratlastinvoiceoffset] [float] NULL, [TimesheetLineType] [tinyint] NOT NULL, [ChargeTime] [nvarchar](20) NULL, [usage] AS ([dbo].[MeterUsage]([meter_at_last_invoice],[meterstop],[itype],[meteratlastinvoiceoffset],[meterstopoffset])), [item_or_regnum] AS (case when [vehicle]=(1) AND [regnum] IS NOT NULL then [regnum] else [item] end), [DoNotAllocate] [bit] NOT NULL, [ActualsProcessed] [bit] NOT NULL, [PrintedOnReturnNote] [bit] NOT NULL, [ProjectedMeterValue] [float] NULL, [ProjectedPostDiscountValue] [float] NULL, [ProjectedPostDiscountMeterCharge] [float] NULL, [ProjectedTaxValue] [float] NULL, [ProjectedLocalTax] [float] NULL, [ProjectedMeterVat] [float] NULL, [ProjectedMeterLocalTax] [float] NULL, [ChargeableDays] [float] NULL, [ChargeableMonths] [int] NULL, [InvoicedChargeableDays] [float] NULL, [InvoicedChargeableMonths] [int] NULL, [RouteVehicleItemID] [int] NULL, [Text1] [nvarchar](150) NULL, [Text2] [nvarchar](150) NULL, CONSTRAINT [PK_deltickitem] PRIMARY KEY NONCLUSTERED ( [ucounter] 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 Insert statement:INSERT INTO [mcsrm_live].[dbo].[deltickitem] ([dticket] ,[counter] ,[item] ,[quant] ,[backorder] ,[descr] ,[min_chg] ,[weekchg] ,[monthchg] ,[ddate] ,[retused] ,[sold] ,[prev_sold] ,[start_rent] ,[stop_rent] ,[return_date] ,[orig_start_rent] ,[discount] ,[taxable] ,[discountable] ,[groupable] ,[itype] ,[days_min] ,[ret_by] ,[meterstart] ,[meterstop] ,[hourlychg] ,[credit_days] ,[um] ,[printctr] ,[pricelevel] ,[deldate] ,[origorder] ,[projret] ,[promin] ,[prorate] ,[proadd] ,[proxweek] ,[proxmonth] ,[weekend] ,[retauth] ,[last_invoice] ,[last_invc_date] ,[taxcode] ,[weight_amt] ,[weight_um] ,[secure_dep] ,[job_number] ,[trans_ucounter] ,[applies_to_itype] ,[total_value_credit] ,[quant_input] ,[printprice] ,[planned_item] ,[grid_id] ,[alloc_date] ,[alloc_customer] ,[alloc_job] ,[applies_to_ucounter] ,[planned_quant] ,[actual_quant] ,[actual_item] ,[alloc_ucounter] ,[parent_ucounter] ,[generate_credit] ,[inv_value_to_date] ,[item_contents_id] ,[offshore_ticket] ,[platform_id] ,[status] ,[dw_level] ,[amended] ,[exchanged] ,[exchange_ucounter] ,[reason_id] ,[alloc_ticket] ,[meter_uom] ,[reduced_onshore_ucounter] ,[line_type] ,[per_item] ,[misc_chg_qty] ,[repair1_qty] ,[repair2_qty] ,[status_code] ,[generate_final_invoice] ,[booking_start] ,[booking_end] ,[uninvoiced_net] ,[uninvoiced_vat] ,[invoiced_net] ,[invoiced_vat] ,[dw1_percent] ,[dw2_percent] ,[rehire] ,[stage] ,[suspend] ,[projected_dw_net] ,[projected_dw_vat] ,[adjust_days] ,[misc_chg_added_on_return] ,[return_depot] ,[transport_distance_id] ,[transport_charge] ,[transport_charge_min] ,[no_delivery_charge] ,[no_collection_charge] ,[supervisor] ,[supervisor_auth_reason_id] ,[supervisor_auth_comment] ,[fully_invoiced] ,[charge_from_spec] ,[meter_invoiced_value] ,[meter_at_last_invoice] ,[meter_inv_start_date] ,[meter_date] ,[reopened] ,[proj_stop_date] ,[local_tax_code] ,[uninvoiced_local_tax] ,[invoiced_local_tax] ,[projected_dw_local_tax] ,[source_depot] ,[post_disc_meter_invoiced_value] ,[meter_invoiced_vat] ,[meter_invoiced_local_tax] ,[invoiced_dw1_net] ,[invoiced_dw1_vat] ,[invoiced_dw1_local_tax] ,[invoiced_dw2_net] ,[invoiced_dw2_vat] ,[invoiced_dw2_local_tax] ,[pre_termination_quant] ,[lower_day] ,[upper_day] ,[cdw_rate] ,[meter_allowance] ,[price_grid_line_id] ,[invoiced_cdw_pre_disc] ,[printed] ,[vehicle] ,[regnum] ,[swapped] ,[swap_ucounter] ,[misc_chg_id] ,[projected_weekly_rate] ,[days_in_projected_weekly_rate] ,[accessory] ,[merchant1_id] ,[merchant2_id] ,[merchant3_id] ,[serial_number] ,[barcode_rfid] ,[cost_price] ,[cost_hire] ,[prices_changed] ,[misc_chg_line_type] ,[pre_disc_uninvoiced_net] ,[meterstartoffset] ,[meterstopoffset] ,[meteratlastinvoiceoffset] ,[TimesheetLineType] ,[ChargeTime] ,[DoNotAllocate] ,[ActualsProcessed] ,[PrintedOnReturnNote] ,[ProjectedMeterValue] ,[ProjectedPostDiscountValue] ,[ProjectedPostDiscountMeterCharge] ,[ProjectedTaxValue] ,[ProjectedLocalTax] ,[ProjectedMeterVat] ,[ProjectedMeterLocalTax] ,[ChargeableDays] ,[ChargeableMonths] ,[InvoicedChargeableDays] ,[InvoicedChargeableMonths] ,[RouteVehicleItemID] ,[Text1] ,[Text2]) VALUES (<dticket, nvarchar(20),> ,<counter, smallint,> ,<item, nvarchar(50),> ,<quant, float,> ,<backorder, float,> ,<descr, nvarchar(255),> ,<min_chg, money,> ,<weekchg, money,> ,<monthchg, money,> ,<ddate, smalldatetime,> ,<retused, float,> ,<sold, float,> ,<prev_sold, float,> ,<start_rent, smalldatetime,> ,<stop_rent, smalldatetime,> ,<return_date, smalldatetime,> ,<orig_start_rent, smalldatetime,> ,<discount, float,> ,<taxable, bit,> ,<discountable, bit,> ,<groupable, bit,> ,<itype, nvarchar(1),> ,<days_min, smallint,> ,<ret_by, nvarchar(50),> ,<meterstart, float,> ,<meterstop, float,> ,<hourlychg, money,> ,<credit_days, smallint,> ,<um, nvarchar(50),> ,<printctr, smallint,> ,<pricelevel, nvarchar(2),> ,<deldate, smalldatetime,> ,<origorder, float,> ,<projret, smalldatetime,> ,<promin, money,> ,<prorate, money,> ,<proadd, money,> ,<proxweek, money,> ,<proxmonth, money,> ,<weekend, bit,> ,<retauth, nvarchar(20),> ,<last_invoice, float,> ,<last_invc_date, smalldatetime,> ,<taxcode, nvarchar(6),> ,<weight_amt, float,> ,<weight_um, nvarchar(20),> ,<secure_dep, money,> ,<job_number, nvarchar(20),> ,<trans_ucounter, int,> ,<applies_to_itype, nvarchar(1),> ,<total_value_credit, bit,> ,<quant_input, bit,> ,<printprice, bit,> ,<planned_item, nvarchar(50),> ,<grid_id, nvarchar(20),> ,<alloc_date, datetime,> ,<alloc_customer, nvarchar(12),> ,<alloc_job, nvarchar(50),> ,<applies_to_ucounter, int,> ,<planned_quant, float,> ,<actual_quant, float,> ,<actual_item, nvarchar(20),> ,<alloc_ucounter, int,> ,<parent_ucounter, int,> ,<generate_credit, bit,> ,<inv_value_to_date, money,> ,<item_contents_id, int,> ,<offshore_ticket, nvarchar(20),> ,<platform_id, int,> ,<status, nvarchar(60),> ,<dw_level, smallint,> ,<amended, smallint,> ,<exchanged, bit,> ,<exchange_ucounter, int,> ,<reason_id, int,> ,<alloc_ticket, nvarchar(20),> ,<meter_uom, nvarchar(50),> ,<reduced_onshore_ucounter, int,> ,<line_type, smallint,> ,<per_item, bit,> ,<misc_chg_qty, float,> ,<repair1_qty, float,> ,<repair2_qty, float,> ,<status_code, smallint,> ,<generate_final_invoice, bit,> ,<booking_start, smalldatetime,> ,<booking_end, smalldatetime,> ,<uninvoiced_net, money,> ,<uninvoiced_vat, money,> ,<invoiced_net, money,> ,<invoiced_vat, money,> ,<dw1_percent, float,> ,<dw2_percent, float,> ,<rehire, bit,> ,<stage, smallint,> ,<suspend, bit,> ,<projected_dw_net, money,> ,<projected_dw_vat, money,> ,<adjust_days, float,> ,<misc_chg_added_on_return, tinyint,> ,<return_depot, nvarchar(3),> ,<transport_distance_id, int,> ,<transport_charge, money,> ,<transport_charge_min, bit,> ,<no_delivery_charge, bit,> ,<no_collection_charge, bit,> ,<supervisor, nvarchar(128),> ,<supervisor_auth_reason_id, int,> ,<supervisor_auth_comment, nvarchar(50),> ,<fully_invoiced, bit,> ,<charge_from_spec, int,> ,<meter_invoiced_value, money,> ,<meter_at_last_invoice, float,> ,<meter_inv_start_date, smalldatetime,> ,<meter_date, smalldatetime,> ,<reopened, bit,> ,<proj_stop_date, smalldatetime,> ,<local_tax_code, nvarchar(6),> ,<uninvoiced_local_tax, money,> ,<invoiced_local_tax, money,> ,<projected_dw_local_tax, money,> ,<source_depot, nvarchar(3),> ,<post_disc_meter_invoiced_value, money,> ,<meter_invoiced_vat, money,> ,<meter_invoiced_local_tax, money,> ,<invoiced_dw1_net, money,> ,<invoiced_dw1_vat, money,> ,<invoiced_dw1_local_tax, money,> ,<invoiced_dw2_net, money,> ,<invoiced_dw2_vat, money,> ,<invoiced_dw2_local_tax, money,> ,<pre_termination_quant, float,> ,<lower_day, smallint,> ,<upper_day, smallint,> ,<cdw_rate, money,> ,<meter_allowance, float,> ,<price_grid_line_id, int,> ,<invoiced_cdw_pre_disc, money,> ,<printed, bit,> ,<vehicle, bit,> ,<regnum, nvarchar(20),> ,<swapped, bit,> ,<swap_ucounter, int,> ,<misc_chg_id, int,> ,<projected_weekly_rate, money,> ,<days_in_projected_weekly_rate, tinyint,> ,<accessory, bit,> ,<merchant1_id, int,> ,<merchant2_id, int,> ,<merchant3_id, int,> ,<serial_number, nvarchar(35),> ,<barcode_rfid, nvarchar(20),> ,<cost_price, money,> ,<cost_hire, money,> ,<prices_changed, bit,> ,<misc_chg_line_type, smallint,> ,<pre_disc_uninvoiced_net, money,> ,<meterstartoffset, float,> ,<meterstopoffset, float,> ,<meteratlastinvoiceoffset, float,> ,<TimesheetLineType, tinyint,> ,<ChargeTime, nvarchar(20),> ,<DoNotAllocate, bit,> ,<ActualsProcessed, bit,> ,<PrintedOnReturnNote, bit,> ,<ProjectedMeterValue, float,> ,<ProjectedPostDiscountValue, float,> ,<ProjectedPostDiscountMeterCharge, float,> ,<ProjectedTaxValue, float,> ,<ProjectedLocalTax, float,> ,<ProjectedMeterVat, float,> ,<ProjectedMeterLocalTax, float,> ,<ChargeableDays, float,> ,<ChargeableMonths, int,> ,<InvoicedChargeableDays, float,> ,<InvoicedChargeableMonths, int,> ,<RouteVehicleItemID, int,> ,<Text1, nvarchar(150),> ,<Text2, nvarchar(150),>)GO |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2014-06-10 : 12:38:15
|
Create for Work Orders table:CREATE TABLE [dbo].[worksorderhdr]( [worknumber] [nvarchar](20) NOT NULL, [itemcode] [nvarchar](20) NULL, [descr] [nvarchar](225) NULL, [task_descr] [ntext] NULL, [qty] [int] NULL, [itemised] [bit] NULL, [serialnum] [nvarchar](35) NULL, [manufacturer] [nvarchar](50) NULL, [model_id] [int] NULL, [depot] [nvarchar](3) NULL, [date_created] [smalldatetime] NULL, [date_in] [smalldatetime] NULL, [date_approved] [smalldatetime] NULL, [parts_order_date] [smalldatetime] NULL, [est_complete_date] [smalldatetime] NULL, [actual_complete_date] [smalldatetime] NULL, [stock_available_date] [smalldatetime] NULL, [replacement_cost] [money] NULL, [est_parts_cost] [money] NULL, [est_labour_cost] [money] NULL, [actual_parts_cost] [money] NULL, [actual_labour_cost] [money] NULL, [actual_parts_sale] [money] NULL, [actual_labour_sale] [money] NULL, [meterstart] [float] NULL, [meterstop] [float] NULL, [custnum] [nvarchar](12) NULL, [name] [nvarchar](60) NULL, [addr1] [nvarchar](60) NULL, [addr2] [nvarchar](60) NULL, [town] [nvarchar](60) NULL, [county] [nvarchar](60) NULL, [postcode] [nvarchar](60) NULL, [country_id] [nvarchar](2) NULL, [contact] [nvarchar](60) NULL, [sitename] [nvarchar](60) NULL, [siteaddr1] [nvarchar](60) NULL, [siteaddr2] [nvarchar](60) NULL, [sitetown] [nvarchar](60) NULL, [sitecounty] [nvarchar](60) NULL, [sitepostcode] [nvarchar](60) NULL, [sitecountry_id] [nvarchar](2) NULL, [phonenum] [nvarchar](30) NULL, [faxnum] [nvarchar](30) NULL, [on_site] [bit] NULL, [charge_to_cust] [bit] NULL, [charge_to_contract] [nvarchar](20) NULL, [cust_ponum] [nvarchar](50) NULL, [charge_as_asset_addition] [bit] NULL, [write_off] [bit] NULL, [qty_write_off] [int] NULL, [estimated_by] [nvarchar](50) NULL, [approved_by] [nvarchar](50) NULL, [technician_id] [int] NULL, [invnotes] [ntext] NULL, [warranty_repair] [bit] NULL, [stage] [smallint] NULL, [created_via] [smallint] NULL, [worktype] [int] NULL, [certificate] [nvarchar](30) NULL, [contract] [nvarchar](20) NULL, [expiry_date] [smalldatetime] NULL, [sched_maint_id] [int] NULL, [superseded] [bit] NULL, [service_supplier] [nvarchar](12) NULL, [est_travel_time] [smalldatetime] NULL, [time_on_site] [smalldatetime] NULL, [time_off_site] [smalldatetime] NULL, [rejected] [bit] NOT NULL, [item_owned_by_cust] [bit] NOT NULL, [ucounter] [int] NULL, [cash] [bit] NULL, [job_number] [nvarchar](50) NULL, [identification] [nvarchar](50) NULL, [cust_job_number] [nvarchar](50) NULL, [fin_charge_reqd] [bit] NOT NULL, [invoice] [float] NULL, [inspection_worktype] [int] NULL, [est_parts_sale] [money] NULL, [est_labour_sale] [money] NULL, [vehicle] [bit] NULL, [regnum] [nvarchar](20) NULL, [costed_job] [nvarchar](20) NULL, [activity_id] [int] NULL, [meterstartoffset] [float] NULL, [meterstopoffset] [float] NULL, [item_or_regnum] AS (case when [vehicle]=(1) AND [regnum] IS NOT NULL then [regnum] else [itemcode] end), [WorkflowStatusChangeID] [int] NULL, [ContactID] [int] NULL, [TransmitDate] [smalldatetime] NULL, [TransmitTechnicianID] [int] NULL, CONSTRAINT [PK_worksorderhdr] PRIMARY KEY CLUSTERED ( [worknumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO Insert statement:INSERT INTO [mcsrm_live].[dbo].[worksorderhdr] ([worknumber] ,[itemcode] ,[descr] ,[task_descr] ,[qty] ,[itemised] ,[serialnum] ,[manufacturer] ,[model_id] ,[depot] ,[date_created] ,[date_in] ,[date_approved] ,[parts_order_date] ,[est_complete_date] ,[actual_complete_date] ,[stock_available_date] ,[replacement_cost] ,[est_parts_cost] ,[est_labour_cost] ,[actual_parts_cost] ,[actual_labour_cost] ,[actual_parts_sale] ,[actual_labour_sale] ,[meterstart] ,[meterstop] ,[custnum] ,[name] ,[addr1] ,[addr2] ,[town] ,[county] ,[postcode] ,[country_id] ,[contact] ,[sitename] ,[siteaddr1] ,[siteaddr2] ,[sitetown] ,[sitecounty] ,[sitepostcode] ,[sitecountry_id] ,[phonenum] ,[faxnum] ,[on_site] ,[charge_to_cust] ,[charge_to_contract] ,[cust_ponum] ,[charge_as_asset_addition] ,[write_off] ,[qty_write_off] ,[estimated_by] ,[approved_by] ,[technician_id] ,[invnotes] ,[warranty_repair] ,[stage] ,[created_via] ,[worktype] ,[certificate] ,[contract] ,[expiry_date] ,[sched_maint_id] ,[superseded] ,[service_supplier] ,[est_travel_time] ,[time_on_site] ,[time_off_site] ,[rejected] ,[item_owned_by_cust] ,[ucounter] ,[cash] ,[job_number] ,[identification] ,[cust_job_number] ,[fin_charge_reqd] ,[invoice] ,[inspection_worktype] ,[est_parts_sale] ,[est_labour_sale] ,[vehicle] ,[regnum] ,[costed_job] ,[activity_id] ,[meterstartoffset] ,[meterstopoffset] ,[WorkflowStatusChangeID] ,[ContactID] ,[TransmitDate] ,[TransmitTechnicianID]) VALUES (<worknumber, nvarchar(20),> ,<itemcode, nvarchar(20),> ,<descr, nvarchar(225),> ,<task_descr, ntext,> ,<qty, int,> ,<itemised, bit,> ,<serialnum, nvarchar(35),> ,<manufacturer, nvarchar(50),> ,<model_id, int,> ,<depot, nvarchar(3),> ,<date_created, smalldatetime,> ,<date_in, smalldatetime,> ,<date_approved, smalldatetime,> ,<parts_order_date, smalldatetime,> ,<est_complete_date, smalldatetime,> ,<actual_complete_date, smalldatetime,> ,<stock_available_date, smalldatetime,> ,<replacement_cost, money,> ,<est_parts_cost, money,> ,<est_labour_cost, money,> ,<actual_parts_cost, money,> ,<actual_labour_cost, money,> ,<actual_parts_sale, money,> ,<actual_labour_sale, money,> ,<meterstart, float,> ,<meterstop, float,> ,<custnum, nvarchar(12),> ,<name, nvarchar(60),> ,<addr1, nvarchar(60),> ,<addr2, nvarchar(60),> ,<town, nvarchar(60),> ,<county, nvarchar(60),> ,<postcode, nvarchar(60),> ,<country_id, nvarchar(2),> ,<contact, nvarchar(60),> ,<sitename, nvarchar(60),> ,<siteaddr1, nvarchar(60),> ,<siteaddr2, nvarchar(60),> ,<sitetown, nvarchar(60),> ,<sitecounty, nvarchar(60),> ,<sitepostcode, nvarchar(60),> ,<sitecountry_id, nvarchar(2),> ,<phonenum, nvarchar(30),> ,<faxnum, nvarchar(30),> ,<on_site, bit,> ,<charge_to_cust, bit,> ,<charge_to_contract, nvarchar(20),> ,<cust_ponum, nvarchar(50),> ,<charge_as_asset_addition, bit,> ,<write_off, bit,> ,<qty_write_off, int,> ,<estimated_by, nvarchar(50),> ,<approved_by, nvarchar(50),> ,<technician_id, int,> ,<invnotes, ntext,> ,<warranty_repair, bit,> ,<stage, smallint,> ,<created_via, smallint,> ,<worktype, int,> ,<certificate, nvarchar(30),> ,<contract, nvarchar(20),> ,<expiry_date, smalldatetime,> ,<sched_maint_id, int,> ,<superseded, bit,> ,<service_supplier, nvarchar(12),> ,<est_travel_time, smalldatetime,> ,<time_on_site, smalldatetime,> ,<time_off_site, smalldatetime,> ,<rejected, bit,> ,<item_owned_by_cust, bit,> ,<ucounter, int,> ,<cash, bit,> ,<job_number, nvarchar(50),> ,<identification, nvarchar(50),> ,<cust_job_number, nvarchar(50),> ,<fin_charge_reqd, bit,> ,<invoice, float,> ,<inspection_worktype, int,> ,<est_parts_sale, money,> ,<est_labour_sale, money,> ,<vehicle, bit,> ,<regnum, nvarchar(20),> ,<costed_job, nvarchar(20),> ,<activity_id, int,> ,<meterstartoffset, float,> ,<meterstopoffset, float,> ,<WorkflowStatusChangeID, int,> ,<ContactID, int,> ,<TransmitDate, smalldatetime,> ,<TransmitTechnicianID, int,>)GO |
|
|
|
|
|
|
Problem with joining tables with multiple criteria