Monday, September 23, 2013

[T-SQL] job history check

[T-SQL] job history check

job history check

Posted: 22 Sep 2013 11:34 PM PDT

Hi friends,I have around 120 jobs in a SQL instance. Some of the jobs are scheduled on hourly basis and some of the jobs are scheduled on daily basis and some of the jobs are scheduled on weekly basis. Is there a way to get the failed job details in such a way that, it explains that this is hourly job this is daily job this is weekly job.Thanks in advance.

Aggregate minutes from varchar datatype.

Posted: 23 Sep 2013 12:49 AM PDT

Hi I have a field in a table that represents the status time spent on activity per employee. The field is stored as a varchar and is in the formatt '000:00:00' I would like to aggregate the minutes, here is a small sample size of the column; however when I try to sum this field I'm receiving this error and cannot cast to a number to aggregate.Msg 241, Level 16, State 1, Line 27Conversion failed when converting date and/or time from character string.Warning: Null value is eliminated by an aggregate or other SET operation.Any suggestions would be appreciated![code="sql"]--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#sum_minutes') IS NOT NULL DROP TABLE #sum_minutes--===== Create the test table with CREATE TABLE #sum_minutes ( STATUSTRACKING VARCHAR(50) )INSERT INTO #sum_minutes --(STATUSTRACKING)SELECT '00:00:01' UNION ALLSELECT NULL UNION ALLSELECT '03:20:01' UNION ALLSELECT '00:00:53' UNION ALLSELECT NULL UNION ALLSELECT '00:08:25' UNION ALLSELECT '00:12:18' UNION ALLSELECT '00:12:18' UNION ALLSELECT '76:03:43' UNION ALLSELECT '00:01:05' UNION ALLSELECT '00:20:24' UNION ALLSELECT '03:58:01' UNION ALLSELECT '00:07:47' SELECT SUM(DATEPART(MINUTE, STATUSTRACKING)) FROM #sum_minutes[/code]

Repeating update or replace statement for column

Posted: 22 Sep 2013 02:21 AM PDT

I want to create an update or replace statement which replaces Event_Code column with repeating values instead for example B100B102B103.......all the way to 10 and repeat again to the end of the table see results wanted SQL scrip example. Is there a way to do this? Thanks for the help.[code="sql"]Create table table1(Participant_ID int not null,Supporter_ID int not null,Event_Code varchar (50),Event_Role varchar (100) null)INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LELEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','LEG_BUR_0000','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','SEMF091212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','RFSEAH091212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','SEAJ251112','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','SERB031212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','SECW021212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','LM1301','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','SEGF050912','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','LM1301','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','SEDB220712','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','GNR0001','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','ELON09','IsVolunteer');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','ELON09','IsVolunteer');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','ELON09','IsVolunteer');[/code]========================================================================Wanted results table sample --Create table resultswanted(Participant_ID int not null,Supporter_ID int not null,Event_Code varchar (50),Event_Role varchar (100) null)INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B100','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','B102','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B103','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','B104','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B105','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','B106','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B107','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','B108','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B109','Did Not Attend');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','B110','Yes Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','B100','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B100','IsCoordinator');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','B102','No Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B103','IsCoordinator');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','B104','No Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B105','IsCoordinator');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','B106','No Reg');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','B107','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','B108','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','B109','Yes Attended');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','B110','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','B100','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','B100','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','B102','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','B103','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','B104','Do Not Register');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','B105','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','B106','Do Not Register');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','B107','Speaker');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','B108','Do Not Register');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','B109','IsVolunteer');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','B110','IsVolunteer');INSERT INTO resultswanted (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','B100','IsVolunteer'); Thanks again
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog