September 1, 2005 at 7:12 am
From a previous post:
We are collecting the department info.
Every department was set two electric power levels one High and the other low. Suppose if The electric power is beyond the higher level and it would be considered as 'pass' and if the electric power is below the lower level it should be considered as 'fail'.
Suppose if it became pass on day9, we should check status for the next 5 days and if its pass during the next 5 days it should be passed otherwise we dont mark anything on that. same with failure.. We need to check five consecutive days.
>
Based on your requirements, if the power level is between the low and high range, I have assummed that this is a pass.
Here is a set-based solution:
create table spr_department
( university_idintegernot null
, college_idintegernot null
, department_id integernot null
, grade_level1integernot null
, grade_level2integernot null
, constraint spr_department_P primary key
( university_id , college_id , department_id )
, constraint spr_department_C_grade_level2 CHECK
( grade_level2> 0 )
, constraint spr_department_C_grade_level_Higher CHECK
( grade_level1> grade_level2 )
)
go
create table spr_real_time_data
( university_idintegernot null
, college_idintegernot null
, department_id integernot null
, exam_datedatetime not null
, electric_power integernot null
, constraint spr_real_time_data_P primary key
( university_id , college_id , department_id , exam_date)
)
go
create table spr_grade_log
( university_idintegernot null
, college_idintegernot null
, department_id integernot null
, grade_timedatetime not null
, grade_condition char(4) not null
, constraint spr_grade_log_P primary key
( university_id , college_id , department_id , grade_time)
go
insert into spr_grade_log
( university_id
, college_id
, department_id
, grade_time
, grade_condition
)
selectuniversity_id , college_id , department_id , exam_date
,CASE CurrentStatus
WHEN 'FAIL' then 'FAIL'
ELSE
CASE FailureCnt
WHEN 0 then 'PASS'
ELSE 'FAIL'
END
END
FROM(
select spr_department.university_id
,spr_department.college_id
, spr_department.department_id
,spr_real_time_data.exam_date
,CASE WHEN spr_department.grade_level1> spr_real_time_data.electric_power
THEN 'FAIL' ELSE 'PASS' end
--Get count of failures over the next five days
,(SELECT COUNT(*)
FROM spr_real_time_data AS OtherDays
WHEREspr_department.university_id= OtherDays.university_id
andspr_department.college_id= OtherDays.college_id
and spr_department.department_id = OtherDays.department_id
andspr_department.grade_level1> OtherDays.electric_power
andOtherDays.exam_date
between spr_real_time_data.exam_date
andspr_real_time_data.exam_date + 5
)
FROM spr_department
JOINspr_real_time_data
on spr_department.university_id= spr_real_time_data.university_id
andspr_department.college_id= spr_real_time_data.college_id
and spr_department.department_id = spr_real_time_data.department_id
) DeptPowerFailures
( university_id , college_id , department_id , exam_date
, CurrentStatus , FailureCnt)
SQL = Scarcely Qualifies as a Language
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply