comparing two values from same field

[RepRider]

CR 8.5 and SQL Server

I would like to compare 2 different values from the same field in a view. The viewe is service_time and the field is service_code.

The table lists various services provided to customers but they may be provided by various providers sometimes at the same time. I need to be able to track down other service codes that may be provided to the same customer at the same time as a "350" service code that is being provided.

I plan a report that shows:
Customer_ID, begin_time, service_code ("350"), and provider_name (of the code "350")

Now I would like to add columns that show services that match the same customer_ID, begin_time of the service_code "350", but show a different service_code (whatever it is), the provider of the "whatever it is" service_code. Is this even possible?

I toyed with creating a copy of the view Service_Time that I am using and calling it Service_Time_Alias but I am not sure I am on the right track.... ?

Can anyone give me some direction or pointers of how to accomplish this?

Thank you for any assistance!

 

[synapsevampire]

Rather than a lengthy description, you'llfind posting the following simplifies requirements:

Example data: show fields involved and a few of their pseudo data elements
Expected output: based on the above data, what you require output

-k

 

[lbass]

I think adding an alias table makes sense. Link it to the first table on the datetime field and on the customer ID field. Then use a record selection formula like:

{servicetiime.servicecode} = 350 and
{servicetime_1.servicecode} <> 350

-LB