Grouping and Distinct Counts 1

[bluraz]

Crystal XI - SQL 2005

I have several hundred invoice records and under each invoice record is a listing of deliveries in numerical order (1,2,3,etc). I need to pull the last delivery for each invoice, but ultimately I need to sort the report by delivery location (city). The final result is to be able to calculate the number of deliveries to each 'final' delivery location (city).

Sample data (as seen in the detail section):
Invoice City Stop
1234567 Chicago 1
1234567 Oswego 2
1234567 Pontiac 3 (last delivery)

Any advice on the best way to accomplish this?

 

[lbass]

Please show a sample that includes multiple invoices and then show a sample of the results you want to see.

-LB

 

[bluraz]

LB -

Here is the sample you requested

Sample data (as seen in the detail section):
Invoice City Stop
1234567 Chicago 1
1234567 Oswego 2
1234567 Pontiac 3 (last delivery)
8765432 Booneville 1
8765432 Corinth 2 (last delivery)
5214789 Newberry 1
5214789 Sumter 2 (last delivery)
6547895 Chicago 1
6547895 Oswego 2
6547895 Pontiac 3 (last delivery)
2145678 Booneville 1
2145678 Corinth 2 (last delivery)

Desired results:
Totals: City # of Deliveries
Pontiac 2
Corinth 2
Sumter 1

 

[lbass]

Create a SQL expression like this {%maxstop}:

(
select max(`stop`)
from table A
where A.`invoice` = table.`invoice`
)

Then enter a record selection formula like this:

{table.stop} = {%maxstop}

Then you should be able to insert a group on city and and insert a count at the group level to get the number of deliveries.

-LB

 

[bluraz]

I am not certain I know how to do what you are suggesting.

And I am not sure what you mean by...
A.`invoice` = table.`invoice`

 

[lbass]

Can you please post your SQL query here? Go to database->show SQL query and copy it.

-LB

 

[bluraz]

Here you go...

SELECT
FMTRIPHEADER.CREATIONDATETIME,
IDMASTER_Carrier.IDENTITYID,
IDMASTER_Consignee.IDENTITYID,
FMTRIPDETAIL.DELSTOPNO, --stop
FMTRIPHEADER.TRIPNO, --invoice number
IDADDRESS.CITY --city

FROM (((( FMTRIPHEADER INNER JOIN FMTRIPDETAIL ON FMTRIPHEADER.ROWID=FMTRIPDETAIL.R_TRIPHEADER)
INNER JOIN FMDISPATCHOBJECT ON FMTRIPDETAIL.R_FMDISPATCHOBJECT=FMDISPATCHOBJECT.ROWID)
INNER JOIN IDMASTER IDMASTER_Consignee ON FMTRIPDETAIL.R_DESTINATIONID=IDMASTER_Consignee.ROWID)
INNER JOIN IDADDRESS ON IDMASTER_Consignee.R_DEFAULTADDRESS=IDADDRESS.ROWID)
INNER JOIN IDMASTER IDMASTER_Carrier ON FMDISPATCHOBJECT.R_CARRIER=IDMASTER_Carrier.ROWID

WHERE FMTRIPHEADER.CREATIONDATETIME>={ts '2008-07-01 05:19:00'} AND
IDMASTER_Carrier.IDENTITYID='STAR'

 

[lbass]

Are you using any parameters in your record selection formula--for date and/or IdentityID?

-LB

 

[bluraz]

Not yet, but yes I will. I will probably add both Date Range and IdentityID parameters.

 

[lbass]

Also, are you using alias tables, e.g., are IDMASTER_consignee, and IDMASTER_carrier aliases?

-LB

 

[bluraz]

Yes. The IDMaster table holds all the codes / data for ID codes in the database. So rather than have IDMASTER and IDMASTER_1, I have just renamed the tables as such.

-A

 

[lbass]

Try creating a command (database->database expert->add command:

Select distinct
(
select max(`STOP`)
FROM FMTRIPHEADER A, FMTRIPDETAIL B, FMDISPATCHOBJECT C, IDMASTER D
WHERE A.`ROWID` = B.`R_TRIPHEADER` AND
B.`R_FMDISPATCHOBJECT` = C.`ROWID` AND
C.`R_CARRIER` = D.`ROWID` AND
A.`TRIPNO` = FMTRIPHEADER.`TRIPNO` AND
A.`CREATIONDATETIME` = {?DateRange} and
D.`IDENTITY_ID` = '{?Identity}'
) as MaxStop, FMTRIPHEADER.`TRIPNO`
From FMTRIPHEADER

Link the command to the FMTRIPHEADER table on the tripno and the date field, and also enforce the joins in the link options screen. This should limit the records to the most recent delivery per invoice.

-LB

 

[bluraz]

I have tried to do as you suggest and I am getting an error message:

incorrect syntax near '''

I tried changing a few things to figure out what is causing the error, without any luck.

Any ideas?

-A

 

[lbass]

Use whatever punctuation was used in the Show SQL Query area for tables and fields (but not the Alias tables A, B, C, and D. Your post showed no punctuation around tables or fields, so I don't know if none is required or if you removed it.

-LB