Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Referral Dates Nightmare Expand / Collapse
Author
Message
Posted Tuesday, January 28, 2014 2:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 2:48 AM
Points: 8, Visits: 13
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




Post #1535631
Posted Tuesday, January 28, 2014 3:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, October 18, 2014 4:43 AM
Points: 5,401, Visits: 7,512
If you take a look at the first link in my signature below, you'll see how we usually prefer code assistance requests to be formatted. We're looking to have consumable schema and data, along with expected results from that data, so we can proof our code before offering it and explaining it. We're volunteers, so the more work you can do up front for us, the more likely we'll be able to spend the time to help you.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1535648
Posted Wednesday, January 29, 2014 1:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 2:48 AM
Points: 8, Visits: 13
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



Post #1535754
Posted Wednesday, January 29, 2014 1:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 6,781, Visits: 13,987
Hi Jon

Anyone wishing to test queries against your tables would first have to convert this:
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


into a series of INSERTs to populate the tables.
It's considered best practice for the OP - that's you - to do the donkey work, so that those who are willing and able to help you out with the code can do so without first spending half an hour setting up sample data. You do the easy bit, we do the hard bit


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1535768
Posted Wednesday, January 29, 2014 3:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 2:48 AM
Points: 8, Visits: 13
Take 3 :)


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

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

/****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,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)
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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


--===== 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,PER_DOB_EST,DTH_DATE,DTH_CONFIRM,PER_GENDER,PER_RELIGION,PER_NATIONALITY,PER_ETHNICITY,PER_MARITAL,PER_LOCALITY,PER_CP_STATUS,PER_CREATED_ON,PER_CREATED_AT,PER_CREATED_BY,PER_AMENDED_ON,PER_AMENDED_AT,PER_AMENDED_BY,PER_ACCESS_KEY,PER_UPDATE_KEY)
Select 27429,MR ,dave,NULL,NULL,smith,00:00.0,00:00.0,NULL,NULL,M ,8,NULL,A1 ,M ,NULL,NULL,29:20.0,18.29,1,09:12.0,12.09,714761,U ,U


--===== 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,RAC_SHARED,RAC_PARENT_ID,RAC_OVERRIDE,RAC_SENSITIVE,RAC_SILENT,RAC_OWNER_TEM_ID)
SELECT 27429,719,619,ALLOCATE ,18,18,00:00.0,0,0,NULL,NULL,1,31:10.0,18.31,19,31:10.0,18.31,19,U ,T0031 ,NULL,NULL,0,0,0,T0031 UNION ALL
SELECT 27429,720,619,CLOS ,18,18,00:00.0,0,0,NULL,NULL,1,31:10.0,18.31,19,31:10.0,18.31,19,U ,T0031 ,NULL,NULL,0,0,0,T0031 UNION ALL
SELECT 27429,721,620,ALLOCATE ,18,18,00:00.0,0,0,NULL,NULL,1,31:10.0,18.31,19,31:10.0,18.31,19,U ,T0307 ,NULL,NULL,0,0,0,T0307 UNION ALL
SELECT 27429,722,620,CLOS ,18,18,00:00.0,0,0,NULL,NULL,1,31:10.0,18.31,19,31:10.0,18.31,19,U ,T0307 ,NULL,NULL,0,0,0,T0307 UNION ALL
SELECT 27429,723,621,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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 27429,1394845,860729,ACCT ,197,197,00:00.0,14.43,0,test,NULL,1,43:13.0,14.43,600168,43:13.0,14.43,600168,U ,T0064 ,NULL,NULL,0,0,0,T0064 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 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 UNION ALL
SELECT 27429,2688764,860729,CLOS ,34,34,00:00.0,13.3,0,test,NULL,1,30:37.0,13.3,600005,30:37.0,13.3,600005,U ,T0064 ,0,NULL,1,0,0,T0064

--===== Set the identity insert back to normal
SET IDENTITY_INSERT ChildReferralAction OFF
Post #1535786
Posted Wednesday, January 29, 2014 3:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 6,781, Visits: 13,987
Did you test it before posting?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1535787
Posted Wednesday, January 29, 2014 3:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 2:48 AM
Points: 8, Visits: 13
Yes?!

Cheers
J
Post #1535790
Posted Wednesday, January 29, 2014 3:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 6,781, Visits: 13,987
jsayerweb (1/29/2014)
Yes?!

Cheers
J


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ':'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ':'.

Come on Jon, you can do better than this!


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1535792
Posted Wednesday, January 29, 2014 3:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 6, 2014 2:48 AM
Points: 8, Visits: 13
/****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
Post #1535804
Posted Wednesday, January 29, 2014 5:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 6,781, Visits: 13,987
jsayerweb (1/29/2014)

...Hope that makes sense and my above code works...


Did you test it? I was planning to work on your problem for you over lunch but the sample data script still doesn't work!
Open a new query window and change database context to TempDB. Run your script and see what happens. Drop objects created in TempDB by your script when you're done.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1535858
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse