SQL Server compare sales with previous month


  • 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


    giovedì 29 marzo 2018 10:50


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

    ;With SalesComparison
    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
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Contrassegnato come risposta RP Reddy mercoledì 4 aprile 2018 10:56
    giovedì 29 marzo 2018 11:32