Common Table Expressions

Hi guys,

I know its not a good idea to give you a big query...but/...

I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...

 

----------------------------------------------

DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)

SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS

(SELECT
 dbo.tmpValidServices_ALL.SiteID,
 dbo.tmpValidServices_ALL.Programme,
 dbo.tmpValidServices_ALL.Complete AS Complete,
 dbo.tmpValidServices_ALL.RID AS PID,
 dbo.tmpValidServices_ALL.COC# AS COC,
 (dbo.lkpService.Notional$) AS NotionalSum,
 (TVMins) AS TVMinsSUM,
 dbo.tmpDemographics_ALL.Postcode,
 dbo.tmpdemographics_ALL.Suburb,
                count(*) as Qty

FROM  lkpService

INNER JOIN dbo.tmpValidServices_ALL vs  ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '

IF @COCGroup IS NOT NULL
SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '

SELECT     @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '      

SELECT     @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '

 IF @StartDate IS NOT NULL
     SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete  >= @StartDate ) '

 IF @EndDate IS NOT NULL
      SELECT     @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '


 IF @SiteID IS NOT NULL
       SELECT     @sql = @sql + 'AND (ag.EXACT# = @SiteID) '

 IF @COCGroup IS NOT NULL
 SELECT     @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '

 IF @Provider IS NOT NULL
 SELECT     @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '

 IF @Postcode IS NOT NULL
SELECT     @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '

SELECT     @sql = @sql  + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
                                                              dbo.tmpValidServices_ALL.Programme,
                                                              dbo.tmpValidServices_ALL.Complete ,
                                                              dbo.tmpValidServices_ALL.RID , 
                                                              dbo.tmpValidServices_ALL.COC# ,
                                                              dbo.tmpDemographics_ALL.postcode, 
                                                              dbo.tmpdemographics_ALL.Suburb,
                                                              (dbo.lkpService.Notional$),
                                                              (dbo.lkpService.TVMins)  ) 

SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20))  + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
 COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients, 
 COUNT(DISTINCT  CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
 SUM(NotionalSum) AS NotionalSum,
 SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
 
FROM Q1

Group by Postcode,Suburb

Order by Postcode,Suburb  '

SET              @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
                     
 EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider