Views

View can be created to address requirements such as Reports, Account summary, Payment summary etc. Some of the views created in this project are shown below

Here are some views along with their explanation

Resolution

Issue Resolutions

As an organization, analyzing historical data related to issues reported by customer and how were they resolved will help define better processes. This view collects all cases that were resolved and points out to its solution

Query:
Create view [dbo].[Issue_Resolutions] as
Select c.cases_id as Case#, c. Subject as Title, c.Description as Issue,cc.comments as Resolution from cases c
left join case_comments cc on
c.cases_id = cc.case_idwherecc.resolved = 1 and c.case_status = 'Closed'

Sample Data

Lifetime Revenue

Revenue from each product offering till date

This view provides overall revenue generated by each and every product offering

Query:
Create view [dbo].[Overall_Revenue_Per_Product] asSelect p.product_ID as ProductID,CASE WHEN p.product_ID = 1 THEN 'Small Dog Bed' WHEN p.product_ID = 2 THEN 'Medium Dog Bed' WHEN p.product_ID = 3 THEN 'Large Dog Bed' WHEN p.product_ID = 4 THEN 'X-Large Dog Bed' WHEN p.product_ID = 1000 THEN 'Orthopedic Dog Bed'END as ProductName,sum(o.Order_total) as Revenuefrom orders oleft join order_details od ono.orders_id = od.orders_idleft join products p onod.product_id = p.product_idwhere o.order_status in ('Completed','In Progress')group by p.product_ID

Sample Data

Page updated

Google Sites

Report abuse