November 9, 2020 at 6:37 am
In a global telecom company, the customer service requests are captured from India, US and UK regions. The company serves around 5000 customers every day from these regions.
Company has a Customer Service Portal to serve two types of customers “Regular Customer” and “Priority Customer”. “Regular services” are given to all customers and “Priority services” are given only to Priority Customers.
SLA for closing a Priority customer request is 1 day and Regular customer is 5 days. But if a regular customer request doesn’t complete as per defined SLA, it moves up to Priority queue.
The customer service manager is supposed to maintain a report of all Customer service requests received based on Region and Zone. Each region is divided into 4 zones East, West, North and South. e.g. If a customer Region is India, it will have 4 Zones i.e. India East Zone, India West Zone, India North Zone and India South Zone.
General Instructions to Follow:
1. Use the server / Database allocated to you.
3. Ensure to create the required master and transaction tables for Data Retrieval and DML Operations.
4. Please ensure to follow proper naming convention and coding guidelines during implementation.
5. All the tables should be created with proper indexes on the relevant columns.
6. Table indexes should be named as "Tablename_Idx".
7. Create a script to insert records in tables. Ensure to insert 1 Lakh plus records in the transaction tables.
8. The query should be optimized for performance and faster data retrieval.
9. The use of cursors is not recommended for the solution.
10. Normalization should be followed in order to reduce data redundancy and improve data integrity.
Note: Following are few mandatory tables and additional tables can be created based on requirement.
1. RegionAndZone – Table has information on all Regions and respective Zones available.
2. Customer – Table stores customer details such as Customer_Id, Name, Customer_MobileNumber, Customer_Zone_Id etc.
3. Service – Table stores all available service codes (Service_Code), Service Details, Rates in USD and Priority_Flag as ‘Y’ to indicate priority service or 'N' for regular service.
4. CurrencyConversionRates – Table stores currency conversion rates from USD to local currency e.g. USD to INR and USD to Pound.
5. RequestStatus - Table has all defined status for a request. E.g. Open, InProgress, Closed, Failed.
6. Request – Table stores details like Request_Id, Service_Code, Customer_Id, Current_Status, Creation_Time, Resolution_Time etc.
Points to Note:
1. The identification of a customer is done via mobile number which they share during raising a service request.
2. In the customer table maintain a column to capture type of customer. For a Priority Customer set Priority_Flag value as 'Y' and for regular customer as 'N'.
3. Request id for priority customer should be prefixed with ‘PR’ and regular customer should be prefixed with ‘RE’.
4. The priority service code should be prefixed with ‘PS’ and regular service code should be prefixed with ‘RS’.
5. All service rates are stored in USD but the report should display in respective customer local currency. E.g. for an Indian customer it has to be shown in INR, for US in USD and for UK display in Pound (GBP).
6. When currency conversion rates are changed the local price for the service should be automatically updated. For conversion, (1 USD = 70 INR) and (1 USD = 0.75 GPB).
Expectations:
1. To generate report create a Stored Procedure named as "RS_CustomerServiceRequest(Request_Id, Month, IsPriority, Zone)". The SP should perform the following validations before displaying the data in the report. Ensure to display the error and warning messages wherever applicable.
a. Based on the given input value the report should display the records accordingly.
b. The user should provide at least one input parameter from Request_Id, Month, RegionZone to display the records in the report.
c. If the “request_id” is not provided and if only month is given, then it should return all records for the current year till given month.
d. The isPriority should be an optional parameter and expected behavior is as following:
o isPriority – true – priority requests only
o isPriority – false – regular requests only
o isPriority – Not provided – All requests
2. Report output should display the following information Request_Id, Service_Code, Customer_MobileNumber, Is_Escalated, Current_Status, SLA, Creation_Time, Resolution_Time, Customer_Region, Zone and Service_Amount based on the latest to oldest requests and based on Region and Zone.
3. Make sure report has Service_Amount only in local currency and not USD.
Test Scenario:
Few Test Scenario to validate your SP. Ensure the below set of values are present in your table to validate.
1. Services having service code as ‘PS009’ of amount USD $100 and Request_Id ‘PR710’ and Request_Id ‘PR810’ were placed by customers having mobile number ‘9830012345’ and ‘9230038482’ from India East Zone and India West Zone respectively.
2. It was observed that the Request_Id ‘PR810’ missed the SLA by 1 day, and this is informed to the management and it should be highlighted in report. It is found that on a day that out of 50 service requests at least 5 requests are escalated.
3. The Request_Id ‘PS009’ is completed on time and hence is marked as resolved.
4. Priority Service codes range is PS001 - PS020 and regular Service codes range is RS001 - RS100.
November 9, 2020 at 8:06 pm
If this is an interview assignment? A bit hefty in that case. Or is a class assignment, like the final one?
In any case, it sounds like you something you are expected to complete on your own, and not just ask others to do you for you.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
November 9, 2020 at 8:37 pm
homework - final big work I would say.
November 12, 2020 at 7:37 pm
Since this sounds like interview or homework (as others indicated), I think you are likely not to get 100% help. You are asking a lot and giving very little.
Starting with the very first requirement though, we (users on this forum trying to help you) hit a snag. It states "Use the server / Database allocated to you." We have no access to that. once I see that requirement, I stopped reading the rest as I know I will not be able to provide 100% help.
So I think the question that comes up is what part are you stuck on? If this is an interview question, I would advise looking for a different job. You can't run to this forum every time you hit a snag at work and hope someone here will have an answer for you. if this is homework, it is possible your professor is on this forum and watching for things like this and when it comes time for the tests, you may not have access to the internet to post questions and might get hit with a problem you can't solve.
But ignoring the fact this looks like homework type stuff, I have 2 questions for you:
1 - what part are you stuck on?
2 - what have you tried?
Knowing the answer to both of those will help us help you. For example, if you have tried nothing and came straight to the forum, my answers will be very vague such as for expectation 1, you are making a stored procedure that takes 4 inputs, does some data validation, and gives a single result set to be consumed by a reporting tool. The inputs are such that only 1 needs to be provided. My assumption is that this is SSRS, but it could be any reporting tool. To verify that an input was provided, you could use an IF statement (the approach I would take) and then the rest is just baked into the WHERE clause with some ISNULL. You MAY hit a snag if the report tool passes back an empty string instead of NULL, in which case you may need a few NULLIFs in there too.
Now, if you had some code for us to review/consume (DDL as we want to see the structures of everything), sample data, and expected output vs current output, we can review the code and offer advice.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply