SELECT 

${if(type='date'," CONVERT(varchar(100),b.[日期],20) as 日期,","")}

${if(type='month', " CONVERT(varchar(7),b.[日期],20) as 日期,  ","")}

${if(type='year', " CONVERT(varchar(4),b.[日期],20) as 日期, ","")}

a.[品牌],

a.[门店],

sum(b.[实际营收]) 实际营收,

sum(c.[计划营收]) 计划营收

FROM

(

SELECT

t1.BrandName 品牌,

t1.HotelName 门店,

t1.ShopID 门店id,

t1.HotelID 饭店id

FROM

[dbo].[DIM_POS_Shop] t1

WHERE

t1.HotelName NOT IN ('', '测试门店')

) a

LEFT JOIN (

SELECT

t2.ShopID 门店ID,

t2.BillDate 日期,

SUM (t2.Amount) 实际营收

FROM

[dbo].[FACT_POS_Bill] t2

WHERE

t2.OrderStatus = '40'

GROUP BY

t2.ShopID,

t2.BillDate

) b ON a.[门店id] = b.[门店ID]

LEFT JOIN(

SELECT

t3.ShopID 门店id,

t3.TargetDate 日期,

SUM (t3.[Value]) 计划营收

FROM

[dbo].[FACT_Target_Day] t3

WHERE

t3.Items = '营收（万元）'

GROUP BY

t3.ShopID,

t3.TargetDate

)c

on c.门店id=a.饭店id

and b.[日期]=c.[日期]

where 1=1

${if(type='date'," and CONVERT(varchar(100),b.[日期],20) >= '" + 开始日期 + "' and CONVERT(varchar(100),b.[日期],20)<='" + 结束日期 + "'", "")}

${if(type='month', " and cast(month(CONVERT(varchar(100),b.[日期],20)) as int) = "+ 月 + " and year(CONVERT(varchar(100),b.[日期],20)) = '" + 年+"'", "")}

${if(type='year', " and year(CONVERT(varchar(100),b.[日期],20)) = '" + 年+"'", "")}

${if(len(品牌)==0,"","and a.[品牌] in ('"+品牌+"')")}

${if(len(门店)==0,"","and a.[门店] in ('"+门店+"')")}

group by 

${if(type='date'," CONVERT(varchar(100),b.[日期,20), ","")}

${if(type='month', " CONVERT(varchar(7),b.[日期,20),  ","")}

${if(type='year', " CONVERT(varchar(4),b.[日期,20), ","")}