Retrieval of distinct records from duplicated records

[jat139]

Hi there,

I'm getting duplicate records in my report due to a left join which is fine, however, I can't
figure out a formula to return the distinct records.

Here is the SQL for those interested:

SELECT "TransHeaders"."ID", "TransHeaders"."TransNo", "TransHeaders"."Branch",
"TransHeaders"."Station", "TransHeaders"."Logged",
TransHeaders.TransStatus, "B_Users"."LoginName", TransLines.IsVoided, TransLines.Line,
TransLines.SubAfterTax, "EventLog"."Logged", EventLog."Operation"


FROM "AKPOS"."dbo"."B_Users" "B_Users"
LEFT OUTER JOIN "AKPOS"."dbo"."TransHeaders" "TransHeaders"
ON ("B_Users"."LoginName"="TransHeaders"."TheUser" AND (Transheaders.Logged>={?StartDate}AND Transheaders.Logged<={?EndDate}))

LEFT OUTER JOIN "AKPOS"."dbo"."TransLines" "TransLines"
ON (TransLines.TransNo=TransHeaders.TransNo AND TransLines.Branch=TransHeaders.Branch AND TransLines.Station=TransHeaders.Station)

LEFT OUTER JOIN "AKPOS"."dbo"."EventLog" "EventLog"
ON (EventLog.TheUser=B_Users.LoginName AND (EventLog.Logged>={?StartDate}AND EventLog.Logged<={?EndDate}))


Effectively my report is returning something similar to the below:
LoginName ... TransID ... LineNo. ... EventLog(Operation)
..... 111 .......... 987 ........... 1 ............ 6/4/15 (ID=1)
..... 111 .......... 987 ........... 1 ............ 7/4/15 (ID=2)
..... 111 .......... 654 ........... 1 ............ 6/4/15 (ID=1)
..... 111 .......... 654 ........... 1 ............ 7/4/15 (ID=2)
..... 111 .......... 654 ........... 2 ............ 6/4/15 (ID=1)
..... 111 .......... 654 ........... 2 ............ 7/4/15 (etc.)
..... 111 .......... 654 ........... 3 ............ 6/4/15
..... 111 .......... 654 ........... 3 ............ 7/4/15

Where LineNo. is the line or item in a particular transaction and EventLog(Operation) is the date which which any logged events occurred.

What I'm trying to do is take each unique EventLog for each LoginName, which in the case above would be 2 events
occurring on the 6/4 and 7/4. Also I should add that I want to do a count for the number of distinct EventLog.Operation
per LoginName.

This is what I tried so far and I know it doesn't incorporate enough for it to work but I just can't figure out
how to achieve what I want:

/////HANDLES DUPLICATION OF RECORDS FOR EVENTLOG.OPERATION
/////****************************************************************
/////IE:For a user, each transaction line of every transaction is repeated
/////the number of times that an eventlog.operation occurred for the date period
If (isnull({OperatorReportCommand.ID}) AND isnull({OperatorReportCommand.Line})) then 0 else

If (({OperatorReportCommand.Line} = 1 OR isnull({OperatorReportCommand.Line}))
/////Reason for "isnull({OperatorReportCommand.Line})" is that if
/////a transaction has only 1 line then there is no line number associated
AND (PreviousIsNull ({OperatorReportCommand.Logged(1)}) OR
{OperatorReportCommand.Logged(1)} <> Previous ({OperatorReportCommand.Logged(1)})))
/////If the current datetime being tested doesn't equal the previous then continue
then 1 else 0

Hopefully someone smarter than I can help me!

Thanks

James

 

[kenhamady]

A formula can't 'return distinct records' so I hope what you mean is that you want to return the COUNT of the unique records. In Crystal this is called a distinct count. My guess is that you need to apply this operation to a formula field that combines 2 or 3 database columns.

For instance if you write a formula that concatenates EventLog and LoginName and then do a distinct count of that formula the result will be a total of 2 based on the data above. The formula would look something like this:

{Table.EventLog} & '-' & {Table.LogInName}

One other note. Left Outer Joins do not create duplicate records. Change the join type to inner and you will see. Duplicates are created by any join when it is between tables that have a one-to-many relationship.


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[jat139]

Wow thanks, some very interesting points you made.
I'm a newbie to CR/ SQL so I still have a lack of understanding/ misconceptions around many things.
I never thought to do a concatenation of those 2 fields to create a unique identifier for each EventLog, that's a very cool approach. And yes
I was hoping to do a distinct count of EventLog per user. --> This is what I meant when I said 'return distinct records' which I realise
now is not a correct statement.
Also the last point regarding the one->many joins relationship creating duplicates makes complete sense as well, obviously
I need to think more carefully about what I am stating.

Thanks

James

 

[Charliy]

One important thing to note. Ken's formula inserts a dash between the two fields, this is a vital piece of the puzzle.

A clear example of why you need to insert the separator is a problem I ran into using hospital data.
Withot a separator, patient 1231, episode 2 would be the same as patient 123, episode 12.
Always include a separator when you concatenate fields.

 

[jat139]

Ah, thats a valid point, thanks for your insight