May 12, 2014 at 10:06 pm
Hi all!
I have a table, Types, which is a setup description for my program.
It has a lot of different fields, and generally something like 50 records.
Generally its so, that there is only two fields, that should not be identical - rest should be.
But to look at the records, and find one which is different is pretty difficult.
So, what i want is an way to script:
Find the records and fields, where there is differrent values in other fields than [ID] and [ope_number]
If possible without having to werite each and every field - Its growing rapidly.
Best regards
Edvard Korsbæk
CREATE TABLE [dbo].[types](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OFFDAYHOURS1] [int] NULL,
[OFFDAYHOURS2] [int] NULL,
[OFFDAYHOURS3] [int] NULL,
[OFFDAYHOURS4] [int] NULL,
[OFFDAYHOURS5] [int] NULL,
[ONCALLHOURS1] [real] NULL,
[ONCALLTYPEHOURSSTART1] [int] NULL,
[ONCALLTYPEHOURSEND1] [int] NULL,
[ONCALLHOURS2] [real] NULL,
[ONCALLHOURSSTART2] [int] NULL,
[ONCALLHOURSEND2] [int] NULL,
[ONCALLHOURS3] [real] NULL,
[ONCALLHOURSSTART3] [int] NULL,
[ONCALLHOURSEND3] [int] NULL,
[MORNINGONCALLPRIORITET] [smallint] NULL,
[EVENINGONCALLPRIORITET] [smallint] NULL,
[NIGHTSHIFTPRIORITET] [smallint] NULL,
[VACATIONTIMESTART1] [int] NULL,
[VACATIONTIMEEND1] [int] NULL,
[VACATIONTIMESTART2] [int] NULL,
[VACATIONTIMEEND2] [int] NULL,
[VACATIONSTART1] [tinyint] NULL,
[VACATIONEND1] [tinyint] NULL,
[VACATIONSTART2] [tinyint] NULL,
[VACATIONEND2] [tinyint] NULL,
[SLEEPINGDAY] [tinyint] NULL,
[WORKSATSUN] [tinyint] NULL,
[NORMSTART] [datetime] NULL,
[NORMLENGHT] [int] NULL,
[VACATIONDAYS] [real] NULL,
[CPRHEADERTEXT] [varchar](20) NULL,
[CPRPICTURE] [varchar](50) NULL,
[AUTOWORKPLANMODE] [tinyint] NULL,
[ShowOM] [tinyint] NULL,
[ShowSDRoll] [tinyint] NULL,
[ShowSDRos] [tinyint] NULL,
[Simplified_Insert_duty] [tinyint] NULL,
[Enable_37_3_offdayinlieu] [tinyint] NULL,
[Enable_17_06_offdayinlieu] [tinyint] NULL,
[Vacation_as_duty] [tinyint] NULL,
[holidayNotFallingOnASunday_Norm_calc] [tinyint] NULL,
[Show_Description_on_vacation_in_roster] [tinyint] NULL,
[Show_Description_And_time_on_vacation_in_roster] [tinyint] NULL,
[Set_Vacation_to_Zero_When_no_Duty] [tinyint] NULL,
[Set_OtherWork_Foa] [decimal](7, 2) NULL,
[Set_OtherWork_BUPL] [decimal](7, 2) NULL,
[Other_work_locked] [tinyint] NULL,
[Ope_Number] [int] NULL,
[HandleVacation] [tinyint] NULL,
[WorkOnHolyDays] [tinyint] NULL,
[use_a_b_days] [tinyint] NULL,
[Show_Only_substitutes_With_Duties] [tinyint] NULL,
[check_double_duty_In_This_schema] [tinyint] NULL,
[use_Total_Vacation_In_Norm_calculations] [tinyint] NULL,
[TimeAccountForOffTimeInLieue] [int] NULL,
[StatusNoOperator] [int] NULL,
[StatusNoSupervisor] [int] NULL,
[Between_Plans_Over_Midnight] [tinyint] NULL,
[No_children_in_sick_days_and_CareDays] [tinyint] NULL,
[Start_Date_For_Totals] [datetime] NULL,
[Set_norm_hours_to_this_month] [tinyint] NULL,
[Change_To_Vacation_In_Roster] [tinyint] NULL,
[Change_vacation_to_Sick_Day] [tinyint] NULL,
[Set_total_hours_to_this_month] [tinyint] NULL,
[BasepathForMontlyReports] [varchar](255) NULL,
[CC_forMail] [varchar](50) NULL,
[text_for_reports] [varchar](255) NULL,
[feriefridage_saldo] [int] NULL,
[Feriefridage_tilgode] [int] NULL,
[ferie_saldo] [int] NULL,
[ferie_tilgode] [int] NULL,
[afspadsering_1707_saldo] [int] NULL,
[Afspadsering_1707_tilgode] [int] NULL,
[overarb_saldo] [int] NULL,
[overarb_tilgode] [int] NULL,
[Norm_factor_BUPL] [decimal](7, 2) NULL,
[Norm_factor_FOA] [decimal](7, 2) NULL,
[Norm_factor_STUDENTS] [decimal](7, 2) NULL,
[Update_Repair_SFO_records] [tinyint] NULL,
[Use_LFS_Agreement] [tinyint] NULL,
[Text_lektioner_1_1] [char](12) NULL,
[Text_lektioner_1_1_LONG] [char](30) NULL,
[Text_lektioner_1_3] [char](12) NULL,
[Text_lektioner_1_3_LONG] [char](30) NULL,
[Text_lektioner_1_5] [char](12) NULL,
[Text_lektioner_1_5_LONG] [char](30) NULL,
[Factor_lektioner_1_1] [decimal](7, 2) NULL,
[Factor_lektioner_1_3] [decimal](7, 2) NULL,
[Factor_lektioner_1_5] [decimal](7, 2) NULL,
[No_Vacation_On_Shifttype_3_AND_20] [tinyint] NULL,
[Use_Roll_for_Norm_and_totals] [tinyint] NULL,
[Add_Vacation_To_Norm_Sum] [tinyint] NULL,
[Include_Holy_Days_In_Norm] [tinyint] NULL,
[FOA_AS_BUPL] [tinyint] NULL,
[use_sfo_norm_mode] [tinyint] NULL,
[rules_can_change_norm_hours] [tinyint] NULL,
[exclude_grundlovsdag_in_Total] [tinyint] NULL,
[exclude_Juleaftensdag_in_Total] [tinyint] NULL,
[Exclude_Nytaarsaftensdag_in_total] [tinyint] NULL,
[Exclude_Holy_Days_In_Norm] [tinyint] NULL,
[suggest_replacement_moves_to_Existing_department] [tinyint] NULL,
[Calculate_norm_Hours_to_This_Month] [tinyint] NULL,
[check_for_rules_in_suggest_replacement] [tinyint] NULL,
[totals_diff_Based_Upon_Account_no] [int] NULL,
[Mail_adress_MTID_Report_SLKE] [char](50) NULL,
[do_recalculate_on_delete] [tinyint] NULL,
[Duties_Count_On_first_day_In_Roll] [tinyint] NULL,
[use_colors_for_saved_duties] [tinyint] NULL,
[Set_Sum_In_personal_view] [tinyint] NULL,
[dont_shov_shifttype_25_in_roster] [tinyint] NULL,
[Show_comments_on_time_figure] [tinyint] NULL,
[Netupdate_Personal_Module] [tinyint] NULL,
[Do_Not_treat_Barsel_as_broken_Apployyment] [tinyint] NULL,
[check_vacation_In_totals] [tinyint] NULL,
[Totals_by_rules_PositionTypeChoice] [tinyint] NULL,
[Show_Roll_in_roster] [tinyint] NULL,
[Afs_NegativeTimeAccount] [tinyint] NULL,
[Afs_includingWeekend] [tinyint] NULL,
[Afs_setToNorm] [tinyint] NULL,
[Afs_CreateNotOccopied] [tinyint] NULL,
[Afs_removeNormHours] [tinyint] NULL,
[Afs_RemoveAdditions] [tinyint] NULL,
[Afs_RemoveOtherWork] [tinyint] NULL,
[No_of_saved_duties_Personal] [tinyint] NULL,
[Norm_basic] [tinyint] NULL,
[use_SLS_Update] [tinyint] NULL,
[Path_for_SLS_update] [varchar](255) NULL,
[sick_incl_weekend] [tinyint] NULL,
[sick_set_to_norm] [tinyint] NULL,
[sick_copy_to_not_occupied] [tinyint] NULL,
[sick_remove_normhours] [tinyint] NULL,
[sick_remove_additions] [tinyint] NULL,
[totals_subtract_offhours] [tinyint] NULL,
[path_for_SLS_template] [varchar](255) NULL,
[use_shifttype_koloni] [tinyint] NULL,
[use_shifttype_on_duty_call] [tinyint] NULL,
[use_shifttype_FO_DAY] [tinyint] NULL,
[use_shifttype_ful_day] [tinyint] NULL,
[negative_offday_inLieu_in_totals] [tinyint] NULL,
[AFS_Offday_With_additions] [tinyint] NULL,
[OnCallChange] [tinyint] NULL,
[timebanks_in_minutes] [tinyint] NULL,
[use_Account_for_shifttype_FO_DAY] [int] NULL,
[recalculate_on_login_to_schema] [tinyint] NULL,
[recalculate_on_save_schema] [tinyint] NULL,
[USE_SHIFTTYPE_CALLONCALLDUTY] [tinyint] NULL,
[Timeaccount_for_km] [int] NULL,
CONSTRAINT [PK_dbo_PK_TYPESID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
May 13, 2014 at 12:10 am
Is it possible to give some sample input and the desired output (may be for a smaller table :-)).
At first thought I'm thinking about using a hash to see if anything has changed.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2014 at 12:44 pm
I'm not sure I totally understand the problem as presented. Are ID and ope_number the only columns that can be different?
I think the EXCEPT operator could be your friend like this:
Select
columns
FROM
table
EXCEPT
Select
columns
FROM
table
This will return rows in set 1 that aren't in set 2.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 13, 2014 at 2:13 pm
Thanks for showing me the EXCEPT operator. I am a 'Newbie', and it's amazing how many operaters I am not aware of.
Yes, in most situations, and in all the situations I am lokking for, the ID and the ope_no should be the only ons which is not different - All the rest should be identical.
The idea is, that the users can have different settings, but in the real world, they do not.
Best regards
Edvard Korsbæk
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply