With Clause Help

I have this query I'm reorganizing.  When I test it with the last line, I get an error and I can't figure out why.  
Error:
Msg 156, Level 15, State 1, Line 72
Incorrect syntax near the keyword 'With'.


DECLARE
   
-- Moved down from above
            @Submission_period_from_date DATETIME,
            @Submission_period_to_date DATETIME,
            @Submission_Method VARCHAR(1),
/* These variables are required for certification and are calculated in the report */@Population NUMERIC,
        @Measure_group VARCHAR(3),
        @National_Provider_Identifier VARCHAR(12),
        @Eligible_instances_reporting_denominator NUMERIC,
        @Meets_performance_numerator NUMERIC,
        @Performance_exclusion_instances NUMERIC,
        @Performance_not_met_instances NUMERIC,
        @Reporting_numerator NUMERIC,
        @Reporting_rate NUMERIC(5, 2),
        @Performance_rate NUMERIC(5, 2),
/* These variables are hardcoded in the report */
        @RegistryName VARCHAR(100),
        @RegistryID VARCHAR(9),
        @Signed VARCHAR(1),
        @VersionA VARCHAR(20),
        @VersionB VARCHAR(20),
        @FileNumber NUMERIC(5, 0),
        @FileCount NUMERIC(5, 0),
        @Option VARCHAR(20),
        @Tin VARCHAR(12),
        @CreateBy VARCHAR(50),
        @FacilityID VARCHAR(25),
        @SourceID VARCHAR(5),
        @Clinical_quality_measure VARCHAR(50),
        @Measure_number_identifier VARCHAR(12)
/* Hardcoded values to be inputted by hospital report writer and */
/* according to PQRI specifications as defined by hospital staff */
/* see HITSP TN906 V1.1 and PQRI specifications for detailed information */

--added
      SET @Submission_period_from_date = '2012-05-01 00:00:00'
    SET @Submission_period_to_date = '2012-07-31 23:59:59'
    SET @Submission_Method = 'C'
    SET @Measure_group = 'X'
    SET @RegistryName = 'PQRI Registry'
    SET @RegistryID = '123456789'
    SET @Signed = 'Y'
    SET @VersionA = '2.0'
    SET @VersionB = '1.0'
    SET @FileNumber = 1
    SET @FileCount = 1
    SET @Option = 'TEST'
    SET @Tin = '12345-6'
    SET @CreateBy = 'MEDITECH'
    SET @FacilityID = 'ENL'
    SET @SourceID = 'ENL'
    SET @Clinical_quality_measure = 'Antithrombotic Therapy By End of Hospital Day 2 (Stroke 5)'
    SET @Measure_number_identifier = 'NQF 0438'
    SET @National_Provider_Identifier = (SELECT InpatientNpi
                                          FROM DMisNatProviderIds
                                          WHERE SourceID = @SourceID
                                                    AND FacilityID = @FacilityID)
      
-- #Initial population holds the records for all unique inpatients between Submission_period_from-_date through ...to_date
/* collect initial population of inpatients */
With #InitialPopulation
As
(SELECT  *
 FROM    AbstractData
 WHERE   SourceID = @SourceID
 AND PtStatus = ('IN')
 AND DischargeDateTime BETWEEN @Submission_period_from_date
 AND @Submission_period_to_date)

With #Population
As
(SELECT COUNT(*) FROM #InitialPopulation)

Select * from #Population

I can run the query as follows with no problems.  Select * from #InitialPopulation
Scotto123Asked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
the error message itself is very clear

;With #InitialPopulation
As
(SELECT  *
 FROM    AbstractData
 WHERE   SourceID = @SourceID
 AND PtStatus = ('IN')
 AND DischargeDateTime BETWEEN @Submission_period_from_date
 AND @Submission_period_to_date)

, #Population
As
(SELECT COUNT(*) FROM #InitialPopulation) cnt

Select * from #Population
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
For starters, if you're coding a Common Table Expression (CTE) using WITH, then the previous T-SQL statement has to end in a semi-colon ( ; )
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
DECLARE
   
-- Moved down from above
            @Submission_period_from_date DATETIME,
            @Submission_period_to_date DATETIME,
            @Submission_Method VARCHAR(1),
/* These variables are required for certification and are calculated in the report */@Population NUMERIC,
        @Measure_group VARCHAR(3),
        @National_Provider_Identifier VARCHAR(12),
        @Eligible_instances_reporting_denominator NUMERIC,
        @Meets_performance_numerator NUMERIC,
        @Performance_exclusion_instances NUMERIC,
        @Performance_not_met_instances NUMERIC,
        @Reporting_numerator NUMERIC,
        @Reporting_rate NUMERIC(5, 2),
        @Performance_rate NUMERIC(5, 2),
/* These variables are hardcoded in the report */
        @RegistryName VARCHAR(100),
        @RegistryID VARCHAR(9),
        @Signed VARCHAR(1),
        @VersionA VARCHAR(20),
        @VersionB VARCHAR(20),
        @FileNumber NUMERIC(5, 0),
        @FileCount NUMERIC(5, 0),
        @Option VARCHAR(20),
        @Tin VARCHAR(12),
        @CreateBy VARCHAR(50),
        @FacilityID VARCHAR(25),
        @SourceID VARCHAR(5),
        @Clinical_quality_measure VARCHAR(50),
        @Measure_number_identifier VARCHAR(12)
/* Hardcoded values to be inputted by hospital report writer and */
/* according to PQRI specifications as defined by hospital staff */
/* see HITSP TN906 V1.1 and PQRI specifications for detailed information */

--added
      SET @Submission_period_from_date = '2012-05-01 00:00:00'
    SET @Submission_period_to_date = '2012-07-31 23:59:59'
    SET @Submission_Method = 'C'
    SET @Measure_group = 'X'
    SET @RegistryName = 'PQRI Registry'
    SET @RegistryID = '123456789'
    SET @Signed = 'Y'
    SET @VersionA = '2.0'
    SET @VersionB = '1.0'
    SET @FileNumber = 1
    SET @FileCount = 1
    SET @Option = 'TEST'
    SET @Tin = '12345-6'
    SET @CreateBy = 'MEDITECH'
    SET @FacilityID = 'ENL'
    SET @SourceID = 'ENL'
    SET @Clinical_quality_measure = 'Antithrombotic Therapy By End of Hospital Day 2 (Stroke 5)'
    SET @Measure_number_identifier = 'NQF 0438'
    SET @National_Provider_Identifier = (SELECT InpatientNpi
                                          FROM DMisNatProviderIds
                                          WHERE SourceID = @SourceID
                                                    AND FacilityID = @FacilityID)
     
-- #Initial population holds the records for all unique inpatients between Submission_period_from-_date through ...to_date
/* collect initial population of inpatients */
;With #InitialPopulation
As
(SELECT  *
 FROM    AbstractData
 WHERE   SourceID = @SourceID
 AND PtStatus = ('IN')
 AND DischargeDateTime BETWEEN @Submission_period_from_date
 AND @Submission_period_to_date)

, #Population
As
(SELECT COUNT(*) FROM #InitialPopulation)

Select * from #Population
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Scotto123Author Commented:
Added semicolons and now I get different error:

Msg 102, Level 15, State 1, Line 70
Incorrect syntax near ';'.
0
 
Scotto123Author Commented:
Ane,
I tried what you have.  I get this error
Msg 8155, Level 16, State 2, Line 63
No column was specified for column 1 of '#Population'.
0
 
Scotto123Author Commented:
What is cnt?  Doesn't like that.
 
Msg 102, Level 15, State 1, Line 74
Incorrect syntax near 'cnt'.
0
 
Scotto123Author Commented:
Oh - I see.  I changed it to this:

,#Population
As
(SELECT COUNT(*) as CNT FROM #InitialPopulation)

Select * from #Population
0
 
Scotto123Author Commented:
Thanks for your knowledge!
0
All Courses

From novice to tech pro — start learning today.