August 21, 2015 at 8:05 am
Hi everyone,
This is my first post and I will try to be as descriptive as possible
What I am trying to accomplish is to make a few extra columns with specified date ranges
I have FY14 date range in the parameters at the top .. I would like to add a FY 15 column so the year would move up by 1.
and also I need to add 2 more columns Prior year current month and This year, current month
Sorry I am kinda new and any tips would be appreciated
<code>
DECLARE @Fy14_start datetime
DECLARE @Fy14_end datetime
SET @Fy14_start = '2013-07-01'
SET @Fy14_end = '2014-06-30'
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 >= @Fy14_start AND PD.PROSPECT_ADMIT_DATE <= @Fy14_end
AND X.STATUS_CODE LIKE 'P%'
GROUP BY
X.STATUS_CODE,
X.ACCOUNT_NAME
ORDER BY
X.STATUS_CODE
</code>
sample output
ACCOUNT_NAME | STATUS_CODE | FY14 | (FY15) | (prior year, current month) | (this year, current month) |
xxxxxx P01 12
xxxxxx P03 3
Thanks Again!
look forward to being an active member
--
M
August 21, 2015 at 8:16 am
Hi ...welcome to SSC
as a start, I would suggest reading this article below in how to provide us with an easy way to see what you are doing and this also helps us both in working with the same set of data. We cant see over you shoulder and see your data...so by providing some sample set ups will help us to help you
good luck
http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 21, 2015 at 8:43 am
Hi and welcome to the forums.
As J Livingston mentioned, we need some sample data to give a good and tested advice. It's important that this sample data is posted as DDL and INSERT statements (previously tested) so we can simply copy, paste and execute so we won't waste time preparing everything.
That said, you seem to need some help with a cross tabs query and some date calculations. You can read more about cross tabs in here: http://www.sqlservercentral.com/articles/T-SQL/63681/
You can also check some data calculations in here: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
I made a guess on your query to show what it might look like. Be sure to understand it and check the changes made to correct any errors that might exist due to the lack of testing.
DECLARE @Fy14_start datetime
DECLARE @Fy14_end datetime
SET @Fy14_start = '2013-07-01'
SET @Fy14_end = '2014-06-30'
SELECT x.ACCOUNT_NAME,
X.STATUS_CODE,
COUNT( CASE WHEN PD.PROSPECT_ADMIT_DATE >= @Fy14_start
AND PD.PROSPECT_ADMIT_DATE < DATEADD( DD, 1, @Fy14_end)
THEN X.PATIENT_CODE END) AS FY14,
COUNT( CASE WHEN PD.PROSPECT_ADMIT_DATE >= DATEADD( YY, 1, @Fy14_start)
AND PD.PROSPECT_ADMIT_DATE < DATEADD( YY, 1, DATEADD( DD, 1, @Fy14_end))
THEN X.PATIENT_CODE END) AS FY15,
COUNT( CASE WHEN PD.PROSPECT_ADMIT_DATE >= DATEADD( MM, DATEDIFF(MM, 0, GETDATE()) - 12, 0)
AND PD.PROSPECT_ADMIT_DATE < DATEADD( MM, DATEDIFF(MM, 0, GETDATE()) - 11, 0)
THEN X.PATIENT_CODE END) AS [prior year current month],
COUNT( CASE WHEN PD.PROSPECT_ADMIT_DATE >= DATEADD( MM, DATEDIFF(MM, 0, GETDATE()), 0)
AND PD.PROSPECT_ADMIT_DATE < DATEADD( MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)
THEN X.PATIENT_CODE END) AS [this year current month]
FROM
(SELECT CASE WHEN PD.STATUS_CODE = 'P02'
THEN PD.NAME_LAST + ' ' + PD.NAME_FIRST
WHEN PD.STATUS_CODE = 'P01' AND PD.NAME_FULL IS NOT NULL
THEN 'Consumer'
END 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 IN( 'P02', 'P01')
UNION
SELECT RB.ORGANIZATION_NAME,
PD.STATUS_CODE,
PD.PATIENT_CODE
FROM PT_Prospect_Date PD
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%'
) X
JOIN PT_Prospect_Date PD ON X.PATIENT_CODE = PD.PATIENT_CODE
WHERE PD.PROSPECT_ADMIT_DATE >= @Fy14_start
AND PD.PROSPECT_ADMIT_DATE <= DATEADD( YY, 1, @Fy14_end)
--AND X.STATUS_CODE LIKE 'P%'--This is not needed. The conditions are set in the derived table
GROUP BY X.STATUS_CODE,
X.ACCOUNT_NAME
ORDER BY X.STATUS_CODE;
Post any questions that you might have.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply