统计 按年，月，季度

select GetShopId,Shop.ShopName,[Term],COUNT(1) GetCount ,SUM(SalesOrderManage.Status) 
    from
    (   select case when MONTH([GetDate])>=1 and MONTH([GetDate])<=3 then str(Year([GetDate])) +'-Q1'
  when MONTH([GetDate])>=4 and MONTH([GetDate])<=6 then str(Year([GetDate])) +'-Q2'
  when MONTH([GetDate])>=7 and MONTH([GetDate])<=9 then str(Year([GetDate])) +'-Q3'
  when MONTH([GetDate])>=10 and MONTH([GetDate])<=12 then str(Year([GetDate])) +'-Q4'
  end[Term],
    * from SalesOrderManage
    ) SalesOrderManage
    inner join Shop on Shop.ShopId=SalesOrderManage.GetShopId
     group by GetShopId ,Shop.ShopName,[Term]

  --按月统计
select TreeItem.Name  ,[Term],COUNT(SaleOrder.CustomerId) as GetCount,SUM(TotalAmount) as TotalAmount
from
(
select substring(CONVERT(varchar,GETDATE(),120),0,8)[Term],*from SaleOrder
)SaleOrder
inner join  Shop on  Shop.ShopId=SaleOrder.ShopId
inner join TreeItem on TreeItem.TreeItemId=Shop.FunctionAreaId
where SaleOrder.SaleOrderId in (select distinct(SaleOrderId) as SaleOrderId from vSalesStatistics)
group by TreeItem.Name ,[Term]
    
 --按季节统计   
select TreeItem.Name  ,[Term],COUNT(SaleOrder.CustomerId) as GetCount,SUM(TotalAmount) as TotalAmount
  from
(
  select case when MONTH(CreateDate)>=1 and MONTH(CreateDate)<=3 then str(Year(CreateDate)) +'-Q1'
  when MONTH(CreateDate)>=4 and MONTH(CreateDate)<=6 then str(Year(CreateDate)) +'-Q2'
  when MONTH(CreateDate)>=7 and MONTH(CreateDate)<=9 then str(Year(CreateDate)) +'-Q3'
  when MONTH(CreateDate)>=10 and MONTH(CreateDate)<=12 then str(Year(CreateDate)) +'-Q4'
  end[Term],
    * from SaleOrder
)SaleOrder
inner join  Shop on  Shop.ShopId=SaleOrder.ShopId
inner join TreeItem on TreeItem.TreeItemId=Shop.FunctionAreaId
 where SaleOrder.SaleOrderId in (select distinct(SaleOrderId) as SaleOrderId from vSalesStatistics)
 group by TreeItem.Name ,[Term]
 

 --按年统计
  select TreeItem.Name   ,[Term],COUNT(SaleOrder.CustomerId) as GetCount,SUM(TotalAmount) as TotalAmount
  from
(
   select YEAR([CreateDate])[Term],*from SaleOrder
)SaleOrder
inner join  Shop on  Shop.ShopId=SaleOrder.ShopId
inner join TreeItem on TreeItem.TreeItemId=Shop.FunctionAreaId
 where SaleOrder.SaleOrderId in (select distinct(SaleOrderId) as SaleOrderId from vSalesStatistics)
 
 group by TreeItem.Name ,[Term]