Compare list for several other lists

Question

I was not lucky to find a solution to this, partly because I'm not quite sure how to best explain the problem! Hope I am clear here, but if not a request, please feel free to ask for more information.

At the top level: In our database, each client can have several services. Services are provided to the client individually. However, for reporting purposes, we need “group” services in “packages” of common service combinations.

I have a database containing package definitions - a table called packageServices that simply lists each package identifier with the associated ServiceID. Therefore, I can get a list of services for each package.

From the same database, I can get a list of ServiceIDs for a particular client.

What I'm trying to do is compare the list of ServiceIDs received for this client and see if this combination matches any of the specific pacakges (i.e. ServiceID combinations), and if so, tell which package (s).

I'm struggling to figure out where to start while the comparison is in progress! I guess I need to start by creating a kind of list of service identifiers for each package (instead of my current KeyValuePairs (in the dictionary) with several lines for each package?), Then iterate over the packages and compare these service lists with the list of services that the client has?

The data types used here may not be the most suitable - in many cases I mean a "logical" list, not a C # list object - I can use the data type that suits :)

Any help is much appreciated!

EDIT . It has been suggested that I collect this information in SQL instead of a C # application. This seems like a good idea, and one of the answers below brought me closer. However, there are “business rules” that determine whether a package is an “update” of a lower package or a package in itself, and this makes the situation very serious.

The more I try to make this work, the more I think I have bitten more than I can chew! I am trying to develop this correctly, so that any changes in services or packages in the future will be handled easily - these changes are unlikely, but possible. However, it would be much easier to just copy the parameters from what I have seen so far!

EDIT 2 . I worked on this, and it has been suggested that I assign a “weight” and a “group” for each package. Groups exist to ensure that the customer can only have one package from each group, and scales are there to ensure that only the "highest level" package is returned. Using this in conjunction with the @MarceloCantos query means that I should be able to return only the "highest" weight packet from each group - it looks like it can meet the requirements of the application!

Thanks to everyone who has helped so far - I am amazed at how quickly the answers started and the quality of the answers. I will give it and see how I am.

Answer 1

You can count on services for the client, and then, for each package with the same number of services, calculate the services that correspond to the customer services. Only an exact match will give the same score.

You can do this in a single request, albeit rather complicated:

SELECT CustomerID, PackageID FROM (SELECT CustomerID, COUNT(*) AS qty FROM customerServices GROUP BY CustomerID ) cs JOIN (SELECT PackageID, COUNT(*) AS qty FROM packageServices GROUP BY PackageID ) ps ON cs.qty = ps.qty WHERE (SELECT COUNT(*) FROM customerServices cs2 JOIN packageServices ps2 ON cs2.ServiceID = ps2.ServiceID WHERE cs2.CustomerID = cs.CustomerID AND ps2.PackageID = ps.PackageID ) = ps.qty 

EDIT: Perhaps I misunderstood the question. If you want to find packages that contain a subset of client services, you can do this instead:

  ... ) ps ON cs.qty >= ps.qty -- Change the test WHERE (SELECT COUNT(*) FROM customerServices cs2 JOIN packageServices ps2 ON cs2.ServiceID = ps2.ServiceID WHERE cs2.CustomerID = cs.CustomerID AND ps2.PackageID = ps.PackageID ) = ps.qty 

EDIT 2: I changed the logic of the second case to use the fact that if the intersection of two sets is the same size as the smaller set, then the smaller set is a subset of the larger one.

Answer 2

I would recommend that you not try to figure out the packages the client is subscribed to on the fly (the signed one is probably not correct, but you know what I mean). I suggest you keep separate records indicating packages that the client subscribes to in the database. every time a client adds a new service, you use a message that a service add event has occurred, and handle this event by updating packages for this client with any packages that are now completed by adding a new product.