November 7, 2012 at 12:32 pm
Hello,
When i run the below script is am getting the total percentage from 2012-Apr-01 to currentdate, which is Numerator/Denomeatorx100
Can i know how to generate the percentage for each month from APR2012 TO CURRENT againaist each ward into a new table?
Ward Apr 12 May 12 June 12 July 12…
APP 60%
APB 55%
APM 49%
…
SELECT xx.EDD/(select CAST(count(*) as Int) as "TEP" from
(select admission_number, admission_date, discharge_date ,estimated_discharge_date, create_date, DIM_IP_ADMISSIONS.update_date,CURRENT_WARD_CODE,current_flag
from DIM_IP_ADMISSIONS
WHERE DATEDIFF(YEAR,person_date_of_birth,ADMISSION_DATE) >=65
and person_deceased = 'N'
and visit_status in ('A', 'D')
and CURRENT_WARD_CODE in ('APP','APB''APM','MTR','MFT')
and admission_date between '2012-04-01 00:00:00.000' and GETDATE()
and current_flag ='y') as y
)
FROM
(select CAST(count(*) as Int)*100 as "EDD" from
(select DISTINCT A.ADMISSION_NUMBER
from DIM_IP_ADMISSIONS A
INNER JOIN
(select * from DIM_IP_ADMISSIONS B
where B.current_flag ='Y') B
ON A.ADMISSION_NUMBER =B.ADMISSION_NUMBER
WHERE
A.estimated_discharge_date IS NOT NULL
and isnull(A.update_date,A.create_date)<= A.admission_date + 2
and DATEDIFF(YEAR,A.person_date_of_birth,A.ADMISSION_DATE) >=65
and A.person_deceased = 'N'
and A.visit_status in ('A', 'D')
and A.CURRENT_WARD_CODE in ('APP','APB''APM','MTR','MFT')
and A.admission_date between '2012-04-01 00:00:00.000' and getdate()) as x) as xx
November 7, 2012 at 12:57 pm
Please see the first link in my signature about best practices when posting questions. From what you posted nobody can run any code and we have no idea what you are trying to do. Once you post some ddl, sample data and desired output in accordance with the best practices you will find lots of people jumping in to help you very quickly.
In the meantime I took the liberty of formatting your code so it is more legible. You can put code inside the code shortcuts (IFCode on the left side when posting).
SELECT xx.EDD / (
SELECT CAST(count(*) AS INT) AS "TEP"
FROM (
SELECT admission_number
,admission_date
,discharge_date
,estimated_discharge_date
,create_date
,DIM_IP_ADMISSIONS.update_date
,CURRENT_WARD_CODE
,current_flag
FROM DIM_IP_ADMISSIONS
WHERE DATEDIFF(YEAR, person_date_of_birth, ADMISSION_DATE) >= 65
AND person_deceased = 'N'
AND visit_status IN (
'A'
,'D'
)
AND CURRENT_WARD_CODE IN (
'APP'
,'APB''APM'
,'MTR'
,'MFT'
)
AND admission_date BETWEEN '2012-04-01 00:00:00.000'
AND GETDATE()
AND current_flag = 'y'
) AS y
)
FROM (
SELECT CAST(count(*) AS INT) * 100 AS "EDD"
FROM (
SELECT DISTINCT A.ADMISSION_NUMBER
FROM DIM_IP_ADMISSIONS A
INNER JOIN (
SELECT *
FROM DIM_IP_ADMISSIONS B
WHERE B.current_flag = 'Y'
) B ON A.ADMISSION_NUMBER = B.ADMISSION_NUMBER
WHERE A.estimated_discharge_date IS NOT NULL
AND isnull(A.update_date, A.create_date) <= A.admission_date + 2
AND DATEDIFF(YEAR, A.person_date_of_birth, A.ADMISSION_DATE) >= 65
AND A.person_deceased = 'N'
AND A.visit_status IN (
'A'
,'D'
)
AND A.CURRENT_WARD_CODE IN (
'APP'
,'APB''APM'
,'MTR'
,'MFT'
)
AND A.admission_date BETWEEN '2012-04-01 00:00:00.000'
AND getdate()
) AS x
) AS xx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 1:08 pm
Thanx Sean,
At present the script will just calculate Numerator/Denomenatorx100 from 2012-apr-01 to current date.
I am looking to split the result for each individual month aganist each ward.
Sample Output should be
Ward Apr12 May12 June12 July12 Aug12 Sep12 Oct12 Nov12
APP 69% 56% 70% -- -- -- --- ---
ABP 56% -- --- --
--
--
--
November 7, 2012 at 1:12 pm
sa06rdy (11/7/2012)
Thanx Sean,At present the script will just calculate Numerator/Denomenatorx100 from 2012-apr-01 to current date.
I am looking to split the result for each individual month aganist each ward.
Sample Output should be
Ward Apr12 May12 June12 July12 Aug12 Sep12 Oct12 Nov12
APP 69% 56% 70% -- -- -- --- ---
ABP 56% -- --- --
--
--
--
Group by? Where?
I can't begin to help because I have no idea what the tables look like. Remember that we can't see your screen and we are not familiar with your project or data. Again...Please see the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 7, 2012 at 1:38 pm
Hi,
EDD script gives the Distinct Admission numbers from the admissions table from apr2012 to current which are satisfying the conditions given.
TEP gives the total Patients from apr2012 to current with the conditions given
EDD
----X100
TEP
now i need to group by Admission Number and spliting by each month by each ward
No of patients admitted into each ward during the each month satisfying the conditions.
November 7, 2012 at 1:49 pm
sa06rdy (11/7/2012)
Hi,EDD script gives the Distinct Admission numbers from the admissions table from apr2012 to current which are satisfying the conditions given.
TEP gives the total Patients from apr2012 to current with the conditions given
EDD
----X100
TEP
now i need to group by Admission Number and spliting by each month by each ward
No of patients admitted into each ward during the each month satisfying the conditions.
Add Admission Number to the query and group by Admission Number.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply