April 27, 2010 at 8:10 am
I have created a view to use as a basis for a data warehouse concept.
However when i query the view i always get the current date instead of the date in which the record was inserted into the view.
The SQL for creating part of the view is as follows
create view [dbo].[view_enrolment_profile_DW]
as
selectgetdate() CURR_DATE,
a.sce_ayrcAC_YEAR,
sce_dptcSECTOR,
dpt_nameDEPARTMENT_TITLE,
a.sce_crsc + ' ' + a.SCE_BLOK+ ' ' + a.SCE_OCCL + ' ' +
The issue seems to be around the getdate() CURR_DATE can someone advise the correct way to do this?
Thanks,
Iain.
April 27, 2010 at 8:16 am
Iain
Yes, you need the name of the column that holds the date, instead of GETDATE(). The code you posted does not have a FROM clause. Please post the whole view definition, together with table definition(s), for more specific help.
John
April 27, 2010 at 8:19 am
heres the full script
USE [sipr]
GO
/****** Object: View [dbo].[view_enrolment_profile_DW] Script Date: 04/27/2010 13:42:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[view_enrolment_profile_DW]
as
selectgetdate() CURR_DATE,
a.sce_ayrcAC_YEAR,
sce_dptcSECTOR,
dpt_nameDEPARTMENT_TITLE,
a.sce_crsc + ' ' + a.SCE_BLOK+ ' ' + a.SCE_OCCL + ' ' + crs_nameCOURSE,
a.sce_scjc+a.sce_ayrc+a.sce_crsc+a.SCE_BLOK+a.SCE_OCCL CHECKITEM,
a.sce_crsc + ' ' + a.SCE_BLOK+ ' ' + a.SCE_OCCLCHECKITEM2,
a.sce_crsc + '/' + a.SCE_BLOK+ '/' + a.SCE_OCCLCHECKITEM3,
a.sce_crscCOURSE_CODE,
a.sce_blok YEAR_OF_COURSE,
a.sce_occl OCCURRENCE,
crs_nameCOURSE_TITLE,
case
crs_fecmwhen'1' then 'HE'
when'2' then 'FE'
end
+
case
sce_moacwhen'01' then 'FT'
when'17' then 'FT'
else 'PT'
end LEVEL_OF_STUDY,
case
crs_fecmwhen'1' then 'HE'
when'2' then 'FE'
end HE_FE,
stu_codeSTUDENT_ID_CODE,
a.sce_scjcJOIN_CODE,
a.sce_seq2SEQ_NO,
a.sce_moac MOA_CODE,
moa_nameMOA_TITLE,
case
a.sce_stacwhen'C' then 'CURRENT'
when'W' then 'WITHDRAWN'
when'X' then 'CANCELLED'
else'OTHER'
endSTATUS,
sce_begd START_DATE,
sce_endd END_DATE,
cbo_reqdREQ_DATE,
a.sce_pgsc OUTCOME_CODE,
case
a.sce_pgsc when'1' then 'ENROLLED ON COURSE BUT NEVER ATTENDED'
when'2' then 'WITHDRAWN FROM COURSE AND GONE INTO EMPLOYMENT'
when'3' then 'WITHDRAWN FROM COURSE AND STUDYING IN AN HEI'
when'4' then 'WITHDRAWN FROM COURSE AND DESTINATION UNKNOWN'
when'5' then 'TRANSFERRED TO ANOTHER COURSE WITHIN COLLEGE'
when'6' then 'COMPLETED COURSE BUT NOT ASSESSED'
when'7' then 'COMPLETED COURSE ASSESSED BUT NOT SUCCESSFUL'
when'8' then 'COMPLETED COURSE ASSESSED AND SUCCESSFUL'
when'9' then 'CONTINUING ONTO NEXT YEAR OF COURSE(INC SPANNING)'
when'10' then 'WITHDRAWN FROM PROGRAMME/COURSE AND NOW STUDYING ELSEWHERE (NOT AN HEI)'
when'14' then 'COMPLETED PROGRAMME/COURSE STUDENT NOT ASSESSED AS PROGRAMME/COURSE NOT DESIGNED TO BE ASSESSED'
when'15' then 'COMPLETED PROGRAMME/COURSE STUDENT NOT ASSESSED ALTHOUGH PROGRAMME/COURSE DESIGNED TO BE ASSESSED'
when'16' then 'DECEASED'
when'99' then 'NOT KNOWN'
when'17' then 'Student has progressed to next year but did not gain 70% of the credits undertaken'
when'18' then 'Student has progressed to next year and has achieved 70% of the credits undertaken'
when'20' then 'Student has achieved 70% of the credits undertaken but has chosen not to progress onto the next year'
when'21' then 'Completed programme/course, student not assessed although programme/course designed to be assessed. Studying on a flexible open learning programme'
when'22' then 'Student completed first year of an HND but has chosen to leave with an HNC'
else'OTHER'
end OUTCOME,
case
a.sce_styc when'15' then 'SCHOOLS S1 - Schools Link'
when'16' then 'SCHOOLS S2- Schools Link'
when'01' then 'SCHOOLS S3- Schools Link'
when'02' then 'SCHOOLS S4- Schools Link'
when'03' then 'SCHOOLS S5- Schools Link'
when'04' then 'SCHOOLS S6- Schools Link'
when'17' then 'Primary School'
when'19' then 'Non-Schools Link'
end STUDENT_CATEGORY,
a.sce_sclc,
case
a.sce_sclcwhen '10931' then 'Taylor High School'
when '10941' then 'Brannock High School'
when '11794' then 'St Ninians High School'
when '12340' then 'Hamilton College'
when '13701' then 'Marr College'
when '13718' then 'Vale Of Leven Academy'
when '13721' then 'Our Ladys High School'
when '13726' then 'Hermitage Academy'
when '13780' then 'Kirkcudbright Academy'
when '13781' then 'Airdrie Academy'
when '13782' then 'Balfron High School'
when '13783' then 'Bellshill Academy'
when '13784' then 'Biggar High School'
when '13787' then 'Coatbridge High School'
when '13790' then 'Calderglen High School'
when '13791' then 'Duncanrig Secondary School'
when '13798' then 'Cathkin High School'
when '13799' then 'Cumbernauld High School'
when '13802' then 'Kilsyth Academy'
when '13809' then 'Uddingston Grammar School'
when '13820' then 'St Mungos Academy'
when '13823' then 'Whitehill Secondary School'
when '13824' then 'Eastbank Academy'
when '13831' then 'Springburn Academy'
when '13833' then 'Craigholme School'
when '13834' then 'Hutchesons Grammar School'
when '13836' then 'Holyrood Secondary School'
when '13841' then 'Hillpark Secondary School'
when '13842' then 'Kings Park Secondary School'
when '13848' then 'Lourdes Secondary School'
when '13854' then 'Glasgow Academy'
when '13855' then 'Cleveden Secondary School'
when '13857' then 'Hyndland Secondary School'
when '13861' then 'Jordanhill School'
when '13862' then 'Knightswood Secondary School'
when '13863' then 'St Thomas Aquinas School'
when '13868' then 'Hamilton Grammar School'
when '13870' then 'Holy Cross High School'
when '13871' then 'Lanark Grammar School'
when '13872' then 'Larkhall Academy'
when '13873' then 'Dalziel High School'
when '13874' then 'Our Ladys High School'
when '13960' then 'Eastwood High School'
when '14235' then 'St Mungos High School'
when '14434' then 'St Andrews Secondary School'
when '14439' then 'St Margarets High School'
when '14491' then 'Earnock High School'
when '14535' then 'St Aidans High School'
when '14547' then 'Greenfaulds High School'
when '14591' then 'Stonelaw High School'
when '14621' then 'St Ambrose High School'
when '14631' then 'Hunter High School'
when '14643' then 'St Andrews High School'
when '14646' then 'St Rochs Secondary School'
when '14675' then 'Braidhurst High School'
when '14806' then 'Coltness High School'
when '14833' then 'Bannerman High School'
when '14848' then 'Rosehall High School'
when '14909' then 'Blantyre High School'
when '14944' then 'Park Mains High School'
when '15177' then 'Auchenharvie Academy'
when '15184' then 'Caldervale High School'
when '15186' then 'Douglas Academy'
when '15188' then 'St Brides High School'
when '15204' then 'Lenzie Academy'
when '15230' then 'Clyde Valley High School'
when '15407' then 'John Ogilvie High School'
when '15414' then 'Trinity High School'
when '15430' then 'Strathaven Academy'
when '15515' then 'Dunblane High School'
when '15545' then 'Cardinal Newman High School'
when '15633' then 'Carluke High School'
when '15642' then 'Mearns Castle High School'
when '15674' then 'St Maurices High School'
when '15685' then 'Calderhead High School'
when '15696' then 'Ashcraig Secondary School'
when '15738' then 'Boclair Academy'
when '15747' then 'Gourock High School'
when '15761' then 'Chryston High School'
when '15901' then 'St Andrews High School'
when '15973' then 'Abronhill High School'
when '15989' then 'Lesmahagow High School'
when '16386' then 'St Vincent School for the Blind and Deaf'
when '16644' then 'Donaldsons College'
when '8358931' then 'st maurices high school'
when 'SC001' then 'FIRPARK SCHOOL'
when 'SC002' then 'DRUMPARK SCHOOL'
when 'SC003' then 'SANDERSON HIGH SCHOOL'
when 'SC004' then 'RUTHERGLEN HIGH SCHOOL'
when 'SC006' then 'EAST PARK SCHOOL'
when 'SC007' then 'CRAIGHEAD SCHOOL'
when 'SC008' then 'PORTLAND HIGH SCHOOL'
when 'SC010' then 'GLENCRYAN SCHOOL'
when 'SC011' then 'BOTHWELL PARK SCHOOL'
when 'SC012' then 'STANMORE HOUSE'
when 'SC013' then 'VICTORIA PARK SCHOOL'
when 'SC014' then 'FALLSIDE SCHOOL'
when 'SC017' then 'DALDORCH SCHOOl'
when 'SC022' then 'ROSE HALL ACADEMY'
when 'SC023' then 'MERKLAND SCHOOL'
when 'SC024' then 'KITTOCH SCHOOL'
when 'SC027' then 'NEWHILLS SCHOOL'
when 'SC028' then 'HAZELWOOD SCHOOL'
else'Not Given'
end SCHOOL,
cbo_eref CBO_GROUP_AWARD_CODE,
substring(cbo_eref,5,2) GROUP_AWARD_LEVEL,
case
when coalesce(sce_asum,sce_csum)='20' then 18
else coalesce(sce_asum,sce_csum)
end ESTIMATED_NO_OF_UNITS,
stu_scot SQA_ID,
stu_titl GREETING,
stu_fnm1 FORENAME,
stu_surn SURNAME,
stu_surn + ', ' + stu_fnm1 FULLNAME,
stu_dob DOB,
floor(datediff(dy,stu_dob,sce_begd)/365.25) AGE,
stu_had1 ADDRESS1,
stu_had2 ADDRESS2,
stu_had3 ADDRESS3,
stu_had4 ADDRESS4,
stu_hapc POSTCODE,
case
when len(stu_hapc) = 6 then substring(stu_hapc,1,4)
when len(stu_hapc) = 7 then substring(stu_hapc,1,5)
when len(stu_hapc) = 8 then substring(stu_hapc,1,6)
end POSTCODE_ZONE,
case
when len(stu_hapc) = 6 then substring(stu_hapc,1,2)
when len(stu_hapc) = 7 then substring(stu_hapc,1,3)
when len(stu_hapc) = 8 then substring(stu_hapc,1,4)
end POSTCODE_ZONE2,
case
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G5 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G11 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G11 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G12 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G13 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G13 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G13 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G14 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G15 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G15 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G15 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G20 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G20 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G20 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G20 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G21 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G21 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G21 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G21 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G22 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G22 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G22 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G23 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G3 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G3 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G31 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G31 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G31 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G31 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G31 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G32 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G32 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G32 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G33 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G33 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G33 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G33 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G33 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G34 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G34 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G4 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G40 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G40 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G40 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G40 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G41 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G41 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G42 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G42 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G42 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G42 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G43 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G45 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G45 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G46 8' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G5 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G5 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G51 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G51 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G51 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G51 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G52 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G52 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G53 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G53 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G53 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G66 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G69 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G71 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G72 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G72 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G73 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G73 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G73 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G81 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G81 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G81 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G81 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G81 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'G82 3' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML1 1' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML1 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML1 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML11 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML2 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML2 7' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML2 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML3 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML3 9' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML4 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML5 2' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML5 4' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML5 5' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML6 0' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML6 6' then 'DEPRIVATION INDEX AREA'
when(case when len(stu_hapc) = 6 then substring(stu_hapc,1,4) when len(stu_hapc) = 7 then substring(stu_hapc,1,5) when len(stu_hapc) = 8 then substring(stu_hapc,1,6)end) = 'ML6 7' then 'DEPRIVATION INDEX AREA'
else 'NON DEPRIVATION INDEX AREA'
end DEP_INDEX_AREA,
case
stu_gendwhen 'F' then 'FEMALE'
when 'M' then 'MALE'
end GENDER,
qrp_qpl1QRP_GROUP_AWARD_CODE,
prs_fnm1 + ' ' + prs_surn CURRICULUM_LEADER,
cty_nameCOURSE_TYPE,
sce_fptcFEE_PROFILE,
lea_nameLEA,
b.dsb_name DISABILITY,
coalesce(sce_asum,sce_csum) RSUMS,
case
sce_elsywhen 'Y' then coalesce(sce_asum,sce_csum)
else '0'
end ELS_SUMS,
cbo_parc DPG,
case
cbo_parc when'01' then '1.523193169'
when'02' then '0.8384451175794'
when'03' then '1.1756977850363'
when'04' then '0.9560660028838'
when'05' then '1.2615624943273'
when'06' then '1.0472159907166'
when'07' then '1.2615624943273'
when'08' then '0.9560660028838'
when'09' then '1.1756977850363'
when'10' then '0.8722141700369'
when'11' then '1.2615624943273'
when'12' then '0.9560660028838'
when'13' then '0.9560660028838'
when'14' then '0.7434171061004'
when'15' then '0.9560660028838'
when'16' then '1.0472159907166'
when'17' then '1.2615624943273'
when'18' then '1.8'
end WGHT,
coalesce(sce_asum,sce_csum)+
case
sce_elsywhen 'Y' then coalesce(sce_asum,sce_csum)
else '0'
end TOTAL_RSUMS,
coalesce(sce_asum,sce_csum)*
case
cbo_parc when'01' then convert(decimal(14,13),1.523193169)
when'02' then convert(decimal(14,13),0.8384451175794)
when'03' then convert(decimal(14,13),1.1756977850363)
when'04' then convert(decimal(14,13),0.9560660028838)
when'05' then convert(decimal(14,13),1.2615624943273)
when'06' then convert(decimal(14,13),1.0472159907166)
when'07' then convert(decimal(14,13),1.2615624943273)
when'08' then convert(decimal(14,13),0.9560660028838)
when'09' then convert(decimal(14,13),1.1756977850363)
when'10' then convert(decimal(14,13),0.8722141700369)
when'11' then convert(decimal(14,13),1.2615624943273)
when'12' then convert(decimal(14,13),0.9560660028838)
when'13' then convert(decimal(14,13),0.9560660028838)
when'14' then convert(decimal(14,13),0.7434171061004)
when'15' then convert(decimal(14,13),0.9560660028838)
when'16' then convert(decimal(14,13),1.0472159907166)
when'17' then convert(decimal(14,13),1.2615624943273)
when'18' then convert(decimal(14,13),1.8)
end WSUMS,
case
sce_elsywhen 'Y' then coalesce(sce_asum,sce_csum)
else '0'
end *1.5 WELS_SUMS,
coalesce(sce_asum,sce_csum)*
case
cbo_parc when'01' then convert(decimal(14,13),1.523193169)
when'02' then convert(decimal(14,13),0.8384451175794)
when'03' then convert(decimal(14,13),1.1756977850363)
when'04' then convert(decimal(14,13),0.9560660028838)
when'05' then convert(decimal(14,13),1.2615624943273)
when'06' then convert(decimal(14,13),1.0472159907166)
when'07' then convert(decimal(14,13),1.2615624943273)
when'08' then convert(decimal(14,13),0.9560660028838)
when'09' then convert(decimal(14,13),1.1756977850363)
when'10' then convert(decimal(14,13),0.8722141700369)
when'11' then convert(decimal(14,13),1.2615624943273)
when'12' then convert(decimal(14,13),0.9560660028838)
when'13' then convert(decimal(14,13),0.9560660028838)
when'14' then convert(decimal(14,13),0.7434171061004)
when'15' then convert(decimal(14,13),0.9560660028838)
when'16' then convert(decimal(14,13),1.0472159907166)
when'17' then convert(decimal(14,13),1.2615624943273)
when'18' then convert(decimal(14,13),1.8)
end + case
sce_elsywhen 'Y' then coalesce(sce_asum,sce_csum)
else '0'
end *1.5 TOTAL_WSUMS,
stu_haem EMAIL,
sce_ssfc SOURCE_OF_FUNDING_CODE,
ssf_name SOURCE_OF_FUNDING_NAME,
case
when sce_endd is null then 'PASS'
when sce_endd > cbo_reqd then 'PASS'
else 'FAIL'
end REQUIRED_DATE,
case
when sce_endd is null then 1
when sce_endd > cbo_reqd then 1
else 0
end REQUIRED_DATE_COUNT,
case
sce_efidwhen '1' then 'FUNDABLE STUDENT'
when '2' then 'NON-FUNDABLE STUDENT'
else 'SET FUNDABILITY FLAG ON SCE'
end STUDENT_FUNDABILITY,
case
cbo_ehcfwhen 'Y' then 'FUNDABLE COURSE'
when 'N' then 'NON-FUNDABLE COURSE'
else 'SET FUNDABILITY FLAG ON CBO'
end COURSE_FUNDABILITY,
case
when (case
crs_fecmwhen'1' then 'HE'
when'2' then 'FE'
end
+
case
sce_moacwhen'01' then 'FT'
when'17' then 'FT'
else 'PT'
end) = 'HEFT' then 0
when (case
crs_fecmwhen'1' then 'HE'
when'2' then 'FE'
end
+
case
sce_moacwhen'01' then 'FT'
when'17' then 'FT'
else 'PT'
end) = 'FEFT' then '984'
/*if student sums=0 then there should be no FWG allocated. SFCs calculations shows no FWG but Colleges will -SFC appear to igore these in FWG calculations.*/
when (case
crs_fecmwhen'1' then 'HE'
when'2' then 'FE'
end
+
case
sce_moacwhen'01' then 'FT'
when'17' then 'FT'
else 'PT'
end) = 'HEPT' then coalesce(sce_asum,sce_csum)*73.76
when (case
crs_fecmwhen'1' then 'HE'
when'2' then 'FE'
end
+
case
sce_moacwhen'01' then 'FT'
when'17' then 'FT'
else 'PT'
end) = 'FEPT' then coalesce(sce_asum,sce_csum)*55.12
end FWG_CLAIM,
case
cbo_ytypwhen'1' then 'WITHIN REPORTING PERIOD'
when'3' then 'STARTING SPANNING PERIOD'
when'5' then 'ENDING SPANNING PERIOD'
else 'QUERY'
end SPAN_TYPE,
case
when coalesce(sce_asum,sce_csum) is null then 'STUDENT HAS ZERO SUMS'
when coalesce(sce_asum,sce_csum)= 0 then 'STUDENT HAS ZERO SUMS'
else 'STUDENT HAS SUMS CALCULATED'
end STU_ZERO_SUMS,
coalesce(cbo_dsum,cbo_phrs/40) PLANNED_SUMS,
qul_name QUALIFICATION,
val_name VALIDATING_BODY,
pgs_name OUTCOMEv2,
case
sce_pgscwhen'7' then 1
when'8' then 1
when'9' then 1
when'14' then 1
when'15' then 1
when '17' then 1
when '18' then 1
when '20' then 1
when '22' then 1
when '99' then 1
else 0
endCOMPLETERS,
case
sce_pgscwhen'8' then 1
when'9' then 1
when'14' then 1
when'15' then 1
when '18' then 1
when'20' then 1
when'22' then 1
else 0
endSUCCESSES,
case
crs_esb1 when 'PC' then 'Hairdressing, beauty and complementary therapies'
when 'AA' then 'Business, management and administration'
when 'AB' then 'Business, management and administration'
when 'AC' then 'Business, management and administration'
when 'AD' then 'Business, management and administration'
when 'AE' then 'Business, management and administration'
when 'AF' then 'Business, management and administration'
when 'AG' then 'Business, management and administration'
when 'AJ' then 'Business, management and administration'
when 'AK' then 'Business, management and administration'
when 'AL' then 'Business, management and administration'
when 'AY' then 'Business, management and administration'
when 'AZ' then 'Business, management and administration'
when 'BA' then 'Business, management and administration'
when 'BB' then 'Business, management and administration'
when 'BC' then 'Business, management and administration'
when 'BD' then 'Business, management and administration'
when 'BE' then 'Business, management and administration'
when 'BF' then 'Business, management and administration'
when 'CA' then 'Computing and ICT'
when 'CB' then 'Computing and ICT'
when 'CC' then 'Computing and ICT'
when 'CD' then 'Computing and ICT'
when 'CE' then 'Computing and ICT'
when 'CH' then 'Computing and ICT'
when 'CX' then 'Computing and ICT'
when 'CY' then 'Business, management and administration'
when 'CZ' then 'Business, management and administration'
when 'DA' then 'Social subjects'
when 'DB' then 'Social subjects'
when 'DC' then 'Social subjects'
when 'DD' then 'Social subjects'
when 'DE' then 'Social subjects'
when 'EA' then 'Social subjects'
when 'EB' then 'Business, management and administration'
when 'EC' then 'Business, management and administration'
when 'ED' then 'Social subjects'
when 'EE' then 'Social subjects'
when 'FB' then 'Social subjects'
when 'FC' then 'Media'
when 'FJ' then 'Languages and ESOL'
when 'FK' then 'Languages and ESOL'
when 'FL' then 'Social subjects'
when 'GA' then 'Education and training'
when 'GB' then 'Education and training'
when 'GC' then 'Education and training'
when 'GD' then 'Education and training'
when 'GE' then 'Education and training'
when 'GF' then 'Education and training'
when 'HB' then 'Special Programmes'
when 'HC' then 'Education and training'
when 'HD' then 'Special Programmes'
when 'HE' then 'Business, management and administration'
when 'HF' then 'Care'
when 'HG' then 'Special Programmes'
when 'HH' then 'Care'
when 'HJ' then 'Sport and leisure'
when 'HK' then 'Hairdressing, Beauty and Complementary Therapies'
when 'HL' then 'Hairdressing, Beauty and Complementary Therapies'
when 'JA' then 'Art and design'
when 'JB' then 'Art and design'
when 'JC' then 'Art and design'
when 'JD' then 'Art and design'
when 'JE' then 'Art and design'
when 'JF' then 'Art and design'
when 'JG' then 'Art and design'
when 'JH' then 'Art and design'
when 'JK' then 'Art and design'
when 'JL' then 'Art and design'
when 'JP' then 'Construction'
when 'JR' then 'Art and design'
when 'KA' then 'Media'
when 'KB' then 'Media'
when 'KC' then 'Media'
when 'KD' then 'Media'
when 'KE' then 'Art and design'
when 'KF' then 'Media'
when 'KG' then 'Media'
when 'KH' then 'Art and design'
when 'LA' then 'Performing arts'
when 'LB' then 'Performing arts'
when 'LC' then 'Performing arts'
when 'LD' then 'Performing arts'
when 'LE' then 'Performing arts'
when 'LF' then 'Performing arts'
when 'LG' then 'Performing arts'
when 'LH' then 'Performing arts'
when 'LJ' then 'Performing arts'
when 'MA' then 'Sport and Leisure'
when 'MB' then 'Sport and Leisure'
when 'MC' then 'Sport and Leisure'
when 'MD' then 'Sport and Leisure'
when 'ME' then 'Sport and Leisure'
when 'MF' then 'Sport and Leisure'
when 'MG' then 'Sport and Leisure'
when 'MH' then 'Sport and Leisure'
when 'MJ' then 'Sport and Leisure'
when 'NA' then 'Hospitality and tourism'
when 'NB' then 'Hospitality and tourism'
when 'NC' then 'Hospitality and tourism'
when 'ND' then 'Hospitality and tourism'
when 'NE' then 'Hospitality and tourism'
when 'NF' then 'Hospitality and tourism'
when 'NG' then 'Hospitality and tourism'
when 'NH' then 'Hospitality and tourism'
when 'NK' then 'Hospitality and tourism'
when 'NL' then 'Sport and leisure'
when 'NM' then 'Sport and leisure'
when 'NN' then 'Sport and leisure'
when 'PA' then 'Care'
when 'PB' then 'Science'
when 'PD' then 'Science'
when 'PE' then 'Science'
when 'PF' then 'Science'
when 'PG' then 'Science'
when 'PH' then 'Care'
when 'PJ' then 'Care'
when 'PK' then 'Care'
when 'PL' then 'Care'
when 'PM' then 'Care'
when 'PN' then 'Care'
when 'PP' then 'Care'
when 'PQ' then 'Care'
when 'QA' then 'Land-based industries'
when 'QB' then 'Construction'
when 'QC' then 'Land-based industries'
when 'QD' then 'Construction'
when 'QE' then 'Hospitality and tourism'
when 'QG' then 'Land-based industries'
when 'QH' then 'Engineering'
when 'QJ' then 'Engineering'
when 'RA' then 'Science'
when 'RB' then 'Science'
when 'RC' then 'Science'
when 'RD' then 'Science'
when 'RE' then 'Science'
when 'RF' then 'Science'
when 'RG' then 'Construction'
when 'RH' then 'Science'
when 'SA' then 'Land-based industries'
when 'SB' then 'Land-based industries'
when 'SC' then 'Land-based industries'
when 'SD' then 'Land-based industries'
when 'SE' then 'Land-based industries'
when 'SF' then 'Land-based industries'
when 'SG' then 'Land-based industries'
when 'SH' then 'Land-based industries'
when 'SJ' then 'Land-based industries'
when 'SK' then 'Land-based industries'
when 'SL' then 'Land-based industries'
when 'SM' then 'Land-based industries'
when 'SN' then 'Land-based industries'
when 'SP' then 'Land-based industries'
when 'TA' then 'Construction'
when 'TC' then 'Construction'
when 'TD' then 'Construction'
when 'TE' then 'Construction'
when 'TF' then 'Construction'
when 'TG' then 'Construction'
when 'TH' then 'Construction'
when 'TJ' then 'Art and design'
when 'TK' then 'Construction'
when 'TL' then 'Construction'
when 'TM' then 'Construction'
when 'VB' then 'Business, management and administration'
when 'VC' then 'Business, management and administration'
when 'VD' then 'Business, management and administration'
when 'VE' then 'Engineering'
when 'VF' then 'Engineering'
when 'VG' then 'Engineering'
when 'WA' then 'Engineering'
when 'WB' then 'Engineering'
when 'WC' then 'Engineering'
when 'WD' then 'Engineering'
when 'WE' then 'Engineering'
when 'WF' then 'Engineering'
when 'WG' then 'Engineering'
when 'WH' then 'Engineering'
when 'WJ' then 'Land-based Industries'
when 'WK' then 'Construction'
when 'WL' then 'Art and design'
when 'WM' then 'Hospitality and tourism'
when 'XA' then 'Engineering'
when 'XD' then 'Engineering'
when 'XE' then 'Engineering'
when 'XF' then 'Engineering'
when 'XH' then 'Engineering'
when 'XJ' then 'Engineering'
when 'XK' then 'Engineering'
when 'XL' then 'Engineering'
when 'XM' then 'Engineering'
when 'XN' then 'Engineering'
when 'XP' then 'Engineering'
when 'XQ' then 'Nautical studies'
when 'XR' then 'Engineering'
when 'XS' then 'Engineering'
when 'XT' then 'Engineering'
when 'YA' then 'Engineering'
when 'YB' then 'Engineering'
when 'YC' then 'Engineering'
when 'YD' then 'Engineering'
when 'YE' then 'Engineering'
when 'ZA' then 'Engineering'
when 'ZD' then 'Engineering'
when 'ZE' then 'Hospitality and tourism'
when 'ZF' then 'Nautical studies'
when 'ZG' then 'Engineering'
when 'ZH' then 'Engineering'
when 'ZJ' then 'Engineering'
when 'ZL' then 'Engineering'
end HMI_SUBJECT_GROUP,
crs_csf1FUNDING_TYPE,
crs_qulcQUALIFICATION_TYPE,
lca_codeLOCATION_CODE,
lca_nameLOCATION_NAME,
sce_udf1CPP,
sce_stycSTUDENT_CATEGORY_CODE,
sty_nameSTUDENT_CATEGORY_NAME,
fac_nameSUB_DEPARTMENT,
case
stu_ethc when '01' then 'WHITE SCOTTISH'
when '10' then 'WHITE SCOTTISH'
when '11' then 'WHITE ENGLISH'
when '12' then 'WHITE WELSH'
when '13' then 'WHITE IRISH'
when '14' then 'OTHER WHITE ORIGIN'
when '15' then 'ANY MIXED'
when '05' then 'ASIAN INDIAN'
when '16' then 'ASIAN INDIAN'
when '06' then 'ASIAN PAKISTANI'
when '17' then 'ASIAN PAKISTANI'
when '07' then 'ASIAN BANGLADESHI'
when '18' then 'ASIAN BANGLADESHI'
when '08' then 'ASIAN CHINESE'
when '19' then 'ASIAN CHINESE'
when '20' then 'OTHER ASIAN ORIGIN'
when '02' then 'BLACK CARIBBEAN'
when '21' then 'BLACK CARIBBEAN'
when '03' then 'BLACK AFRICAN'
when '22' then 'BLACK AFRICAN'
when '04' then 'OTHER BLACK ORIGIN'
when '23' then 'OTHER BLACK ORIGIN'
when '09' then 'OTHER'
when '24' then 'OTHER'
when '00' then 'INFORMATION REFUSED'
when '98' then 'INFORMATION REFUSED'
when '99' then 'INFORMATION NOT KNOWN'
else 'INFORMATION NOT KNOWN'
endETHNIC_ORIGIN,
(cbo_dsld/100)FTE,
case
sce_udfa when '1' then 'NOT MADE REDUNDANT'
when '0' then 'MADE REDUNDANT'
end REDUNDANCY_FLAG,
case
sce_udfb when 'Y' then 'YES DATA SHARED WITH SDS'
when 'N' then 'NO DATA NOT SHARED WITH SDS'
end SDS_OPT_OUT,
sce_elsyELS_STUDENT,
cbo_hrweekHOURS_PER_WEEK,
crs_esb1COURSE_SUPERCLASS,
esb_nameCOURSE_SUPERCLASS_TITLE
from srs_crs left outer join srs_cty on crs_ctyc = cty_code left outer join srs_qul on crs_qulc = qul_code left outer join srs_fac on crs_facc = fac_code left outer join ins_esb on esb_code = crs_esb1, srs_sce AS a left outer join ins_moa on sce_moac = moa_code left outer join srs_qrp on sce_scjc = qrp_scjc left outer join srs_ssf on sce_ssfc=ssf_code left outer join srs_pgs on sce_pgsc = pgs_code left outer join ins_dpt on sce_dptc = dpt_code left outer join srs_sty on sce_styc = sty_code, ins_stu left outer join srs_lea on stu_leac =lea_code left outer join srs_dsb as b on stu_dsbc = b.dsb_code, srs_cbo left outer join ins_prs on cbo_prsc = prs_code left outer join ins_val on cbo_valc = val_code left outer join ins_lca on cbo_lcac = lca_code
where crs_code = a.sce_crsc
and a.sce_stuc = stu_code
and crs_code = cbo_crsc
and cbo_ayrc = a.sce_ayrc
and cbo_crsc = a.sce_crsc
and cbo_blok = a.sce_blok
and cbo_occl = a.sce_occl
and a.sce_ayrc /*= '07/08'*/ in ('09/10','08/09','07/08')
/*and a.sce_sclc in ('15973','13781','13783','14675','10941','15685','15184','15545','15761','15230','13787','14806','13799','13873','14547','13802','13721','13874','14848','14535','14621','14439','15674','10931') NLC Schools*/
April 27, 2010 at 9:31 am
On second thought, maybe we don't want the full text of your view.:w00t:
Select GetDate() will always return the current system date and time.
The concept of "when the record was inserted into the view" doesn't really exist -- the issue is when were the records inserted into the underlying tables, since a (non-materialized) view is basically just a stored query into its underlying data. Do any of your tables contain a 'Date Inserted' type of field that is updated when a record is inserted into the database? (I confess to not having looked through the entire text of your post.) That would be the field that you want to select in your view.
Hope that gets you moving in the right direction.
Rob Schripsema
Propack, Inc.
April 29, 2010 at 7:13 am
Two words. "Lookup Tables".
As an example: Instead of that long case statement for sce_sclc, create a table in your database with 2 columns, such as sce_sclc and 'school'. Then link to that table. Repeat for each of the fields with long hard coded case statements. Taking it one step further (just glanced at the where clause), you may be able to add a third column to the table for school type or region or whatever NLC is and you can get rid of that hard coded list as well.
Also, keep in mind that using left joins and then putting criteria in your where clause targeting that table (unless you handle nulls) essentially converts that left join back to an inner join.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply