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.

Question

1.00/5 (1 vote)

See more:

SQL-Server-2012

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 11-Jun-20 22:55pm

Anuj Kumar (kanhaiya dhakarey)

Updated 11-Jun-20 22:59pm

Add a Solution

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.

Anuj Kumar (kanhaiya dhakarey) 12-Jun-20 7:21am

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-06-11T22:59:00

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.