I need to show a result of the top 10 branches of a store, which have seen greater monthly sales in February than January.

My columns look like this -

BranchID
SaleDate
SaleAmount

I tried partitioning by both branchid and the month of salesdate, but I'm not sure how to incorporate grouping along with partitioning.

select branchid, 
       datepart(month, saledate) as month, 
       sum(saleamount) over (partition by datepart(month, saledate), branchid  order by saledate) as 'Monthly Sales' 
from sales

1 Answers

0
Matt On

If you're looking for the top 10 highest increase from Jan to Feb in sales, here is one way that you could do this below.

SELECT TOP(10) --Get top 10
        [BranchID],
        ISNULL(SUM(
            CASE WHEN MONTH([SaleDate]) = 2
            THEN [SaleAmount]
            ELSE 0 END
        ),0) as [SaleAmountFeb], --Sum all Feb sales
        ISNULL(SUM(
            CASE WHEN MONTH([SaleDate]) = 1
            THEN [SaleAmount]
            ELSE 0 END
        ),0) as [SaleAmountJan], --Sum all Jan sales

        ISNULL(SUM(
            CASE WHEN MONTH([SaleDate]) = 2
            THEN [SaleAmount]
            ELSE 0 END
        ),0) - 
        ISNULL(SUM(
            CASE WHEN MONTH([SaleDate]) = 1
            THEN [SaleAmount]
            ELSE 0 END
        ),0) as [Increase in Sales] --Increase in sales Jan to Feb

    FROM sales
    GROUP BY
        [BranchID]
    ORDER BY
        4 DESC --Order by Increase in sales Jan to Feb (Largest to smallest)