/****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