SQL Basics

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesSQL Basics

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old January 7th, 2016, 05:17 PM
kawi6rr kawi6rr is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Jun 2011
Posts: 4 kawi6rr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 8 m 43 sec
Reputation Power: 0
Help with a create table query

I'm getting a syntax error at the create table portion and can't figure out how to fix it. I bolded where it's erroring.

Code:
WITH cred_date 
AS(SELECT
		pract_id
		,MIN( COALESCE(date_on_staff, startdate) ) AS initial_cred_date
	FROM [provider].[tbl_msow_practitioner_facilities]
	GROUP BY pract_id
CREATE TABLE NCQA.NCQA_dataPull 
AS
(SELECT
	fac_info.pract_id
	,fac_info.last_name
	,fac_info.first_name
	,fac_info.middle_initial
	,fac_info.degree
	,fac_info.id_number AS provider_number
	,fac_info.faccode
	,fac_info.current_status
	,fac_info.status_category
	,fac_info.status_from_date
	,fac_info.department_name  AS department
	,fac_info.section_name AS section
	,fac_info.expertise
	,cred_date.initial_cred_date
	,CASE WHEN current_status NOT IN ('Active', 'Provisional', 'Inactive')
		THEN status_from_date
	 END AS final_term_date
FROM (
	SELECT
		prac.pract_id
		,prac.last_name
		,prac.first_name
		,prac.middle_initial
		,prac.degree
		,prac.id_number
		,fac.faccode
		,fac.current_status
		,fac.status_category
		,fac.status_from_date
		,fac.department_name
		,fac.section_name
		,fac.expertise
		,ROW_NUMBER() OVER 
			(PARTITION BY prac.pract_id 
				ORDER BY
				/*	The following CASE statements represent an example of logic for 
					prioritizing a single facility to pull information from.  Actual 
					requirements may be different!  */
				CASE 
					WHEN current_status = 'Active' THEN 1
					WHEN current_status = 'Provisional' THEN 2
					WHEN current_status = 'Inactive' THEN 3
					ELSE 4
				END
				/*	For non-Active practitioners we choose the facility with the latest
					termination date  */
				,CASE 
					WHEN current_status NOT IN ('Active', 'Provisional', 'Inactive')
							THEN status_from_date
				END DESC
				,CASE 
					WHEN fac.status_category = 'Staff' THEN 1
					WHEN fac.status_category = 'Pro Tem' THEN 2
					ELSE 3
					END
				,CASE 
					WHEN fac.faccode = 'KFH' THEN 1
					WHEN fac.faccode = 'HON' THEN 2
					WHEN fac.faccode = 'WAI' THEN 3
					WHEN fac.faccode = 'AMB' THEN 4
				END ) fac_order
						
	FROM [provider].[tbl_msow_practitioner] prac
		INNER JOIN [provider].[tbl_msow_practitioner_facilities] fac ON prac.pract_id = fac.pract_id
	WHERE fac.faccode IN ('KFH', 'HON', 'WAI', 'AMB')
		AND current_status IN ('Active', 'Provisional', 'Inactive', 'Deceased', 'Provisional', 'Retired', 'Terminated')
	) fac_info
	LEFT JOIN cred_date ON fac_info.pract_id = cred_date.pract_id
WHERE fac_info.fac_order = 1
ORDER BY fac_info.last_name, fac_info.first_name, fac_info.middle_initial)
;

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesSQL Basics > Help with a create table query


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap