Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 21st, 2011, 06:15 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default Should I use a cursor?

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
__________________
Rob Carter
 
Old June 22nd, 2011, 10:42 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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.
__________________
--Jeff Moden
 
Old June 22nd, 2011, 11:13 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

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
__________________
Rob Carter
 
Old June 22nd, 2011, 03:22 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
__________________
--Jeff Moden
 
Old June 23rd, 2011, 06:23 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

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
__________________
Rob Carter

Last edited by RobCarter; June 23rd, 2011 at 06:26 AM..
 
Old June 23rd, 2011, 07:17 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
 
Old June 23rd, 2011, 08:26 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
;
__________________
--Jeff Moden
The Following User Says Thank You to Jeff Moden For This Useful Post:
RobCarter (June 23rd, 2011)
 
Old June 23rd, 2011, 09:24 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Talking

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
__________________
Rob Carter
 
Old June 23rd, 2011, 09:27 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

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.
__________________
Rob Carter
 
Old June 24th, 2011, 08:29 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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.
__________________
--Jeff Moden
The Following User Says Thank You to Jeff Moden For This Useful Post:
RobCarter (June 24th, 2011)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
Regarding Cursor param99 SQL Server 2000 1 September 8th, 2006 10:03 AM
Regarding Cursor param99 SQL Language 0 September 8th, 2006 03:56 AM
concat 2 different cursor sreekesh ADO.NET 0 August 18th, 2006 07:11 AM
cursor trinnie SQL Server 2000 0 August 6th, 2006 09:55 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.