Calculating Sales Performance And Ranking Salespeople

Calculating Total Sales and Profit per Year

Here, we are going to prepare the reporting queries for provided data base that is finance database. So, user needs to open the Finance DB and it is illustrated as below("Create and use an index to improve performance", 2018).

Query: 1

EVALUATE

    SUMMARIZECOLUMNS('City'[Sales Territory], "% of Grand Total", 'Order'[% of Grand Total])

ORDER BY

  [% of Grand Total] DESC, 'City'[Sales Territory]

Annual Sales

CREATE TABLE SalesPerYear

 (Id INT PRIMARY KEY IDENTITY(1,1),

SalesYear INT,

Amount Float)

INSERT INTO SalesPerYear

VALUES    ('2014','77'),('2015','66'),('2016','50') ,('2017','60'),('2018','73')

SELECT * ,

CASE

WHEN KpiTarget >   Amount THEN -1

WHEN            KpiTarget <    Amount THEN 1

ELSE

0

END AS Sts   

FROM

SELECT * , (SELECT AVG(Sl.Amount)*1.1 FROM SalesPerYear Sl) as KpiTarget                                                           

                                                              FROM SalesPerYear

                                                              ) as tmp_tbl

                                                              order by SalesYear ASC

Case

When IsEmpty

  (ParallelPeriod

   ([Dim Date].[Calendar Date].[Calendar Year],1,

       [Dim Date].[Calendar Date].CurrentMember))

  Then 0 

   When VBA!Abs   (

      KpiValue( "Product Gross Profit Margin" ) -       (

         KpiValue ( "Product Gross Profit Margin" ),

          ParallelPeriod          (

            [Dim Date].[ Calendar Date].[ Calendar Year],         

            [Dim Date].[ Calendar Date].CurrentMember     (

          KpiValue ( "Product Gross Profit Margin" ),

          ParallelPeriod          (

            [Dim Date].[ Calendar Date].[ Calendar Year],

            [Dim Date].[ Calendar Date].CurrentMembe    ) <=.02

  Then 0

  When KpiValue( "Product Gross Profit Margin" ) -       (

         KpiValue ( "Product Gross Profit Margin" ),    ParallelPerio

           [Dim Date].[ Calendar Date].[ Calendar Year],

           [Dim Date].[ Calendar Date].CurrentMember

        KpiValue ( "Product Gross Profit Margin" ),

         ParallelPeriod        

           [Dim Date].[Calendar Date].[Calendar Year],

           [Dim Date].[Calendar Date].CurrentMember  >.02

SELECT {KPIValue("Product Gross Profit Margin"),

KPIGoal("Product Gross Profit Margin"),

KPIStatus("Product Gross Profit Margin"),

KPITrend("Product Gross Profit Margin")} on 0

FROM [Adventure Works DW]

sELECT

    SUM(IF(month = 'Jan', total, 0)) AS 'Jan',

    SUM(IF(month = 'Feb', total, 0)) AS 'Feb',

    SUM(IF(month = 'Mar', total, 0)) AS 'Mar',

    SUM(IF(month = 'Apr', total, 0)) AS 'Apr',

    SUM(IF(month = 'May', total, 0)) AS 'May',

    SUM(IF(month = 'Jun', total, 0)) AS 'Jun',

    SUM(IF(month = 'Jul', total, 0)) AS 'Jul',

    SUM(IF(month = 'Aug', total, 0)) AS 'Aug',

    SUM(IF(month = 'Sep', total, 0)) AS 'Sep',

    SUM(IF(month = 'Oct', total, 0)) AS 'Oct',

    SUM(IF(month = 'Nov', total, 0)) AS 'Nov',

    SUM(IF(month = 'Dec', total, 0)) AS 'Dec',

    SUM(total) AS total_yearly

    FROM (

SELECT DATE_FORMAT(date, "%b") AS month, SUM(total_price) as total

FROM cart

WHERE date <= NOW() and date >= Date_add(Now(),interval - 12 month)

GROUP BY DATE_FORMAT(date, "%m-%Y")) as sub

The representation presented helps to represent each month’s Total Sales Performance, based on a chart representation. Request of period classification is likewise correct, due to a direct result of the setting called as, Sort by Column (Zait, 2018).

Calculating Sales KPI for Each Country and Segment

Evaluate the focal point of a businessperson's execution by following not simply the general arrangements and edge focuses against spending plan, yet the specific quantifiable targets perceived previously. Starting now and into the foreseeable future, balance these results with similar arrangements gatherings, both to the extent size and customer mix. How might they rate? Take a gander at against the association's exhibited benchmark. What is their rank? The answer to this gives the business delegate a review of the business execution by giving a situating of sorting when it appears uniquely in association with the benchmark of the irganization..

select salesname, orderamount

from (

select

rank() over(partition by s.SalesName order by o.OrderAmount desc) as rnk

,s.SalesName 

,o.OrderAmount

From Orders o

join SalesPerson s on o.SalesID = s.SalesID) t

WITH total_sales

AS (SELECT TOP 10 FROM Sales),

(SELECT      region, person, count(*) as thousands

    FROM        sales

    GROUP BY    region, person

    ORDER BY    region, count(*) desc

, ranked_sales

AS (SELECT      region, person, thousands,

                ROW_NUMBER() OVER (PARTITION BY region ORDER BY thousands DESC, person) AS region_rank

    FROM        total_sales

SELECT  region, person, thousands

FROM    ranked_sales

order by rank;

select

            year(so.SalesOrderDate) as SalesYear,

            c.CountryName,

            s.SegmentName,

            sp.FirstName,

            sp.LastName,

            p.ProductName,

            count(*) as TotalProductSales,

            sum(case when sli.PromotionID = 0 then 0 else 1 end) as TotalPromotionalSales

from SalesOrderLineItem sli

            inner join Product p on p.ProductID = sli.ProductID

            inner join SalesOrder so on so.SalesOrderID = sli.SalesOrderID

            inner join SalesRegion sr on sr.SalesRegionID = so.SalesRegionID

            inner join SalesPerson sp on sp.SalesPersonID = sr.SalesPersonID

            inner join Region r on r.RegionID = sr.RegionID

            inner join Segment s on s.SegmentID = r.SegmentID

            inner join Country c on c.CountryID = r.CountryID

where year(so.SalesOrderDate) > 2012

group by

            year(so.SalesOrderDate),

            c.CountryName,

            s.SegmentName,

            sp.FirstName,

            sp.LastName,

            p.ProductName

B1A: The above query is used to present the product ID, sales order, sales region, sales person, region, segments and country depends on sales order date.

B1B: It generally aren't the sort of thing you'd have to discover in a request plan. A record channel infers that all the leaf-level of the document was hoped to find the information for the inquiry: When the rundown is a bundled list, this is the same as looking at the entire table. With only a few exclusions, this isn't incredible; we need to endeavour to change inspects into searches for, which suggests recuperating the data by just using the record tree.

Visualizing Sales Performance by Month

B1C

CREATE CLUSTERED INDEX [ix_Customer_ID] ON [dbo].[Customers]

            [Customer_ID] ASC

WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

In SQL, the database records engagesan individual to instanly complete the execution of SELECT inquiry.The record doesn’t empower much, for small number of tables. In any case, in case you have tables with a ton of data, records can essentially upgrade execution.

B1F

The records are optional structures related with tables and gatherings that empower SQL clarifications to execute simply more quickly against a table. Also as the record in this manual supports you discover information faster than if there were no rundown, an Oracle Database list gives a snappier access approach to table data. You can use records without overhauling any request. Your results are the same, anyway you see them simply more quickly.

B1G

It is possible to make use oflists to empower the Access for finding and sorting out the records instantly. A document stores the territory of records in light of the field or fields that you record. Once the access gets the zone from the record, it would then have the capacity to recuperate the data by moving direct to the correct territory. Henceforth, using a rundown can be fundamentally snappier than investigating most of the records to find the data.

       select

                        sales_info.SalesMonth,

                        c.CountryName,

                        s.SegmentName,

                        sales_info.PromotionRate,

                        sales_info.TotalMonthlySales

            from Region r

                        inner join Country c on c.CountryID = r.CountryID

                        inner join Segment s on s.SegmentID = r.SegmentID

                        inner join SalesRegion sr on sr.RegionID = r.RegionID

                        left join (

                                    select

                                                so.SalesRegionID,

                                                so.SalesMonth,

                                                sum(case when sli.PromotionID = 0 then 0.0 else 1.0 end) / count(*) as PromotionRate,

                                                sum(SalePrice) as TotalMonthlySales

                                    from SalesOrder so

                                                inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID

                                    group by

                                                so.SalesRegionID,

                                                so.SalesMonth

                        ) sales_info on sales_info.SalesRegionID = sr.SalesRegionID

select *

from monthly_sales_info

where SalesMonth >= '2016-01-01'; 

B2A

The overhead query is used to show the region based on county, segment and sales region and also count the promotion rate to sum the sales price that is total monthly sales from the sales order.

B2B

Create Index

create index idx_promotions on SalesOrderLineItem (PromotionID, SalesOrderID);

Execution plans discloses how an inquiry might be executed, or how a request was executed. This is consequentlythe basic strategies of DBA to research the performing requests which are an insufficient. Rather than hypothesize why a given inquiry is playing out countless, putting your I/O through the housetop, you can use the execution plan to recognize the right piece of SQL code that is causing the issue. For example, it may channel an entire table-worth of data when, with the most ideal record, it could only backpack out only the lines you require. The execution plan represents all of this.

Ranking Salespeople based on Performance

B2C

select

    b.Name,

    SUM(ti.Pages * ti.Rate) as TotalSales,

    SUM(ti.Pages) as TotalPages,

    bt.Amount,

    bt.Month,

    bt.Year

from Sales.BranchTarget bt

    left join Sales.[Transaction] t

        on bt.BranchId = t.BranchId

        AND MONTH(t.Date) = bt.Month

        AND YEAR(t.Date) = bt.Year

    left join Sales.TransactionItem ti

        on ti.TransactionId = t.Id 

    left join Sales.Branch b

        on b.Id = bt.BranchId

group by  bt.Month, bt.Year, bt.Amount, b.Name

order by bt.Month, bt.Year, b.Name

CA

The execution plan refers to the visual description of all the exercises that are conducted by the database engine, using the true objective for re-establishing the necessary data based on your inquiry. Now and again, you will be astonished by what they reveal, despite for the most safe looking request. More number of requests could be sensibly understood inside the execution plan’ssetting. The execution plan will immediately represent any kind of problem due to the inquiry.

CB

select

            basic_metrics.SalesOrderDate,

            basic_metrics.SalesOrderNumber,

            basic_metrics.SalesPersonID,

            margin_calculation.SalesOrderID,

            basic_metrics.TotalSalesPrice,

            basic_metrics.TotalCost,

            basic_metrics.TotalRRP,

            basic_metrics.UniqueItems,

            basic_metrics.TotalItems,

            round(margin_calculation.Margin, 2) as Margin,

            round(discount_calculation.PercentageDiscount, 2) as PercentageDiscount

from (

            -- Calculate Discount

            select

                        so.SalesOrderID,

                        sum((pc.RRP * sli.UnitsSold) - SalePrice) / sum(pc.RRP * sli.UnitsSold) as PercentageDiscount

            from SalesOrder so

                        inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID

                        inner join ProductCost pc on pc.ProductID = sli.ProductID

            group by

                        so.SalesOrderID

 discount_calculation

            inner join (

                        -- Calculate Margin

                        select

                                    so.SalesOrderID,

                                    case

                                                when sum(SalePrice) = 0 then 0

                                                else sum(SalePrice - (pc.ManufacturingPrice * sli.UnitsSold)) / sum(SalePrice)

                                    end as Margin

                        from SalesOrder so

                                    inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID

                                    inner join ProductCost pc on pc.ProductID = sli.ProductID

                        group by

                                    so.SalesOrderID            )

margin_calculation on margin_calculation.SalesOrderID = discount_calculation.SalesOrderID

            inner join (

            -- basic metrics

            select

                        so.SalesOrderID,

                        so.SalesOrderNumber,

                        so.SalesOrderDate,

                        so.SalesPersonID,

                        so.SalesMonth,

                        sum(sli.SalePrice) as TotalSalesPrice,

                        sum(pc.ManufacturingPrice * sli.UnitsSold) as TotalCost,

                        sum(pc.RRP * sli.UnitsSold) as TotalRRP,

                        count(distinct sli.ProductID) as UniqueItems,

                        sum(UnitsSold) as TotalItems

            from SalesOrder so

                        inner join SalesOrderLineItem sli on sli.SalesOrderID = so.SalesOrderID

                        inner join ProductCost pc on pc.ProductID = sli.ProductID

            group by

                        so.SalesOrderID,

                        so.SalesOrderNumber,

                        so.SalesOrderDate,

                        so.SalesPersonID,

                        so.SalesMonth

 basic_metrics on basic_metrics.SalesOrderID = margin_calculation.SalesOrderID

where SalesOrderDate > '2016-01-01'

CC

1.

Initially, there is a common confusion that in case of absence of new bits of knowledge, the execution plans has to reliably proceed as previously, i.e., without collecting the bits of knowledge, somehow can ensure and accreditation the database will basically perform comparably and create a comparative execution plans. This is on an exceptionally essential level not veritable. To be sure, an amazing reverse can be legitimate. One may need to accumulate new estimations to guarantee crucial execution plans don't change. It's the exhibit of not empowering estimations that can on the grounds that execution needs to out of the blue change. The next important point refers to the following- While one encounters each something that may have changed in the database, two fundamental perspectives are consistently neglected.

3 and 4.

EXPLAIN PLAN’s clarification indicates the design of execution that is selected by the Oracle streamlining operator to do the following operation, SELECT, INSERT, UPDATE and DELETE. A declaration's execution plan is the course of action of assignments Oracle works to run the declaration. The section source tree is the focal point of the execution plan. The following data is demonstrated:

• For each table determined in the declaration, getthe methodology.
• Asking for the tables which are referenced using declaration.
• Data exercises like channel, sort, or accumulation
• The tables impacted by join undertakings in the declaration, use the join methodology.

Despite the segment source tree, the course of action table contains information about the going with:

1. Partitioning

Example:Course of action of got to distributions.

1. Optimization

Example: Cost and cardinality of each assignment.

1. Parallel execution

Example:Allocation system for join inputs.

Create and use an index to improve performance. (2018). 

Zait, M. (2018). How do I display and read the execution plans for a SQL statement.