February 22, 2007 at 7:56 am
Hello
I'm trying to add a median column to a table of aggregates, e.g. a pivot table or a cube. I've got a detail table with the fields PATIENT, WARDNAME, DATE, and LENGTH OF STAY. I would like to calculate the median length of stay for each ward and each day (i.e. the median for ward x on day y = z), and include it as an additional field in the detail table. I can calculate the median of all the records in the table, but I can't figure out how to calculate it for each combination of WARDNAME and DATE. Does anyone know how to do this?
Thanks
Tom
February 22, 2007 at 4:42 pm
Try this:
--
SET NOCOUNT ON
-- Create Temp Tables
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Detail]
Create Table [dbo].Detail
(PATIENT varchar(50) NULL, WARDNAME varchar(50) NULL, [DATE] DateTime NULL,
LENGTHSTAY INT NULL, Median Float NULL)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TmpDetail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TmpDetail]
Create Table [dbo].TmpDetail
([ID] INT Identity(1,1),WARDNAME varchar(50) NULL, [DATE] DateTime NULL,
LENGTHSTAY INT NULL)
-- Data population
INSERT INto Detail
Select 'abc','Ward1', '2007-01-01', 5,NULL UNION ALL
Select 'abd','Ward1', '2007-01-01', 10,NULL UNION ALL
Select 'abe','Ward1', '2007-01-01', 6,NULL UNION ALL
Select 'abf','Ward1', '2007-01-01', 2,NULL UNION ALL
Select 'abg','Ward1', '2007-01-01', 3,NULL UNION ALL
Select 'abh','Ward2', '2007-01-01', 5,NULL UNION ALL
Select 'abi','Ward2', '2007-01-01', 4,NULL UNION ALL
Select 'abj','Ward2', '2007-01-01', 3,NULL UNION ALL
Select 'abk','Ward3', '2007-01-01', 2,NULL UNION ALL
Select 'abl','Ward3', '2007-01-01', 5,NULL UNION ALL
Select 'abm','Ward3', '2007-01-01', 3,NULL UNION ALL
Select 'abn','Ward3', '2007-01-01', 4,NULL UNION ALL
Select 'abo','Ward3', '2007-01-01', 5,NULL UNION ALL
Select 'abp','Ward3', '2007-01-01', 6,NULL UNION ALL
Select 'abq','Ward3', '2007-01-01', 2,NULL UNION ALL
Select 'abr','Ward4', '2007-01-01', 5,NULL UNION ALL
Select 'abs','Ward4', '2007-01-01', 4,NULL
-- Local vars
DECLARE @WARDNAME Varchar(50)
DECLARE @DATE DateTime
DECLARE @MidRow INT
-- Cursor for Median calculation
DECLARE Median_Cursor CURSOR FOR
SELECT Distinct WARDNAME, [DATE]
FROM Detail
ORDER BY WARDNAME, [DATE]
OPEN Median_Cursor
FETCH NEXT FROM Median_Cursor INTO @WARDNAME, @DATE
WHILE @@FETCH_STATUS = 0
BEGIN
-- Clean up the temp table
Truncate Table [dbo].TmpDetail
--Print @WARDNAME + Cast(@DATE as varchar(11))
-- Select only for the day for the ward
Insert Into [dbo].TmpDetail
SELECT WARDNAME, [DATE], LENGTHSTAY
FROM Detail
WHERE WARDNAME=@WARDNAME and [DATE]=@DATE
ORDER BY WARDNAME, [DATE], LENGTHSTAY
SELECT @MidRow = (Count(*)+1)/2 FROM [dbo].TmpDetail
-- prevent trivial error
IF @MidRow=0
SET @MidRow=1
Update [dbo].Detail
SET [Median] = (SELECT LENGTHSTAY FROM [dbo].TmpDetail WHERE [ID]= @MidRow)
WHERE WARDNAME=@WARDNAME and [DATE]=@DATE
FETCH NEXT FROM Median_Cursor INTO @WARDNAME, @DATE
END
CLOSE Median_Cursor
DEALLOCATE Median_Cursor
SELECT * FROM [dbo].Detail
February 23, 2007 at 2:33 am
An excellent method, thank you.
Tom
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply