June 7, 2012 at 5:59 am
Please help me with this error
PRINT 'Starting Student Master Table Re-creation'
PRINT 'Dropping temporary tables...'
drop table #enr_detail
drop table #demo
drop table #addr_split
drop table #reg_detail
drop table #subj_detail
drop table #assg_detail
drop table #assg_dispatch
drop table #assg_passed
drop table #assg_submitted
drop table #Tot_assg_dispatch
drop table #assg_passed_reg
drop table #assg_submitted_reg
drop table #Tot_assg_dispatch_reg
drop table #assg_passed_subj
drop table #assg_submitted_subj
drop table #Tot_Assg_dispatch_subj
-- Don't need to drop the indexes if the tables have already been dropped
--drop index #tot_assg_dispatch_subj.idx_#Assg_dsp3
--drop index #tot_assg_dispatch_reg.idx_#Assg_dsp2
--drop index #tot_assg_dispatch.idx_#Assg_dsp1
--drop index #subj_detail.idx_subj_detail
PRINT 'Creating temporary table #enr_detail'
SELECT student.number as student_nr,
student.student_id,
enrollment.enrollment_id,
title = case enrollment.title
WHEN 0 THEN 'None'
WHEN 1 THEN 'Mr'
WHEN 2 THEN 'Mrs'
WHEN 3 THEN 'Miss'
WHEN 4 THEN 'Ms'
WHEN 5 THEN 'Mnr'
WHEN 6 THEN 'Mev'
WHEN 7 THEN 'Mej'
WHEN 8 THEN 'Dr'
WHEN 9 THEN 'Prof'
END,
enrollment.initials,
enrollment.first_name,
enrollment.last_name,
--enrollment.address,
--enrollment.city,
enrollment.address+'\' as address,
isnull(enrollment.city,'') as city,
enrollment.postal_code,
cast(' ' as varchar(250)) as post_addr1,
cast(' ' as varchar(250)) as post_addr2,
cast(' ' as varchar(150)) as post_addr3,
cast(' ' as varchar(150)) as post_addr4,
isnull(enrollment.postal_code,'') as post_postal_code,
cast('unknown' as varchar(50)) as province,
country.name as country_name,
enrollment.home_area_code,
enrollment.home_phone,
enrollment.work_area_code,
enrollment.work_phone,
enrollment.cell_phone,
enrollment.email,
action_history.enrllmnt_prcssd_dt,
enrollment.contract_end_date,
contract = case
WHEN enrollment.contract_end_date >= getdate() THEN 'InContract'
WHEN enrollment.contract_end_date < getdate() THEN 'OutContract'
END,
enrollment.status as enrollment_status,
enr_status = CASE enrollment.status
WHEN 0 THEN 'Captured'
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Registered'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'Handed over'
WHEN 5 THEN 'Suspended'
WHEN 6 THEN 'Rejected'
WHEN 7 THEN 'Transferred'
END,
enrollment.id_number,
product.product_id,
product.code as prod_code,
product.full_name as prod_desc,
product.version as prod_version,
school.short_name as prod_school,
student_counsellor.code as sc_code,
student_counsellor.last_name as sc_name,
branch.name as branch,
(enrollment.cash /100) as cash,
gev = case
WHEN enrollment.payment_type = 1 THEN (enrollment.cash /100)
WHEN enrollment.payment_type = 2 THEN (enrollment.installment*enrollment.payment_months+enrollment.deposit)/100
END,
--(enrollment.installment*enrollment.payment_months+enrollment.deposit)/100 as gev,
(enrollment.rcipt_amount_cents/100) as fm,
(enrollment.installment /100) as installment,
enrollment.payment_type,
enrollment.omnx_ccount_number as acc_nr,
enrollment.opportunity_id as oppo_number,
0 as acc_month,
0 as acc_year,
--DATEDIFF (month, enrollment.date_of_birth, getdate())/12 as age,
age = case
when month(enrollment.date_of_birth) = month(getdate()) and day(enrollment.date_of_birth) > day(getdate()) then (DATEDIFF (mm, enrollment.date_of_birth, getdate())-1)/12
else (DATEDIFF (mm, enrollment.date_of_birth, getdate()))/12 end,
convert(varchar(30),'unknown' ) as race,
convert(varchar(30),'unknown' ) as gender,
convert(varchar(30),'unknown' ) as occupation,
convert(varchar(80),'unknown' ) as salary,
convert(varchar(80),'unknown' ) as language,
0 as tot_assg_dispatch,
0 as tot_assg_submitted,
0 as tot_assg_passed,
0 as tot_assg_outstanding,
cast(null as datetime) as last_assg_date,
tracking_num_link.tracking_number,
cast(0 as decimal(10,2)) as bal_tot,
cast(0 as decimal(10,2)) as r_arrears,
--ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))) as bal_tot,
--ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2))) as r_arrears,
email_notfc = case enrollment.emil_address_valid
when 1 then 'Yes'
when 0 then 'No'
end,
sms_notfc = case enrollment.cllphn_nmber_valid
when 1 then 'Yes'
when 0 then 'No'
end,
0 as tot_issues,
cast(0 as decimal(8,2)) as tot_issues_avg_cost,
0 as tot_unissued,
cast(0 as decimal(8,2)) as tot_unissued_avg_cost,
cast(null as varchar(50)) as issues_status,
0 as tot_sm_dispatch,
cast(0 as decimal(8,2)) as tot_sm_avg_cost,
brand.short_name as brand_name,
enrollment.employee_number,
enrollment.mailing_list_id,
enrollment.company_id,
enrollment.company_region_id,
enrollment.company_branch_id,
brand.brand_id,
convert(varchar(80),'unknown') as co_code,
convert(varchar(100),'unknown') as co_name,
convert(varchar(100),'unknown') as co_region,
convert(varchar(100),'unknown') as co_branch,
enrollment.payer_id,
enrollment.guarantor_id,
usr.login as captured_usr,
action_history.date_captured as capture_date,
action_history.date_pended as pended_date,
a.login as register_usr,
enrollment.discount_reason_id,
enrollment.selling_price_id
INTO #enr_detail
FROM student,
student_enrollment,
enrollment LEFT OUTER JOIN branch on enrollment.branch_id = branch.branch_id
LEFT OUTER JOIN country on enrollment.country_country_id = country.country_id,
action_history LEFT OUTER JOIN usr as a on action_history.rgstrd_b_usr_id = a.usr_id,
product,
student_counsellor,
brand,
tracking_num_link,
school,
usr
WHERE student.student_id = student_enrollment.student_id
AND student_enrollment.enrollment_id = enrollment.enrollment_id
AND enrollment.product_id = product.product_id
AND enrollment.tracking_number_id = tracking_num_link.tracking_number_id
AND enrollment.stdnt_cunsellor_id = student_counsellor.stdnt_cunsellor_id
AND product.school_id = school.school_id
AND enrollment.brand_id = brand.brand_id
AND action_history.cptrd_by_usr_id = usr.usr_id
And action_history.action_history_id = enrollment.action_history_id
GO
PRINT 'Creating index idx_#enr_detail on temporary table #enr_detail'
create index idx_#enr_detail on #enr_detail(enrollment_id)
GO
PRINT 'Creating index idx_#enr_detail2 on temporary table #enr_detail'
create index idx_#enr_detail2 on #enr_detail(acc_nr)
GO
/* this is trying to use Omnix data so remove it
UPDATE #enr_detail
SET #enr_detail.bal_tot = ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))),
#enr_detail.r_arrears = ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2)))
FROM #enr_detail,
drsmas
WHERE #enr_detail.acc_nr = drsmas.drs_acc
GO */
PRINT 'Updating #enr_detail temporary table to include month end dates'
UPDATE #enr_detail
SET #enr_detail.acc_month = month_end_dates.acc_month,
#enr_detail.acc_year = month_end_dates.acc_year
FROM month_end_dates
WHERE convert(smalldatetime,convert(varchar(12),#enr_detail.enrllmnt_prcssd_dt) ,111) >= month_end_dates.start_date
AND convert(smalldatetime,convert(varchar(12),#enr_detail.enrllmnt_prcssd_dt) ,111) <= month_end_dates.end_date
AND #enr_detail.brand_id = month_end_dates.dwh_brand_id
GO
PRINT 'Creating the #demo temporary table'
select #enr_detail.enrollment_id,
attribute.code as demo_code,
substring(code,charindex('-',code)-1,1) as prefix,
attribute.prent_attribute_id as parent,
attribute.description as demo_desc,
#enr_detail.brand_id
INTO #demo
FROM #enr_detail,
attribute,
enrllmnt_attribute
WHERE #enr_detail.enrollment_id = enrllmnt_attribute.enrollment_id
AND enrllmnt_attribute.attribute_id = attribute.attribute_id
AND #enr_detail.brand_id = attribute.brand_id
PRINT 'Updating the #enr_detail temporary table with race demographics'
UPDATE #enr_detail
SET #enr_detail.race = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'R'
GO
PRINT 'Updating the #enr_detail temporary table with gender demographics'
UPDATE #enr_detail
SET #enr_detail.gender = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'G'
GO
PRINT 'Updating the #enr_detail temporary table with occupation demographics'
UPDATE #enr_detail
SET #enr_detail.occupation = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'O'
GO
June 7, 2012 at 6:03 am
don't allow a GO to exist inside the comments:
WHERE #enr_detail.acc_nr = drsmas.drs_acc
GO */
Lowell
June 7, 2012 at 8:00 am
Thanks but it's still not working at all:crying:
June 7, 2012 at 8:28 am
Bulelani M (6/7/2012)
Thanks but it's still not working at all:crying:
What does that mean? Same error? Different error? Not expected output?
Jared
CE - Microsoft
June 7, 2012 at 8:40 am
I formatted it. (Or, more precisely, RedGate SQL Prompt formatted it for me.) Didn't see any problems or get any errors.
Then I had SSMS parse it. No errors.
Is this what you're actually running and getting the error on?
Here's what it looks like formatted for readability. I usually find that helps locate this kind of error.
PRINT 'Starting Student Master Table Re-creation'
PRINT 'Dropping temporary tables...'
DROP TABLE #enr_detail
DROP TABLE #demo
DROP TABLE #addr_split
DROP TABLE #reg_detail
DROP TABLE #subj_detail
DROP TABLE #assg_detail
DROP TABLE #assg_dispatch
DROP TABLE #assg_passed
DROP TABLE #assg_submitted
DROP TABLE #Tot_assg_dispatch
DROP TABLE #assg_passed_reg
DROP TABLE #assg_submitted_reg
DROP TABLE #Tot_assg_dispatch_reg
DROP TABLE #assg_passed_subj
DROP TABLE #assg_submitted_subj
DROP TABLE #Tot_Assg_dispatch_subj
-- Don't need to drop the indexes if the tables have already been dropped
--drop index #tot_assg_dispatch_subj.idx_#Assg_dsp3
--drop index #tot_assg_dispatch_reg.idx_#Assg_dsp2
--drop index #tot_assg_dispatch.idx_#Assg_dsp1
--drop index #subj_detail.idx_subj_detail
PRINT 'Creating temporary table #enr_detail'
SELECT student.number AS student_nr,
student.student_id,
enrollment.enrollment_id,
title = CASE enrollment.title
WHEN 0 THEN 'None'
WHEN 1 THEN 'Mr'
WHEN 2 THEN 'Mrs'
WHEN 3 THEN 'Miss'
WHEN 4 THEN 'Ms'
WHEN 5 THEN 'Mnr'
WHEN 6 THEN 'Mev'
WHEN 7 THEN 'Mej'
WHEN 8 THEN 'Dr'
WHEN 9 THEN 'Prof'
END,
enrollment.initials,
enrollment.first_name,
enrollment.last_name,
--enrollment.address,
--enrollment.city,
enrollment.address + '\' AS address,
ISNULL(enrollment.city, '') AS city,
enrollment.postal_code,
CAST(' ' AS VARCHAR(250)) AS post_addr1,
CAST(' ' AS VARCHAR(250)) AS post_addr2,
CAST(' ' AS VARCHAR(150)) AS post_addr3,
CAST(' ' AS VARCHAR(150)) AS post_addr4,
ISNULL(enrollment.postal_code, '') AS post_postal_code,
CAST('unknown' AS VARCHAR(50)) AS province,
country.name AS country_name,
enrollment.home_area_code,
enrollment.home_phone,
enrollment.work_area_code,
enrollment.work_phone,
enrollment.cell_phone,
enrollment.email,
action_history.enrllmnt_prcssd_dt,
enrollment.contract_end_date,
contract = CASE WHEN enrollment.contract_end_date >= GETDATE() THEN 'InContract'
WHEN enrollment.contract_end_date < GETDATE() THEN 'OutContract'
END,
enrollment.status AS enrollment_status,
enr_status = CASE enrollment.status
WHEN 0 THEN 'Captured'
WHEN 1 THEN 'Pending'
WHEN 2 THEN 'Registered'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'Handed over'
WHEN 5 THEN 'Suspended'
WHEN 6 THEN 'Rejected'
WHEN 7 THEN 'Transferred'
END,
enrollment.id_number,
product.product_id,
product.code AS prod_code,
product.full_name AS prod_desc,
product.version AS prod_version,
school.short_name AS prod_school,
student_counsellor.code AS sc_code,
student_counsellor.last_name AS sc_name,
branch.name AS branch,
(enrollment.cash / 100) AS cash,
gev = CASE WHEN enrollment.payment_type = 1 THEN (enrollment.cash / 100)
WHEN enrollment.payment_type = 2
THEN (enrollment.installment * enrollment.payment_months + enrollment.deposit) / 100
END,
--(enrollment.installment*enrollment.payment_months+enrollment.deposit)/100 as gev,
(enrollment.rcipt_amount_cents / 100) AS fm,
(enrollment.installment / 100) AS installment,
enrollment.payment_type,
enrollment.omnx_ccount_number AS acc_nr,
enrollment.opportunity_id AS oppo_number,
0 AS acc_month,
0 AS acc_year,
--DATEDIFF (month, enrollment.date_of_birth, getdate())/12 as age,
age = CASE WHEN MONTH(enrollment.date_of_birth) = MONTH(GETDATE())
AND DAY(enrollment.date_of_birth) > DAY(GETDATE())
THEN (DATEDIFF(mm, enrollment.date_of_birth, GETDATE()) - 1) / 12
ELSE (DATEDIFF(mm, enrollment.date_of_birth, GETDATE())) / 12
END,
CONVERT(VARCHAR(30), 'unknown') AS race,
CONVERT(VARCHAR(30), 'unknown') AS gender,
CONVERT(VARCHAR(30), 'unknown') AS occupation,
CONVERT(VARCHAR(80), 'unknown') AS salary,
CONVERT(VARCHAR(80), 'unknown') AS language,
0 AS tot_assg_dispatch,
0 AS tot_assg_submitted,
0 AS tot_assg_passed,
0 AS tot_assg_outstanding,
CAST(NULL AS DATETIME) AS last_assg_date,
tracking_num_link.tracking_number,
CAST(0 AS DECIMAL(10, 2)) AS bal_tot,
CAST(0 AS DECIMAL(10, 2)) AS r_arrears,
--ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))) as bal_tot,
--ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2))) as r_arrears,
email_notfc = CASE enrollment.emil_address_valid
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
sms_notfc = CASE enrollment.cllphn_nmber_valid
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
0 AS tot_issues,
CAST(0 AS DECIMAL(8, 2)) AS tot_issues_avg_cost,
0 AS tot_unissued,
CAST(0 AS DECIMAL(8, 2)) AS tot_unissued_avg_cost,
CAST(NULL AS VARCHAR(50)) AS issues_status,
0 AS tot_sm_dispatch,
CAST(0 AS DECIMAL(8, 2)) AS tot_sm_avg_cost,
brand.short_name AS brand_name,
enrollment.employee_number,
enrollment.mailing_list_id,
enrollment.company_id,
enrollment.company_region_id,
enrollment.company_branch_id,
brand.brand_id,
CONVERT(VARCHAR(80), 'unknown') AS co_code,
CONVERT(VARCHAR(100), 'unknown') AS co_name,
CONVERT(VARCHAR(100), 'unknown') AS co_region,
CONVERT(VARCHAR(100), 'unknown') AS co_branch,
enrollment.payer_id,
enrollment.guarantor_id,
usr.login AS captured_usr,
action_history.date_captured AS capture_date,
action_history.date_pended AS pended_date,
a.login AS register_usr,
enrollment.discount_reason_id,
enrollment.selling_price_id
INTO #enr_detail
FROM student,
student_enrollment,
enrollment
LEFT OUTER JOIN branch
ON enrollment.branch_id = branch.branch_id
LEFT OUTER JOIN country
ON enrollment.country_country_id = country.country_id,
action_history
LEFT OUTER JOIN usr AS a
ON action_history.rgstrd_b_usr_id = a.usr_id,
product,
student_counsellor,
brand,
tracking_num_link,
school,
usr
WHERE student.student_id = student_enrollment.student_id
AND student_enrollment.enrollment_id = enrollment.enrollment_id
AND enrollment.product_id = product.product_id
AND enrollment.tracking_number_id = tracking_num_link.tracking_number_id
AND enrollment.stdnt_cunsellor_id = student_counsellor.stdnt_cunsellor_id
AND product.school_id = school.school_id
AND enrollment.brand_id = brand.brand_id
AND action_history.cptrd_by_usr_id = usr.usr_id
AND action_history.action_history_id = enrollment.action_history_id
GO
PRINT 'Creating index idx_#enr_detail on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail ON #enr_detail(enrollment_id)
GO
PRINT 'Creating index idx_#enr_detail2 on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail2 ON #enr_detail(acc_nr)
GO
/* this is trying to use Omnix data so remove it
UPDATE #enr_detail
SET #enr_detail.bal_tot = ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))),
#enr_detail.r_arrears = ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2)))
FROM #enr_detail,
drsmas
WHERE #enr_detail.acc_nr = drsmas.drs_acc
GO */
PRINT 'Updating #enr_detail temporary table to include month end dates'
UPDATE #enr_detail
SET #enr_detail.acc_month = month_end_dates.acc_month,
#enr_detail.acc_year = month_end_dates.acc_year
FROM month_end_dates
WHERE CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) >= month_end_dates.start_date
AND CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) <= month_end_dates.end_date
AND #enr_detail.brand_id = month_end_dates.dwh_brand_id
GO
PRINT 'Creating the #demo temporary table'
SELECT #enr_detail.enrollment_id,
attribute.code AS demo_code,
SUBSTRING(code, CHARINDEX('-', code) - 1, 1) AS prefix,
attribute.prent_attribute_id AS parent,
attribute.description AS demo_desc,
#enr_detail.brand_id
INTO #demo
FROM #enr_detail,
attribute,
enrllmnt_attribute
WHERE #enr_detail.enrollment_id = enrllmnt_attribute.enrollment_id
AND enrllmnt_attribute.attribute_id = attribute.attribute_id
AND #enr_detail.brand_id = attribute.brand_id
PRINT 'Updating the #enr_detail temporary table with race demographics'
UPDATE #enr_detail
SET #enr_detail.race = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'R'
GO
PRINT 'Updating the #enr_detail temporary table with gender demographics'
UPDATE #enr_detail
SET #enr_detail.gender = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'G'
GO
PRINT 'Updating the #enr_detail temporary table with occupation demographics'
UPDATE #enr_detail
SET #enr_detail.occupation = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'O'
GO
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2012 at 8:43 am
I would suggest that you clean up you joins. You are using the mix of the old style and new style joins.
FROM student
,student_enrollment
,enrollment
LEFT JOIN branch ON enrollment.branch_id = branch.branch_id
LEFT JOIN country ON enrollment.country_country_id = country.country_id
,action_history
LEFT JOIN usr AS a ON action_history.rgstrd_b_usr_id = a.usr_id
,product
,student_counsellor
,brand
,tracking_num_link
,school
,usr
This is going to be a nightmare to debug someday.
Just in case somebody else tries to look at this I formatted the entire code for you using http://poorsql.com
PRINT 'Starting Student Master Table Re-creation'
PRINT 'Dropping temporary tables...'
DROP TABLE #enr_detail
DROP TABLE #demo
DROP TABLE #addr_split
DROP TABLE #reg_detail
DROP TABLE #subj_detail
DROP TABLE #assg_detail
DROP TABLE #assg_dispatch
DROP TABLE #assg_passed
DROP TABLE #assg_submitted
DROP TABLE #Tot_assg_dispatch
DROP TABLE #assg_passed_reg
DROP TABLE #assg_submitted_reg
DROP TABLE #Tot_assg_dispatch_reg
DROP TABLE #assg_passed_subj
DROP TABLE #assg_submitted_subj
DROP TABLE #Tot_Assg_dispatch_subj
-- Don't need to drop the indexes if the tables have already been dropped
--drop index #tot_assg_dispatch_subj.idx_#Assg_dsp3
--drop index #tot_assg_dispatch_reg.idx_#Assg_dsp2
--drop index #tot_assg_dispatch.idx_#Assg_dsp1
--drop index #subj_detail.idx_subj_detail
PRINT 'Creating temporary table #enr_detail'
SELECT student.number AS student_nr
,student.student_id
,enrollment.enrollment_id
,title = CASE enrollment.title
WHEN 0
THEN 'None'
WHEN 1
THEN 'Mr'
WHEN 2
THEN 'Mrs'
WHEN 3
THEN 'Miss'
WHEN 4
THEN 'Ms'
WHEN 5
THEN 'Mnr'
WHEN 6
THEN 'Mev'
WHEN 7
THEN 'Mej'
WHEN 8
THEN 'Dr'
WHEN 9
THEN 'Prof'
END
,enrollment.initials
,enrollment.first_name
,enrollment.last_name
,
--enrollment.address,
--enrollment.city,
enrollment.address + '\' AS address
,isnull(enrollment.city, '') AS city
,enrollment.postal_code
,cast(' ' AS VARCHAR(250)) AS post_addr1
,cast(' ' AS VARCHAR(250)) AS post_addr2
,cast(' ' AS VARCHAR(150)) AS post_addr3
,cast(' ' AS VARCHAR(150)) AS post_addr4
,isnull(enrollment.postal_code, '') AS post_postal_code
,cast('unknown' AS VARCHAR(50)) AS province
,country.NAME AS country_name
,enrollment.home_area_code
,enrollment.home_phone
,enrollment.work_area_code
,enrollment.work_phone
,enrollment.cell_phone
,enrollment.email
,action_history.enrllmnt_prcssd_dt
,enrollment.contract_end_date
,contract = CASE
WHEN enrollment.contract_end_date >= getdate()
THEN 'InContract'
WHEN enrollment.contract_end_date < getdate()
THEN 'OutContract'
END
,enrollment.STATUS AS enrollment_status
,enr_status = CASE enrollment.STATUS
WHEN 0
THEN 'Captured'
WHEN 1
THEN 'Pending'
WHEN 2
THEN 'Registered'
WHEN 3
THEN 'Cancelled'
WHEN 4
THEN 'Handed over'
WHEN 5
THEN 'Suspended'
WHEN 6
THEN 'Rejected'
WHEN 7
THEN 'Transferred'
END
,enrollment.id_number
,product.product_id
,product.code AS prod_code
,product.full_name AS prod_desc
,product.version AS prod_version
,school.short_name AS prod_school
,student_counsellor.code AS sc_code
,student_counsellor.last_name AS sc_name
,branch.NAME AS branch
,(enrollment.cash / 100) AS cash
,gev = CASE
WHEN enrollment.payment_type = 1
THEN (enrollment.cash / 100)
WHEN enrollment.payment_type = 2
THEN (enrollment.installment * enrollment.payment_months + enrollment.deposit) / 100
END
,
--(enrollment.installment*enrollment.payment_months+enrollment.deposit)/100 as gev,
(enrollment.rcipt_amount_cents / 100) AS fm
,(enrollment.installment / 100) AS installment
,enrollment.payment_type
,enrollment.omnx_ccount_number AS acc_nr
,enrollment.opportunity_id AS oppo_number
,0 AS acc_month
,0 AS acc_year
,
--DATEDIFF (month, enrollment.date_of_birth, getdate())/12 as age,
age = CASE
WHEN month(enrollment.date_of_birth) = month(getdate())
AND day(enrollment.date_of_birth) > day(getdate())
THEN (DATEDIFF(mm, enrollment.date_of_birth, getdate()) - 1) / 12
ELSE (DATEDIFF(mm, enrollment.date_of_birth, getdate())) / 12
END
,convert(VARCHAR(30), 'unknown') AS race
,convert(VARCHAR(30), 'unknown') AS gender
,convert(VARCHAR(30), 'unknown') AS occupation
,convert(VARCHAR(80), 'unknown') AS salary
,convert(VARCHAR(80), 'unknown') AS LANGUAGE
,0 AS tot_assg_dispatch
,0 AS tot_assg_submitted
,0 AS tot_assg_passed
,0 AS tot_assg_outstanding
,cast(NULL AS DATETIME) AS last_assg_date
,tracking_num_link.tracking_number
,cast(0 AS DECIMAL(10, 2)) AS bal_tot
,cast(0 AS DECIMAL(10, 2)) AS r_arrears
,
--ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))) as bal_tot,
--ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2))) as r_arrears,
email_notfc = CASE enrollment.emil_address_valid
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END
,sms_notfc = CASE enrollment.cllphn_nmber_valid
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END
,0 AS tot_issues
,cast(0 AS DECIMAL(8, 2)) AS tot_issues_avg_cost
,0 AS tot_unissued
,cast(0 AS DECIMAL(8, 2)) AS tot_unissued_avg_cost
,cast(NULL AS VARCHAR(50)) AS issues_status
,0 AS tot_sm_dispatch
,cast(0 AS DECIMAL(8, 2)) AS tot_sm_avg_cost
,brand.short_name AS brand_name
,enrollment.employee_number
,enrollment.mailing_list_id
,enrollment.company_id
,enrollment.company_region_id
,enrollment.company_branch_id
,brand.brand_id
,convert(VARCHAR(80), 'unknown') AS co_code
,convert(VARCHAR(100), 'unknown') AS co_name
,convert(VARCHAR(100), 'unknown') AS co_region
,convert(VARCHAR(100), 'unknown') AS co_branch
,enrollment.payer_id
,enrollment.guarantor_id
,usr.LOGIN AS captured_usr
,action_history.date_captured AS capture_date
,action_history.date_pended AS pended_date
,a.LOGIN AS register_usr
,enrollment.discount_reason_id
,enrollment.selling_price_id
INTO #enr_detail
FROM student
,student_enrollment
,enrollment
LEFT JOIN branch ON enrollment.branch_id = branch.branch_id
LEFT JOIN country ON enrollment.country_country_id = country.country_id
,action_history
LEFT JOIN usr AS a ON action_history.rgstrd_b_usr_id = a.usr_id
,product
,student_counsellor
,brand
,tracking_num_link
,school
,usr
WHERE student.student_id = student_enrollment.student_id
AND student_enrollment.enrollment_id = enrollment.enrollment_id
AND enrollment.product_id = product.product_id
AND enrollment.tracking_number_id = tracking_num_link.tracking_number_id
AND enrollment.stdnt_cunsellor_id = student_counsellor.stdnt_cunsellor_id
AND product.school_id = school.school_id
AND enrollment.brand_id = brand.brand_id
AND action_history.cptrd_by_usr_id = usr.usr_id
AND action_history.action_history_id = enrollment.action_history_id
GO
PRINT 'Creating index idx_#enr_detail on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail ON #enr_detail (enrollment_id)
GO
PRINT 'Creating index idx_#enr_detail2 on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail2 ON #enr_detail (acc_nr)
GO
/* this is trying to use Omnix data so remove it
UPDATE #enr_detail
SET #enr_detail.bal_tot = ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))),
#enr_detail.r_arrears = ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2)))
FROM #enr_detail,
drsmas
WHERE #enr_detail.acc_nr = drsmas.drs_acc
GO */
PRINT 'Updating #enr_detail temporary table to include month end dates'
UPDATE #enr_detail
SET #enr_detail.acc_month = month_end_dates.acc_month
,#enr_detail.acc_year = month_end_dates.acc_year
FROM month_end_dates
WHERE convert(SMALLDATETIME, convert(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) >= month_end_dates.start_date
AND convert(SMALLDATETIME, convert(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) <= month_end_dates.end_date
AND #enr_detail.brand_id = month_end_dates.dwh_brand_id
GO
PRINT 'Creating the #demo temporary table'
SELECT #enr_detail.enrollment_id
,attribute.code AS demo_code
,substring(code, charindex('-', code) - 1, 1) AS prefix
,attribute.prent_attribute_id AS parent
,attribute.description AS demo_desc
,#enr_detail.brand_id
INTO #demo
FROM #enr_detail
,attribute
,enrllmnt_attribute
WHERE #enr_detail.enrollment_id = enrllmnt_attribute.enrollment_id
AND enrllmnt_attribute.attribute_id = attribute.attribute_id
AND #enr_detail.brand_id = attribute.brand_id
PRINT 'Updating the #enr_detail temporary table with race demographics'
UPDATE #enr_detail
SET #enr_detail.race = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'R'
GO
PRINT 'Updating the #enr_detail temporary table with gender demographics'
UPDATE #enr_detail
SET #enr_detail.gender = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'G'
GO
PRINT 'Updating the #enr_detail temporary table with occupation demographics'
UPDATE #enr_detail
SET #enr_detail.occupation = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'O'
GO
--EDIT--
Looks like Gus beat me to posting the formatted version by a bit.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 8, 2012 at 7:51 am
Thanks for your help guys..
This is a old query that I was updating, I never created it and when I first saw it I did feel good the way it was designed...
June 11, 2012 at 2:02 am
Hello
I should have done this from the beginning
Here is the whole query and it breaks and I'm running out of time, people are waiting for the reports today.
Please help me, tell me what is wrong with this query...
PRINT 'Starting Student Master Table Re-creation'
PRINT 'Dropping temporary tables...'
DROP TABLE #enr_detail
DROP TABLE #demo
DROP TABLE #addr_split
DROP TABLE #reg_detail
DROP TABLE #subj_detail
DROP TABLE #assg_detail
DROP TABLE #assg_dispatch
DROP TABLE #assg_passed
DROP TABLE #assg_submitted
DROP TABLE #Tot_assg_dispatch
DROP TABLE #assg_passed_reg
DROP TABLE #assg_submitted_reg
DROP TABLE #Tot_assg_dispatch_reg
DROP TABLE #assg_passed_subj
DROP TABLE #assg_submitted_subj
DROP TABLE #Tot_Assg_dispatch_subj
-- Don't need to drop the indexes if the tables have already been dropped
--drop index #tot_assg_dispatch_subj.idx_#Assg_dsp3
--drop index #tot_assg_dispatch_reg.idx_#Assg_dsp2
--drop index #tot_assg_dispatch.idx_#Assg_dsp1
--drop index #subj_detail.idx_subj_detail
PRINT 'Creating temporary table #enr_detail'
SELECT student.number AS student_nr
,student.student_id
,enrollment.enrollment_id
,title = CASE enrollment.title
WHEN 0
THEN 'None'
WHEN 1
THEN 'Mr'
WHEN 2
THEN 'Mrs'
WHEN 3
THEN 'Miss'
WHEN 4
THEN 'Ms'
WHEN 5
THEN 'Mnr'
WHEN 6
THEN 'Mev'
WHEN 7
THEN 'Mej'
WHEN 8
THEN 'Dr'
WHEN 9
THEN 'Prof'
END
,enrollment.initials
,enrollment.first_name
,enrollment.last_name
,
--enrollment.address,
--enrollment.city,
enrollment.address + '\' AS address
,ISNULL(enrollment.city, '') AS city
,enrollment.postal_code
,CAST(' ' AS VARCHAR(250)) AS post_addr1
,CAST(' ' AS VARCHAR(250)) AS post_addr2
,CAST(' ' AS VARCHAR(150)) AS post_addr3
,CAST(' ' AS VARCHAR(150)) AS post_addr4
,ISNULL(enrollment.postal_code, '') AS post_postal_code
,CAST('unknown' AS VARCHAR(50)) AS province
,country.NAME AS country_name
,enrollment.home_area_code
,enrollment.home_phone
,enrollment.work_area_code
,enrollment.work_phone
,enrollment.cell_phone
,enrollment.email
,action_history.enrllmnt_prcssd_dt
,enrollment.contract_end_date
,contract = CASE
WHEN enrollment.contract_end_date >= GETDATE()
THEN 'InContract'
WHEN enrollment.contract_end_date < GETDATE()
THEN 'OutContract'
END
,enrollment.STATUS AS enrollment_status
,enr_status = CASE enrollment.STATUS
WHEN 0
THEN 'Captured'
WHEN 1
THEN 'Pending'
WHEN 2
THEN 'Registered'
WHEN 3
THEN 'Cancelled'
WHEN 4
THEN 'Handed over'
WHEN 5
THEN 'Suspended'
WHEN 6
THEN 'Rejected'
WHEN 7
THEN 'Transferred'
END
,enrollment.id_number
,product.product_id
,product.code AS prod_code
,product.full_name AS prod_desc
,product.version AS prod_version
,school.short_name AS prod_school
,student_counsellor.code AS sc_code
,student_counsellor.last_name AS sc_name
,branch.NAME AS branch
,(enrollment.cash / 100) AS cash
,gev = CASE
WHEN enrollment.payment_type = 1
THEN (enrollment.cash / 100)
WHEN enrollment.payment_type = 2
THEN (enrollment.installment * enrollment.payment_months + enrollment.deposit) / 100
END
,
--(enrollment.installment*enrollment.payment_months+enrollment.deposit)/100 as gev,
(enrollment.rcipt_amount_cents / 100) AS fm
,(enrollment.installment / 100) AS installment
,enrollment.payment_type
,enrollment.omnx_ccount_number AS acc_nr
,enrollment.opportunity_id AS oppo_number
,0 AS acc_month
,0 AS acc_year
,
--DATEDIFF (month, enrollment.date_of_birth, getdate())/12 as age,
age = CASE
WHEN MONTH(enrollment.date_of_birth) = MONTH(GETDATE())
AND DAY(enrollment.date_of_birth) > DAY(GETDATE())
THEN (DATEDIFF(mm, enrollment.date_of_birth, GETDATE()) - 1) / 12
ELSE (DATEDIFF(mm, enrollment.date_of_birth, GETDATE())) / 12
END
,CONVERT(VARCHAR(30), 'unknown') AS race
,CONVERT(VARCHAR(30), 'unknown') AS gender
,CONVERT(VARCHAR(30), 'unknown') AS occupation
,CONVERT(VARCHAR(80), 'unknown') AS salary
,CONVERT(VARCHAR(80), 'unknown') AS LANGUAGE
,0 AS tot_assg_dispatch
,0 AS tot_assg_submitted
,0 AS tot_assg_passed
,0 AS tot_assg_outstanding
,CAST(NULL AS DATETIME) AS last_assg_date
,tracking_num_link.tracking_number
,CAST(0 AS DECIMAL(10, 2)) AS bal_tot
,CAST(0 AS DECIMAL(10, 2)) AS r_arrears
,
--ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))) as bal_tot,
--ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2))) as r_arrears,
email_notfc = CASE enrollment.emil_address_valid
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END
,sms_notfc = CASE enrollment.cllphn_nmber_valid
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END
,0 AS tot_issues
,CAST(0 AS DECIMAL(8, 2)) AS tot_issues_avg_cost
,0 AS tot_unissued
,CAST(0 AS DECIMAL(8, 2)) AS tot_unissued_avg_cost
,CAST(NULL AS VARCHAR(50)) AS issues_status
,0 AS tot_sm_dispatch
,CAST(0 AS DECIMAL(8, 2)) AS tot_sm_avg_cost
,brand.short_name AS brand_name
,enrollment.employee_number
,enrollment.mailing_list_id
,enrollment.company_id
,enrollment.company_region_id
,enrollment.company_branch_id
,brand.brand_id
,CONVERT(VARCHAR(80), 'unknown') AS co_code
,CONVERT(VARCHAR(100), 'unknown') AS co_name
,CONVERT(VARCHAR(100), 'unknown') AS co_region
,CONVERT(VARCHAR(100), 'unknown') AS co_branch
,enrollment.payer_id
,enrollment.guarantor_id
,usr.LOGIN AS captured_usr
,action_history.date_captured AS capture_date
,action_history.date_pended AS pended_date
,a.LOGIN AS register_usr
,enrollment.discount_reason_id
,enrollment.selling_price_id
INTO #enr_detail
FROM student
,student_enrollment
,enrollment
LEFT JOIN branch ON enrollment.branch_id = branch.branch_id
LEFT JOIN country ON enrollment.country_country_id = country.country_id
,action_history
LEFT JOIN usr AS a ON action_history.rgstrd_b_usr_id = a.usr_id
,product
,student_counsellor
,brand
,tracking_num_link
,school
,usr
WHERE student.student_id = student_enrollment.student_id
AND student_enrollment.enrollment_id = enrollment.enrollment_id
AND enrollment.product_id = product.product_id
AND enrollment.tracking_number_id = tracking_num_link.tracking_number_id
AND enrollment.stdnt_cunsellor_id = student_counsellor.stdnt_cunsellor_id
AND product.school_id = school.school_id
AND enrollment.brand_id = brand.brand_id
AND action_history.cptrd_by_usr_id = usr.usr_id
AND action_history.action_history_id = enrollment.action_history_id
GO
PRINT 'Creating index idx_#enr_detail on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail ON #enr_detail (enrollment_id)
GO
PRINT 'Creating index idx_#enr_detail2 on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail2 ON #enr_detail (acc_nr)
GO
/* this is trying to use Omnix data so remove it
UPDATE #enr_detail
SET #enr_detail.bal_tot = ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))),
#enr_detail.r_arrears = ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2)))
FROM #enr_detail,
drsmas
WHERE #enr_detail.acc_nr = drsmas.drs_acc
GO */
PRINT 'Updating #enr_detail temporary table to include month end dates'
UPDATE #enr_detail
SET #enr_detail.acc_month = month_end_dates.acc_month
,#enr_detail.acc_year = month_end_dates.acc_year
FROM month_end_dates
WHERE CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) >= month_end_dates.start_date
AND CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) <= month_end_dates.end_date
AND #enr_detail.brand_id = month_end_dates.dwh_brand_id
GO
PRINT 'Creating the #demo temporary table'
SELECT #enr_detail.enrollment_id
,attribute.code AS demo_code
,SUBSTRING(code, CHARINDEX('-', code) - 1, 1) AS prefix
,attribute.prent_attribute_id AS parent
,attribute.description AS demo_desc
,#enr_detail.brand_id
INTO #demo
FROM #enr_detail
,attribute
,enrllmnt_attribute
WHERE #enr_detail.enrollment_id = enrllmnt_attribute.enrollment_id
AND enrllmnt_attribute.attribute_id = attribute.attribute_id
AND #enr_detail.brand_id = attribute.brand_id
PRINT 'Updating the #enr_detail temporary table with race demographics'
UPDATE #enr_detail
SET #enr_detail.race = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'R'
GO
PRINT 'Updating the #enr_detail temporary table with gender demographics'
UPDATE #enr_detail
SET #enr_detail.gender = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'G'
GO
PRINT 'Updating the #enr_detail temporary table with occupation demographics'
UPDATE #enr_detail
SET #enr_detail.occupation = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'O'
GO
PRINT 'Updating the #enr_detail temporary table with salary demographics'
UPDATE #enr_detail
SET #enr_detail.salary = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
--AND #demo.prefix = 'S'
AND #demo.parent IN (
1
,205
,305
,405
,1087
)
GO
PRINT 'Updating the #enr_detail temporary table with language demographics'
UPDATE #enr_detail
SET #enr_detail.LANGUAGE = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'L'
GO
PRINT 'Splitting addresses into multiple lines'
SELECT #enr_detail.enrollment_id
,#enr_detail.address
,charindex('\', #enr_detail.address) AS post_line1
,charindex('\', #enr_detail.address, charindex('\', #enr_detail.address) + 1) AS post_line2
,post_line3 = CASE
WHEN charindex('\', #enr_detail.address, charindex('\', #enr_detail.address) + 1) = 0
THEN 0
ELSE charindex('\', #enr_detail.address, charindex('\', #enr_detail.address, charindex('\', #enr_detail.address) + 1) + 1)
END
INTO #addr_split
FROM #enr_detail
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 1'
UPDATE #enr_detail
SET #enr_detail.post_addr1 = substring(#enr_detail.address, 1, #addr_split.post_line1 - 1)
FROM #addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 2'
UPDATE #enr_detail
SET #enr_detail.post_addr2 = substring(#enr_detail.address, #addr_split.post_line1 + 1, (#addr_split.post_line2 - #addr_split.post_line1) - 1)
FROM #addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line2 <> 0
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 3'
UPDATE #enr_detail
SET #enr_detail.post_addr3 = substring(#enr_detail.address, #addr_split.post_line2 + 1, (len(#enr_detail.address) - #addr_split.post_line2) - 1)
FROM #addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line3 <> 0
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 4'
UPDATE #enr_detail
SET #enr_detail.post_addr4 = #enr_detail.city
FROM #enr_detail
,#addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line3 <> 0
GO
PRINT 'Updating the #enr_detail temporary table with postal city on line 3'
UPDATE #enr_detail
SET #enr_detail.post_addr3 = #enr_detail.city
FROM #enr_detail
,#addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line2 <> 0
AND #addr_split.post_line3 = 0
GO
PRINT 'Updating the #enr_detail temporary table with postal city on line 2'
UPDATE #enr_detail
SET #enr_detail.post_addr2 = #enr_detail.city
FROM #enr_detail
,#addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line2 = 0
AND #addr_split.post_line3 = 0
GO
PRINT 'Updating the #enr_detail temporary table with provinces'
UPDATE #enr_detail
SET #enr_detail.province = postal_code_ranges.province
FROM postal_code_ranges
WHERE #enr_detail.post_postal_code >= postal_code_ranges.range_start
AND #enr_detail.post_postal_code <= postal_code_ranges.range_end
AND #enr_detail.country_name = 'South Africa' ---Added 70aug 2006
GO
PRINT 'Updating the #enr_detail temporary table with company namd and code'
UPDATE #enr_detail
SET #enr_detail.co_code = company.code
,#enr_detail.co_name = company.NAME
FROM company
WHERE #enr_detail.company_id = company.company_id
GO
PRINT 'Updating the #enr_detail temporary table with company region'
UPDATE #enr_detail
SET #enr_detail.co_region = company_region.nme
FROM company_region
WHERE #enr_detail.company_region_id = company_region.company_region_id
GO
PRINT 'Updating the #enr_detail temporary table with company branch'
UPDATE #enr_detail
SET #enr_detail.co_branch = company_branch.nme
FROM company_branch
WHERE #enr_detail.company_branch_id = company_branch.company_branch_id
GO
/* Registration details */
PRINT 'Creating the #reg_detail temporary table from #enr_detail'
SELECT #enr_detail.*
,registration.registration_id
,registration.STATUS AS reg_status
,reg_status_def = CASE registration.STATUS
WHEN 1
THEN 'Active'
WHEN 2
THEN 'Complete'
WHEN 3
THEN 'Certificated'
WHEN 4
THEN 'Pending'
WHEN 5
THEN 'Cancelled'
WHEN 6
THEN 'Transferred'
END
,course.course_id
,course.code AS crs_code
,course.full_name AS crs_desc
,course.version AS crs_version
,school.code AS crs_school_code
,school.short_name AS crs_school
,course.type AS crs_type
,crs_type_def = CASE course.type
WHEN 1
THEN 'Non exam'
WHEN 2
THEN 'Internal exam'
WHEN 3
THEN 'External exam'
END
,qualification_type = CASE course.qualification_type
WHEN 0
THEN 'Not set'
WHEN 1
THEN 'Certificate'
WHEN 2
THEN 'Higher certificate'
WHEN 3
THEN 'Adv certificate'
WHEN 4
THEN 'Diploma'
WHEN 5
THEN 'Advanced diploma'
WHEN 6
THEN 'Subject completion cert'
WHEN 7
THEN 'Further diploma'
WHEN 8
THEN 'Prep certificate'
WHEN 9
THEN 'Prep diploma'
WHEN 10
THEN 'Higher diploma'
WHEN 11
THEN 'Advanced certificate'
WHEN 12
THEN 'External outcome'
WHEN 13
THEN 'Short course certificate'
WHEN 14
THEN 'Foundation certificate'
END
,course.qualification_name
,registration.lst_contacted_date
,registration.lst_submitted_date
,registration.submitted_percent
,registration.at_risk
,registration.course_completed_date
INTO #reg_detail
FROM rgstrtn_enrollment
,registration
,#enr_detail
,course
,school
WHERE #enr_detail.enrollment_id = rgstrtn_enrollment.enrollment_id
AND rgstrtn_enrollment.registration_id = registration.registration_id
AND registration.course_id = course.course_id
AND course.school_id = school.school_id
GO
/* Subject details */
PRINT 'Creating the #subj_detail temporary table from #reg_detail'
SELECT #reg_detail.*
,rgstration_subject.rgstrtn_subject_id
,subject.subject_id
,subject.code AS subj_code
,subject.full_name AS subj_desc
,subject.version AS subj_version
,subj_exam_type = CASE subject.exam_type
WHEN 1
THEN 'Non exam'
WHEN 2
THEN 'Internal exam'
WHEN 3
THEN 'External exam'
END
INTO #subj_detail
FROM #reg_detail
,rgstration_subject
,subject
WHERE #reg_detail.registration_id = rgstration_subject.registration_id
AND rgstration_subject.subject_id = subject.subject_id
GO
PRINT 'Creating index idx_subj_detail on the #subj_detail temporary table'
CREATE INDEX idx_subj_detail ON #subj_detail (
registration_id
,subject_id
)
GO
/* Assignment Detail*/
PRINT 'Creating #assg_detail temporary table'
SELECT #subj_detail.*
,assignment_case.assignment_case_id
,assignment.assignment_id
,assignment.code AS assg_code
,assignment_case.mark
,assg_case_status = CASE
WHEN assignment_case.STATUS = 1
THEN 'Open'
WHEN assignment_case.STATUS = 2
THEN 'Accepted'
WHEN assignment_case.STATUS = 3
THEN 'Rejected'
WHEN assignment_case.STATUS = 4
THEN 'Allocated'
WHEN assignment_case.STATUS = 5
THEN 'Sent to tutor'
WHEN assignment_case.STATUS = 6
THEN 'Passed'
WHEN assignment_case.STATUS = 7
THEN 'Failed'
WHEN assignment_case.STATUS = 8
THEN 'Ptp not met'
WHEN assignment_case.STATUS = 9
THEN 'Withheld'
WHEN assignment_case.STATUS = 12
THEN 'Resolved'
WHEN assignment_case.STATUS = 99
THEN 'Credit'
WHEN assignment_case.STATUS = 100
THEN 'Closed'
END
,assignment_case.STATUS
,assignment_case.date_created
,assignment_case.last_action_date
,(cast(#subj_detail.enrollment_id AS VARCHAR(20)) + cast(#subj_detail.registration_id AS VARCHAR(20)) + cast(assignment.assignment_id AS VARCHAR(20))) AS assg_key
INTO #assg_detail
FROM assignment_case
,assignment
,#subj_detail
WHERE #subj_detail.rgstrtn_subject_id = assignment_case.rgstrtn_subject_id
AND assignment_case.assignment_id = assignment.assignment_id
GO
PRINT 'Creating index idx_subj_detail on the #subj_detail temporary table'
SELECT #subj_detail.*
,assignment.assignment_id
,assignment.code AS assg_code
INTO #assg_dispatch
FROM #subj_detail
,dispatch
,dspatch_assignment
,assignment
WHERE #subj_detail.subject_id = dispatch.subject_id
AND dispatch.dispatch_id = dspatch_assignment.dispatch_id
AND dspatch_assignment.assignment_id = assignment.assignment_id
GO
PRINT 'Dropping StudentMaster table if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'StudentMaster'
)
BEGIN
DROP TABLE StudentMaster
END
GO
PRINT 'Populating StudentMaster table'
SELECT *
INTO StudentMaster
FROM #enr_detail
GO
PRINT 'Creating indexes on StudentMaster table'
CREATE INDEX idx_StudentMaster1 ON StudentMaster (enrollment_id)
GO
CREATE CLUSTERED INDEX idx_StudentMaster2 ON StudentMaster (enrllmnt_prcssd_dt)
GO
CREATE INDEX idx_StudentMaster3 ON StudentMaster (brand_id)
GO
CREATE INDEX idx_StudentMaster4 ON StudentMaster (acc_nr)
GO
CREATE INDEX idx_StudentMaster5 ON StudentMaster (contract)
GO
CREATE INDEX idx_StudentMaster6 ON StudentMaster (capture_date)
GO
CREATE INDEX idx_StudentMaster7 ON StudentMaster (captured_usr)
GO
CREATE INDEX idx_StudentMaster8 ON StudentMaster (enr_status)
GO
/* assignment enrollment update */
PRINT 'Creating #assg_passed temporary table'
SELECT #assg_detail.enrollment_id
,count(DISTINCT #assg_detail.assignment_id) AS tot_assg_passed
INTO #assg_passed
FROM #assg_detail
WHERE #assg_detail.STATUS = 6
GROUP BY #assg_detail.enrollment_id
GO
PRINT 'Creating #assg_submitted temporary table'
SELECT #assg_detail.enrollment_id
,max(#assg_detail.date_created) AS last_assg_date
,count(#assg_detail.assignment_id) AS tot_assg_submitted
INTO #assg_submitted
FROM #assg_detail
GROUP BY #assg_detail.enrollment_id
GO
PRINT 'Updating StudentMaster with assignments passed'
UPDATE StudentMaster
SET StudentMaster.tot_assg_passed = #assg_passed.tot_assg_passed
FROM #assg_passed
WHERE StudentMaster.enrollment_id = #assg_passed.enrollment_id
GO
PRINT 'Updating StudentMaster with assignments submitted'
UPDATE StudentMaster
SET StudentMaster.tot_assg_submitted = #assg_submitted.tot_assg_submitted
,StudentMaster.last_assg_date = #assg_submitted.last_assg_date
FROM #assg_submitted
WHERE StudentMaster.enrollment_id = #assg_submitted.enrollment_id
GO
PRINT 'Creating #tot_assg_dispatch temporary table'
SELECT #assg_dispatch.enrollment_id
,count(#assg_dispatch.assg_code) tot_assg_dispatch
INTO #tot_assg_dispatch
FROM #assg_dispatch
GROUP BY #assg_dispatch.enrollment_id
GO
PRINT 'Creating idx_#assg_dsp1 index on #tot_assg_dispatch temporary table'
CREATE INDEX idx_#assg_dsp1 ON #tot_assg_dispatch (enrollment_id)
GO
PRINT 'Updating StudentMaster with total assignments dispatched'
UPDATE StudentMaster
SET StudentMaster.tot_assg_dispatch = #tot_assg_dispatch.tot_assg_dispatch
FROM #tot_assg_dispatch
WHERE StudentMaster.enrollment_id = #tot_assg_dispatch.enrollment_id
GO
PRINT 'Updating StudentMaster with total assignments outstanding'
UPDATE StudentMaster
SET StudentMaster.tot_assg_outstanding = isnull((StudentMaster.tot_assg_dispatch - StudentMaster.tot_assg_passed), StudentMaster.tot_assg_dispatch)
FROM StudentMaster
GO
PRINT 'Drop the RegistrationMaster table if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'RegistrationMaster'
)
BEGIN
DROP TABLE RegistrationMaster
END
GO
PRINT 'Create the RegistrationMaster table from the #reg_detail temporary table'
SELECT *
INTO RegistrationMaster
FROM #reg_detail
GO
PRINT 'Create indexes on the RegistrationMaster table'
CREATE INDEX idx_RegistrationMaster1 ON RegistrationMaster (brand_id)
GO
CREATE CLUSTERED INDEX idx_RegistrationMaster2 ON RegistrationMaster (
enrollment_id
,registration_id
)
GO
CREATE INDEX idx_RegistrationMaster3 ON RegistrationMaster (registration_id)
GO
CREATE INDEX idx_RegistrationMaster4 ON RegistrationMaster (contract)
GO
CREATE INDEX idx_RegistrationMaster5 ON RegistrationMaster (crs_code)
GO
/* assignment registration update */
PRINT 'Create #assg_passed_reg temporary table'
SELECT #assg_detail.registration_id
,count(DISTINCT #assg_detail.assignment_id) AS tot_assg_passed
INTO #assg_passed_reg
FROM #assg_detail
WHERE #assg_detail.STATUS = 6
GROUP BY #assg_detail.registration_id
GO
PRINT 'Create #assg_submitted_reg temporary table'
SELECT #assg_detail.registration_id
,max(#assg_detail.date_created) AS last_assg_date
,count(#assg_detail.assignment_id) AS tot_assg_submitted
INTO #assg_submitted_reg
FROM #assg_detail
GROUP BY #assg_detail.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments passed'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_passed = #assg_passed_reg.tot_assg_passed
FROM #assg_passed_reg
WHERE RegistrationMaster.registration_id = #assg_passed_reg.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments submitted'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_submitted = #assg_submitted_reg.tot_assg_submitted
,RegistrationMaster.last_assg_date = #assg_submitted_reg.last_assg_date
FROM #assg_submitted_reg
WHERE RegistrationMaster.registration_id = #assg_submitted_reg.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments dispatched'
SELECT #assg_dispatch.registration_id
,count(#assg_dispatch.assg_code) tot_assg_dispatch
INTO #tot_assg_dispatch_reg
FROM #assg_dispatch
GROUP BY #assg_dispatch.registration_id
GO
CREATE INDEX idx_#assg_dsp2 ON #tot_assg_dispatch_reg (registration_id)
GO
PRINT 'Update RegistrationMaster table with total assignments dispatched'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_dispatch = #tot_assg_dispatch_reg.tot_assg_dispatch
FROM #tot_assg_dispatch_reg
WHERE RegistrationMaster.registration_id = #tot_assg_dispatch_reg.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments outstanding'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_outstanding = isnull((RegistrationMaster.tot_assg_dispatch - RegistrationMaster.tot_assg_passed), RegistrationMaster.tot_assg_dispatch)
FROM RegistrationMaster
GO
PRINT 'Drop the RegSubjMaster table if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'RegSubjMaster'
)
BEGIN
DROP TABLE RegSubjMaster
END
GO
PRINT 'Populate the RegSubjMaster table from the #subj_detail temporary table'
SELECT *
INTO RegSubjMaster
FROM #subj_detail
GO
PRINT 'Create indexes on the RegSubjMaster table'
CREATE INDEX idx_RegSubjMaster1 ON RegSubjMaster (subject_id)
GO
CREATE INDEX idx_RegSubjMaster2 ON RegSubjMaster (registration_id)
GO
CREATE INDEX idx_RegSubjMaster3 ON RegSubjMaster (enrollment_id)
GO
CREATE INDEX idx_RegSubjMaster4 ON RegSubjMaster (contract)
GO
CREATE INDEX idx_RegSubjMaster5 ON RegSubjMaster (subj_code)
GO
CREATE INDEX idx_RegSubjMaster6 ON RegSubjMaster (
registration_id
,subject_id
)
GO
CREATE CLUSTERED INDEX idx_RegSubjMaster7 ON RegSubjMaster (enrllmnt_prcssd_dt)
GO
/* subject registration update */
PRINT 'Create #assg_passed_subj temporary table'
SELECT #assg_detail.registration_id
,#assg_detail.subject_id
,count(DISTINCT #assg_detail.assignment_id) AS tot_assg_passed
INTO #assg_passed_subj
FROM #assg_detail
WHERE #assg_detail.STATUS = 6
GROUP BY #assg_detail.registration_id
,#assg_detail.subject_id
GO
PRINT 'Create #assg_submitted_subj temporary table'
SELECT #assg_detail.registration_id
,#assg_detail.subject_id
,max(#assg_detail.date_created) AS last_assg_date
,count(#assg_detail.assignment_id) AS tot_assg_submitted
INTO #assg_submitted_subj
FROM #assg_detail
GROUP BY #assg_detail.registration_id
,#assg_detail.subject_id
GO
PRINT 'Update RegSubjMaster with total assignments passed'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_passed = #assg_passed_subj.tot_assg_passed
FROM #assg_passed_subj
WHERE RegSubjMaster.registration_id = #assg_passed_subj.registration_id
AND RegSubjMaster.subject_id = #assg_passed_subj.subject_id
GO
PRINT 'Update RegSubjMaster with total assignments submitted'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_submitted = #assg_submitted_subj.tot_assg_submitted
,RegSubjMaster.last_assg_date = #assg_submitted_subj.last_assg_date
FROM #assg_submitted_subj
WHERE RegSubjMaster.registration_id = #assg_submitted_subj.registration_id
AND RegSubjMaster.subject_id = #assg_submitted_subj.subject_id
GO
PRINT 'Create #tot_assg_dispatch_subj temporary table'
SELECT #assg_dispatch.registration_id
,#assg_dispatch.subject_id
,count(#assg_dispatch.assg_code) tot_assg_dispatch
INTO #tot_assg_dispatch_subj
FROM #assg_dispatch
GROUP BY #assg_dispatch.registration_id
,#assg_dispatch.subject_id
GO
CREATE INDEX idx_#assg_dsp3 ON #tot_assg_dispatch_subj (
registration_id
,subject_id
)
GO
PRINT 'Update RegSubjMaster with total assignments dispatched'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_dispatch = #tot_assg_dispatch_subj.tot_assg_dispatch
FROM #tot_assg_dispatch_subj
WHERE RegSubjMaster.registration_id = #tot_assg_dispatch_subj.registration_id
AND RegSubjMaster.subject_id = #tot_assg_dispatch_subj.subject_id
GO
PRINT 'Update RegSubjMaster with total assignments outstanding'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_outstanding = isnull((RegSubjMaster.tot_assg_dispatch - RegSubjMaster.tot_assg_passed), RegSubjMaster.tot_assg_dispatch)
FROM RegSubjMaster
GO
PRINT 'Drop table AssgDispatchMaster if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'AssgDispatchMaster'
)
BEGIN
DROP TABLE AssgDispatchMaster
END
GO
PRINT 'Populate the AssgDispatchMaster table'
SELECT student_nr
,enrollment_id
,title
,first_name
,last_name
,post_addr1
,post_addr2
,post_addr3
,post_addr4
,post_postal_code
,province
,home_area_code
,home_phone
,work_area_code
,work_phone
,cell_phone
,enrllmnt_prcssd_dt
,contract_end_date
,contract
,acc_month
,acc_year
,enrollment_status
,enr_status
,id_number
,product_id
,prod_code
,prod_desc
,prod_version
,prod_school
,acc_nr
,tracking_number
,bal_tot
,r_arrears
,brand_id
,registration_id
,reg_status
,reg_status_def
,course_id
,crs_code
,crs_desc
,crs_version
,crs_school_code
,crs_school
,crs_type
,crs_type_def
,rgstrtn_subject_id
,subject_id
,subj_code
,subj_desc
,subj_version
,subj_exam_type
,assignment_id
,assg_code
,brand_name
INTO AssgDispatchMaster
FROM #assg_dispatch
GO
PRINT 'Create indexes on the AssgDispatchMaster table'
CREATE INDEX idx_AssgDispatchMaster1 ON AssgDispatchMaster (enrollment_id)
GO
CREATE INDEX idx_AssgDispatchMaster2 ON AssgDispatchMaster (registration_id)
GO
CREATE INDEX idx_AssgDispatchMaster3 ON AssgDispatchMaster (subject_id)
GO
CREATE INDEX idx_AssgDispatchMaster4 ON AssgDispatchMaster (assg_code)
GO
PRINT 'Drop table RegAssgMaster if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'RegAssgMaster'
)
BEGIN
DROP TABLE RegAssgMaster
END
GO
PRINT 'Populate table RegAssgMaster'
SELECT student_nr
,enrollment_id
,title
,first_name
,last_name
,post_addr1
,post_addr2
,post_addr3
,post_addr4
,post_postal_code
,province
,home_area_code
,home_phone
,work_area_code
,work_phone
,cell_phone
,enrllmnt_prcssd_dt
,contract_end_date
,contract
,acc_month
,acc_year
,enrollment_status
,enr_status
,id_number
,product_id
,prod_code
,prod_desc
,prod_version
,prod_school
,acc_nr
,tracking_number
,bal_tot
,r_arrears
,brand_id
,brand_name
,registration_id
,reg_status
,reg_status_def
,course_id
,crs_code
,crs_desc
,crs_version
,crs_school_code
,crs_school
,crs_type
,crs_type_def
,rgstrtn_subject_id
,subject_id
,subj_code
,subj_desc
,subj_version
,subj_exam_type
,assignment_case_id
,assignment_id
,assg_code
,mark
,isnull(assg_case_status, 'Unknown') AS assg_case_status
,STATUS AS assg_status
,date_created
,last_action_date
INTO RegAssgMaster
FROM #assg_detail
GO
PRINT 'Create indexes on table RegAssgMaster'
CREATE INDEX idx_RegAssgMaster ON RegAssgMaster (enrollment_id)
GO
CREATE INDEX idx_RegAssgMaster1 ON RegAssgMaster (
registration_id
,subject_id
,assignment_id
)
GO
CREATE INDEX idx_RegAssgMaster2 ON RegAssgMaster (
registration_id
,subject_id
)
GO
CREATE INDEX idx_RegAssgMaster3 ON RegAssgMaster (
brand_name
,assg_code
)
GO
June 11, 2012 at 5:57 am
Formatted:
PRINT 'Starting Student Master Table Re-creation'
PRINT 'Dropping temporary tables...'
DROP TABLE #enr_detail
DROP TABLE #demo
DROP TABLE #addr_split
DROP TABLE #reg_detail
DROP TABLE #subj_detail
DROP TABLE #assg_detail
DROP TABLE #assg_dispatch
DROP TABLE #assg_passed
DROP TABLE #assg_submitted
DROP TABLE #Tot_assg_dispatch
DROP TABLE #assg_passed_reg
DROP TABLE #assg_submitted_reg
DROP TABLE #Tot_assg_dispatch_reg
DROP TABLE #assg_passed_subj
DROP TABLE #assg_submitted_subj
DROP TABLE #Tot_Assg_dispatch_subj
-- Don't need to drop the indexes if the tables have already been dropped
--drop index #tot_assg_dispatch_subj.idx_#Assg_dsp3
--drop index #tot_assg_dispatch_reg.idx_#Assg_dsp2
--drop index #tot_assg_dispatch.idx_#Assg_dsp1
--drop index #subj_detail.idx_subj_detail
PRINT 'Creating temporary table #enr_detail'
SELECT student.number AS student_nr
,student.student_id
,enrollment.enrollment_id
,title = CASE enrollment.title
WHEN 0
THEN 'None'
WHEN 1
THEN 'Mr'
WHEN 2
THEN 'Mrs'
WHEN 3
THEN 'Miss'
WHEN 4
THEN 'Ms'
WHEN 5
THEN 'Mnr'
WHEN 6
THEN 'Mev'
WHEN 7
THEN 'Mej'
WHEN 8
THEN 'Dr'
WHEN 9
THEN 'Prof'
END
,enrollment.initials
,enrollment.first_name
,enrollment.last_name
,
--enrollment.address,
--enrollment.city,
enrollment.address + '\' AS address
,ISNULL(enrollment.city, '') AS city
,enrollment.postal_code
,CAST(' ' AS VARCHAR(250)) AS post_addr1
,CAST(' ' AS VARCHAR(250)) AS post_addr2
,CAST(' ' AS VARCHAR(150)) AS post_addr3
,CAST(' ' AS VARCHAR(150)) AS post_addr4
,ISNULL(enrollment.postal_code, '') AS post_postal_code
,CAST('unknown' AS VARCHAR(50)) AS province
,country.NAME AS country_name
,enrollment.home_area_code
,enrollment.home_phone
,enrollment.work_area_code
,enrollment.work_phone
,enrollment.cell_phone
,enrollment.email
,action_history.enrllmnt_prcssd_dt
,enrollment.contract_end_date
,contract = CASE
WHEN enrollment.contract_end_date >= GETDATE()
THEN 'InContract'
WHEN enrollment.contract_end_date < GETDATE()
THEN 'OutContract'
END
,enrollment.STATUS AS enrollment_status
,enr_status = CASE enrollment.STATUS
WHEN 0
THEN 'Captured'
WHEN 1
THEN 'Pending'
WHEN 2
THEN 'Registered'
WHEN 3
THEN 'Cancelled'
WHEN 4
THEN 'Handed over'
WHEN 5
THEN 'Suspended'
WHEN 6
THEN 'Rejected'
WHEN 7
THEN 'Transferred'
END
,enrollment.id_number
,product.product_id
,product.code AS prod_code
,product.full_name AS prod_desc
,product.version AS prod_version
,school.short_name AS prod_school
,student_counsellor.code AS sc_code
,student_counsellor.last_name AS sc_name
,branch.NAME AS branch
,(enrollment.cash / 100) AS cash
,gev = CASE
WHEN enrollment.payment_type = 1
THEN (enrollment.cash / 100)
WHEN enrollment.payment_type = 2
THEN (enrollment.installment * enrollment.payment_months + enrollment.deposit) / 100
END
,
--(enrollment.installment*enrollment.payment_months+enrollment.deposit)/100 as gev,
(enrollment.rcipt_amount_cents / 100) AS fm
,(enrollment.installment / 100) AS installment
,enrollment.payment_type
,enrollment.omnx_ccount_number AS acc_nr
,enrollment.opportunity_id AS oppo_number
,0 AS acc_month
,0 AS acc_year
,
--DATEDIFF (month, enrollment.date_of_birth, getdate())/12 as age,
age = CASE
WHEN MONTH(enrollment.date_of_birth) = MONTH(GETDATE())
AND DAY(enrollment.date_of_birth) > DAY(GETDATE())
THEN (DATEDIFF(mm, enrollment.date_of_birth, GETDATE()) - 1) / 12
ELSE (DATEDIFF(mm, enrollment.date_of_birth, GETDATE())) / 12
END
,CONVERT(VARCHAR(30), 'unknown') AS race
,CONVERT(VARCHAR(30), 'unknown') AS gender
,CONVERT(VARCHAR(30), 'unknown') AS occupation
,CONVERT(VARCHAR(80), 'unknown') AS salary
,CONVERT(VARCHAR(80), 'unknown') AS LANGUAGE
,0 AS tot_assg_dispatch
,0 AS tot_assg_submitted
,0 AS tot_assg_passed
,0 AS tot_assg_outstanding
,CAST(NULL AS DATETIME) AS last_assg_date
,tracking_num_link.tracking_number
,CAST(0 AS DECIMAL(10, 2)) AS bal_tot
,CAST(0 AS DECIMAL(10, 2)) AS r_arrears
,
--ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))) as bal_tot,
--ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2))) as r_arrears,
email_notfc = CASE enrollment.emil_address_valid
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END
,sms_notfc = CASE enrollment.cllphn_nmber_valid
WHEN 1
THEN 'Yes'
WHEN 0
THEN 'No'
END
,0 AS tot_issues
,CAST(0 AS DECIMAL(8, 2)) AS tot_issues_avg_cost
,0 AS tot_unissued
,CAST(0 AS DECIMAL(8, 2)) AS tot_unissued_avg_cost
,CAST(NULL AS VARCHAR(50)) AS issues_status
,0 AS tot_sm_dispatch
,CAST(0 AS DECIMAL(8, 2)) AS tot_sm_avg_cost
,brand.short_name AS brand_name
,enrollment.employee_number
,enrollment.mailing_list_id
,enrollment.company_id
,enrollment.company_region_id
,enrollment.company_branch_id
,brand.brand_id
,CONVERT(VARCHAR(80), 'unknown') AS co_code
,CONVERT(VARCHAR(100), 'unknown') AS co_name
,CONVERT(VARCHAR(100), 'unknown') AS co_region
,CONVERT(VARCHAR(100), 'unknown') AS co_branch
,enrollment.payer_id
,enrollment.guarantor_id
,usr.LOGIN AS captured_usr
,action_history.date_captured AS capture_date
,action_history.date_pended AS pended_date
,a.LOGIN AS register_usr
,enrollment.discount_reason_id
,enrollment.selling_price_id
INTO #enr_detail
FROM student
,student_enrollment
,enrollment
LEFT JOIN branch ON enrollment.branch_id = branch.branch_id
LEFT JOIN country ON enrollment.country_country_id = country.country_id
,action_history
LEFT JOIN usr AS a ON action_history.rgstrd_b_usr_id = a.usr_id
,product
,student_counsellor
,brand
,tracking_num_link
,school
,usr
WHERE student.student_id = student_enrollment.student_id
AND student_enrollment.enrollment_id = enrollment.enrollment_id
AND enrollment.product_id = product.product_id
AND enrollment.tracking_number_id = tracking_num_link.tracking_number_id
AND enrollment.stdnt_cunsellor_id = student_counsellor.stdnt_cunsellor_id
AND product.school_id = school.school_id
AND enrollment.brand_id = brand.brand_id
AND action_history.cptrd_by_usr_id = usr.usr_id
AND action_history.action_history_id = enrollment.action_history_id
GO
PRINT 'Creating index idx_#enr_detail on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail ON #enr_detail (enrollment_id)
GO
PRINT 'Creating index idx_#enr_detail2 on temporary table #enr_detail'
CREATE INDEX idx_#enr_detail2 ON #enr_detail (acc_nr)
GO
/* this is trying to use Omnix data so remove it
UPDATE #enr_detail
SET #enr_detail.bal_tot = ISNULL(drsmas.bal_tot,cast(0 as decimal(10,2))),
#enr_detail.r_arrears = ISNULL(drsmas.r_arrears,cast(0 as decimal(10,2)))
FROM #enr_detail,
drsmas
WHERE #enr_detail.acc_nr = drsmas.drs_acc
GO */
PRINT 'Updating #enr_detail temporary table to include month end dates'
UPDATE #enr_detail
SET #enr_detail.acc_month = month_end_dates.acc_month
,#enr_detail.acc_year = month_end_dates.acc_year
FROM month_end_dates
WHERE CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) >= month_end_dates.start_date
AND CONVERT(SMALLDATETIME, CONVERT(VARCHAR(12), #enr_detail.enrllmnt_prcssd_dt), 111) <= month_end_dates.end_date
AND #enr_detail.brand_id = month_end_dates.dwh_brand_id
GO
PRINT 'Creating the #demo temporary table'
SELECT #enr_detail.enrollment_id
,attribute.code AS demo_code
,SUBSTRING(code, CHARINDEX('-', code) - 1, 1) AS prefix
,attribute.prent_attribute_id AS parent
,attribute.description AS demo_desc
,#enr_detail.brand_id
INTO #demo
FROM #enr_detail
,attribute
,enrllmnt_attribute
WHERE #enr_detail.enrollment_id = enrllmnt_attribute.enrollment_id
AND enrllmnt_attribute.attribute_id = attribute.attribute_id
AND #enr_detail.brand_id = attribute.brand_id
PRINT 'Updating the #enr_detail temporary table with race demographics'
UPDATE #enr_detail
SET #enr_detail.race = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'R'
GO
PRINT 'Updating the #enr_detail temporary table with gender demographics'
UPDATE #enr_detail
SET #enr_detail.gender = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'G'
GO
PRINT 'Updating the #enr_detail temporary table with occupation demographics'
UPDATE #enr_detail
SET #enr_detail.occupation = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'O'
GO
PRINT 'Updating the #enr_detail temporary table with salary demographics'
UPDATE #enr_detail
SET #enr_detail.salary = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
--AND #demo.prefix = 'S'
AND #demo.parent IN (
1
,205
,305
,405
,1087
)
GO
PRINT 'Updating the #enr_detail temporary table with language demographics'
UPDATE #enr_detail
SET #enr_detail.LANGUAGE = #demo.demo_desc
FROM #demo
WHERE #enr_detail.enrollment_id = #demo.enrollment_id
AND #demo.prefix = 'L'
GO
PRINT 'Splitting addresses into multiple lines'
SELECT #enr_detail.enrollment_id
,#enr_detail.address
,charindex('\', #enr_detail.address) AS post_line1
,charindex('\', #enr_detail.address, charindex('\', #enr_detail.address) + 1) AS post_line2
,post_line3 = CASE
WHEN charindex('\', #enr_detail.address, charindex('\', #enr_detail.address) + 1) = 0
THEN 0
ELSE charindex('\', #enr_detail.address, charindex('\', #enr_detail.address, charindex('\', #enr_detail.address) + 1) + 1)
END
INTO #addr_split
FROM #enr_detail
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 1'
UPDATE #enr_detail
SET #enr_detail.post_addr1 = substring(#enr_detail.address, 1, #addr_split.post_line1 - 1)
FROM #addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 2'
UPDATE #enr_detail
SET #enr_detail.post_addr2 = substring(#enr_detail.address, #addr_split.post_line1 + 1, (#addr_split.post_line2 - #addr_split.post_line1) - 1)
FROM #addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line2 <> 0
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 3'
UPDATE #enr_detail
SET #enr_detail.post_addr3 = substring(#enr_detail.address, #addr_split.post_line2 + 1, (len(#enr_detail.address) - #addr_split.post_line2) - 1)
FROM #addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line3 <> 0
GO
PRINT 'Updating the #enr_detail temporary table with postal address line 4'
UPDATE #enr_detail
SET #enr_detail.post_addr4 = #enr_detail.city
FROM #enr_detail
,#addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line3 <> 0
GO
PRINT 'Updating the #enr_detail temporary table with postal city on line 3'
UPDATE #enr_detail
SET #enr_detail.post_addr3 = #enr_detail.city
FROM #enr_detail
,#addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line2 <> 0
AND #addr_split.post_line3 = 0
GO
PRINT 'Updating the #enr_detail temporary table with postal city on line 2'
UPDATE #enr_detail
SET #enr_detail.post_addr2 = #enr_detail.city
FROM #enr_detail
,#addr_split
WHERE #enr_detail.enrollment_id = #addr_split.enrollment_id
AND #addr_split.post_line2 = 0
AND #addr_split.post_line3 = 0
GO
PRINT 'Updating the #enr_detail temporary table with provinces'
UPDATE #enr_detail
SET #enr_detail.province = postal_code_ranges.province
FROM postal_code_ranges
WHERE #enr_detail.post_postal_code >= postal_code_ranges.range_start
AND #enr_detail.post_postal_code <= postal_code_ranges.range_end
AND #enr_detail.country_name = 'South Africa' ---Added 70aug 2006
GO
PRINT 'Updating the #enr_detail temporary table with company namd and code'
UPDATE #enr_detail
SET #enr_detail.co_code = company.code
,#enr_detail.co_name = company.NAME
FROM company
WHERE #enr_detail.company_id = company.company_id
GO
PRINT 'Updating the #enr_detail temporary table with company region'
UPDATE #enr_detail
SET #enr_detail.co_region = company_region.nme
FROM company_region
WHERE #enr_detail.company_region_id = company_region.company_region_id
GO
PRINT 'Updating the #enr_detail temporary table with company branch'
UPDATE #enr_detail
SET #enr_detail.co_branch = company_branch.nme
FROM company_branch
WHERE #enr_detail.company_branch_id = company_branch.company_branch_id
GO
/* Registration details */
PRINT 'Creating the #reg_detail temporary table from #enr_detail'
SELECT #enr_detail.*
,registration.registration_id
,registration.STATUS AS reg_status
,reg_status_def = CASE registration.STATUS
WHEN 1
THEN 'Active'
WHEN 2
THEN 'Complete'
WHEN 3
THEN 'Certificated'
WHEN 4
THEN 'Pending'
WHEN 5
THEN 'Cancelled'
WHEN 6
THEN 'Transferred'
END
,course.course_id
,course.code AS crs_code
,course.full_name AS crs_desc
,course.version AS crs_version
,school.code AS crs_school_code
,school.short_name AS crs_school
,course.type AS crs_type
,crs_type_def = CASE course.type
WHEN 1
THEN 'Non exam'
WHEN 2
THEN 'Internal exam'
WHEN 3
THEN 'External exam'
END
,qualification_type = CASE course.qualification_type
WHEN 0
THEN 'Not set'
WHEN 1
THEN 'Certificate'
WHEN 2
THEN 'Higher certificate'
WHEN 3
THEN 'Adv certificate'
WHEN 4
THEN 'Diploma'
WHEN 5
THEN 'Advanced diploma'
WHEN 6
THEN 'Subject completion cert'
WHEN 7
THEN 'Further diploma'
WHEN 8
THEN 'Prep certificate'
WHEN 9
THEN 'Prep diploma'
WHEN 10
THEN 'Higher diploma'
WHEN 11
THEN 'Advanced certificate'
WHEN 12
THEN 'External outcome'
WHEN 13
THEN 'Short course certificate'
WHEN 14
THEN 'Foundation certificate'
END
,course.qualification_name
,registration.lst_contacted_date
,registration.lst_submitted_date
,registration.submitted_percent
,registration.at_risk
,registration.course_completed_date
INTO #reg_detail
FROM rgstrtn_enrollment
,registration
,#enr_detail
,course
,school
WHERE #enr_detail.enrollment_id = rgstrtn_enrollment.enrollment_id
AND rgstrtn_enrollment.registration_id = registration.registration_id
AND registration.course_id = course.course_id
AND course.school_id = school.school_id
GO
/* Subject details */
PRINT 'Creating the #subj_detail temporary table from #reg_detail'
SELECT #reg_detail.*
,rgstration_subject.rgstrtn_subject_id
,subject.subject_id
,subject.code AS subj_code
,subject.full_name AS subj_desc
,subject.version AS subj_version
,subj_exam_type = CASE subject.exam_type
WHEN 1
THEN 'Non exam'
WHEN 2
THEN 'Internal exam'
WHEN 3
THEN 'External exam'
END
INTO #subj_detail
FROM #reg_detail
,rgstration_subject
,subject
WHERE #reg_detail.registration_id = rgstration_subject.registration_id
AND rgstration_subject.subject_id = subject.subject_id
GO
PRINT 'Creating index idx_subj_detail on the #subj_detail temporary table'
CREATE INDEX idx_subj_detail ON #subj_detail (
registration_id
,subject_id
)
GO
/* Assignment Detail*/
PRINT 'Creating #assg_detail temporary table'
SELECT #subj_detail.*
,assignment_case.assignment_case_id
,assignment.assignment_id
,assignment.code AS assg_code
,assignment_case.mark
,assg_case_status = CASE
WHEN assignment_case.STATUS = 1
THEN 'Open'
WHEN assignment_case.STATUS = 2
THEN 'Accepted'
WHEN assignment_case.STATUS = 3
THEN 'Rejected'
WHEN assignment_case.STATUS = 4
THEN 'Allocated'
WHEN assignment_case.STATUS = 5
THEN 'Sent to tutor'
WHEN assignment_case.STATUS = 6
THEN 'Passed'
WHEN assignment_case.STATUS = 7
THEN 'Failed'
WHEN assignment_case.STATUS = 8
THEN 'Ptp not met'
WHEN assignment_case.STATUS = 9
THEN 'Withheld'
WHEN assignment_case.STATUS = 12
THEN 'Resolved'
WHEN assignment_case.STATUS = 99
THEN 'Credit'
WHEN assignment_case.STATUS = 100
THEN 'Closed'
END
,assignment_case.STATUS
,assignment_case.date_created
,assignment_case.last_action_date
,(cast(#subj_detail.enrollment_id AS VARCHAR(20)) + cast(#subj_detail.registration_id AS VARCHAR(20)) + cast(assignment.assignment_id AS VARCHAR(20))) AS assg_key
INTO #assg_detail
FROM assignment_case
,assignment
,#subj_detail
WHERE #subj_detail.rgstrtn_subject_id = assignment_case.rgstrtn_subject_id
AND assignment_case.assignment_id = assignment.assignment_id
GO
PRINT 'Creating index idx_subj_detail on the #subj_detail temporary table'
SELECT #subj_detail.*
,assignment.assignment_id
,assignment.code AS assg_code
INTO #assg_dispatch
FROM #subj_detail
,dispatch
,dspatch_assignment
,assignment
WHERE #subj_detail.subject_id = dispatch.subject_id
AND dispatch.dispatch_id = dspatch_assignment.dispatch_id
AND dspatch_assignment.assignment_id = assignment.assignment_id
GO
PRINT 'Dropping StudentMaster table if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'StudentMaster'
)
BEGIN
DROP TABLE StudentMaster
END
GO
PRINT 'Populating StudentMaster table'
SELECT *
INTO StudentMaster
FROM #enr_detail
GO
PRINT 'Creating indexes on StudentMaster table'
CREATE INDEX idx_StudentMaster1 ON StudentMaster (enrollment_id)
GO
CREATE CLUSTERED INDEX idx_StudentMaster2 ON StudentMaster (enrllmnt_prcssd_dt)
GO
CREATE INDEX idx_StudentMaster3 ON StudentMaster (brand_id)
GO
CREATE INDEX idx_StudentMaster4 ON StudentMaster (acc_nr)
GO
CREATE INDEX idx_StudentMaster5 ON StudentMaster (contract)
GO
CREATE INDEX idx_StudentMaster6 ON StudentMaster (capture_date)
GO
CREATE INDEX idx_StudentMaster7 ON StudentMaster (captured_usr)
GO
CREATE INDEX idx_StudentMaster8 ON StudentMaster (enr_status)
GO
/* assignment enrollment update */
PRINT 'Creating #assg_passed temporary table'
SELECT #assg_detail.enrollment_id
,count(DISTINCT #assg_detail.assignment_id) AS tot_assg_passed
INTO #assg_passed
FROM #assg_detail
WHERE #assg_detail.STATUS = 6
GROUP BY #assg_detail.enrollment_id
GO
PRINT 'Creating #assg_submitted temporary table'
SELECT #assg_detail.enrollment_id
,max(#assg_detail.date_created) AS last_assg_date
,count(#assg_detail.assignment_id) AS tot_assg_submitted
INTO #assg_submitted
FROM #assg_detail
GROUP BY #assg_detail.enrollment_id
GO
PRINT 'Updating StudentMaster with assignments passed'
UPDATE StudentMaster
SET StudentMaster.tot_assg_passed = #assg_passed.tot_assg_passed
FROM #assg_passed
WHERE StudentMaster.enrollment_id = #assg_passed.enrollment_id
GO
PRINT 'Updating StudentMaster with assignments submitted'
UPDATE StudentMaster
SET StudentMaster.tot_assg_submitted = #assg_submitted.tot_assg_submitted
,StudentMaster.last_assg_date = #assg_submitted.last_assg_date
FROM #assg_submitted
WHERE StudentMaster.enrollment_id = #assg_submitted.enrollment_id
GO
PRINT 'Creating #tot_assg_dispatch temporary table'
SELECT #assg_dispatch.enrollment_id
,count(#assg_dispatch.assg_code) tot_assg_dispatch
INTO #tot_assg_dispatch
FROM #assg_dispatch
GROUP BY #assg_dispatch.enrollment_id
GO
PRINT 'Creating idx_#assg_dsp1 index on #tot_assg_dispatch temporary table'
CREATE INDEX idx_#assg_dsp1 ON #tot_assg_dispatch (enrollment_id)
GO
PRINT 'Updating StudentMaster with total assignments dispatched'
UPDATE StudentMaster
SET StudentMaster.tot_assg_dispatch = #tot_assg_dispatch.tot_assg_dispatch
FROM #tot_assg_dispatch
WHERE StudentMaster.enrollment_id = #tot_assg_dispatch.enrollment_id
GO
PRINT 'Updating StudentMaster with total assignments outstanding'
UPDATE StudentMaster
SET StudentMaster.tot_assg_outstanding = isnull((StudentMaster.tot_assg_dispatch - StudentMaster.tot_assg_passed), StudentMaster.tot_assg_dispatch)
FROM StudentMaster
GO
PRINT 'Drop the RegistrationMaster table if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'RegistrationMaster'
)
BEGIN
DROP TABLE RegistrationMaster
END
GO
PRINT 'Create the RegistrationMaster table from the #reg_detail temporary table'
SELECT *
INTO RegistrationMaster
FROM #reg_detail
GO
PRINT 'Create indexes on the RegistrationMaster table'
CREATE INDEX idx_RegistrationMaster1 ON RegistrationMaster (brand_id)
GO
CREATE CLUSTERED INDEX idx_RegistrationMaster2 ON RegistrationMaster (
enrollment_id
,registration_id
)
GO
CREATE INDEX idx_RegistrationMaster3 ON RegistrationMaster (registration_id)
GO
CREATE INDEX idx_RegistrationMaster4 ON RegistrationMaster (contract)
GO
CREATE INDEX idx_RegistrationMaster5 ON RegistrationMaster (crs_code)
GO
/* assignment registration update */
PRINT 'Create #assg_passed_reg temporary table'
SELECT #assg_detail.registration_id
,count(DISTINCT #assg_detail.assignment_id) AS tot_assg_passed
INTO #assg_passed_reg
FROM #assg_detail
WHERE #assg_detail.STATUS = 6
GROUP BY #assg_detail.registration_id
GO
PRINT 'Create #assg_submitted_reg temporary table'
SELECT #assg_detail.registration_id
,max(#assg_detail.date_created) AS last_assg_date
,count(#assg_detail.assignment_id) AS tot_assg_submitted
INTO #assg_submitted_reg
FROM #assg_detail
GROUP BY #assg_detail.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments passed'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_passed = #assg_passed_reg.tot_assg_passed
FROM #assg_passed_reg
WHERE RegistrationMaster.registration_id = #assg_passed_reg.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments submitted'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_submitted = #assg_submitted_reg.tot_assg_submitted
,RegistrationMaster.last_assg_date = #assg_submitted_reg.last_assg_date
FROM #assg_submitted_reg
WHERE RegistrationMaster.registration_id = #assg_submitted_reg.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments dispatched'
SELECT #assg_dispatch.registration_id
,count(#assg_dispatch.assg_code) tot_assg_dispatch
INTO #tot_assg_dispatch_reg
FROM #assg_dispatch
GROUP BY #assg_dispatch.registration_id
GO
CREATE INDEX idx_#assg_dsp2 ON #tot_assg_dispatch_reg (registration_id)
GO
PRINT 'Update RegistrationMaster table with total assignments dispatched'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_dispatch = #tot_assg_dispatch_reg.tot_assg_dispatch
FROM #tot_assg_dispatch_reg
WHERE RegistrationMaster.registration_id = #tot_assg_dispatch_reg.registration_id
GO
PRINT 'Update RegistrationMaster table with assignments outstanding'
UPDATE RegistrationMaster
SET RegistrationMaster.tot_assg_outstanding = isnull((RegistrationMaster.tot_assg_dispatch - RegistrationMaster.tot_assg_passed), RegistrationMaster.tot_assg_dispatch)
FROM RegistrationMaster
GO
PRINT 'Drop the RegSubjMaster table if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'RegSubjMaster'
)
BEGIN
DROP TABLE RegSubjMaster
END
GO
PRINT 'Populate the RegSubjMaster table from the #subj_detail temporary table'
SELECT *
INTO RegSubjMaster
FROM #subj_detail
GO
PRINT 'Create indexes on the RegSubjMaster table'
CREATE INDEX idx_RegSubjMaster1 ON RegSubjMaster (subject_id)
GO
CREATE INDEX idx_RegSubjMaster2 ON RegSubjMaster (registration_id)
GO
CREATE INDEX idx_RegSubjMaster3 ON RegSubjMaster (enrollment_id)
GO
CREATE INDEX idx_RegSubjMaster4 ON RegSubjMaster (contract)
GO
CREATE INDEX idx_RegSubjMaster5 ON RegSubjMaster (subj_code)
GO
CREATE INDEX idx_RegSubjMaster6 ON RegSubjMaster (
registration_id
,subject_id
)
GO
CREATE CLUSTERED INDEX idx_RegSubjMaster7 ON RegSubjMaster (enrllmnt_prcssd_dt)
GO
/* subject registration update */
PRINT 'Create #assg_passed_subj temporary table'
SELECT #assg_detail.registration_id
,#assg_detail.subject_id
,count(DISTINCT #assg_detail.assignment_id) AS tot_assg_passed
INTO #assg_passed_subj
FROM #assg_detail
WHERE #assg_detail.STATUS = 6
GROUP BY #assg_detail.registration_id
,#assg_detail.subject_id
GO
PRINT 'Create #assg_submitted_subj temporary table'
SELECT #assg_detail.registration_id
,#assg_detail.subject_id
,max(#assg_detail.date_created) AS last_assg_date
,count(#assg_detail.assignment_id) AS tot_assg_submitted
INTO #assg_submitted_subj
FROM #assg_detail
GROUP BY #assg_detail.registration_id
,#assg_detail.subject_id
GO
PRINT 'Update RegSubjMaster with total assignments passed'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_passed = #assg_passed_subj.tot_assg_passed
FROM #assg_passed_subj
WHERE RegSubjMaster.registration_id = #assg_passed_subj.registration_id
AND RegSubjMaster.subject_id = #assg_passed_subj.subject_id
GO
PRINT 'Update RegSubjMaster with total assignments submitted'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_submitted = #assg_submitted_subj.tot_assg_submitted
,RegSubjMaster.last_assg_date = #assg_submitted_subj.last_assg_date
FROM #assg_submitted_subj
WHERE RegSubjMaster.registration_id = #assg_submitted_subj.registration_id
AND RegSubjMaster.subject_id = #assg_submitted_subj.subject_id
GO
PRINT 'Create #tot_assg_dispatch_subj temporary table'
SELECT #assg_dispatch.registration_id
,#assg_dispatch.subject_id
,count(#assg_dispatch.assg_code) tot_assg_dispatch
INTO #tot_assg_dispatch_subj
FROM #assg_dispatch
GROUP BY #assg_dispatch.registration_id
,#assg_dispatch.subject_id
GO
CREATE INDEX idx_#assg_dsp3 ON #tot_assg_dispatch_subj (
registration_id
,subject_id
)
GO
PRINT 'Update RegSubjMaster with total assignments dispatched'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_dispatch = #tot_assg_dispatch_subj.tot_assg_dispatch
FROM #tot_assg_dispatch_subj
WHERE RegSubjMaster.registration_id = #tot_assg_dispatch_subj.registration_id
AND RegSubjMaster.subject_id = #tot_assg_dispatch_subj.subject_id
GO
PRINT 'Update RegSubjMaster with total assignments outstanding'
UPDATE RegSubjMaster
SET RegSubjMaster.tot_assg_outstanding = isnull((RegSubjMaster.tot_assg_dispatch - RegSubjMaster.tot_assg_passed), RegSubjMaster.tot_assg_dispatch)
FROM RegSubjMaster
GO
PRINT 'Drop table AssgDispatchMaster if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'AssgDispatchMaster'
)
BEGIN
DROP TABLE AssgDispatchMaster
END
GO
PRINT 'Populate the AssgDispatchMaster table'
SELECT student_nr
,enrollment_id
,title
,first_name
,last_name
,post_addr1
,post_addr2
,post_addr3
,post_addr4
,post_postal_code
,province
,home_area_code
,home_phone
,work_area_code
,work_phone
,cell_phone
,enrllmnt_prcssd_dt
,contract_end_date
,contract
,acc_month
,acc_year
,enrollment_status
,enr_status
,id_number
,product_id
,prod_code
,prod_desc
,prod_version
,prod_school
,acc_nr
,tracking_number
,bal_tot
,r_arrears
,brand_id
,registration_id
,reg_status
,reg_status_def
,course_id
,crs_code
,crs_desc
,crs_version
,crs_school_code
,crs_school
,crs_type
,crs_type_def
,rgstrtn_subject_id
,subject_id
,subj_code
,subj_desc
,subj_version
,subj_exam_type
,assignment_id
,assg_code
,brand_name
INTO AssgDispatchMaster
FROM #assg_dispatch
GO
PRINT 'Create indexes on the AssgDispatchMaster table'
CREATE INDEX idx_AssgDispatchMaster1 ON AssgDispatchMaster (enrollment_id)
GO
CREATE INDEX idx_AssgDispatchMaster2 ON AssgDispatchMaster (registration_id)
GO
CREATE INDEX idx_AssgDispatchMaster3 ON AssgDispatchMaster (subject_id)
GO
CREATE INDEX idx_AssgDispatchMaster4 ON AssgDispatchMaster (assg_code)
GO
PRINT 'Drop table RegAssgMaster if it exists'
IF EXISTS (
SELECT 1
FROM sysobjects
WHERE type = 'U'
AND NAME = 'RegAssgMaster'
)
BEGIN
DROP TABLE RegAssgMaster
END
GO
PRINT 'Populate table RegAssgMaster'
SELECT student_nr
,enrollment_id
,title
,first_name
,last_name
,post_addr1
,post_addr2
,post_addr3
,post_addr4
,post_postal_code
,province
,home_area_code
,home_phone
,work_area_code
,work_phone
,cell_phone
,enrllmnt_prcssd_dt
,contract_end_date
,contract
,acc_month
,acc_year
,enrollment_status
,enr_status
,id_number
,product_id
,prod_code
,prod_desc
,prod_version
,prod_school
,acc_nr
,tracking_number
,bal_tot
,r_arrears
,brand_id
,brand_name
,registration_id
,reg_status
,reg_status_def
,course_id
,crs_code
,crs_desc
,crs_version
,crs_school_code
,crs_school
,crs_type
,crs_type_def
,rgstrtn_subject_id
,subject_id
,subj_code
,subj_desc
,subj_version
,subj_exam_type
,assignment_case_id
,assignment_id
,assg_code
,mark
,isnull(assg_case_status, 'Unknown') AS assg_case_status
,STATUS AS assg_status
,date_created
,last_action_date
INTO RegAssgMaster
FROM #assg_detail
GO
PRINT 'Create indexes on table RegAssgMaster'
CREATE INDEX idx_RegAssgMaster ON RegAssgMaster (enrollment_id)
GO
CREATE INDEX idx_RegAssgMaster1 ON RegAssgMaster (
registration_id
,subject_id
,assignment_id
)
GO
CREATE INDEX idx_RegAssgMaster2 ON RegAssgMaster (
registration_id
,subject_id
)
GO
CREATE INDEX idx_RegAssgMaster3 ON RegAssgMaster (
brand_name
,assg_code
)
GO
Again, this parses fine. How are you executing this? (through ssms, data source in SSRS, other method?) There is nothing wrong with this SQL and it should run fine, or give a different error, when run in SSMS.
Jared
CE - Microsoft
June 11, 2012 at 6:08 am
Hey Jared
I'm using ssms
It worked this time around and still don't know why it didn't work.
I kind of said to myself, its one of those things when they just do go right. I've been called called by my CEO to explain what was going on because users were waiting for reports.
Many Thanks to you guys.
I'm still learning and I won't stop
June 11, 2012 at 7:26 am
Bulelani M (6/11/2012)
Hey JaredI'm using ssms
It worked this time around and still don't know why it didn't work.
I kind of said to myself, its one of those things when they just do go right. I've been called called by my CEO to explain what was going on because users were waiting for reports.
Many Thanks to you guys.
I'm still learning and I won't stop
Good to hear it is working. It is strange that it did not work with the same script, so something must have changed. Let me ask, are you simply opening a script from a file and running it when it needs to be run? Like, how and why is this script run. You should be able to put it into a stored procedure and schedule it, or at least have the stored procedure so that there is no possibility of the script getting errors by opening a file or pasting the code.
Jared
CE - Microsoft
June 11, 2012 at 7:55 am
This script is on a batch file that is scheduled to run early in the morning before business hours start. It is used to aggregate certain tables for reporting purposes and once it fails, no one can run any reports. It sits on a data warehouse db and there are many other master tables that depend on it, so if it fails, all the other masters fail too.
June 11, 2012 at 8:10 am
Bulelani M (6/11/2012)
This script is on a batch file that is scheduled to run early in the morning before business hours start. It is used to aggregate certain tables for reporting purposes and once it fails, no one can run any reports. It sits on a data warehouse db and there are many other master tables that depend on it, so if it fails, all the other masters fail too.
Hmm... I would think it better to wrap the script into 1 or more stored procedures and schedule it as an SQL Agent Job. Personally, I would divide up the script into separate stored procedures and call each one in a separate step. This way, I know which one failed and can fix it much quicker.
Jared
CE - Microsoft
June 11, 2012 at 8:22 am
Thanks J
I'll use your advice and see how it goes, we've been having problems with these scripts for some time now. I feel like changing everything on this database, the legacy here is quite not a good one and its giving us hard time to do things.
Regards
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply