Untitled

USE [Parikshak8]
GO

/****** Object:  StoredProcedure [schema_HRM3623ER].[SP_prob4_Q1]    Script Date: 19-03-2024 14:30:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER procedure [schema_HRM3623ER].[SP_prob4_Q1] 
 AS
 /*
 Refer tables Sales_2, product_details, Users.
 
 The tables provided to you has your username prefixed to the table name.
 For example, if your SQL Server username is HRM1746EV, the name of the tables provided to you will be: H_R_M_1_7_4_6_E_V_Sales_2, 
 H_R_M_1_7_4_6_E_V_product_details, 
 H_R_M_1_7_4_6_E_V_Users
 
 Create a view to display the list of users who has spent total amount of more than 2000. 
 Check the details only for users with user_id consisting of 3 or 4 digits and the quantity ordered should be more than 20.
 Display Username - where first name will be as it is and last name all should be in capital letters, 
 Email address  - which should be displayed without the domain name, but with @, and also without the number appearing after the
 name in the email address, 
 Salesorderid, User id, Order qty, Product id, total amount. 
 Also, display the count of products each users purchased per day from the above obtained resultset.
 Note: The count calculated is not from the entire data of the tables. First, the result should be calculated based on the mentioned condtions
 and then the count 
 is to be calculated on that resultset.
 Create a non-clustered index named idx_Sales_userid on User_id column in Sales_2 table.
 Few output sample records are shown below.
                                                                          
 user_name		 Emailid	 Salesorderid    User_id	OrderQty  ProductID		Total_amount	Product_count
 Alexander BERGER alexander@	  53483		 		463		 21			876				2520			1
 Richard BREADY	 richard@	  51089		 		517		 33			869				2309.67			1
 Richard BREADY	 richard@	  55241		 		517		 33			867				2309.67			1
 Richard BREADY	 richard@	  67266		 		517		 41			869				2869.59			2
 Richard BREADY	 richard@	  67266		 		517		 38			867				2659.62			2
 Ryan CALAFATO	 ryan@		  46616		 		591		 21			760				16442.79		1
 
  Please keep object name as follows:
 View -> vw_user_data
 Index Name -> idx_Sales_userid
 
 Sample Output :- 
 (Please keep same column names as above)
 */
GO

create or alter view vw_user_data AS
SELECT 
    CONCAT(LEFT(u.user_name, CHARINDEX(' ', u.user_name)), UPPER(RIGHT(u.user_name, LEN(u.user_name) - CHARINDEX(' ', u.user_name)))) AS user_name,
    LEFT(u.emailaddress, CHARINDEX('@', u.emailaddress) - 1) + '@'  AS Emailid,
    s.Salesorderid,
    s.user_id,
    s.Orderqty,
    s.ProductID,
    s.Orderqty * p.Listprice AS Total_amount,
    COUNT(s.ProductID) OVER(PARTITION BY s.user_id, CONVERT(date, s.orderDate)) AS Product_count
FROM 
    product_details p
JOIN 
    Sales_2 s ON p.ProductID = s.ProductID
JOIN 
    Users u ON s.user_id = u.User_id
WHERE 
    LEN(u.User_id) BETWEEN 3 AND 4
    AND s.Orderqty > 20
	AND (s.orderqty*p.Listprice) > 2000
GROUP BY 
    u.user_name,
    u.emailaddress,
    s.Salesorderid,
    s.user_id,
    s.Orderqty,
    s.ProductID,
    p.Listprice,
    s.orderDate;
GO 
DROP INDEX IF EXISTS idxSalesUserid ON Sales_2 
GO
CREATE NONCLUSTERED INDEX idxSalesUserid ON Sales_2 (User_id)