Click here to Skip to main content
14,640,651 members
Rate this:
Please Sign up or sign in to vote.
See more:
The client has a promotion that is highly customized to the customer group and state fields in the customer table. The client has asked us to create a testing SQL view from the CUSTOMER and PRODUCT tables that will capture 2 records from each possibility. (For example, if there are 3 records in the customer table that match GROUP A and STATE of IL, you should only show 2 of the 3 records for testing – but if a customer is in GROUP A and STATE of PA or GROUP B and STATE of IL, those additional records will also need to be captured in this SQL view.)

The SQL view generated should contain all of the fields from the CUSTOMER table and only the fields of Product Name and Product Desc from the PRODUCT table. If the customer is receiving multiple products, the same customer may appear in view multiple times as long as the final view created for the client doesn’t exceed the 2 records per GROUP and STATE as described above.
As if, that request wasn’t hard enough – the client sent over some bad data and would like us to clean it up.  If we encounter the STATE field as ‘YN’ we should rename it ‘NY’ within this view.

Assume that the records shown in our example on page 2 and 3 are just the first rows of the table.  There are more groups and states than are displayed in the sample.  The table has an upper-based functional index on CUSTOMER_ID, PRODUCT_ID, EMAIL_ADDRESS, GROUP and STATE

What I have tried:

I try to solve it by store procedure
Posted
Updated 11-Jun-20 22:59pm
Comments
Richard MacCutchan 12-Jun-20 4:58am
   
"Assume that the records shown in our example on page 2 and 3"
I think we can assume this is your college assignment.
   
For example, if there are 3 records in the customer table that match GROUP A and STATE of IL, you should only show 2 of the 3 records for testing – but if a customer is in GROUP A and STATE of PA or GROUP B and STATE of IL, those additional records will also need to be captured in this SQL view.)
Richard MacCutchan 12-Jun-20 9:14am
   
Yes you already stated that. But what actual code have you written, and what problems does it cause?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100