Having columns based on date conditions

Transact-SQL
1

Hi everyone,

What I am trying to accomplish is to add an additional column with FY 2015
the fiscal year is ran in between 2 years

example FY2014 starts on 2013-07-01 and ends on 2014-06-30

I am able to display the correct figure for FY2014 but I am trying to
add FY2015 as well with dates ranging - start on 2014-07-01 and ends on 2015-06-30

I am new to the forum so I am sorry in advance if I am not being descriptive enough if you require any more information
I will post asap

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

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

Thanks Again!

Matt

2

Not sure but how about

SELECT x.ACCOUNT_NAME, X.STATUS_CODE, 
SUM(case when PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2013-07-01',120) AND CONVERT(DATETIME,'2014-06-30',120) then 1 else 0 end) AS FY2014, 
SUM(case when PD.PROSPECT_ADMIT_DATE BETWEEN CONVERT(DATETIME,'2014-07-01',120) AND CONVERT(DATETIME,'2015-06-30',120) then 1 else 0 end) AS FY2015 

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 1 = 1
AND X.STATUS_CODE LIKE 'P%'


GROUP BY
X.STATUS_CODE,
X.ACCOUNT_NAME	


ORDER BY
X.STATUS_CODE
1 Like
3

Looks great! but some null values are being shown for codes p02 and other P codes
how would I remove the rows that are 0 in both FY2014 and FY2015 because its creating unnecessary rows

4

SELECT x.ACCOUNT_NAME, X.STATUS_CODE,
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) AS FY2014,
SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) AS FY2015

FROM

(SELECT
PD.NAME_LAST + ' ' + PD.NAME_FIRST AS ACCOUNT_NAME, PD.STATUS_CODE, PD.PATIENT_CODE , PD.PROSPECT_ADMIT_DATE
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, PD.PROSPECT_ADMIT_DATE
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 , PD.PROSPECT_ADMIT_DATE
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 >= '20130701' AND PD.PROSPECT_ADMIT_DATE < '20150701'
AND X.STATUS_CODE LIKE 'P%'

GROUP BY
X.STATUS_CODE,
X.ACCOUNT_NAME

ORDER BY
X.STATUS_CODE

1 Like
5

Watch out for BETWEEN with dates.

SELECT MyDateColumn
 , [Between] = CASE WHEN MyDateColumn BETWEEN CONVERT(DATETIME,'2013-07-01',120) 
					AND CONVERT(DATETIME,'2014-06-30',120) 
		THEN 'Yes' ELSE 'No' END
 , [Range] = CASE WHEN MyDateColumn >= CONVERT(DATETIME,'2013-07-01',120) 
			AND MyDateColumn < CONVERT(DATETIME,'2014-07-01',120)	-- EndDate + 1
		THEN 'Yes' ELSE 'No' END
FROM
(
	SELECT [MyDateColumn] = CONVERT(datetime, '20130630 23:59:59') -- Too early
	UNION ALL SELECT '20130701 00:00:00'
	UNION ALL SELECT '20140630 00:00:00' -- Within BETWEEN range
	UNION ALL SELECT '20140630 00:00:01' -- Outside BETWEEN range!!
	UNION ALL SELECT '20140701 00:00:00' -- Outside intended range
) AS X

Results:

MyDateColumn            Between Range 
----------------------- ------- ----- 
2013-06-30 23:59:59.000 No      No
2013-07-01 00:00:00.000 Yes     Yes
2014-06-30 00:00:00.000 Yes     Yes
2014-06-30 00:00:01.000 No!!!!  Yes
2014-07-01 00:00:00.000 No      No
1 Like
6

This is great work Scott really open my eyes to the different possibilities
Thank you!

7

Kristen good point! I see how data can be skewed and alter results
Thank you!

8

D'OH, sorry, I forgot about removing the 0 and 0 rows. Add a HAVING clause after the GROUP BY:

...
GROUP BY
...

HAVING NOT ( SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2014 THEN 1 ELSE 0 END) = 0 AND
             SUM(CASE WHEN YEAR(DATEADD(MONTH, 6, PD.PROSPECT_ADMIT_DATE)) = 2015 THEN 1 ELSE 0 END) = 0 )
1 Like