Referral Nightmare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Referral Nightmare

Author

Topic

99jsayer
Starting Member

3 Posts

Posted - 2014-01-28 : 16:45:54

Hello Guys,

I am somewhat new to SQL. I really need some help with the following query. I have three tables like so:

tblclient
perid
name
dob

tblreferral
refid
type
info
commments

tblreferralaction
refactid
status (allocate, nfa, closed)
status date

I need to get the list of referrals, per client, in chronological order using the dates from the referralaction table. A allocations status will be given once a referral is open. A status of NFA or Closed means that referral is closed.
A client can have many referrals, i need to identify the current one and have the others with their open and closed dates.
I have tried pivoting the data and just cannot get it to work due to the various dates following on.

Please can someone help?!

Thanks in advanced!

Kind Regards
Jonathan

Jsayer

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 17:03:46

Can you provide some sample data.. It would be much easier and faster to answer this query..

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-28 : 19:33:09

I don't see any column(s) that can be used to join those tables. Thus, I'm not sure how one would relate any given action to a specific referral, or a referral to a specific client, etc..

99jsayer
Starting Member

3 Posts

Posted - 2014-01-29 : 03:29:11

Hello,

I am sorry for my first post, here is some more detail:

Child Table

USE [LLMigration]
GO

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

USE [LLMigration]
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,
[PER_DOB_EST] [numeric](1, 0) NULL,
[DTH_DATE] [datetime] NULL,
[DTH_CONFIRM] [numeric](1, 0) NULL,
[PER_GENDER] [varchar](10) NULL,
[PER_RELIGION] [varchar](10) NULL,
[PER_NATIONALITY] [varchar](10) NULL,
[PER_ETHNICITY] [varchar](10) NULL,
[PER_MARITAL] [varchar](10) NULL,
[PER_LOCALITY] [varchar](10) NULL,
[PER_CP_STATUS] [varchar](10) NULL,
[PER_CREATED_ON] [datetime] NULL,
[PER_CREATED_AT] [numeric](4, 2) NULL,
[PER_CREATED_BY] [numeric](8, 0) NULL,
[PER_AMENDED_ON] [datetime] NULL,
[PER_AMENDED_AT] [numeric](4, 2) NULL,
[PER_AMENDED_BY] [numeric](8, 0) NULL,
[PER_ACCESS_KEY] [varchar](15) NULL,
[PER_UPDATE_KEY] [varchar](15) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Child Referral

USE [LLMigration]
GO

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

USE [LLMigration]
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,
[REF_REFERRED_BY] [numeric](8, 0) NULL,
[REF_REFERRER_ROLE] [varchar](10) NULL,
[REF_SOURCE_TEAM] [varchar](10) NULL,
[REF_REFERRED_TO] [varchar](10) NULL,
[REF_RECEIVED_BY] [varchar](10) NULL,
[REF_REFERRED_ON] [datetime2](7) NULL,
[REF_RECEIVED_ON] [datetime2](7) NULL,
[REF_RECEIVED_AT] [numeric](4, 2) NULL,
[REF_REFERRED_AT] [numeric](4, 2) NULL,
[REF_TO_PER_ID] [numeric](8, 0) NULL,
[REF_PRIORITY] [varchar](10) NULL,
[REF_METHOD] [varchar](10) NULL,
[REF_TREAT_REASON] [varchar](10) NULL,
[REF_REASON] [varchar](1999) NULL,
[REF_STATUS] [varchar](10) NULL,
[REF_CLI_AWARE] [numeric](1, 0) NULL,
[REF_FAM_AWARE] [numeric](1, 0) NULL,
[REF_ANONYMOUS] [numeric](1, 0) NULL,
[REF_CREATED_ON] [datetime2](7) NULL,
[REF_CREATED_AT] [numeric](4, 2) NULL,
[REF_CREATED_BY] [numeric](8, 0) NULL,
[REF_AMENDED_ON] [datetime2](7) NULL,
[REF_AMENDED_AT] [numeric](4, 2) NULL,
[REF_AMENDED_BY] [numeric](8, 0) NULL,
[REF_ACCESS_KEY] [varchar](15) NULL,
[REF_PARENT_ID] [numeric](8, 0) NULL,
[REF_OWNER_TEM_ID] [varchar](15) NULL,
[StartDate] [datetime2](7) NULL,
[EndDate] [datetime2](7) NULL,
[REF_TYPE] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Referral Action

USE [LLMigration]
GO

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

USE [LLMigration]
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,
[RAC_SHARED] [numeric](1, 0) NULL,
[RAC_PARENT_ID] [numeric](8, 0) NULL,
[RAC_OVERRIDE] [numeric](1, 0) NULL,
[RAC_SENSITIVE] [numeric](1, 0) NULL,
[RAC_SILENT] [numeric](1, 0) NULL,
[RAC_OWNER_TEM_ID] [varchar](15) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Data:

Child Details table is fairly set explanatory, 1 row per person, name etc PER_ID is the identifier

Referral Table:

CLI_PER_ID,REF_ID,REF_REFERRED_BY,REF_REFERRER_ROLE,REF_SOURCE_TEAM,REF_REFERRED_TO,REF_RECEIVED_BY,REF_REFERRED_ON,REF_RECEIVED_ON,REF_RECEIVED_AT,REF_REFERRED_AT,REF_TO_PER_ID,REF_PRIORITY,REF_METHOD,REF_TREAT_REASON,REF_REASON,REF_STATUS,REF_CLI_AWARE,REF_FAM_AWARE,REF_ANONYMOUS,REF_CREATED_ON,REF_CREATED_AT,REF_CREATED_BY,REF_AMENDED_ON,REF_AMENDED_AT,REF_AMENDED_BY,REF_ACCESS_KEY,REF_PARENT_ID,REF_OWNER_TEM_ID,StartDate,EndDate,REF_TYPE
27429,621,NULL,1,NULL,T0286 ,NULL,00:00.0,00:00.0,NULL,8.26,NULL,NULL,LACMIG ,60,*,CLO ,0,0,0,31:10.0,18.31,1,45:48.0,11.45,1,U ,NULL,T0286 ,00:00.0,00:00.0,MIG
27429,622,NULL,79,NULL,T0286 ,NULL,00:00.0,00:00.0,NULL,13.18,NULL,NULL,LACMIG ,G2 ,*,CLO ,0,0,0,31:10.0,18.31,1,45:48.0,11.45,1,U ,NULL,T0286 ,00:00.0,00:00.0,MIG
27429,624,NULL,1,NULL,T0064 ,NULL,00:00.0,00:00.0,NULL,15.33,NULL,NULL,LACMIG ,210,*,CLO ,0,0,0,31:10.0,18.31,1,45:48.0,11.45,1,U ,NULL,T0064 ,00:00.0,00:00.0,MIG
27429,860729,27429,1,NULL,T0064 ,4R41 ,00:00.0,00:00.0,NULL,NULL,NULL,NULL,INTERNAL ,240,*,CLO ,0,0,0,35:59.0,9.35,600168,30:37.0,13.3,600005,U ,NULL,T0064 ,NULL,00:00.0,TRA
27429,619,NULL,1,NULL,T0031 ,NULL,00:00.0,00:00.0,NULL,8.26,NULL,NULL,LACMIG ,60,*,CLO ,0,0,0,31:10.0,18.31,1,45:48.0,11.45,1,U ,NULL,T0031 ,00:00.0,00:00.0,MIG

Referral Action

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 RAC_SHARED RAC_PARENT_ID RAC_OVERRIDE RAC_SENSITIVE RAC_SILENT RAC_OWNER_TEM_ID
27429 719 619 ALLOCATE 18 18 00:00.0 00:00.0 00:00.0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0031 NULL NULL 0 00:00.0 0 T0031
27429 720 619 CLOS 18 18 00:00.0 00:00.0 00:00.0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0031 NULL NULL 0 00:00.0 0 T0031
27429 721 620 ALLOCATE 18 18 00:00.0 00:00.0 00:00.0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0307 NULL NULL 0 00:00.0 0 T0307
27429 722 620 CLOS 18 18 00:00.0 00:00.0 00:00.0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0307 NULL NULL 0 00:00.0 0 T0307
27429 723 621 ALLOCATE 18 18 00:00.0 00:00.0 00:00.0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0286 NULL NULL 0 00:00.0 0 T0286
27429 724 621 CLOS 18 18 00:00.0 0 0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0286 NULL NULL 0 0 0 T0286
27429 725 622 ALLOCATE 18 18 00:00.0 0 0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0286 NULL NULL 0 0 0 T0286
27429 726 622 CLOS 18 18 00:00.0 0 0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0286 NULL NULL 0 0 0 T0286
27429 729 624 ALLOCATE 18 18 00:00.0 0 0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0064 NULL NULL 0 0 0 T0064
27429 730 624 CLOS 18 18 00:00.0 0 0 NULL NULL 1 31:10.0 18.31 19 31:10.0 18.31 19 U T0064 NULL NULL 0 0 0 T0064
27429 1392816 860729 REQT 197 197 00:00.0 9.36 0 NULL NULL 1 36:00.0 9.36 600168 36:00.0 9.36 600168 U T0064 NULL NULL 0 0 0 T0064
27429 1394845 860729 ACCT 197 197 00:00.0 14.43 0 CLIENT TRANSFER ACCEPTED NULL 1 43:13.0 14.43 600168 43:13.0 14.43 600168 U T0064 NULL NULL 0 0 0 T0064
27429 1397310 860729 ALLOCATE 197 197 00:00.0 13.18 0 NULL NULL 1 18:37.0 13.18 600168 18:37.0 13.18 600168 U T0064 NULL NULL 0 0 0 NULL
27429 1443476 860729 ALLOCATE 194 194 00:00.0 15.14 0 NULL NULL 1 15:35.0 15.15 600165 15:35.0 15.15 600165 U T0064 0 NULL 1 0 0 T0064
27429 2121498 860729 ALLOCATE 197 197 00:00.0 16.12 0 NULL NULL 1 12:43.0 16.12 600168 12:43.0 16.12 600168 U T0064 NULL NULL 0 0 0 T0064
27429 2688764 860729 CLOS 34 34 00:00.0 13.3 0 Fostering record open on partner Carole Oakes NULL 1 30:37.0 13.3 600005 30:37.0 13.3 600005 U T0064 0 NULL 1 0 0 T0064

hope this helps.

Cheers
Jon

Jsayer

99jsayer
Starting Member

3 Posts

Posted - 2014-01-29 : 06:53:01

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

Jsayer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources