SELECT

a.BillDate 日期,

YEAR (a.BillDate) 年份,

MONTH (a.BillDate) 月份,

DAY (a.BillDate) 日,

a.ShopID 门店ID,

b.BrandName 品牌,

b.HotelName 门店,

SUM (a.Amount) 实际营收,

c.[Value] 计划营收

FROM

[dbo].[DIM_POS_Shop] b

LEFT JOIN [dbo].[FACT_POS_Bill] a ON a.ShopID = b.ShopID

LEFT JOIN [dbo].[FACT_Target_Day] c ON c.ShopID = b.HotelID

where  c.Items = '营收（万元）'

${if(type='date', " and a.BillDate >= '" + 开始日期 + "' and a.BillDate<='" + 结束日期 + "'", "")}

${if(type='month', " and cast(month(a.BillDate) as int) = "+ 月 + " and year(a.BillDate) = '" + 年+"'", "")}

${if(type='year', " and year(a.BillDate) = '" + 年+"'", "")}

${if(len(品牌)==0,"","and b.BrandName in ('"+品牌+"')")}

${if(len(门店)==0,"","and b.HotelName in ('"+门店+"')")}

GROUP BY

a.ShopID,

a.BillDate,

b.BrandName,

b.HotelName,

c.[Value]

ORDER BY

a.ShopID ASC