Post reply

Recursive Solution?: Advice needed

  • November 29, 2007 at 9:11 pm

    #184511

    Hi,

    I am seeking some advice on how best to approach a problem I have been asked to solve.

    I need to calculate the 'total number of days' for related admissions for a inpatient hospital stay.

    For example, for the current admission, I need to find the previous admission (if its within seven days) and calculate its length of stay and add it to the total (and so on) until there are no more related admissions or 60 days has been reached.

    I have been doing some online searching and come across some example for sql 2005 using something called CTE, but i need an approach that will work for both 2000 and 2005.

    Should i be investigating recursive functions? Recursive stored procedures? or....??

    An example or two would be awesome...

    Thanks,

  • November 29, 2007 at 9:52 pm

    #755796

    Well - examples ARE nice, but they'll actually be useful if we have some clue on how your data is laid out.

    Your DDL (table structure) and some example data would be helpful. That way - you actually have a shot at getting an "example" that might not turn out to be an example (might be the REAL deal.).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • November 29, 2007 at 10:14 pm

    #755802

    Hi,

    I was more interested in discovering if a function or stored procedure was the best approach considering i need to sue both 2000 and 2005 🙂 (Assuming a relational approach was out of order).

    However, I am happy to receive more customised assistance.... :-0

    DDL:

    CREATE TABLE [dbo].[SB_PATIENT_EPISODE](

    [SB_EPISODE_NUMBER] [numeric](18, 0) IDENTITY(1,1) NOT NULL, --primary key field

    [SB_ADMISSION_DATE] [datetime] NULL,

    [SB_DISCHARGE_DATE] [datetime] NULL,

    ...snip...

    [SB_ACUTE_CARE_REQD_DATE] [datetime] NULL

    CONSTRAINT [PK_SB_PATIENT_EPISODE] PRIMARY KEY CLUSTERED

    (

    [SB_EPISODE_NUMBER] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

    So, the fields of interest are:

    episode number: the primary key

    admission and discharge dates

    and a field to store the date which is the admission date + 60days - 'calcuated previous days'.

    Its home time now... I will give it some thought over the weekend,

    thanks for any replies,

    cheers.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply