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]