January 28, 2014 at 2:16 pm
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
January 28, 2014 at 3:15 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 29, 2014 at 1:28 am
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_IDRAC_IDRAC_REF_IDRAC_TYPERAC_ACTORRAC_AUTHORISEDRAC_DATERAC_TIMERAC_DONE_ONDUTYRAC_COMMENTRAC_TO_TEAM_KEYRAC_CONFIRMEDRAC_CREATED_ONRAC_CREATED_ATRAC_CREATED_BYRAC_AMENDED_ONRAC_AMENDED_ATRAC_AMENDED_BYRAC_ACCESS_KEYRAC_UPDATE_KEYRAC_SHAREDRAC_PARENT_IDRAC_OVERRIDERAC_SENSITIVERAC_SILENTRAC_OWNER_TEM_ID
27429719619ALLOCATE 181800:00.000:00.000:00.0NULLNULL131:10.018.311931:10.018.3119U T0031 NULLNULL000:00.00T0031
27429720619CLOS 181800:00.000:00.000:00.0NULLNULL131:10.018.311931:10.018.3119U T0031 NULLNULL000:00.00T0031
27429721620ALLOCATE 181800:00.000:00.000:00.0NULLNULL131:10.018.311931:10.018.3119U T0307 NULLNULL000:00.00T0307
27429722620CLOS 181800:00.000:00.000:00.0NULLNULL131:10.018.311931:10.018.3119U T0307 NULLNULL000:00.00T0307
27429723621ALLOCATE 181800:00.000:00.000:00.0NULLNULL131:10.018.311931:10.018.3119U T0286 NULLNULL000:00.00T0286
27429724621CLOS 181800:00.000NULLNULL131:10.018.311931:10.018.3119U T0286 NULLNULL000T0286
27429725622ALLOCATE 181800:00.000NULLNULL131:10.018.311931:10.018.3119U T0286 NULLNULL000T0286
27429726622CLOS 181800:00.000NULLNULL131:10.018.311931:10.018.3119U T0286 NULLNULL000T0286
27429729624ALLOCATE 181800:00.000NULLNULL131:10.018.311931:10.018.3119U T0064 NULLNULL000T0064
27429730624CLOS 181800:00.000NULLNULL131:10.018.311931:10.018.3119U T0064 NULLNULL000T0064
274291392816860729REQT 19719700:00.09.360NULLNULL136:00.09.3660016836:00.09.36600168U T0064 NULLNULL000T0064
274291394845860729ACCT 19719700:00.014.430CLIENT TRANSFER ACCEPTED NULL143:13.014.4360016843:13.014.43600168U T0064 NULLNULL000T0064
274291397310860729ALLOCATE 19719700:00.013.180NULLNULL118:37.013.1860016818:37.013.18600168U T0064 NULLNULL000NULL
274291443476860729ALLOCATE 19419400:00.015.140NULLNULL115:35.015.1560016515:35.015.15600165U T0064 0NULL100T0064
274292121498860729ALLOCATE 19719700:00.016.120NULLNULL112:43.016.1260016812:43.016.12600168U T0064 NULLNULL000T0064
274292688764860729CLOS 343400:00.013.30Fostering record open on partner Carole Oakes NULL130:37.013.360000530:37.013.3600005U T0064 0NULL100T0064
hope this helps.
Cheers
Jon
January 29, 2014 at 1:57 am
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 😉
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
January 29, 2014 at 3:01 am
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
January 29, 2014 at 3:03 am
Did you test it before posting?
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
January 29, 2014 at 3:05 am
Yes?!
Cheers
J
January 29, 2014 at 3:08 am
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!
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
January 29, 2014 at 3:48 am
/****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
January 29, 2014 at 5:57 am
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.
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
January 29, 2014 at 6:15 am
/****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_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_DATE] [datetime2](7) 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'
--===== 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_SURNAME,PER_DOB)
SELECT '27429','MR','ROBERT','OAKES','Jul 6 1956 12:00AM'
--===== 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_DATE)
SELECT '27429','719','619','ALLOCATE ','1990-03-22 00:00:00.0000000' UNION ALL
SELECT '27429','720','619','CLOS ','1999-06-03 00:00:00.0000000' UNION ALL
SELECT '27429','721','620','ALLOCATE ','1988-07-04 00:00:00.0000000' UNION ALL
SELECT '27429','722','620','CLOS ','1990-03-22 00:00:00.0000000' UNION ALL
SELECT '27429','723','621','ALLOCATE ','1991-11-14 00:00:00.0000000' UNION ALL
SELECT '27429','724','621','CLOS ','1999-04-14 00:00:00.0000000' UNION ALL
SELECT '27429','725','622','ALLOCATE ','1999-04-14 00:00:00.0000000' UNION ALL
SELECT '27429','726','622','CLOS ','2000-10-10 00:00:00.0000000' UNION ALL
SELECT '27429','729','624','ALLOCATE ','2001-04-25 00:00:00.0000000' UNION ALL
SELECT '27429','730','624','CLOS ','2002-06-17 00:00:00.0000000' UNION ALL
SELECT '27429','1392816','860729','REQT ','2009-01-12 00:00:00.0000000' UNION ALL
SELECT '27429','1394845','860729','ACCT ','2009-01-12 00:00:00.0000000' UNION ALL
SELECT '27429','1397310','860729','ALLOCATE ','2009-01-13 00:00:00.0000000' UNION ALL
SELECT '27429','1443476','860729','ALLOCATE ','2009-02-23 00:00:00.0000000' UNION ALL
SELECT '27429','2121498','860729','ALLOCATE ','2011-01-31 00:00:00.0000000' UNION ALL
SELECT '27429','2688764','860729','CLOS ','2013-08-16 00:00:00.0000000'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT ChildReferralAction OFF
Fully tested... Again sorry 🙂
January 29, 2014 at 6:24 am
Msg 8106, Level 16, State 1, Line 5
Table 'ChildReferrals' does not have the identity property. Cannot perform SET operation.
Jon - I suspect you will learn as much from this exercise as you will from having the primary problem solved 😉
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
January 29, 2014 at 6:32 am
jsayerweb (1/29/2014)
Fully tested... Again sorry 🙂
Msg 8106, Level 16, State 1, Line 5
Table 'ChildReferrals' does not have the identity property. Cannot perform SET operation.
Msg 8106, Level 16, State 1, Line 19
Table 'ChildDetails' does not have the identity property. Cannot perform SET operation.
Msg 8106, Level 16, State 1, Line 29
Table 'ChildReferralAction' does not have the identity property. Cannot perform SET operation.
This runs without error:
/****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_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_DATE] [datetime2](7) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****Populate Referral Action Table ****/
--===== 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'
/****Populate Child Table ****/
--===== Insert the test data into the test table
INSERT INTO ChildDetails
(PER_ID,NAM_TITLE,NAM_FORE1,NAM_SURNAME,PER_DOB)
SELECT '27429','MR','ROBERT','OAKES','Jul 6 1956 12:00AM'
/****Populate Referral Action ****/
--===== Insert the test data into the test table
INSERT INTO ChildReferralAction
(CLI_PER_ID,RAC_ID,RAC_REF_ID,RAC_TYPE,RAC_DATE)
SELECT '27429','719','619','ALLOCATE ','1990-03-22 00:00:00.0000000' UNION ALL
SELECT '27429','720','619','CLOS ','1999-06-03 00:00:00.0000000' UNION ALL
SELECT '27429','721','620','ALLOCATE ','1988-07-04 00:00:00.0000000' UNION ALL
SELECT '27429','722','620','CLOS ','1990-03-22 00:00:00.0000000' UNION ALL
SELECT '27429','723','621','ALLOCATE ','1991-11-14 00:00:00.0000000' UNION ALL
SELECT '27429','724','621','CLOS ','1999-04-14 00:00:00.0000000' UNION ALL
SELECT '27429','725','622','ALLOCATE ','1999-04-14 00:00:00.0000000' UNION ALL
SELECT '27429','726','622','CLOS ','2000-10-10 00:00:00.0000000' UNION ALL
SELECT '27429','729','624','ALLOCATE ','2001-04-25 00:00:00.0000000' UNION ALL
SELECT '27429','730','624','CLOS ','2002-06-17 00:00:00.0000000' UNION ALL
SELECT '27429','1392816','860729','REQT ','2009-01-12 00:00:00.0000000' UNION ALL
SELECT '27429','1394845','860729','ACCT ','2009-01-12 00:00:00.0000000' UNION ALL
SELECT '27429','1397310','860729','ALLOCATE ','2009-01-13 00:00:00.0000000' UNION ALL
SELECT '27429','1443476','860729','ALLOCATE ','2009-02-23 00:00:00.0000000' UNION ALL
SELECT '27429','2121498','860729','ALLOCATE ','2011-01-31 00:00:00.0000000' UNION ALL
SELECT '27429','2688764','860729','CLOS ','2013-08-16 00:00:00.0000000'
Could you edit previous posts and wrap the commands on one of the code tags (see panel on the left)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 29, 2014 at 6:46 am
Huge Learning curve...
I have got to start somewhere:
See below:
/****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].[ChildDetails2]') AND type in (N'U'))
DROP TABLE [dbo].[ChildDetails2]
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].[ChildDetails2](
[PER_ID] [numeric](8, 0) NULL,
[NAM_TITLE] [varchar](10) NULL,
[NAM_FORE1] [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].[ChildReferrals2]') AND type in (N'U'))
DROP TABLE [dbo].[ChildReferrals2]
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].[ChildReferrals2](
[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].[ChildReferralAction2]') AND type in (N'U'))
DROP TABLE [dbo].[ChildReferralAction2]
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].[ChildReferralAction2](
[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_DATE] [datetime2](7) NULL,
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****Populate Referral Action Table ****/
--===== Insert the test data into the test table
INSERT INTO ChildReferrals2
(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'
/****Populate Child Table ****/
--===== Insert the test data into the test table
INSERT INTO ChildDetails2
(PER_ID,NAM_TITLE,NAM_FORE1,NAM_SURNAME,PER_DOB)
SELECT '27429','MR','ROBERT','OAKES','Jul 6 1956 12:00AM'
/****Populate Referral Action ****/
--===== Insert the test data into the test table
INSERT INTO ChildReferralAction2
(CLI_PER_ID,RAC_ID,RAC_REF_ID,RAC_TYPE,RAC_DATE)
SELECT '27429','719','619','ALLOCATE ','1990-03-22 00:00:00.0000000' UNION ALL
SELECT '27429','720','619','CLOS ','1999-06-03 00:00:00.0000000' UNION ALL
SELECT '27429','721','620','ALLOCATE ','1988-07-04 00:00:00.0000000' UNION ALL
SELECT '27429','722','620','CLOS ','1990-03-22 00:00:00.0000000' UNION ALL
SELECT '27429','723','621','ALLOCATE ','1991-11-14 00:00:00.0000000' UNION ALL
SELECT '27429','724','621','CLOS ','1999-04-14 00:00:00.0000000' UNION ALL
SELECT '27429','725','622','ALLOCATE ','1999-04-14 00:00:00.0000000' UNION ALL
SELECT '27429','726','622','CLOS ','2000-10-10 00:00:00.0000000' UNION ALL
SELECT '27429','729','624','ALLOCATE ','2001-04-25 00:00:00.0000000' UNION ALL
SELECT '27429','730','624','CLOS ','2002-06-17 00:00:00.0000000' UNION ALL
SELECT '27429','1392816','860729','REQT ','2009-01-12 00:00:00.0000000' UNION ALL
SELECT '27429','1394845','860729','ACCT ','2009-01-12 00:00:00.0000000' UNION ALL
SELECT '27429','1397310','860729','ALLOCATE ','2009-01-13 00:00:00.0000000' UNION ALL
SELECT '27429','1443476','860729','ALLOCATE ','2009-02-23 00:00:00.0000000' UNION ALL
SELECT '27429','2121498','860729','ALLOCATE ','2011-01-31 00:00:00.0000000' UNION ALL
SELECT '27429','2688764','860729','CLOS ','2013-08-16 00:00:00.0000000'
January 29, 2014 at 7:11 am
Using Gail's script:
/*
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.
*/
-- Have a look at the data
SELECT
cd.*, '#' '#',
cr.*, '#' '#',
x.*
FROM dbo.ChildDetails cd
INNER JOIN dbo.ChildReferrals cr
ON cr.CLI_PER_ID = cd.PER_ID
CROSS APPLY (
SELECT *
FROM dbo.ChildReferralAction cra
WHERE cra.CLI_PER_ID = cr.CLI_PER_ID
AND cra.RAC_REF_ID = cr.REF_ID
) x
ORDER BY cd.PER_ID
-- possible solution
SELECT
cd.*, '#' '#',
cr.*, '#' '#',
x.*
FROM dbo.ChildDetails cd
INNER JOIN dbo.ChildReferrals cr
ON cr.CLI_PER_ID = cd.PER_ID
CROSS APPLY (
SELECT
[OpenDate] = MAX(CASE WHEN RAC_TYPE = 'ALLOCATE' THEN RAC_DATE ELSE NULL END),
[CloseDate] = MAX(CASE WHEN RAC_TYPE IN ('CLOS', 'NFA') THEN RAC_DATE ELSE NULL END)
FROM dbo.ChildReferralAction cra
WHERE cra.CLI_PER_ID = cr.CLI_PER_ID
AND cra.RAC_REF_ID = cr.REF_ID
) x
ORDER BY cd.PER_ID, x.CloseDate
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply