FishKiller
IS-IT--Management
CR 9 PRO, SQL 2K USING STATIC TABLE.
GOAL: I need to be able to indicate what percent of procedures were actually completed under 4 hours.
1st step: Create formula for manual cross tab to include total procedures performed during the same day. (This works)
After creating this formula I added it to the crosstab and had it do a sum to return the correct answer.
@TotalProcedureCount
2nd step: Create formula for manual cross tab to include total procedures performed during the same day which have been completed under 4 hours.
After creating this formula also added it to the crosstab and had it do a sum to return the correct answer. (This also works, if you need the code for @totalhours see below)
@TA_Under
This is where I am stuck. I cannot figure how to get the average between @TA_Under and @TotalProcedureCount to show in crosstab.
ANY AND ALL HELP IS ALWAYS APPRICIATED
@TOTALHOURS
GOAL: I need to be able to indicate what percent of procedures were actually completed under 4 hours.
1st step: Create formula for manual cross tab to include total procedures performed during the same day. (This works)
After creating this formula I added it to the crosstab and had it do a sum to return the correct answer.
@TotalProcedureCount
Code:
whileprintingrecords;
if({NDC_TA_Ended_Tech.ACCESSION_NUMBER})<>previous({NDC_TA_Ended_Tech.ACCESSION_NUMBER})
then
1
else
0
2nd step: Create formula for manual cross tab to include total procedures performed during the same day which have been completed under 4 hours.
After creating this formula also added it to the crosstab and had it do a sum to return the correct answer. (This also works, if you need the code for @totalhours see below)
@TA_Under
Code:
whileprintingrecords;
if({NDC_TA_Ended_Tech.ACCESSION_NUMBER})<>previous({NDC_TA_Ended_Tech.ACCESSION_NUMBER}) and ({@totalhours}) < 4
then
1
else
0
This is where I am stuck. I cannot figure how to get the average between @TA_Under and @TotalProcedureCount to show in crosstab.
ANY AND ALL HELP IS ALWAYS APPRICIATED
@TOTALHOURS
Code:
dateVar StartDate := DateValue ({NDC_Turn_Around.PROC_END_DTTM});
timeVar StartTime := TimeValue ({NDC_Turn_Around.PROC_END_DTTM});
dateTimeVar StartDate1 := DateTime (StartDate, StartTime);
dateTimeVar EndDate := {NDC_Turn_Around.PRINT_DTTM};
stringVar array Holidays := ["07/04/05", "09/05/05", "11/24/05", "11/25/05", "12/26/05"];
numberVar DayNum;
numberVar i;
dateVar range StartToEnd := Date(StartDate1) to Date(EndDate);
If Date(StartDate1) = Date(EndDate) Then
DateDiff ("s", StartDate1, EndDate) / 3600
Else (
DayNum := DateDiff ("d", StartDate1, EndDate) - DateDiff ("ww", StartDate1, EndDate, crSaturday) - DateDiff ("ww", StartDate1, EndDate, crSunday);
For i := 1 To Count (Holidays) Do (
If DateValue (Holidays[i]) In StartToEnd Then
DayNum := DayNum - 1
);
If (Hour (StartDate1) < 18) And (Hour (EndDate) < 8) Then
(DayNum - 1) * 12 + DateDiff ("s", StartDate1, DateTime (Date(StartDate1), Time (18, 00, 00))) / 3600
Else If (Hour (StartDate1) < 18) And (Hour (EndDate) >= 8) Then
(DayNum - 1) * 12 + (DateDiff ("s", StartDate1, DateTime (Date(StartDate1), Time (18, 00, 00))) + DateDiff ("s", DateTime (Date(EndDate), Time (08, 00, 00)), EndDate)) / 3600
Else If (Hour (StartDate1) >= 18) And (Hour (EndDate) < 8) Then
(DayNum - 1) * 12
Else If (Hour (StartDate1) >= 18) And (Hour (EndDate) >= 8) Then
(DayNum - 1) * 12 + DateDiff ("s", DateTime (Date(EndDate), Time (08, 00, 00)), EndDate) / 3600
)