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 2005 Forums
 Transact-SQL (2005)
 Matching Rows Issue

Author  Topic 

tripleZ
Starting Member

7 Posts
Posted - 2010-07-16 : 10:24:03
I am running the following query to find labor records for am employee that start at the same time during the day (when running multiple machines at the same time). Part of the result set are records which shouldn't be there (i.e. they match only themselves). I have rebuilt indexes and consistency checks show everything is fine. Is there something wrong with my query that I am missing?

Thanks for any help.

select * from jobtran j
where j.job_rate = 0 and j.trans_type = 'R' and 1< (select count(z.trans_num) from jobtran z
where z.emp_num = j.emp_num and datediff(dayofyear,z.trans_date,j.trans_date)= 0
and datediff(year,z.trans_date,j.trans_date)=0
and datediff(hour,z.trans_date,j.trans_date)=0
and datediff(minute,z.trans_date,j.trans_date)=0)
order by j.trans_date,j.emp_num,j.trans_num

Mike

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-07-16 : 12:07:58
Can you supply some DML, DDL and expected output? It's a little hard to tell what is wrong. But, I'm sure we can help you come up with a correct and more effecient query. Here is a link that might help when posting an issue:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

tripleZ
Starting Member

7 Posts
Posted - 2010-07-16 : 14:38:54
Here is the additional info requested:
DML:

insert into jobtran (job,suffix, trans_type, trans_date,qty_complete ,qty_scrapped,oper_num, a_hrs, next_oper, emp_num, a_$, start_time,  end_time, ind_code, pay_rate, qty_moved ,whse, loc ,user_code, close_job, issue_parent, lot, complete_op, pr_rate, job_rate , shift, posted ,low_level ,backflush, reason_code, trans_class, ps_num ,  wc,  awaiting_eop
																																																												, fixovhd,varovhd ,cost_code, co_product_mix, NoteExistsFlag,InWorkflow, import_doc_id, Uf_sss_indirect_acct_num, Uf_sss_direct_acct_num, Uf_sss_department, Uf_sss_rework_code, Uf_sss_deltrol_qty,Uf_sss_standard_rate , Uf_sss_temporary_rate , Uf_Approved, Uf_DEL_Job_Count, Uf_DL_FreezeTran )

select    '7492', 0, 'R', '2009-12-05 07:37:00.000' ,713.00000000, 0.00000000 , 10, 3.310,20,'    460', 0.00000000, 27432,39348,NULL,'R', 713.00000000, 'FP',NULL ,'LTR', 0, 0, NULL,'0', 0.000, 0.000,'1',1, 0,0,NULL,'J', NULL ,'S2020', 0,129.09000000, 0.00000000 ,'  ', 0,0,0, NULL, NULL,'1053','215',NULL,0.00000000,0.00526000 ,0.00000000, 1, 0, NULL      union all
select    '6916', 0, 'R', '2009-12-05 07:37:00.000' ,1580.00000000, 0.00000000 , 10, 3.310,30,'    460', 0.00000000, 27432,39348,NULL,'R', 1580.00000000, 'FP',NULL ,'LTR', 0, 0, NULL,'0', 0.000, 0.000,'1',1, 0,0,NULL,'J', NULL ,'S2030', 0,129.09000000, 0.00000000 ,'  ', 0,0,0, NULL, NULL,'1053','215',NULL,0.00000000,0.00267000 ,0.00000000, 1, 0, NULL  union all
select '8026', 0, 'R', '2009-12-05 08:41:00.000' ,58.00000000, 0.00000000 , 110, 2.240,120,'    458', 0.00000000, 31248,39312,NULL,'R', 58.00000000, 'FP',NULL ,'LTR', 0, 0, NULL,'0', 0.000, 0.000,'1',1, 0,0,NULL,'J', NULL ,'D3120', 0,129.09000000, 0.00000000 ,'  ', 0,0,0, NULL, NULL,'1053','265',NULL,0.00000000,0.00558700 ,0.00000000, 1, 0, NULL


DDL:


CREATE TABLE [dbo].[jobtran](
	[trans_num] [dbo].[HugeTransNumType] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[job] [dbo].[JobType] NULL,
	[suffix] [dbo].[SuffixType] NULL CONSTRAINT [DF_jobtran_suffix]  DEFAULT ((0)),
	[trans_type] [dbo].[JobtranTypeType] NULL,
	[trans_date] [dbo].[DateType] NULL,
	[qty_complete] [dbo].[QtyUnitType] NULL,
	[qty_scrapped] [dbo].[QtyUnitType] NULL,
	[oper_num] [dbo].[OperNumType] NOT NULL CONSTRAINT [DF_jobtran_oper_num]  DEFAULT ((0)),
	[a_hrs] [dbo].[TotalHoursType] NULL,
	[next_oper] [dbo].[OperNumType] NULL,
	[emp_num] [dbo].[EmpNumType] NULL,
	[a_$] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobtran_a_$]  DEFAULT ((0)),
	[start_time] [dbo].[TimeSecondsType] NULL,
	[end_time] [dbo].[TimeSecondsType] NULL,
	[ind_code] [dbo].[IndCodeType] NULL,
	[pay_rate] [dbo].[PayBasisType] NULL,
	[qty_moved] [dbo].[QtyUnitType] NULL,
	[whse] [dbo].[WhseType] NULL,
	[loc] [dbo].[LocType] NULL,
	[user_code] [dbo].[UserCodeType] NULL,
	[close_job] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobtran_close_job]  DEFAULT ((0)),
	[issue_parent] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobtran_issue_parent]  DEFAULT ((0)),
	[lot] [dbo].[LotType] NULL,
	[complete_op] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobtran_complete_op]  DEFAULT ((0)),
	[pr_rate] [dbo].[PayRateType] NULL,
	[job_rate] [dbo].[PayRateType] NULL,
	[shift] [dbo].[ShiftType] NULL,
	[posted] [dbo].[ListYesNoType] NOT NULL CONSTRAINT [DF_jobtran_posted]  DEFAULT ((0)),
	[low_level] [dbo].[LowLevelType] NULL CONSTRAINT [DF_jobtran_low_level]  DEFAULT ((0)),
	[backflush] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobtran_backflush]  DEFAULT ((0)),
	[reason_code] [dbo].[ReasonCodeType] NULL,
	[trans_class] [dbo].[JobtranClassType] NULL CONSTRAINT [DF_jobtran_trans_class]  DEFAULT ('J'),
	[ps_num] [dbo].[PsNumType] NULL,
	[wc] [dbo].[WcType] NULL,
	[awaiting_eop] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobtran_awaiting_eop]  DEFAULT ((0)),
	[fixovhd] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobtran_fixovhd]  DEFAULT ((0)),
	[varovhd] [dbo].[CostPrcType] NULL CONSTRAINT [DF_jobtran_varovhd]  DEFAULT ((0)),
	[cost_code] [dbo].[CostCodeType] NULL,
	[co_product_mix] [dbo].[ListYesNoType] NULL CONSTRAINT [DF_jobtran_co_product_mix]  DEFAULT ((0)),
	[NoteExistsFlag] [dbo].[FlagNyType] NOT NULL CONSTRAINT [DF_jobtran_NoteExistsFlag]  DEFAULT ((0)),
	[RecordDate] [dbo].[CurrentDateType] NOT NULL CONSTRAINT [DF_jobtran_RecordDate]  DEFAULT (getdate()),
	[RowPointer] [dbo].[RowPointerType] NOT NULL CONSTRAINT [DF_jobtran_RowPointer]  DEFAULT (newid()),
	[CreatedBy] [dbo].[UsernameType] NOT NULL CONSTRAINT [DF_jobtran_CreatedBy]  DEFAULT (suser_sname()),
	[UpdatedBy] [dbo].[UsernameType] NOT NULL CONSTRAINT [DF_jobtran_UpdatedBy]  DEFAULT (suser_sname()),
	[CreateDate] [dbo].[CurrentDateType] NOT NULL CONSTRAINT [DF_jobtran_CreateDate]  DEFAULT (getdate()),
	[InWorkflow] [dbo].[FlagNyType] NOT NULL CONSTRAINT [DF_jobtran_InWorkflow]  DEFAULT ((0)),
	[import_doc_id] [dbo].[ImportDocIdType] NULL,
	[Uf_sss_indirect_acct_num] [dbo].[AcctType] NULL,
	[Uf_sss_direct_acct_num] [dbo].[SourceType] NULL,
	[Uf_sss_department] [dbo].[DeptType] NULL,
	[Uf_sss_rework_code] [dbo].[CategoryType] NULL,
	[Uf_sss_deltrol_qty] [dbo].[QtyUnitType] NULL,
	[Uf_sss_standard_rate] [dbo].[RunHoursPiecesType] NULL,
	[Uf_sss_temporary_rate] [dbo].[RunHoursPiecesType] NULL,
	[Uf_Approved] [tinyint] NULL CONSTRAINT [DF_jobtran_Uf_Approved]  DEFAULT ((0)),
	[Uf_DEL_Job_Count] [dbo].[GenericIntType] NULL,
	[Uf_DL_FreezeTran] [dbo].[ListYesNoType] NULL,
 CONSTRAINT [PK_jobtran] PRIMARY KEY NONCLUSTERED 
(
	[posted] ASC,
	[trans_num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_jobtran] UNIQUE CLUSTERED 
(
	[trans_num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'Module', @value=N'ERP Shop Floor Control' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'jobtran'

GO
EXEC sys.sp_addextendedproperty @name=N'TriggerAttributes', @value=N'IupModifiesInsertedRows,RememberIdentity' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'jobtran'

GO
USE [DTLF_App]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [jobtranFK1] FOREIGN KEY([import_doc_id])
REFERENCES [dbo].[tax_free_import] ([import_doc_id])
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [jobtranFK1]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [jobtranFk2] FOREIGN KEY([emp_num])
REFERENCES [dbo].[employee] ([emp_num])
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [jobtranFk2]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [jobtranFk3] FOREIGN KEY([ind_code])
REFERENCES [dbo].[indcode] ([ind_code])
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [jobtranFk3]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_awaiting_eop] CHECK  (([awaiting_eop]=(0) OR [awaiting_eop]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_awaiting_eop]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_backflush] CHECK  (([backflush]=(0) OR [backflush]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_backflush]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_close_job] CHECK  (([close_job]=(0) OR [close_job]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_close_job]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_co_product_mix] CHECK  (([co_product_mix]=(0) OR [co_product_mix]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_co_product_mix]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_complete_op] CHECK  (([complete_op]=(0) OR [complete_op]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_complete_op]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_issue_parent] CHECK  (([issue_parent]=(0) OR [issue_parent]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_issue_parent]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_pay_rate] CHECK  (([pay_rate]='D' OR ([pay_rate]='O' OR [pay_rate]='R')))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_pay_rate]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_posted] CHECK  (([posted]=(0) OR [posted]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_posted]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_trans_class] CHECK  (([trans_class]='W' OR ([trans_class]='S' OR ([trans_class]='K' OR [trans_class]='J'))))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_trans_class]
GO
ALTER TABLE [dbo].[jobtran]  WITH NOCHECK ADD  CONSTRAINT [CK_jobtran_trans_type] CHECK  (([trans_type]='U' OR ([trans_type]='E' OR ([trans_type]='D' OR ([trans_type]='Q' OR ([trans_type]='C' OR ([trans_type]='I' OR ([trans_type]='M' OR ([trans_type]='R' OR [trans_type]='S')))))))))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_trans_type]



Expected output:

trans_num job suffix trans_type trans_date qty_complete qty_scrapped oper_num a_hrs next_oper emp_num a_$ start_time end_time ind_code pay_rate qty_moved whse loc user_code close_job issue_parent lot complete_op pr_rate job_rate shift posted low_level backflush reason_code trans_class ps_num wc awaiting_eop fixovhd varovhd cost_code co_product_mix NoteExistsFlag RecordDate RowPointer CreatedBy UpdatedBy CreateDate InWorkflow import_doc_id Uf_sss_indirect_acct_num Uf_sss_direct_acct_num Uf_sss_department Uf_sss_rework_code Uf_sss_deltrol_qty Uf_sss_standard_rate Uf_sss_temporary_rate Uf_Approved Uf_DEL_Job_Count Uf_DL_FreezeTran
--------------------------------------- -------------------- ------ ---------- ----------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- ----------- ------- --------------------------------------- ----------- ----------- -------- -------- --------------------------------------- ---- --------------- --------- --------- ------------ --------------- ----------- --------------------------------------- --------------------------------------- ----- ------ --------- --------- ----------- ----------- ---------- ------ ------------ --------------------------------------- --------------------------------------- --------- -------------- -------------- ----------------------- ------------------------------------ ------------------------------ ------------------------------ ----------------------- ---------- ------------------------- ------------------------ ---------------------- ----------------- ------------------ --------------------------------------- --------------------------------------- --------------------------------------- ----------- ---------------- ----------------
31039 7492 0 R 2009-12-05 07:37:00.000 713.00000000 0.00000000 10 3.310 20 460 0.00000000 27432 39348 NULL R 713.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2020 0 129.09000000 0.00000000 0 0 2009-12-07 15:43:42.353 E256A33D-45C0-40EB-A12F-F7275A658AB2 DELTROL-DOM\sqlserver sa 2009-12-05 10:58:00.450 0 NULL NULL 1053 215 NULL 0.00000000 0.00526000 0.00000000 1 0 NULL
31040 6916 0 R 2009-12-05 07:37:00.000 1580.00000000 0.00000000 10 3.310 30 460 0.00000000 27432 39348 NULL R 1580.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2030 0 129.09000000 0.00000000 0 0 2009-12-07 15:43:33.993 6237A21A-99FA-4FB7-9881-6F99BCB1D43F DELTROL-DOM\sqlserver sa 2009-12-05 10:58:00.460 0 NULL NULL 1053 215 NULL 0.00000000 0.00267000 0.00000000 1 0 NULL
38395 7492 0 R 2010-01-04 16:14:00.000 1147.00000000 0.00000000 10 5.940 20 463 0.00000000 58428 79812 NULL R 1147.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2040 0 231.66000000 0.00000000 0 0 2010-01-07 14:20:13.487 5CF43C27-B1C0-4277-B384-27E370549A01 DELTROL-DOM\sqlserver zozakiewiczm 2010-01-04 22:14:00.763 0 NULL NULL 1053 215 NULL 0.00000000 0.00526000 0.00000000 1 0 NULL
38398 7262 0 R 2010-01-04 16:14:00.000 1846.00000000 0.00000000 10 5.940 20 463 0.00000000 58428 79812 NULL R 1846.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2020 0 231.66000000 0.00000000 0 0 2010-01-07 14:20:11.820 FA8F947D-BEEF-4B07-9885-6605967CA26F DELTROL-DOM\sqlserver zozakiewiczm 2010-01-04 22:58:00.920 0 NULL NULL 1053 215 NULL 0.00000000 0.00187000 0.00000000 1 0 NULL





Mike
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-07-16 : 15:39:00
WOW.. I don't think I've ever seen a table with so many columns that are almost all defined with custom user defined types. Unfortunatly, I don't have those types so I can't execute the CREATE TABLE statement.
Go to Top of Page

tripleZ
Starting Member

7 Posts
Posted - 2010-07-19 : 13:03:14

I modified the DDL to use the cooresponding native data types:

DML:

insert into jobtran (job,suffix, trans_type, trans_date,qty_complete ,qty_scrapped,oper_num, a_hrs, next_oper, emp_num, a_$, start_time, end_time, ind_code, pay_rate, qty_moved ,whse, loc ,user_code, close_job, issue_parent, lot, complete_op, pr_rate, job_rate , shift, posted ,low_level ,backflush, reason_code, trans_class, ps_num , wc, awaiting_eop
, fixovhd,varovhd ,cost_code, co_product_mix, NoteExistsFlag,InWorkflow, import_doc_id, Uf_sss_indirect_acct_num, Uf_sss_direct_acct_num, Uf_sss_department, Uf_sss_rework_code, Uf_sss_deltrol_qty,Uf_sss_standard_rate , Uf_sss_temporary_rate , Uf_Approved, Uf_DEL_Job_Count, Uf_DL_FreezeTran )

select '7492', 0, 'R', '2009-12-05 07:37:00.000' ,713.00000000, 0.00000000 , 10, 3.310,20,' 460', 0.00000000, 27432,39348,NULL,'R', 713.00000000, 'FP',NULL ,'LTR', 0, 0, NULL,'0', 0.000, 0.000,'1',1, 0,0,NULL,'J', NULL ,'S2020', 0,129.09000000, 0.00000000 ,' ', 0,0,0, NULL, NULL,'1053','215',NULL,0.00000000,0.00526000 ,0.00000000, 1, 0, NULL union all
select '6916', 0, 'R', '2009-12-05 07:37:00.000' ,1580.00000000, 0.00000000 , 10, 3.310,30,' 460', 0.00000000, 27432,39348,NULL,'R', 1580.00000000, 'FP',NULL ,'LTR', 0, 0, NULL,'0', 0.000, 0.000,'1',1, 0,0,NULL,'J', NULL ,'S2030', 0,129.09000000, 0.00000000 ,' ', 0,0,0, NULL, NULL,'1053','215',NULL,0.00000000,0.00267000 ,0.00000000, 1, 0, NULL union all
select '8026', 0, 'R', '2009-12-05 08:41:00.000' ,58.00000000, 0.00000000 , 110, 2.240,120,' 458', 0.00000000, 31248,39312,NULL,'R', 58.00000000, 'FP',NULL ,'LTR', 0, 0, NULL,'0', 0.000, 0.000,'1',1, 0,0,NULL,'J', NULL ,'D3120', 0,129.09000000, 0.00000000 ,' ', 0,0,0, NULL, NULL,'1053','265',NULL,0.00000000,0.00558700 ,0.00000000, 1, 0, NULL


DDL:


CREATE TABLE [dbo].[jobtran](
[trans_num] [decimal(11,0)] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[job] [nvarchar(20)] NULL,
[suffix] [smallint] NULL CONSTRAINT [DF_jobtran_suffix] DEFAULT ((0)),
[trans_type] [nchar(1)] NULL,
[trans_date] [datetime] NULL,
[qty_complete] [decimal(19,8)] NULL,
[qty_scrapped] [decimal(19,8)] NULL,
[oper_num] [int] NOT NULL CONSTRAINT [DF_jobtran_oper_num] DEFAULT ((0)),
[a_hrs] [decimal(9,3)] NULL,
[next_oper] [int] NULL,
[emp_num] [nvarchar(7)] NULL,
[a_$] [decimal(18,8)] NULL CONSTRAINT [DF_jobtran_a_$] DEFAULT ((0)),
[start_time] [int] NULL,
[end_time] [int] NULL,
[ind_code] [nvarchar(3)] NULL,
[pay_rate] [nchar(1)] NULL,
[qty_moved] [decimal(19,8)] NULL,
[whse] [nvarchar(4)] NULL,
[loc] [nvarchar(15)] NULL,
[user_code] [nvarchar(3)] NULL,
[close_job] [tinyint] NULL CONSTRAINT [DF_jobtran_close_job] DEFAULT ((0)),
[issue_parent] [tinyint] NULL CONSTRAINT [DF_jobtran_issue_parent] DEFAULT ((0)),
[lot] [nvarchar(15)] NULL,
[complete_op] [tinyint] NULL CONSTRAINT [DF_jobtran_complete_op] DEFAULT ((0)),
[pr_rate] [decimal(9,3)] NULL,
[job_rate] [decimal(9,3)] NULL,
[shift] [nvarchar(3)] NULL,
[posted] [tinyint] NOT NULL CONSTRAINT [DF_jobtran_posted] DEFAULT ((0)),
[low_level] [tinyint] NULL CONSTRAINT [DF_jobtran_low_level] DEFAULT ((0)),
[backflush] [tinyint] NULL CONSTRAINT [DF_jobtran_backflush] DEFAULT ((0)),
[reason_code] [nvarchar(3)] NULL,
[trans_class] [nchar(1)] NULL CONSTRAINT [DF_jobtran_trans_class] DEFAULT ('J'),
[ps_num] [nvarchar(10)] NULL,
[wc] [nvarchar(16)] NULL,
[awaiting_eop] [tinyint] NULL CONSTRAINT [DF_jobtran_awaiting_eop] DEFAULT ((0)),
[fixovhd] [decimal(18,8)] NULL CONSTRAINT [DF_jobtran_fixovhd] DEFAULT ((0)),
[varovhd] [decimal(18,8)] NULL CONSTRAINT [DF_jobtran_varovhd] DEFAULT ((0)),
[cost_code] [nvarchar(3)] NULL,
[co_product_mix] [tinyint] NULL CONSTRAINT [DF_jobtran_co_product_mix] DEFAULT ((0)),
[NoteExistsFlag] [tinyint] NOT NULL CONSTRAINT [DF_jobtran_NoteExistsFlag] DEFAULT ((0)),
[RecordDate] [datetime] NOT NULL CONSTRAINT [DF_jobtran_RecordDate] DEFAULT (getdate()),
[RowPointer] [uniqueidentifier] NOT NULL CONSTRAINT [DF_jobtran_RowPointer] DEFAULT (newid()),
[CreatedBy] [nvarchar(30)] NOT NULL CONSTRAINT [DF_jobtran_CreatedBy] DEFAULT (suser_sname()),
[UpdatedBy] [nvarchar(30)] NOT NULL CONSTRAINT [DF_jobtran_UpdatedBy] DEFAULT (suser_sname()),
[CreateDate] [datetime] NOT NULL CONSTRAINT [DF_jobtran_CreateDate] DEFAULT (getdate()),
[InWorkflow] [tinyint] NOT NULL CONSTRAINT [DF_jobtran_InWorkflow] DEFAULT ((0)),
[import_doc_id] [nvarchar(25)] NULL,
[Uf_sss_indirect_acct_num] [nvarchar(12)] NULL,
[Uf_sss_direct_acct_num] [nvarchar(4)] NULL,
[Uf_sss_department] [nvarchar(6)] NULL,
[Uf_sss_rework_code] [nvarchar(6)] NULL,
[Uf_sss_deltrol_qty] [decimal(18,8)] NULL,
[Uf_sss_standard_rate] [decimal(15,8)] NULL,
[Uf_sss_temporary_rate] [decimal(15,8)] NULL,
[Uf_Approved] [tinyint] NULL CONSTRAINT [DF_jobtran_Uf_Approved] DEFAULT ((0)),
[Uf_DEL_Job_Count] [int] NULL,
[Uf_DL_FreezeTran] [tinyint] NULL,
CONSTRAINT [PK_jobtran] PRIMARY KEY NONCLUSTERED
(
[posted] ASC,
[trans_num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_jobtran] UNIQUE CLUSTERED
(
[trans_num] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_addextendedproperty @name=N'Module', @value=N'ERP Shop Floor Control' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'jobtran'

GO
EXEC sys.sp_addextendedproperty @name=N'TriggerAttributes', @value=N'IupModifiesInsertedRows,RememberIdentity' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'jobtran'

GO
USE [DTLF_App]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [jobtranFK1] FOREIGN KEY([import_doc_id])
REFERENCES [dbo].[tax_free_import] ([import_doc_id])
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [jobtranFK1]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [jobtranFk2] FOREIGN KEY([emp_num])
REFERENCES [dbo].[employee] ([emp_num])
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [jobtranFk2]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [jobtranFk3] FOREIGN KEY([ind_code])
REFERENCES [dbo].[indcode] ([ind_code])
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [jobtranFk3]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_awaiting_eop] CHECK (([awaiting_eop]=(0) OR [awaiting_eop]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_awaiting_eop]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_backflush] CHECK (([backflush]=(0) OR [backflush]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_backflush]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_close_job] CHECK (([close_job]=(0) OR [close_job]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_close_job]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_co_product_mix] CHECK (([co_product_mix]=(0) OR [co_product_mix]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_co_product_mix]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_complete_op] CHECK (([complete_op]=(0) OR [complete_op]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_complete_op]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_issue_parent] CHECK (([issue_parent]=(0) OR [issue_parent]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_issue_parent]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_pay_rate] CHECK (([pay_rate]='D' OR ([pay_rate]='O' OR [pay_rate]='R')))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_pay_rate]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_posted] CHECK (([posted]=(0) OR [posted]=(1)))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_posted]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_trans_class] CHECK (([trans_class]='W' OR ([trans_class]='S' OR ([trans_class]='K' OR [trans_class]='J'))))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_trans_class]
GO
ALTER TABLE [dbo].[jobtran] WITH NOCHECK ADD CONSTRAINT [CK_jobtran_trans_type] CHECK (([trans_type]='U' OR ([trans_type]='E' OR ([trans_type]='D' OR ([trans_type]='Q' OR ([trans_type]='C' OR ([trans_type]='I' OR ([trans_type]='M' OR ([trans_type]='R' OR [trans_type]='S')))))))))
GO
ALTER TABLE [dbo].[jobtran] CHECK CONSTRAINT [CK_jobtran_trans_type]



Expected output:

trans_num job suffix trans_type trans_date qty_complete qty_scrapped oper_num a_hrs next_oper emp_num a_$ start_time end_time ind_code pay_rate qty_moved whse loc user_code close_job issue_parent lot complete_op pr_rate job_rate shift posted low_level backflush reason_code trans_class ps_num wc awaiting_eop fixovhd varovhd cost_code co_product_mix NoteExistsFlag RecordDate RowPointer CreatedBy UpdatedBy CreateDate InWorkflow import_doc_id Uf_sss_indirect_acct_num Uf_sss_direct_acct_num Uf_sss_department Uf_sss_rework_code Uf_sss_deltrol_qty Uf_sss_standard_rate Uf_sss_temporary_rate Uf_Approved Uf_DEL_Job_Count Uf_DL_FreezeTran
--------------------------------------- -------------------- ------ ---------- ----------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- ----------- ------- --------------------------------------- ----------- ----------- -------- -------- --------------------------------------- ---- --------------- --------- --------- ------------ --------------- ----------- --------------------------------------- --------------------------------------- ----- ------ --------- --------- ----------- ----------- ---------- ------ ------------ --------------------------------------- --------------------------------------- --------- -------------- -------------- ----------------------- ------------------------------------ ------------------------------ ------------------------------ ----------------------- ---------- ------------------------- ------------------------ ---------------------- ----------------- ------------------ --------------------------------------- --------------------------------------- --------------------------------------- ----------- ---------------- ----------------
31039 7492 0 R 2009-12-05 07:37:00.000 713.00000000 0.00000000 10 3.310 20 460 0.00000000 27432 39348 NULL R 713.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2020 0 129.09000000 0.00000000 0 0 2009-12-07 15:43:42.353 E256A33D-45C0-40EB-A12F-F7275A658AB2 DELTROL-DOM\sqlserver sa 2009-12-05 10:58:00.450 0 NULL NULL 1053 215 NULL 0.00000000 0.00526000 0.00000000 1 0 NULL
31040 6916 0 R 2009-12-05 07:37:00.000 1580.00000000 0.00000000 10 3.310 30 460 0.00000000 27432 39348 NULL R 1580.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2030 0 129.09000000 0.00000000 0 0 2009-12-07 15:43:33.993 6237A21A-99FA-4FB7-9881-6F99BCB1D43F DELTROL-DOM\sqlserver sa 2009-12-05 10:58:00.460 0 NULL NULL 1053 215 NULL 0.00000000 0.00267000 0.00000000 1 0 NULL
38395 7492 0 R 2010-01-04 16:14:00.000 1147.00000000 0.00000000 10 5.940 20 463 0.00000000 58428 79812 NULL R 1147.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2040 0 231.66000000 0.00000000 0 0 2010-01-07 14:20:13.487 5CF43C27-B1C0-4277-B384-27E370549A01 DELTROL-DOM\sqlserver zozakiewiczm 2010-01-04 22:14:00.763 0 NULL NULL 1053 215 NULL 0.00000000 0.00526000 0.00000000 1 0 NULL
38398 7262 0 R 2010-01-04 16:14:00.000 1846.00000000 0.00000000 10 5.940 20 463 0.00000000 58428 79812 NULL R 1846.00000000 FP NULL LTR 0 0 NULL 0 0.000 0.000 1 1 0 0 NULL J NULL S2020 0 231.66000000 0.00000000 0 0 2010-01-07 14:20:11.820 FA8F947D-BEEF-4B07-9885-6605967CA26F DELTROL-DOM\sqlserver zozakiewiczm 2010-01-04 22:58:00.920 0 NULL NULL 1053 215 NULL 0.00000000 0.00187000 0.00000000 1 0 NULL





Mike


Mike
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