/****** Object:  StoredProcedure [dbo].[GetCommonGroupByRegion]    Script Date: 03/23/2017 17:31:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc  [dbo].[GetCommonGroupByRegion]@CarCategory  varchar(50)=null,---车型@CreateTimeStart datetime=null,  --入会开始时间@CreateTimeEnd datetime=null,    --入会结束时间@AuthenticationTimeStart datetime=null,    --认证开始时间@AuthenticationTimeEnd datetime=null,    --认证结束时间@BuyTimeStart datetime=null,    --购车开始时间@BuyTimeEnd datetime=null    --购车结束时间asbeginDECLARE @strPC VARCHAR(2000), @strAPP VARCHAR(2000), @strWX VARCHAR(2000), @strWAP VARCHAR(2000)---------------------pc startSET @strPC = ( SELECT DISTINCT STUFF(    ( SELECT ‘,‘‘‘+ qudao+‘‘‘‘ FROM CreatedType WITH ( NOLOCK )      WHERE   qtype = ‘pc‘ ORDER BY qudao      FOR XML PATH(‘‘)), 1,4, ‘‘) AS jzhw    FROM   CreatedType t) set @strPC= left(@strPC,len(@strPC)-1)-------------------------pc end***********------------------------app startSET @strAPP = ( SELECT DISTINCT STUFF(    ( SELECT ‘,‘‘‘+ qudao+‘‘‘‘ FROM CreatedType WITH ( NOLOCK )      WHERE   qtype = ‘app‘ ORDER BY qudao      FOR XML PATH(‘‘)), 1,4, ‘‘) AS jzhw    FROM   CreatedType t) set @strAPP= left(@strAPP,len(@strAPP)-1)------------------------- app end *******------------------------wx startSET @strWX = ( SELECT DISTINCT STUFF(    ( SELECT ‘,‘‘‘+ qudao+‘‘‘‘ FROM CreatedType WITH ( NOLOCK )      WHERE   qtype = ‘wx‘ ORDER BY qudao      FOR XML PATH(‘‘)), 1,4, ‘‘) AS jzhw    FROM   CreatedType t) set @strWX= left(@strWX,len(@strWX)-1)------------------------- wx end *******------------------------wap startSET @strWAP = ( SELECT DISTINCT STUFF(    ( SELECT ‘,‘‘‘+ qudao+‘‘‘‘ FROM CreatedType WITH ( NOLOCK )      WHERE   qtype = ‘wap‘ ORDER BY qudao      FOR XML PATH(‘‘)), 1,4, ‘‘) AS jzhw    FROM   CreatedType t) set @strWAP= left(@strWAP,len(@strWAP)-1)------------------------- wap end ******* select   c.CarCategory as ‘车型‘, d.Region as ‘区域‘, COUNT(case when a.CreatedPerson like ‘%D%‘ and  len(a.CreatedPerson)=5 then a.Id else null end ) as ‘经销商‘, COUNT(case when a.CreatedPerson in( @strPC)  then a.Id else null end ) as ‘网站‘, COUNT(case when a.CreatedPerson in(@strAPP) then a.Id else null end ) as ‘APP‘, COUNT(case when a.CreatedPerson in(@strWX) then a.Id else null end ) as ‘微信‘,  COUNT(case when a.CreatedPerson in( @strWAP) then a.Id else null end ) as ‘wap‘  from      Membership  a left join IF_Customer b  on a.IdentityNumber=b.IdentityNumber left join IF_Car  c on b.CustId=c.CustId left join CS_CarDealerShip  d on c.DealerId=d.DealerId where  a.IsDel=0    --case when @CreateTimeStart is not null then  and a.CreateTime>@CreateTimeStart else null end  --and ( (@CreateTimeStart is not null and @CreateTimeStart!=‘‘) and (a.CreateTime>@CreateTimeStart) ) --and ( (@CreateTimeEnd is not null and @CreateTimeEnd!=‘‘) and (a.CreateTime<=@CreateTimeEnd) )  and (a.CreateTime>=@CreateTimeStart or @CreateTimeStart is null)  and (a.CreateTime<=@CreateTimeEnd or @CreateTimeEnd is null)   --and ( (@AuthenticationTimeStart is not null and @AuthenticationTimeStart!=‘‘) and (a.AuthenticationTime>@AuthenticationTimeStart) ) --and ( (@AuthenticationTimeEnd is not null and @AuthenticationTimeEnd!=‘‘) and (a.AuthenticationTime<=@AuthenticationTimeEnd) )   and (a.AuthenticationTime>=@AuthenticationTimeStart or @AuthenticationTimeStart is null)  and (a.AuthenticationTime<=@AuthenticationTimeEnd or @AuthenticationTimeEnd is null)    --and ( (@CarCategory is not null and @CarCategory!=‘‘) and (c.CarCategory=@CarCategory) ) and (c.CarCategory=@CarCategory or @CarCategory is null )   --and ( (@BuyTimeStart is not null and @BuyTimeStart!=‘‘) and ( c.BuyTime>@BuyTimeStart) ) --and ( (@BuyTimeEnd is not null and @BuyTimeEnd!=‘‘) and ( c.BuyTime<=@BuyTimeEnd) )  and (c.BuyTime>=@BuyTimeStart or @BuyTimeStart is null)  and (c.BuyTime<=@BuyTimeEnd or @BuyTimeEnd is null)    and d.Region<>‘‘ and d.Region<>‘-‘ group by  c.CarCategory, d.Region  ;  end