February 10, 2012 at 9:33 am
Hello All,
First, this is my first posting to a forum like this so I apologize in advance if I am violating any standards of decency in this post.
That said, I am a complete novice at SQL and was wondering if anyone could easily identify a problem with the following bit of code. I am running SQL Server 2005...
As background, I work for a research organization in which we are trying to reconstruct tables which have been extracted from the state foster care agency's data warehouse. So, we basically have a bunch of flat DIM and FACT tables that we are trying to put back together for use by our researchers.
The specific situation I am dealing with is as follows:
1) I have two tables: WRK_INTAKE and WRK_REMOVALS. WRK_INTAKE contains "intakes" (i.e. initial reports made to the agency) and WRK_REMOVALS contains records of foster care "placements".
2) There is no explicit link between WRK_INTAKE and WRK_REMOVALS (i.e. an individual child can have multiple placements and multiple intakes). Thus, we have come up with some rules to make the link: (1) We select the first placements for a given calendar year, and (2) look in the WRK_REMOVALS table and select the first placement within 90 days following this intake.
There are other filters built into our selection logic, but this is the basic idea...
3) We join these tables into a third table called WRK_INTAKE_AGGR_UNIVERSE_JM. This table contains a single record for each child in a given calendar year (i.e. the first intake received in that year) as well as the resulting placement identified above.
4) Some children are placed in foster care but not all children end up in foster care. There are some children who receive services in the home and other cases that are closed with no further action from the foster care agency. My current problem is to try and obtain the disposition of the cases that DO NOT end up in foster care. The disposition exists in WRK_INTAKE as a field called CD_INVS_DISP. I want this as an additional field in WRK_INTAKE_AGGR_UNIVERSE_JM.
The code used to create these tables without my added field is as follows. The code is not my own but it is what I have to work with and I do not currently have access to the actual author for further consultation. Hence my posting...
IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'WRK_INTAKE_AGGR_UNIVERSE_JM') AND type = (N'U'))
DROP TABLE WRK_INTAKE_AGGR_UNIVERSE_JM
select distinct
Year(DT_ACCESS_RCVD) as YEAR_INTAKE
, ID_PEOPLE_DIM
,TX_GNDR
,TX_RACE
,DT_BIRTH
,min(DT_ACCESS_RCVD) as INITIAL_INTAKE_DATE
,max(DT_ACCESS_RCVD) as LAST_INTAKE_DATE_IN_CY
,count(distinct ID_INTAKE_FACT) as COUNT_INTAKE
,cast(null as datetime) as Initial_Intake_Date_WithIn_90_Day_Removal_Date
,cast(null as datetime) as Removal_Date_WITHIN_90_Days
,cast(null as int) as Count_Intakes_In_CY_Prior_Removal
,cast(null as datetime) as Initial_Intake_Date_With_Investigation
,cast(null as int) as Days_Between_Initial_Intake_Date_With_Investigation_LEVEL2_Approval_Date
,cast(null as int) as Length_Of_Stay
,cast(null as int) as Age
,cast(null as varchar(200)) as tx_dsch_rsn
,cast(null as varchar(200)) as TX_SRVC
into WRK_INTAKE_AGGR_UNIVERSE_JM
from WRK_INTAKE
where FLAG_SCREEN_OUT='N' or (FLAG_SCREEN_OUT='Y' and NOT (age >=0 and age < 18))
group by Year(DT_ACCESS_RCVD)
,ID_PEOPLE_DIM
,TX_GNDR
,TX_RACE
,DT_BIRTH
--Initial_Intake_Date_WithIn_90_Day_Removal_Date
update Child
set Initial_Intake_Date_WithIn_90_Day_Removal_Date = Q_90.Initial_Intake_Date_Prior_To_90_Day_Removal
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join (
Select
Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
,min(I.DT_ACCESS_RCVD) as Initial_Intake_Date_Prior_To_90_Day_Removal
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join WRK_Removals R on R.ID_PEOPLE_DIM_CHILD=Child.ID_PEOPLE_DIM
join WRK_INTAKE I on I.ID_PEOPLE_DIM =Child.ID_PEOPLE_DIM
and I.DT_ACCESS_RCVD between INITIAL_INTAKE_DATE and LAST_INTAKE_DATE_IN_CY
where i.DT_ACCESS_RCVD <=Removal_Episode_Begin_Date
AND datediff(dd,i.DT_ACCESS_RCVD,Removal_Episode_Begin_Date)<=90
group by Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
) Q_90 on Q_90.YEAR_INTAKE=Child.YEAR_INTAKE
and Q_90.ID_PEOPLE_DIM=Child.ID_PEOPLE_DIM
--Initial_Intake_Date_With_Investigation
update Child
set Initial_Intake_Date_With_Investigation = Q.Initial_Intake_Date_With_Investigation
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join (
Select
Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
,min(I.DT_ACCESS_RCVD) as Initial_Intake_Date_With_Investigation
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
--join WRK_Removals R on R.ID_PEOPLE_DIM_CHILD=Child.ID_PEOPLE_DIM
join WRK_INTAKE I on I.ID_PEOPLE_DIM =Child.ID_PEOPLE_DIM
and I.DT_ACCESS_RCVD between INITIAL_INTAKE_DATE and LAST_INTAKE_DATE_IN_CY
where I.ID_DISPOSITION_DIM is not null
group by Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
) Q on Q.YEAR_INTAKE=Child.YEAR_INTAKE
and Q.ID_PEOPLE_DIM=Child.ID_PEOPLE_DIM
--Days_Between_Initial_Intake_Date_With_Investigation_LEVEL2_Approval_Date
update Child
set Days_Between_Initial_Intake_Date_With_Investigation_LEVEL2_Approval_Date=ISNULL(I.QT_DAYS_LEVEL2_APPROVAL,DAYS_Between_Intake_Date_Level2_Approved)
from WRK_INTAKE_AGGR_UNIVERSE_JM CHILD
join WRK_INTAKE i on I.ID_PEOPLE_DIM =Child.ID_PEOPLE_DIM
and I.DT_ACCESS_RCVD =Initial_Intake_Date_With_Investigation
--Removal_Date_WITHIN_90_Days
update Child
set Removal_Date_WITHIN_90_Days = Q_90.Removal_Date_WITHIN_90_Days
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join (
Select
Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
,Initial_Intake_Date_WithIn_90_Day_Removal_Date
,min(R.Removal_Episode_Begin_Date) as Removal_Date_WITHIN_90_Days
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join WRK_Removals R on R.ID_PEOPLE_DIM_CHILD=Child.ID_PEOPLE_DIM
AND Removal_Episode_Begin_Date >=Initial_Intake_Date_WithIn_90_Day_Removal_Date
where Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date is not null
AND datediff(dd,Initial_Intake_Date_WithIn_90_Day_Removal_Date,Removal_Episode_Begin_Date)<=90
group by Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
,Initial_Intake_Date_WithIn_90_Day_Removal_Date
) Q_90 on Q_90.YEAR_INTAKE=Child.YEAR_INTAKE
and Q_90.ID_PEOPLE_DIM=Child.ID_PEOPLE_DIM
and Q_90.Initial_Intake_Date_WithIn_90_Day_Removal_Date=Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date
where Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date is not null
--Length_Of_Stay and tx_dsch_rsn
update Child
set Length_Of_Stay=R.LENGTH_OF_STAY
,tx_dsch_rsn=R.tx_dsch_rsn
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join WRK_REMOVALS R on Child.ID_PEOPLE_DIM=R.ID_PEOPLE_DIM_CHILD and R.Removal_Episode_Begin_Date=Child.Removal_Date_WITHIN_90_Days
where Child.Removal_Date_WITHIN_90_Days is not null
update Child
set Count_Intakes_In_CY_Prior_Removal = Q_90.Count_Intakes_In_CY_Prior_Removal
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join (
Select
Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
,Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date
,COUNT(distinct I.ID_INTAKE_FACT) as Count_Intakes_In_CY_Prior_Removal
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join WRK_INTAKE I on I.ID_PEOPLE_DIM =Child.ID_PEOPLE_DIM
and I.DT_ACCESS_RCVD between INITIAL_INTAKE_DATE and LAST_INTAKE_DATE_IN_CY
where Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date is not null
AND i.DT_ACCESS_RCVD <=Initial_Intake_Date_WithIn_90_Day_Removal_Date
group by Child.YEAR_INTAKE
,Child.ID_PEOPLE_DIM
,Child.INITIAL_INTAKE_DATE
,Child.LAST_INTAKE_DATE_IN_CY
,Child.COUNT_INTAKE
,Initial_Intake_Date_WithIn_90_Day_Removal_Date
) Q_90 on Q_90.YEAR_INTAKE=Child.YEAR_INTAKE
and Q_90.ID_PEOPLE_DIM=Child.ID_PEOPLE_DIM
and Q_90.Initial_Intake_Date_WithIn_90_Day_Removal_Date=Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date
where Child.Initial_Intake_Date_WithIn_90_Day_Removal_Date is not null
update WRK_INTAKE_AGGR_UNIVERSE_JM
set age=dbo.fnc_DateDiff_YEARS(DT_BIRTH,INITIAL_INTAKE_DATE_WITH_INVESTIGATION)
where INITIAL_INTAKE_DATE_WITH_INVESTIGATION IS NOT NULL
UPDATE WRK_INTAKE_AGGR_UNIVERSE_JM
set Days_Between_Initial_Intake_Date_With_Investigation_LEVEL2_Approval_Date = null
where Days_Between_Initial_Intake_Date_With_Investigation_LEVEL2_Approval_Date <0
If I am understanding this code correctly, the following should work to get CD_INVS_DISP into WRK_INTAKE_AGGR_UNIVERSE_JM:
1) Enter into my initial select statement ,cast(null as int) as CD_INVS_DISP, and
2) Run the following update query:
update Child
set CD_INVS_DISP=I.CD_INVS_DISP
from WRK_INTAKE_AGGR_UNIVERSE_JM Child
join WRK_INTAKE I on I.ID_PEOPLE_DIM=Child.ID_PEOPLE_DIM and I.DT_ACCESS_RCVD=Child.INITIAL_INTAKE_DATE
where Child.INITIAL_INTAKE_DATE is not null
However, when I try this, SQL throws an (Invalid column name ''. Error number: 207) error.
Any thoughts?
February 10, 2012 at 12:14 pm
My first thought is, OMG that's thick.
I looked through it. Nothing jumped out as obviously out of line. I was able to run it through SQL Prompt for formatting, so there weren't any clear syntax errors. Best thing I can suggest is breaking it down, attacking it a piece at a time. Trying to troubleshoot that much code is just inherently difficult.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy