Average from two results in crosstab 1

[FishKiller]

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

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

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

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
)

 

[lbass]

Your post is confusing as it references a "manual" crosstab and yet sounds like you are using an inserted one. Also your first two formulas would not be available in an inserted crosstab or for inserted summaries in a manual crosstab, so I wonder whether you added on the "whileprintingrecords" just for the post--it is unnecessary and would conflict with your ability to create the crosstab. Please clarify.

-LB

 

[FishKiller]

LB-

Sorry for the confusion and thank you. I always assumed a manual crosstab was a crosstab which you inserted formulas instead of actual fields. You know what they say about assume. I actually have the "whileprintingrecords" in the formula and have them inserted into the crosstab. I am not actually sure what the "whileprintingrecords" means or does however, every post I read has this command in it so I always start my formulas with this command.

- Fish

 

[lbass]

Well, I just tested this in xi and a crosstab will accept a whileprintingrecords formula, and to my surprise, even accepts a formula containing "previous()", but not if it references a variable.

A manual crosstab is when you don't use an inserted crosstab, but instead use formulas to create a horizontal display based on a particular field.

I think I can help you with the current problem, but need to know your row field(s) and column field(s). I assume your first two formulas are your current summary fields.

-LB

 

[FishKiller]

I looked at some of your past post and found one that indicates the same. There was a clue about using "Weighted Averages". I did this and changed the formula for @TA_UNDER from [... then 1 else 0] to read [... then 100 else 0] and added this to the cross tab changing the summary to Weighted Average based on @TotalProcedureCount. This now returns the correct percentage. Kind of sloppy but it works.

Do you know of any good links that describe the command "Whileprintingrecords" or can you briefly indicate to me when you are supposed to utilize this field?

I am going to try to experiement with Manual Cross Tabs now that I think I understand what they are. If I am tracking you correctly from you past post I want to create the manual crosstab from the desired level of grouping. Surpressing Header and Detail placing the formulas in a horizontal fashion along the group footer. Does this sound right?

-Fish

 

[lbass]

There is a solution that uses weighted averages, but I don't recognize the changing of 1 to 100 as anything I would have suggested.

Try reading the Help section for CR--it's really full of useful information and has a section on whileprintingrecords, whilereadingrecords, etc.

For a manual crosstab, you would insert a group on the field you would have used as your row field in an inserted crosstab. Then you create detail level formulas that are specific to an instance of what would have been your column field. If you column field was {table.carbrand}, then one of your detail level formulas might have looked like:

if {table.carbrand} = "Toyota" then 1 //or {table.amt}

Repeat for "Honda," "Chevrolet," etc. Then you would insert a sum on these formulas, drag the groupname into the groupfooter and suppress the detail section and group header section.

-LB

 

[FishKiller]

Thanks for all your help LB. You did not recommend the 1 to 100 I changed this to give an actual percentage reading. The sloppy comment was directed towards my lack of programming skills.

The instructions you gave for creating manual cross tabs are AWESOME and SIMPLE. This works better than a regular crosstab.


- Fish