• /****Child Table ****/

    /****** Object: Table [dbo].[ChildDetails] Script Date: 01/29/2014 08:21:30 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChildDetails]') AND type in (N'U'))

    DROP TABLE [dbo].[ChildDetails]

    GO

    /****** Object: Table [dbo].[ChildDetails] Script Date: 01/29/2014 08:21:30 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ChildDetails](

    [PER_ID] [numeric](8, 0) NULL,

    [NAM_TITLE] [varchar](10) NULL,

    [NAM_FORE1] [varchar](35) NULL,

    [NAM_FORE2] [varchar](35) NULL,

    [NAM_FORE3] [varchar](35) NULL,

    [NAM_SURNAME] [varchar](35) NULL,

    [PER_DOB] [datetime] NULL,

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****Referral Table ****/

    /****** Object: Table [dbo].[ChildReferrals] Script Date: 01/29/2014 08:22:27 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChildReferrals]') AND type in (N'U'))

    DROP TABLE [dbo].[ChildReferrals]

    GO

    /****** Object: Table [dbo].[ChildReferrals] Script Date: 01/29/2014 08:22:27 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ChildReferrals](

    [CLI_PER_ID] [numeric](8, 0) NULL,

    [REF_ID] [numeric](8, 0) NULL,

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****Referral Action Table ****/

    /****** Object: Table [dbo].[ChildReferralAction] Script Date: 01/29/2014 08:22:51 ******/

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ChildReferralAction]') AND type in (N'U'))

    DROP TABLE [dbo].[ChildReferralAction]

    GO

    /****** Object: Table [dbo].[ChildReferralAction] Script Date: 01/29/2014 08:22:51 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[ChildReferralAction](

    [CLI_PER_ID] [numeric](8, 0) NULL,

    [RAC_ID] [numeric](8, 0) NULL,

    [RAC_REF_ID] [numeric](8, 0) NULL,

    [RAC_TYPE] [varchar](10) NULL,

    [RAC_ACTOR] [numeric](8, 0) NULL,

    [RAC_AUTHORISED] [numeric](8, 0) NULL,

    [RAC_DATE] [datetime2](7) NULL,

    [RAC_TIME] [numeric](4, 2) NULL,

    [RAC_DONE_ONDUTY] [numeric](1, 0) NULL,

    [RAC_COMMENT] [varchar](255) NULL,

    [RAC_TO_TEAM_KEY] [varchar](5) NULL,

    [RAC_CONFIRMED] [numeric](1, 0) NULL,

    [RAC_CREATED_ON] [datetime2](7) NULL,

    [RAC_CREATED_AT] [numeric](4, 2) NULL,

    [RAC_CREATED_BY] [numeric](8, 0) NULL,

    [RAC_AMENDED_ON] [datetime2](7) NULL,

    [RAC_AMENDED_AT] [numeric](4, 2) NULL,

    [RAC_AMENDED_BY] [numeric](8, 0) NULL,

    [RAC_ACCESS_KEY] [varchar](15) NULL,

    [RAC_UPDATE_KEY] [varchar](15) NULL,

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    /****Populate Referral Action Table ****/

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT ChildReferrals ON

    --===== Insert the test data into the test table

    INSERT INTO ChildReferrals

    (CLI_PER_ID,REF_ID)

    SELECT '27429','619', UNION ALL

    SELECT '27429','620', UNION ALL

    SELECT '27429','621', UNION ALL

    SELECT '27429','622', UNION ALL

    SELECT '27429','624', UNION ALL

    SELECT '27429','860729', UNION ALL

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT ChildReferrals OFF

    /****Populate Child Table ****/

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT ChildDetails ON

    --===== Insert the test data into the test table

    INSERT INTO ChildDetails

    (PER_ID,NAM_TITLE,NAM_FORE1,NAM_FORE2,NAM_FORE3,NAM_SURNAME,PER_DOB)

    SELECT '27429','MR','ROBERT','OAKES','Jul 6 1956 12:00AM' UNION ALL

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT ChildDetails OFF

    /****Populate Referral Action ****/

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT ChildReferralAction ON

    --===== Insert the test data into the test table

    INSERT INTO ChildReferralAction

    (CLI_PER_ID,RAC_ID,RAC_REF_ID,RAC_TYPE,RAC_ACTOR,RAC_AUTHORISED,RAC_DATE,RAC_TIME,RAC_DONE_ONDUTY,RAC_COMMENT,RAC_TO_TEAM_KEY,RAC_CONFIRMED,RAC_CREATED_ON,RAC_CREATED_AT,RAC_CREATED_BY,RAC_AMENDED_ON,RAC_AMENDED_AT,RAC_AMENDED_BY,RAC_ACCESS_KEY,RAC_UPDATE_KEY)

    SELECT '27429','719','619','ALLOCATE ','18','18','1990-03-22 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0031 ', UNION ALL

    SELECT '27429','720','619','CLOS ','18','18','1999-06-03 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0031 ', UNION ALL

    SELECT '27429','721','620','ALLOCATE ','18','18','1988-07-04 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0307 ', UNION ALL

    SELECT '27429','722','620','CLOS ','18','18','1990-03-22 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0307 ', UNION ALL

    SELECT '27429','723','621','ALLOCATE ','18','18','1991-11-14 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0286 ', UNION ALL

    SELECT '27429','724','621','CLOS ','18','18','1999-04-14 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0286 ', UNION ALL

    SELECT '27429','725','622','ALLOCATE ','18','18','1999-04-14 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0286 ', UNION ALL

    SELECT '27429','726','622','CLOS ','18','18','2000-10-10 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0286 ', UNION ALL

    SELECT '27429','729','624','ALLOCATE ','18','18','2001-04-25 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0064 ', UNION ALL

    SELECT '27429','730','624','CLOS ','18','18','2002-06-17 00:00:00.0000000','0.00','18.31','19','2006-12-08 18:31:10.0000000','18.31','19','U ','T0064 ', UNION ALL

    SELECT '27429','1392816','860729','REQT ','197','197','2009-01-12 00:00:00.0000000','9.36','9.36','600168','2009-01-12 09:36:00.0000000','9.36','600168','U ','T0064 ', UNION ALL

    SELECT '27429','1394845','860729','ACCT ','197','197','2009-01-12 00:00:00.0000000','14.43','14.43','600168','2009-01-12 14:43:13.0000000','14.43','600168','U ','T0064 ', UNION ALL

    SELECT '27429','1397310','860729','ALLOCATE ','197','197','2009-01-13 00:00:00.0000000','13.18','13.18','600168','2009-01-13 13:18:37.0000000','13.18','600168','U ','T0064 ', UNION ALL

    SELECT '27429','1443476','860729','ALLOCATE ','194','194','2009-02-23 00:00:00.0000000','15.14','15.15','600165','2009-02-23 15:15:35.0000000','15.15','600165','U ','T0064 ', UNION ALL

    SELECT '27429','2121498','860729','ALLOCATE ','197','197','2011-01-31 00:00:00.0000000','16.12','16.12','600168','2011-01-31 16:12:43.0000000','16.12','600168','U ','T0064 ', UNION ALL

    SELECT '27429','2688764','860729','CLOS ','34','34','2013-08-16 00:00:00.0000000','13.30','13.30','600005','2013-08-16 13:30:37.0000000','13.30','600005','U ','T0064 ', UNION ALL

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT ChildReferralAction OFF

    Right.... Sorry about this... I have trimmed out all of the rubbish fields. Basically i need a way of getting one line per referral with start and end dates (null end date if open) the referral action table holds a status RAC_TYPE 'allocate = open date' 'CLOS = Closed Date' 'NFA = Closed date' other status i am not to interested in. I could with selecting the min allocated date per referral as start date and the max nfa/close date as end date per referral?

    Hope that makes sense and my above code works... :S sorry for being so rubbish

    Thanks again 🙂

    Jon