none
SQL Server compare sales with previous month

    Frage

  • Good day,

    Please will you help me with SQL Server query on following scenario

    COUNT(CompanyName) WHERE PreviousMonthSales < CurrentMonthSales

    Please Note: "CompanyName" form DimCompany table. Sales from "FactSales" table. FactSales table has "COB Date" 

    Thank you


    BABA

    Donnerstag, 29. März 2018 10:50

Antworten

  • assuming Fact table has companyid as foreign key you can do this

    ;With SalesComparison
    AS
    (
    SELECT CompanyID,
    DATEADD(mm,DATEDIFF(mm,0,COBDate),0) AS MonthDate,
    SUM(Sales) AS MonthSales
    FROM FactSales
    GROUP BY CompanyID
    )
    
    SELECT COUNT(s1.CompanyID) AS CompName
    FROM SalesComparison s1
    INNER JOIN SalesComparison s2
    ON s2.CompanyID = s1.CompanyID
    AND s2.MonthDate = DATEADD(mm,-1,s1.MonthDate)
    WHERE s1.MonthSales > s2.MonthSales


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Als Antwort markiert RP Reddy Mittwoch, 4. April 2018 10:56
    Donnerstag, 29. März 2018 11:32