Creating separate date columns with a union

Transact-SQL
1

Hi Everyone,

I am new to the forum and I hope I can help others in the future.
Anyway, I have a 3 table union . I need to be able to create additional columns with different date ranges

SELECT x.ACCOUNT_NAME, X.STATUS_CODE, COUNT(X.PATIENT_CODE) AS FY14

FROM

(SELECT
PD.NAME_LAST + ' ' + PD.NAME_FIRST AS ACCOUNT_NAME, PD.STATUS_CODE, PD.PATIENT_CODE
FROM PT_Prospect_Date PD
WHERE PD.NAME_LAST IS NOT NULL
AND PD.NAME_LAST IS NOT NULL
AND PD.STATUS_CODE LIKE 'P02'

   UNION

SELECT
RB.ORGANIZATION_NAME, PD.STATUS_CODE, PD.PATIENT_CODE
FROM PT_Prospect_Date PD INNER JOIN
RES_BASIC RB ON RB.RESOURCE_ID = PD.ASSOCIATED_FACILITY_ID
WHERE RB.ORGANIZATION_NAME IS NOT NULL
AND PD.STATUS_CODE LIKE 'P%'

   UNION

SELECT
CASE WHEN PD.NAME_FULL IS NOT NULL THEN 'Consumer' END , PD.STATUS_CODE, PD.PATIENT_CODE
FROM PT_Prospect_Date PD
WHERE PD.STATUS_CODE LIKE 'P01'

   ) X INNER JOIN PT_Prospect_Date PD ON X.PATIENT_CODE = PD.PATIENT_CODE

WHERE

PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-07-01',120) AND CONVERT(DATETIME,'2014-06-30',120)
AND X.STATUS_CODE LIKE 'P%'


GROUP BY
X.STATUS_CODE,
X.ACCOUNT_NAME--,	


ORDER BY
X.STATUS_CODE

right now I have 1 column date range referring to FY14
I would need to create 5 additional date ranges but I am unsure how I would do this since my first date range is occupying the variable that is needed to count.

Sorry in advance if I didn't give enough detail

Thanks again I look forward to using the forum

M

2

can you give us an example of what you want for output?

3

I am guessing something like this, but like djj55 said, if you have some sample input data and corresponding expected output, that would help

SELECT * FROM 
(
	SELECT x.Account_Name, x.Status_Code, x.Patient_code , 
		DATEPART(YEAR,DATEADD(MONTH,6,PD.PROSPECT_ADMIT_DATE)) AS FiscalYear
	FROM
	(
		-- your union query here
	) X INNER JOIN PT_Prospect_Date PD ON X.PATIENT_CODE = PD.PATIENT_CODE
		WHERE

		PD.PROSPECT_ADMIT_DATE >= '2013001' AND PD.PROSPECT_ADMIT_DATE < '20160701' -- for FY 2014-2016
		AND X.STATUS_CODE LIKE 'P%'
) s
PIVOT(COUNT(Patient_Code) FOR FiscalYear IN ([2014],[2015],[2016])) P
4

The output:

ACCOUNT_NAME| STATUS_CODE| FY14| FY15 | Prior year,Current Month| this year, current month|
Consumer P01 1155
xxxxxxxx P02 1
xxxxxxxx P02 1
xxxxxxxx P02 1
xxxxxxxx P02 1
xxxxxxxx P02 1
xxxxxxxx P02 1
xxxxxxxx P02 1

I am trying pull the number of COUNT(X.PATIENT_CODE) with each date column
The fiscal years start on July to June so fy14 would be july2013 to june 2014
Sorry about late reply