mcqueries

gistfile1.txt

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate = dateadd(dd, datepart(dd, getdate())*-1, @StartDate)
SET @EndDate = dateadd(mm, 1, @StartDate)
SET @CustId = 2
-- hom many orders customer got last month
select count(*) from t_order where t_order.customer_id = @CustId and
    order_date BETWEEN @StartDate AND @EndDate;

-- how much $ customer spent last year
select SUM(total_amount) from t_order where t_order.customer_id = @CustId and
    YEAR(order_date) =YEAR(GETDATE()) - 1;

-- 10 most profitable items in a year
select top 10
    t_item.code, t_item.description,
    sum ( ((retail_price - wholesale_price) * oi.quantity)) as profit
from t_item
         join t_order_item oi on t_item.id = oi.item_id
         join t_order ord on oi.order_id = ord.id
where ord.order_date <= '2020-08-06' and ord.order_date >= '2019-08-06'
group by t_item.code, t_item.description
order by profit desc;