SQL Create MIN Source from one to many field

[spcc07]

Cr 8.5
SQL

This table is set up as a one to many. I need a SQL statement to get the First Source based on Minimum Source Date.
Person_ID
Source
Source Date

 

[Madawc]

You ought to be able to do it in Crystal. With a one-to-many link you will get several detail lines for the account or whatever. Group on the 'one' value, maybe suppress the detail lines and show values in the group header or footer. Use the sort option or a summary or a running total to get the value you want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[spcc07]

I am doing a running total that is counting all sources for that person instead of just the First Source.

 

[Madawc]

They can be made conditional, e.g. evaluate once per group.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[spcc07]

I am grouping on Source and the running total looks like this.

Field to Summarize:
If {CandidateStage.Stage} = "APPLIC"
then 1
else 0;

Evaluate:
Entry = Fall08

Reset:
On change of Group Source

 

[lbass]

Change your evaluation formula to:

(
onfirstrecord or
{table.personID} <> previous({table.personID})
) and
{table.entry} = 'Fall08'

I'm not sure you have the right reset, as I'm unclear about your overall design.

Make sure that you are using 'sum' as the summary, not 'count'.

-LB

 

[spcc07]

LBass,

What the report will look like when I am finished:

Sources Fall08
SAT 50
ACT 10

I want to group on the Sources so I see them once. Then sum all students who have that first source.

I am really trying to get to a SQL statement that will give me a person_id, Source and their First Inquiry_date.

 

[lbass]

Please show some sample data that captures the variation at the detail level and then the corresponding results youw would expect to see.

I'm not clear on how the source fits in. For a specific individual who has more than one source, do you only want to count the most recent instance of one source or of each source? Also, how does the term fit into this?

-LB

 

[Madawc]

You can write SQL and slot it into Crystal as a Stored Procedure. In Crystal 8.5 this is awkward, it cannot be used with the database except via a subreport, and you need the correct software to bridge the gap between SQL and Crystal. Later versions are easier.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[spcc07]

Details:
Person Source Source_Date Entry Term Stage
Student1 campusvisit 09/01/2007 FA2008 Applic
Student1 Lead 1 03/15/2007 FA2008 Applic
Student1 Material Req 03/15/2007 FA2008 Applic
Student2 Lead 1 04/16/2007 FA2007 Applic
Student2 College Fair 04/16/2007 FA2007 Applic
Student2 Athletic Visit 06/20/2007 FA2007 Applic
Student3 Inquiry Lead 03/14/2006 FA2006 Folbeg
Student3 Lead 1 03/14/2006 FA2006 Folbeg
Student3 Group Visit 03/14/2006 FA2006 Folbeg
Student4 Materials Req 05/21/2005 FA2005 Deposi

I am grouping on Source so I get all Sources listed once. Then set up running totals for each stage and year. I want to count all First Sources (by date) that are not Lead 1 or Inquiry Lead. I only want to count one (First)source per person.
Applic Folbeg Deposits
Sources 05 06 07 08 05 06 07 08 05 06 07 08
Ath Visit
Campus Vis
College Fair 1
Group Visit 1
Mat Req 1 1


There should be College Fair Applic 07 = 1
Group Visit Folbeg 06 = 1
Mat Reg 08 Applic = 1
Mat Req 05 DEPOSIT = 1

 

[lbass]

Go to the field explorer->SQL expression->new and name it "maxdate" and enter:

(
select max(A.`source_date`) from table A
where A.`person` = table.`person` and
A.`source` <> 'Lead 1' and
A.`source` <> 'Inquiry Lead'
)

Then go to report->selection formula->record and enter:

{%maxdate} = {table.source_date}

This will return one record per person. In the SQL expression, you will need to use the punctuation that your specific datasource uses, so if you are unsure, go to database->"Show SQL Query" in your current report and notice how it is done. Also you need to replace "table" with your table name. Leave "A" as is, as it represents an alias table name.

Then you should be able to insert a crosstab that uses person as the row, and stage and entry term as the columns, and distinct count of person as the summary field.

-LB

 

[spcc07]

LB
Thank you very much!

I need to add % columns to this report. Is there a way to do running totals? I need to be able to work with the numbers in the columns?

Why does Max work to get the minimum Source date?

 

[lbass]

Once you have the SQL expression in place in the record selection formula, you can use running totals instead of inserting a crosstab. I'm not sure what your percentages are of, but you could do this within the crosstab, although in 8.5 your display would show the percentage below the summary. Otherwise, you are fine doing a manual crosstab by using running totals. I'm not sure what your question is then.

I'm sorry, but that shouldn't have been max. Change the SQL expression to use "min" instead of "max".

-LB

 

[spcc07]

Grouped on Source
Running total
I have
Field to Summarize:
Person_ID
Evaluate:
Entry_Term = "FA2008"
Reset:
Group.Source

This is only bringing back 0 and 1's?

 

[lbass]

I don't know what you are expecting to see. What are you trying to summarize? What question do you want to answer with this calculation? Please also identify your group structure.

-LB

 

[spcc07]

Applic Folbeg Deposits
Sources 05 06 07 08 05 06 07 08 05 06 07 08
Ath Visit
Campus Vis
College Fair 1
Group Visit 1
Mat Req 1 1

Conversion %
05 06 07 08

First a running total that does what the Crosstab does. Then I need to be able to divide 05deposits by the number of 05Applic and so on. I just did not think that I could add this to the cross tab?

 

[lbass]

Insert a group on source, and then set up 12 running totals like this:

distinctcount personID
evaluate using a formula:

{table.term} = "FA2005" and
{table.stage} = "Applic"

Reset on change of {table.source}.

These should be placed in the group footer for {table.source} (drag the groupname there), and then suppress the GH and the detail section. Then you can create corresponding percentages (again, 12) using formulas like:

if {#05Applic} > 0 then
{#05Deposits} % {#05Applic}

Place these in the group footer and then click on the % icon in the toolbar.

-LB

 

[spcc07]

The running totals are still off.

I have grouped on Source and pulled the Group name into the Group Footer.

The running total:
Summary
distinctcount person_id from the same table as used in the maxdate SQL expression.

Evaluate
Term = "FA2008" and
Stage = "Applic"

On change of group
Source
On change of field
Source
neither works.

 

[spcc07]

Got it.

Thank you for your time!

 

[lbass]

Can't help if you don't explain in what way they are off. Are they inflated? Are you using multiple tables in the report? Please show some sample data at the detail level, ie., show what the fields return when placed in the detail section, after you have entered your groups. Do you have any other groups besides Source? Please answer all of these questions.

-LB