-- ============================================= -- author: fordareh -- create date: 04/30/07 -- update history: -- *jas 04/30/07 - created as part of the driving force applicant selection program. -- -- description of what this sql currently already does: -- 1) create the sql tables, etask and etimelog -- 2) fill the sql tables with test data -- 3) provide stored procedure shell record -- 4) execute stored procedure using a valid value -- and a null value -- -- instructions for what this sql should do upon completion (to run, you'll have to replace xxxxxx with your -- database name ): -- -- - this sql should run in any installation of sql server 2005. [in the event that you do -- not have access to sql server 2005 for testing or do not have create/drop table/procedure privelages, -- please make your best attempt at writing the stored procedure and return as is.] -- -- - the sql below drops (if necessary) then recreates 2 sql tables: -- etask -- taskid int identity(1,1) primary key -- name varchar(50) not null -- description varchar(max) null -- duedate datetime not null -- -- etimelog -- timelogid int identity(1,1) primary key -- taskid int foreign key for etask -- hoursspend decimal(4,2) -- -- - these tables are intended to represent a very rudimentary time logging system for an hourly job -- -- - your job is to fill in the stored procedure (prcgetaggregatetimeloginfo) in part 3 such -- that the queries executed in part 4 will run successfully -- -- - prcgetaggregatetimeloginfo is designed to take a single, optional taskid as a parameter, -- and return a table containing one of 2 types of information: -- -- 1) for non-null taskids, prcgetaggregatetimeloginfo returns a summary of the time log data for a -- single task -- -- 2) for null taskids, prcgetaggregatetimeloginfo returns a summary of the time log data for every -- task record in etask -- -- - prcgetaggregatetimeloginfo should return the following column list: -- 1) etask.taskid -- 2) etask.name -- 3) etask.description -- 4) etask.duedate -- 5) sum(etimelog.hoursspend) -- 5) avg(etimelog.hoursspend) -- 5) min(etimelog.hoursspend) -- 5) max(etimelog.hoursspend) -- 5) count(etimelog.hoursspend) -- -- good luck! -- -- ============================================= -- replace xxxxxx with your database name use [xxxxxx] go -- ============================================= -- ============================================= -- 1) create the sql tables, etask and etimelog -- ============================================= -- ============================================= /* get rid of the foreign key from the time log to the task table */ if exists (select * from sys.foreign_keys where object_id = object_id(n'[dbo].[fk_etimelog_etask]') and parent_object_id = object_id(n'[dbo].[etimelog]')) alter table [dbo].[etimelog] drop constraint [fk_etimelog_etask] go /****** drop the time log ******/ if exists (select * from sys.objects where object_id = object_id(n'[dbo].[etimelog]') and type in (n'u')) drop table [dbo].[etimelog] go /****** drop the task table ******/ if exists (select * from sys.objects where object_id = object_id(n'[dbo].[etask]') and type in (n'u')) drop table [dbo].[etask] go /* recreate the task table */ set ansi_nulls on go set quoted_identifier on go set ansi_padding on go create table [dbo].[etask]( [taskid] [int] identity(1,1) not null, [name] [varchar](50) collate sql_latin1_general_cp1_ci_as not null, [description] [varchar](max) collate sql_latin1_general_cp1_ci_as null, [duedate] [datetime] not null, constraint [pk_etask] primary key clustered ( [taskid] 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 set ansi_padding off /****** recreate the time log and foreign key ******/ set ansi_nulls on go set quoted_identifier on go create table [dbo].[etimelog]( [timelogid] [int] identity(1,1) not null, [taskid] [int] not null, [hoursspend] [decimal](4, 2) not null, constraint [pk_etimelog] primary key clustered ( [timelogid] 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 alter table [dbo].[etimelog] with check add constraint [fk_etimelog_etask] foreign key([taskid]) references [dbo].[etask] ([taskid]) go alter table [dbo].[etimelog] check constraint [fk_etimelog_etask] go -- ============================================= -- ============================================= -- 2) fill the sql tables with test data -- ============================================= -- ============================================= /* local to hold inserted task id */ declare @itaskid int insert into [etask] ([name] ,[description] ,[duedate]) values ('task 1' ,'description of task 1' ,dateadd(mm,2,getdate())) select @itaskid = scope_identity() insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,1.2) insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,0.2) insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,10.15) insert into [etask] ([name] ,[description] ,[duedate]) values ('task 2' ,'description of task 2' ,dateadd(mm,3,getdate())) select @itaskid = scope_identity() insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,1.9) insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,4.5) insert into [etask] ([name] ,[description] ,[duedate]) values ('task 3' ,'description of task 3' ,dateadd(mm,3,getdate())) select @itaskid = scope_identity() insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,7.2) insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,1.5) insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,1.2) insert into [etimelog] ([taskid] ,[hoursspend]) values (@itaskid ,11.5) go -- ============================================= -- ============================================= -- 3) provide stored procedure shell record -- ============================================= -- ============================================= if exists(select * from sysobjects where [name] = n'prcgetaggregatetimeloginfo') drop procedure [prcgetaggregatetimeloginfo] go set ansi_nulls on go set quoted_identifier on go create procedure [prcgetaggregatetimeloginfo] ( @taskid int = null ) as begin /* answer goes here */ end go -- =============================================== -- =============================================== -- 4) execute stored procedure using a valid value -- and a null value -- =============================================== -- =============================================== execute [prcgetaggregatetimeloginfo] 3 execute [prcgetaggregatetimeloginfo] null