use CubeDemo;
go
SET NOCOUNT ON
/*库存模块*/
if OBJECT_ID(N'BusinessCommon_SaleModule',N'U') is not null
drop table BusinessCommon_SaleModule
go
create table BusinessCommon_SaleModule
(
BCId int identity(1,1) primary key not null,
ShopId varchar(20) null, ---门店Id
ShopName nvarchar(50) null, ---门店名称
SaleCarCount int null, --销售量
BCDate datetime null --生成日期
)
goinsert into BusinessCommon_SaleModule
select 'MD001','广丰店',80,'2010-4-10' union all
select 'MD001','广丰店',20,'2010-5-25' union all
select 'MD001','广丰店',55,'2011-5-1' union all
select 'MD001','广丰店',48,'2011-5-11' union all
select 'MD001','广丰店',80,'2011-3-18' union all
select 'MD001','广丰店',10,'2011-3-12' union all
select 'MD001','广丰店',40,'2011-3-25' union allselect 'MD002','现代店',50,'2011-3-27' union all
select 'MD002','现代店',40,'2011-5-15' union all
select 'MD002','现代店',80,'2010-4-18' union all
select 'MD002','现代店',40,'2010-4-13' union all
select 'MD004','海马店',70,'2011-3-15' union allselect 'MD005','一丰店',80,'2010-4-10' union all
select 'MD005','一丰店',80,'2011-3-10' union all
select 'MD005','一丰店',80,'2011-5-22' union all
select 'MD003','宝马店',20,'2011-4-10' union all
select 'MD003','宝马店',80,'2010-8-10'
goselect * from BusinessCommon_SaleModule;/* 如何根据条件 日 2011-4-18 动态查询到2010年4月(从2010年4月1日到2010年4月18日的数据) 2011年1月(从2011年1月1日到2011年1月18日的数据) 2011年2月(从2011年2月1日到2011年2月18日的数据) 2011年3月(从2011年3月1日到2011年3月18日的数据)
2011年4月(从2011年4月1日到2011年4月18日的数据) 2011年累计(从2011年1月1日到2011年4月18日的数据)
2010年累计(从2010年1月1日到2010年4月18日的数据)这些数据
*//* 要得到的结果如下: 门店名称 2010年4月 2011年1月 2011年2月 2011年3月 2011年4月 2011年累计 2010年累计
广丰店 80 0 0 90 0 130 80
现代店 120 0 0 0 0 50 120
宝马店 0 0 0 0 20 20 0
海马店 0 0 0 70 0 70 0
一丰店 80 0 0 80 0 80 80
*/
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------
CASE WHEN BCDate BETWEEN 2010-04-01日 AND 2010-04-18这样的一共有几行就写几行
睡觉了 如果明天还没解决 我就帮你写完全
sum(case when BCDate between '2010-04-01' and '2010-04-18' then SaleCarCount else 0 end) as [2010年4月],
sum(case when BCDate between '2011-01-01' and '2011-01-18' then SaleCarCount else 0 end) as [2011年1月],
sum(case when BCDate between '2011-02-01' and '2011-02-18' then SaleCarCount else 0 end) as [2011年2月],
sum(case when BCDate between '2011-03-01' and '2011-03-18' then SaleCarCount else 0 end) as [2011年3月],
sum(case when BCDate between '2011-04-01' and '2011-04-18' then SaleCarCount else 0 end) as [2011年4月],
sum(case when BCDate between '2011-01-01' and '2011-04-18' then SaleCarCount else 0 end) as [2011年累计],
sum(case when BCDate between '2010-01-01' and '2010-04-18' then SaleCarCount else 0 end) as [2010年累计]
from BusinessCommon_SaleModule
group by
ShopName
Create View vw_BusinessCommon_SaleModule
AS
select ShopName, Year(BCDate) iYear, Month(BCDate) iMonth, cast(Year(BCDate) as varchar)+'年'+cast(Month(BCDate) as varchar)+'月' dDate, Sum(SaleCarCount) iCount
from BusinessCommon_SaleModule
group by ShopName, BCDate
declare @Sql varchar(8000)
set @Sql='Select ShopName '
select @Sql = @Sql + ', Sum(Case when dDate='''+dDate+''' then iCount else 0 end) AS '''+dDate+'''' from vw_BusinessCommon_SaleModule
select @Sql = @Sql + ', Sum(Case when iYear='+cast(iYear as varchar)+' then iCount else 0 end) AS '''+cast(iYear as varchar)+'年累计''' from vw_BusinessCommon_SaleModule group by iYear
select @Sql = @Sql + ' From vw_BusinessCommon_SaleModule Group by ShopName'
Exec (@Sql)
drop table BusinessCommon_SaleModule
go
create table BusinessCommon_SaleModule
(
BCId int identity(1,1) primary key not null,
ShopId varchar(20) null, ---门店Id
ShopName nvarchar(50) null, ---门店名称
SaleCarCount int null, --销售量
BCDate datetime null --生成日期
)
goinsert into BusinessCommon_SaleModule
select 'MD001','广丰店',80,'2010-4-10' union all
select 'MD001','广丰店',20,'2010-5-25' union all
select 'MD001','广丰店',55,'2011-5-1' union all
select 'MD001','广丰店',48,'2011-5-11' union all
select 'MD001','广丰店',80,'2011-3-18' union all
select 'MD001','广丰店',10,'2011-3-12' union all
select 'MD001','广丰店',40,'2011-3-25' union allselect 'MD002','现代店',50,'2011-3-27' union all
select 'MD002','现代店',40,'2011-5-15' union all
select 'MD002','现代店',80,'2010-4-18' union all
select 'MD002','现代店',40,'2010-4-13' union all
select 'MD004','海马店',70,'2011-3-15' union allselect 'MD005','一丰店',80,'2010-4-10' union all
select 'MD005','一丰店',80,'2011-3-10' union all
select 'MD005','一丰店',80,'2011-5-22' union all
select 'MD003','宝马店',20,'2011-4-10' union all
select 'MD003','宝马店',80,'2010-8-10'
go--select * from BusinessCommon_SaleModule;DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(CONVERT(VARCHAR(7),BCDate,23)),
@s1=ISNULL(@s1+',','')+'ISNULL(['+CONVERT(VARCHAR(7),BCDate,23)+'],0)'+QUOTENAME(CONVERT(VARCHAR(7),BCDate,23))
FROM BusinessCommon_SaleModule
GROUP BY CONVERT(VARCHAR(7),BCDate,23)
EXEC('
SELECT ShopName,'+@s1+',[2011年累计],[2010年累计]
FROM (
SELECT ShopName,SaleCarCount,CONVERT(VARCHAR(7),BCDate,23) BCMon,
SUM(CASE YEAR(BCDate) WHEN 2011 THEN SaleCarCount ELSE 0 END)OVER(PARTITION BY ShopId) [2011年累计],
SUM(CASE YEAR(BCDate) WHEN 2010 THEN SaleCarCount ELSE 0 END)OVER(PARTITION BY ShopId) [2010年累计]
FROM BusinessCommon_SaleModule
) t
PIVOT(SUM(SaleCarCount) FOR BCMon IN('+@s+')) pvt
')
/*
ShopName 2010-04 2010-05 2010-08 2011-03 2011-04 2011-05 2011年累计 2010年累计
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
宝马店 0 0 80 0 20 0 20 80
广丰店 80 20 0 130 0 103 233 100
海马店 0 0 0 70 0 0 70 0
现代店 120 0 0 50 0 40 90 120
一丰店 80 0 0 80 0 80 160 80(5 行受影响)
*/
drop table BusinessCommon_SaleModule
go
create table BusinessCommon_SaleModule
(
BCId int identity(1,1) primary key not null,
ShopId varchar(20) null, ---门店Id
ShopName nvarchar(50) null, ---门店名称
SaleCarCount int null, --销售量
BCDate datetime null --生成日期
)
goinsert into BusinessCommon_SaleModule
select 'MD001','广丰店',80,'2010-4-10' union all
select 'MD001','广丰店',20,'2010-5-25' union all
select 'MD001','广丰店',55,'2011-5-1' union all
select 'MD001','广丰店',48,'2011-5-11' union all
select 'MD001','广丰店',80,'2011-3-18' union all
select 'MD001','广丰店',10,'2011-3-12' union all
select 'MD001','广丰店',40,'2011-3-25' union allselect 'MD002','现代店',50,'2011-3-27' union all
select 'MD002','现代店',40,'2011-5-15' union all
select 'MD002','现代店',80,'2010-4-18' union all
select 'MD002','现代店',40,'2010-4-13' union all
select 'MD004','海马店',70,'2011-3-15' union allselect 'MD005','一丰店',80,'2010-4-10' union all
select 'MD005','一丰店',80,'2011-3-10' union all
select 'MD005','一丰店',80,'2011-5-22' union all
select 'MD003','宝马店',20,'2011-4-10' union all
select 'MD003','宝马店',80,'2010-8-10'
go--select * from BusinessCommon_SaleModule;DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+QUOTENAME(CONVERT(VARCHAR(7),BCDate,23)),
@s1=ISNULL(@s1+',','')+'ISNULL(['+CONVERT(VARCHAR(7),BCDate,23)+'],0)'+QUOTENAME(CONVERT(VARCHAR(7),BCDate,23))
FROM BusinessCommon_SaleModule
WHERE DAY(BCDate)<=18
GROUP BY CONVERT(VARCHAR(7),BCDate,23)
EXEC('
SELECT ShopName,'+@s1+',[2011年累计],[2010年累计]
FROM (
SELECT ShopName,SaleCarCount,CONVERT(VARCHAR(7),BCDate,23) BCMon,
SUM(CASE YEAR(BCDate) WHEN 2011 THEN SaleCarCount ELSE 0 END)OVER(PARTITION BY ShopId) [2011年累计],
SUM(CASE YEAR(BCDate) WHEN 2010 THEN SaleCarCount ELSE 0 END)OVER(PARTITION BY ShopId) [2010年累计]
FROM BusinessCommon_SaleModule
WHERE DAY(BCDate)<=18
) t
PIVOT(SUM(SaleCarCount) FOR BCMon IN('+@s+')) pvt
')
/*
ShopName 2010-04 2010-08 2011-03 2011-04 2011-05 2011年累计 2010年累计
-------------------------------------------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
宝马店 0 80 0 20 0 20 80
广丰店 80 0 90 0 103 193 80
海马店 0 0 70 0 0 70 0
现代店 120 0 0 0 40 40 120
一丰店 80 0 80 0 0 80 80(5 行受影响)
*/加上条件
drop table BusinessCommon_SaleModule
go
create table BusinessCommon_SaleModule
(
BCId int identity(1,1) primary key not null,
ShopId varchar(20) null, ---门店Id
ShopName nvarchar(50) null, ---门店名称
SaleCarCount int null, --销售量
BCDate datetime null --生成日期
)
goinsert into BusinessCommon_SaleModule
select 'MD001','广丰店',80,'2010-4-10' union all
select 'MD001','广丰店',20,'2010-5-25' union all
select 'MD001','广丰店',55,'2011-5-1' union all
select 'MD001','广丰店',48,'2011-5-11' union all
select 'MD001','广丰店',80,'2011-3-18' union all
select 'MD001','广丰店',10,'2011-3-12' union all
select 'MD001','广丰店',40,'2011-3-25' union allselect 'MD002','现代店',50,'2011-3-27' union all
select 'MD002','现代店',40,'2011-5-15' union all
select 'MD002','现代店',80,'2010-4-18' union all
select 'MD002','现代店',40,'2010-4-13' union all
select 'MD004','海马店',70,'2011-3-15' union allselect 'MD005','一丰店',80,'2010-4-10' union all
select 'MD005','一丰店',80,'2011-3-10' union all
select 'MD005','一丰店',80,'2011-5-22' union all
select 'MD003','宝马店',20,'2011-4-10' union all
select 'MD003','宝马店',80,'2010-8-10'
go--select * from BusinessCommon_SaleModule;
IF OBJECT_ID('p_test')>0
DROP PROC p_test
GO
CREATE PROC p_test
@d DATETIME
AS
SET NOCOUNT ON
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX),@year1 VARCHAR(4),@year2 VARCHAR(4),@day VARCHAR(2),@month VARCHAR(2)
SELECT @year1=LTRIM(YEAR(@d)),@year2=LTRIM(YEAR(@d)-1),@day=LTRIM(DAY(@d)),@month=LTRIM(MONTH(@d))
SELECT @s=ISNULL(@s+',','')+QUOTENAME(CONVERT(VARCHAR(7),DATEADD(mm,-number,@d),23)),
@s1=ISNULL(@s1+',','')+'ISNULL(['+CONVERT(VARCHAR(7),DATEADD(mm,-number,@d),23)+'],0)'+REPLACE(QUOTENAME(CONVERT(VARCHAR(7),DATEADD(mm,-number,@d),23)+'月'),'-','年')
FROM MASTER..spt_values
WHERE TYPE='p'
AND number<MONTH(@d)
ORDER BY number descSELECT @s=QUOTENAME(CONVERT(varchar(7),DATEADD(yy,-1,@d),23))+','+@s,
@s1='ISNULL(['+CONVERT(VARCHAR(7),DATEADD(yy,-1,@d),23)+'],0)'+REPLACE(QUOTENAME(CONVERT(VARCHAR(7),DATEADD(yy,-1,@d),23)+'月'),'-','年')+','+@s1
EXEC('
SELECT ShopName,'+@s1+',['+@year1+'年累计],['+@year2+'年累计]
FROM (
SELECT ShopName,CASE WHEN DAY(BCDate)<='+@day+' THEN SaleCarCount ELSE 0 END SaleCarCount,CONVERT(VARCHAR(7),BCDate,23) BCMon,
SUM(CASE WHEN YEAR(BCDate)='+@year1+' AND BCDate<='''+@year1+'-'+@month+'-'+@day+''' THEN SaleCarCount ELSE 0 END)OVER(PARTITION BY ShopId) ['+@year1+'年累计],
SUM(CASE YEAR(BCDate) WHEN '+@year2+' THEN SaleCarCount ELSE 0 END)OVER(PARTITION BY ShopId) ['+@year2+'年累计]
FROM BusinessCommon_SaleModule
WHERE ( YEAR(BCDate)='+@year1+' AND BCDate<='''+@year1+'-'+@month+'-'+@day+''')
OR (YEAR(BCDate)='+@year2+' AND MONTH(BCDate)='+@month+' AND DAY(BCDate)<='+@day+')
) t
PIVOT(SUM(SaleCarCount) FOR BCMon IN('+@s+')) pvt
')
GO
EXEC p_test '2011-4-18'
/*
ShopName 2010年04月 2011年01月 2011年02月 2011年03月 2011年04月 2011年累计 2010年累计
------------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
宝马店 0 0 0 0 20 20 0
广丰店 80 0 0 90 0 130 80
海马店 0 0 0 70 0 70 0
现代店 120 0 0 0 0 50 120
一丰店 80 0 0 80 0 80 80
*/
DECLARE @SEARCHTIME nvarchar(1000);--条件的时间
DECLARE @LastYear nvarchar(100);--去年当月当天的时间
DECLARE @FirstMonthToNowSQL nvarchar(4000);--总的SQL语句
set @FirstMonthToNowSQL=''
SET @SEARCHTIME='2011-4-18'
set @LastYear=cast(dateadd(year,-1,@SEARCHTIME) as nvarchar(100))declare @LastYearFileName nvarchar(50)
set @LastYearFileName=''--去年销售量的字段名
set @LastYearFileName=cast(year(@LastYear) as nvarchar(10))+'年'+cast(month(@LastYear) as nvarchar(10))+'月'--该CTE用做获取所以的门店
set @FirstMonthToNowSQL=@FirstMonthToNowSQL+
'
with AllShopId as
(
select distinct ShopId,ShopName from dbo.BusinessCommon_SaleModule
)
'
--去年当月累计到当天的销售俩
set @FirstMonthToNowSQL=@FirstMonthToNowSQL+
'
,LastYear_Month as
(
select ShopId,ShopName,SUM(SaleCarCount) as ['
+@LastYearFileName+
'] from dbo.BusinessCommon_SaleModule
WHERE YEAR(BCDate)=YEAR('''+@SEARCHTIME+''')-1 AND MONTH(BCDate)=MONTH('''+@SEARCHTIME+''') AND DAY(BCDate)<=DAY('''+@SEARCHTIME+''')
group by ShopId,ShopName
)
'
declare @count int
set @count=0--循环中每个字段的名称，如：,isnull([2011年1月],0) as [2011年1月],isnull([2011年2月],0) as [2011年2月],isnull([2011年3月],0) as [2011年3月],isnull([2011年4月],0) as [2011年4月]
declare @cteFileName nvarchar(1000)
set @cteFileName=''
--循环中每个cte的链接，如： left join cte1 on cte1.ShopId=AllShopId.ShopId left join cte2 on cte2.ShopId=AllShopId.ShopId left join cte3 on cte3.ShopId=AllShopId.ShopId left join cte4 on cte4.ShopId=AllShopId.ShopId
declare @cteInnerJoin nvarchar(1000)
set @cteInnerJoin=''
while @count<cast(datepart(month,@SEARCHTIME) as int)
begin
select @count=@count+1
select @cteFileName=@cteFileName+',isnull(['+cast(year(@SEARCHTIME) as nvarchar(50))+'年'+cast(@count as nvarchar(50))+'月],0) as ['+cast(year(@SEARCHTIME) as nvarchar(50))+'年'+cast(@count as nvarchar(50))+'月]'select @cteInnerJoin=@cteInnerJoin+' left join cte'+cast(@count as nvarchar(10))+
'
on cte'+cast(@count as nvarchar(10))+'.ShopId=AllShopId.ShopId
'
--循环获取前几个月的销售量
select @FirstMonthToNowSQL=@FirstMonthToNowSQL+',cte'+cast(@count as nvarchar(10))+'
as
(
select ShopId,ShopName,SUM(SaleCarCount) as ['
+cast(year(@SEARCHTIME) as nvarchar(50))+'年'+cast(@count as nvarchar(50))+'月'+
'] from dbo.BusinessCommon_SaleModule
WHERE YEAR(BCDate)=YEAR('''+@SEARCHTIME+''') AND MONTH(BCDate)='+cast(@count as nvarchar(20))+' AND DAY(BCDate)<=DAY('''+@SEARCHTIME+''')
group by ShopId,ShopName
)
'if @count=cast(datepart(month,@SEARCHTIME) as int)
break
else
continue
end--本年年累计
select @FirstMonthToNowSQL=@FirstMonthToNowSQL+'
,ThisYearAll as
(
select ShopId,ShopName,SUM(SaleCarCount) as ['+cast(year(@SEARCHTIME) as nvarchar(100))+'年累计] from dbo.BusinessCommon_SaleModule
WHERE YEAR(BCDate)=YEAR('''+@SEARCHTIME+''') AND BCDate<='''+@SEARCHTIME+'''
group by ShopId,ShopName
)
'
--去年同期年累计
select @FirstMonthToNowSQL=@FirstMonthToNowSQL+'
,LastYearAll as
(
select ShopId,ShopName,SUM(SaleCarCount) as ['+cast(year(@SEARCHTIME)-1 as nvarchar(100))+'年累计] from dbo.BusinessCommon_SaleModule
WHERE YEAR(BCDate)=YEAR('''+@SEARCHTIME+''')-1 AND BCDate<=dateadd(year,-1,'''+@SEARCHTIME+''')
group by ShopId,ShopName
)'--最后的的SQL查询语句
select @FirstMonthToNowSQL=@FirstMonthToNowSQL+
'
select AllShopId.ShopName as [门店名称], isnull(['+@LastYearFileName+'],0) as ['+@LastYearFileName+']'+@cteFileName+'
,isnull(['+cast(year(@SEARCHTIME) as nvarchar(100))+'年累计],0) as ['+cast(year(@SEARCHTIME) as nvarchar(100))+'年累计]
,isnull(['+cast(year(@SEARCHTIME)-1 as nvarchar(100))+'年累计],0) as ['+cast(year(@SEARCHTIME)-1 as nvarchar(100))+'年累计]
from AllShopId
left join LastYear_Month on AllShopId.ShopId=LastYear_Month.ShopId
'+@cteInnerJoin+
'
left join ThisYearAll on ThisYearAll.ShopId=AllShopId.ShopId
left join LastYearAll on LastYearAll.ShopId=AllShopId.ShopId
'exec sp_executesql @FirstMonthToNowSQL