Should I use a cursor?

RobCarter · June 21st, 2011, 06:15 AM

Hi

I am trying to populate a table with the date range that a patient was on a ward. The result I need to see, if a patient was admitted on the 1st July 10 and discharged on 4th October is:

date ID onWard
01/10/2010 P999999x 1
02/10/2010 P999999x 1
03/10/2010 P999999x 1
04/10/2010 P999999x 1

I need to do this for each patient to show how many people were on the ward at any one time

I only have the start date and end date of their ward stay.

Can anyone give me suggestions on how I can generate the extra rows required, and also to turn it into a function so I can do this for each patient? My mind has turned to mush.

Thanks

Rob

Jeff Moden · June 22nd, 2011, 10:42 AM

Rob,

I'm on my way to work and I can't get to this right now. If you could post, say, 10 rows of test data (in the form of INSERT statements for a table would be really great!), it would save me some time tonight.

This is actually a pretty simple problem and I'll also include some references so that you can make other things like this easy on yourself.

Thanks.

RobCarter · June 22nd, 2011, 11:13 AM

Hi Jeff

I was about to post actually.

I solved the problem using a vb application to loop through the days between 2 fixed points using a dateadd() function and a counter variable.

I have an inline table function that has been in use to find the figures I need for 1 particular date to give me the figures so i loop each individual date into that function and it gives me a collective figure per ward per day.

I would still be interested in the references so I can do this in sql in the future rather than going outside the "box", as it were.

Thanks for your offer of help

Rob

Jeff Moden · June 22nd, 2011, 03:22 PM

Hi Rob,

My recommendation is that, although quite clever, using a VB app for this very simple task is the wrong way to do it.

Again, if you'll post some data as INSERT statements, I'll show you the right way to do it without having to make an excursion to the outside world.

--Jeff Moden

RobCarter · June 23rd, 2011, 06:23 AM

Hi Jeff

Thanks for your response. here is the code. I do have more fields than this but here is the pertinent data

Code:

create table testOccupancy
(
patid varchar(6) not null,
admit_date datetime not null,
discharge_date datetime null,
count int not null
)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X001', '2011-04-01 09:00:00', '2011-04-05 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X002', '2011-04-02 09:00:00', '2011-04-04 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X003', '2011-04-07 09:00:00',  ,1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X004', '2011-04-01 09:00:00', '2011-04-02 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X005', '2011-04-08 09:00:00', '2011-04-10 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X006', '2011-04-04 09:00:00', '2011-04-05 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X007', '2011-04-01 09:00:00', '2011-04-03 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X008', '2011-04-07 09:00:00', '2011-04-10 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X009', '2011-04-02 09:00:00', '2011-04-05 14:00:00',1)
insert into testoccupancy
(patid, admit_date,discharge_date,count)
values ('X010', '2011-04-09 09:00:00', ,1)

I am really interested in this actually.

Let me know if you need any more.

Cheers

Rob

Jeff Moden · June 23rd, 2011, 07:17 AM

Nope. That'll do it. One solution for SQL Server 2000 coming right up. This would be a bit easier in SQL Server 2005 and up but it's relatively simple in SQL Server 2000.

Jeff Moden · June 23rd, 2011, 08:26 AM

Here we go. As always for me, the explanation for everything is in the code. For more information about how a Tally table works, please see the following URL. A Calendar table works in a similar fashion but with dates.
http://www.sqlservercentral.com/articles/T-SQL/62867/

--================================================== ===========================
--　　　　　　Setup.　　This　creates　a　test　table　and　popula tes　it　with　data.
--　　　　　　Nothing　in　this　section　is　a　part　of　the　sol ution.　
--================================================== ===========================
--=====　Do　this　in　a　nice,　safe　place　that　everyone　 has
　　　　USE　tempdb;
--=====　Conditionally　drop　the　test　tables　to　make　r eruns　easier　in　SSMS
　　　　　IF　OBJECT_ID('tempdb.dbo.testOccupancy','U')　IS　NOT　NULL　
　　　　　　　　DROP　TABLE　dbo.testOccupancy;
　　　　　IF　OBJECT_ID('tempdb.dbo.Calendar','U')　IS　NOT　NULL　
　　　　　　　　DROP　TABLE　dbo.Calendar;
　　　　　IF　OBJECT_ID('tempdb.dbo.Tally','U')　IS　NOT　NULL　
　　　　　　　　DROP　TABLE　dbo.Tally;
GO
--=====　Create　the　test　table　using　data　provided　by 　the　OP
　CREATE　TABLE　testOccupancy
　　　　　　　　(
　　　　　　　　PatID　　　　　　　　　　VARCHAR(6)　NOT　NULL,
　　　　　　　　Admit_Date　　　　　DATETIME　　　NOT　NULL,
　　　　　　　　Discharge_Date　DATETIME　　　NULL,
　　　　　　　　Ward　　　　　　　　　　　INT　　　　　　　　NOT　NULL
　　　　　　　　)
;
　INSERT　INTO　dbo.testOccupancy
　　　　　　　　(PatID,　Admit_Date　　　　　　　　　　　,Discharge_Date　　　　　　　,Ward)
　SELECT　'X001',　'2011-04-01　09:00:00','2011-04-05　14:00:00',1　UNION　ALL
　SELECT　'X002',　'2011-04-02　09:00:00','2011-04-04　14:00:00',1　UNION　ALL
　SELECT　'X003',　'2011-04-07　09:00:00',NULL　　　　　　　　　　　　　　　　　,1　UNION　ALL
　SELECT　'X004',　'2011-04-01　09:00:00','2011-04-02　14:00:00',1　UNION　ALL
　SELECT　'X005',　'2011-04-08　09:00:00','2011-04-10　14:00:00',1　UNION　ALL
　SELECT　'X006',　'2011-04-04　09:00:00','2011-04-05　14:00:00',1　UNION　ALL
　SELECT　'X007',　'2011-04-01　09:00:00','2011-04-03　14:00:00',1　UNION　ALL
　SELECT　'X008',　'2011-04-07　09:00:00','2011-04-10　14:00:00',1　UNION　ALL
　SELECT　'X009',　'2011-04-02　09:00:00','2011-04-05　14:00:00',1　UNION　ALL
　SELECT　'X010',　'2011-04-09　09:00:00',NULL　　　　　　　　　　　　　　　　　,1
;
GO
--================================================== ===========================
--　　　　　　Before　we　get　to　solving　your　problem. ..
--　　　　　　Since　you're　going　to　be　working　with　dates　 so　much,　I　strongly
--　　　　　　recommend　building　the　following　2　permanent 　"helper"　tables.
--================================================== ===========================
--=====　Build　a　"Tally"　(Numbers)　table
　SELECT　TOP　73049　--
　　　　　　　　N　=　IDENTITY(INT,0,1)
　　　INTO　dbo.Tally
　　　FROM　Master.dbo.SysColumns　sc1
　　CROSS　JOIN　Master.dbo.SysColumns　sc2
;
　　ALTER　TABLE　dbo.Tally
　　　　ADD　CONSTRAINT　PK_Tally　
　　　　　　　　PRIMARY　KEY　CLUSTERED　(N)　WITH　FILLFACTOR　=　100
;
--=====　Build　the　abbreviated　"Calendar"　(Date)　tabl e　for　1900　to　2100
　　　　　--　This　ISNULL's　are　just　there　to　make　a　NON-NULL　columns
　SELECT　Date　　　　　=　ISNULL(DATEADD(dd,N　　,'1900'),0),
　　　　　　　　NextDate　=　ISNULL(DATEADD(dd,N+1,'1900'),0)
　　　INTO　dbo.Calendar
　　　FROM　dbo.Tally
;
　　ALTER　TABLE　dbo.Calendar
　　　　ADD　CONSTRAINT　PK_Calendar　
　　　　　　　　PRIMARY　KEY　CLUSTERED　(Date,　NextDate)　WITH　FILLFACTOR　=　100
;
--================================================== ===========================
--　　　　　　This　section　is　the　solution　to　the　original 　problem.
--　　　　　　Create　Whole　Date　Ranges　of　individual　days　 using　the　Admit_Date　as　the
--　　　　　　starting　date　and　the　Discharge_Date　as　the　 end　date.
--　　　　　　With　the　help　of　the　Calendar　table,　it　beco mes　child's　play　and　
--　　　　　　it's　nasty　fast.
--================================================== ===========================
--=====　Solve　the　entire　problem　using　a　simple,　ver y　high　performance
　　　　　--　CROSS　JOIN　with　the　Calendar　table.
　　　　　--　This　will　run　comparatively　slow　only　on　the　firs t　time.　　After　that,
　　　　　--　statistics　will　have　built　and　it'll　run　at　near　 machine-language
　　　　　--　speeds.
　SELECT　cal.Date,
　　　　　　　　src.PatID,
　　　　　　　　OnWard　=　src.Ward
　　　FROM　dbo.testOccupancy　src
　　CROSS　JOIN　dbo.Calendar　cal
　　WHERE　src.Discharge_Date　>=　cal.Date
　　　　AND　src.Admit_Date　　　　　<=　cal.NextDate
　　ORDER　BY　src.PatID,　cal.Date
;

RobCarter · June 23rd, 2011, 09:24 AM

Hi Jeff

That's so much easier than the way I did things. Thanks so much.

I'll definitely have to look more into using cross joins in the future. I can think of so many reports that would benefit from this.

Genius

Rob

RobCarter · June 23rd, 2011, 09:27 AM

Hi again Jeff

If its not too much trouble I'd love the SQL 2005 and 2008 solutions or some references concerning the things I need to change.

Thanks again.

Jeff Moden · June 24th, 2011, 08:29 AM

Hi Rob,

There's no difference between 2000 and 2005 if the DBA allows for the Tally and Calendar (which can be expanded for other utility) tables. If the DBA doesn't allow it, then the use of a CTE (non-recursive, of course) to build a Tally Source on the fly to build a #Calendar table does the job nicely.

The reason why I stated that it was an SQL Server 2000 solution was so that people would know that it would work for SQL Server 2000, for sure. The BIG difference between a 2K and 2K5 solution would be which table is used as a Pseudo Cursor Source for the initial build of the Tally Table... in 2k5, you should use sys.all_columns instead of dbo.syscolumns.