January 26, 2010 at 1:32 am
Problem:
I have the following tables:
1. Patient (ID, Name, Sex, Age, City (FK))
2. Clinical Record (PatientID, Visit, Diagnoses, IsUrgentCase, VisitDate)
And I need to fill out the following report:
From [date1] to [date2]
City - Male - Female - U18 - Male_UrgentCase - Female_UrgentCase
--- sample data ----
AA - 100 - 80 - 40 - 10 - 20
BB - 80 - 80 - 24 - 10 - 8
where
Male : Number of male patients
Female : Number of female patients
U18 : Number of U18 patients
Male_UrgentCase: Number of male urgent case
Female_UrgentCase: Number of female urgent case
My first idea is that:
- Create the query for subreports (City - Male - Female), (City - U18) and (City - Male_Urgent - Female_Urgent)
- Then join them all together.
How do you think about this solution? Please help me to optimize this.
Thanh Tung
January 26, 2010 at 2:01 am
I'm not sure if you want help writing a query or optimising an existing one.
If you want help writing a query, please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses. http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you want help optimising an existing query, then please post the query, table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 26, 2010 at 3:23 am
@Gail Shaw: Thank you for your advice.
Here is the table for the problem:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Patient','U') IS NOT NULL
DROP TABLE #Patient
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Record','U') IS NOT NULL
DROP TABLE #Record
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT YMD
--===== Create the test table with
CREATE TABLE #Patient (
[ID] [int] PRIMARY KEY CLUSTERED ,
[Age] [int],
[IsMale] [bit],
[CityCode] [varchar] (4),
)
--===== Create the test table with
CREATE TABLE #Record (
[PatientID] [int],
[Visit] [int],
[IsUrgent] [bit],
[VisitDate] [datetime]
CONSTRAINT [PK_Record] PRIMARY KEY CLUSTERED
(
[PatientID],
[Visit]
) ON [PRIMARY] ,
CONSTRAINT [FK_Record_Patient] FOREIGN KEY
(
[PatientID]
) REFERENCES [Patient] (
[ID])
)
INSERT INTO #Patient
(ID, Age, IsMale, CityCode)
SELECT '1', '20', '0', 'AA' UNION ALL
SELECT '2','30', '1', 'CC' UNION ALL
SELECT '3','15', '1', 'CC' UNION ALL
SELECT '4','40', '1', 'AA' UNION ALL
SELECT '5','50', '1', 'BB' UNION ALL
SELECT '6','80', '0', 'AA' UNION ALL
SELECT '7','12', '0', 'CC' UNION ALL
SELECT '8','8', '0', 'BB'
INSERT INTO #Record
(PatientID, Visit, IsUrgent, VisitDate)
SELECT '1', '1', '0', '2009-01-01' UNION ALL
SELECT '1', '2', '1', '2009-04-19' UNION ALL
SELECT '1', '3', '1', '2009-11-01' UNION ALL
SELECT '2', '1', '0', '2009-01-11' UNION ALL
SELECT '3', '1', '0', '2009-12-03' UNION ALL
SELECT '4', '1', '0', '2009-10-22' UNION ALL
SELECT '5', '1', '0', '2009-09-15' UNION ALL
SELECT '6', '1', '0', '2009-08-12' UNION ALL
SELECT '7', '1', '0', '2009-03-20' UNION ALL
SELECT '8', '1', '0', '2009-05-01' UNION ALL
SELECT '4', '2', '0', '2010-01-11' UNION ALL
SELECT '3', '2', '0', '2010-01-05' UNION ALL
SELECT '5', '2', '1', '2010-01-10' UNION ALL
SELECT '6', '2', '0', '2010-01-12'
Select CityCode,
COUNT(CASE IsMale WHEN 1 THEN 1 END) AS 'SL_Nam',
COUNT(CASE IsMale WHEN 0 THEN 1 END) AS 'SL_Nu'
From #Patient inner join #Record on #Patient.ID = #Record.PatientID
GROUP BY CityCode
Select CityCode,
COUNT (ID)
From #Patient inner join #Record on #Patient.ID = #Record.PatientID
WHERE Age<18
GROUP BY CityCode
SELECT CityCode,
COUNT(CASE IsMale WHEN 1 THEN 1 END) AS 'SL_Nam_NCD',
COUNT(CASE IsMale WHEN 0 THEN 1 END) AS 'SL_Nu_NCD'
FROM #Patient inner join #Record on #Patient.ID = #Record.PatientID
WHERE (IsUrgent = 1)
GROUP BY CityCode
--============ Desired output ===============
From 2009-01-01 to 2010-01-20
CityCode Male Female U18 Male_Urgent Female_Urgent
--------------------------------------------------
AA 2 5 0 0 2
BB 2 1 1 1 0
CC 3 1 3 0 0
-----------------------------------------------
where
Male: Number male patient records
Female: Number female patient records
U18: Number U18 patient records
Male_Urgent : Number of male patient - urgent records
Female_Urgent : Number of female patient - urgent records
-------------------------------------------------------------
Please help me to write the query to fill out the report above.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply