asked on

Calculating Resident days.

Well, here we go.
Hi all! We are running SQL 7.0

Before I explain the question, here is the table structure:
Table Name: ResidentInfo
Fields: id (int),firstname (varchar), lastname (varchar) ,moveindate (datetime),moveoutdate (datetime),active (numeric) "1" is active, siteid (numeric)

OK.

We need an sql statement or stored procedure that will create a report that will calculate the number of days a resident has lived at a particular site (nursing home facility) based on their move in date. Some residents have null values for the move out dates. The twist is that we need the report to caculate per month and grand total: For example:

*** This example is based on a current date of 4/31/2003

ResidentDays Month MoveInDate MoveOutDate Name
31 1 1/1/2003 3/31/2003 John Doe
28 2 1/1/2003 3/31/2003
31 3 1/1/2003 3/31/2003
continued ... til the current date....

ResidentDays Month MoveInDate MoveOutDate Name
31 1 1/1/2003 3/15/2003 Don Brown
28 2 1/1/2003 3/15/2003
16 3 1/1/2003 3/15/2003
continued ... til the current date.

The query must take in account leap year, and have the "year" as a variable so that we could just pass it a year and id of the resident.
Well let us know what you think!

PaulBarbin

Without doing all the work for you, I would create a Dates table. It can have as few or as many columns as you think you might need but at least have an id column, a datetime column and a prettydate column. Something like this:

CREATE TABLE Dates (DateId int Identity(1,1), SqlDate datetime, Prettydate char(10))

Create a loop to insert all dates that seem reasonable to you - every date for 5 years back and 5 years forward (~365*10 = ~3700 rows). Use the dateadd function to make sure you get all leap year dates.

While @cnt < 3700
Begin
Insert into Dates values (Dateadd(d,@mystartdate, @cnt), Convert(char(10), Dateadd(d,@mystartdate, @cnt),101)
Set @cnt = @cnt + 1
End (you get the picture)

Then you can create a join between your residentinfo table and the dates table to include the dates and the count of days in the month you are interested in. Something like this:

SELECT      COUNT(*),
            MONTH(sqldate),
            moveindate,
            moveoutdate,
            Rtrim(firstname) + ' ' +Rtrim(lastname)
FROM       ResidentInfo RI
      JOIN Dates D ON
            D.SqlDate BETWEEN RI.MoveinDate AND ISNULL(RI.MoveOutDate, '12-31-9999')
GROUP BY MONTH(sqldate), moveindate, moveoutdate, firstname

Then you just gotta plug in some variables to get you the year and resident id and you're on your way.

Good luck.

Paul

Dexstar

@tracknfld:

> The query must take in account leap year, and have the "year" as a variable
> so that we could just pass it a year and id of the resident.
> Well let us know what you think!

Okay, here is what I have so far. It's a little messy, but it works. It should only take a little tweaking to get what you want. The only thing is that it requires a table called "Months" with a field called "MonNum" with entries 1 through 12 [ANYONE: Please! Is there a better way to do this?]

Once you have that, this query should give you what you need:

SELECT
ResidentDays = DATEDIFF(d, CASE WHEN MoveInDate > MonthStart THEN MoveInDate ELSE MonthStart END,
CASE WHEN MoveOutDate <= MonthEnd THEN MoveOutDate ELSE MonthEnd END),
MonNum As Month, MoveInDate, MoveOutDate, Name
FROM
(SELECT FirstName + ' ' + LastName As Name, MoveInDate, COALESCE(MoveOutDate,GETDATE()) AS MoveOutDate, MonNum,
CONVERT(DATETIME, CONVERT(VARCHAR,MonNum) + '/1/' + CONVERT(VARCHAR,Year(GetDate()))) As MonthStart,
DATEADD(m,1,CONVERT(DATETIME, CONVERT(VARCHAR,MonNum) + '/1/' + CONVERT(VARCHAR,Year(GetDate())))) As MonthEnd
FROM Residents, Months
WHERE MonNum BETWEEN Month(MoveInDate) AND Month(COALESCE(MoveOutDate,GETDATE()))) AS ResData

(I'm hoping others will be able to improve on that... It was just something quick and dirty to get the ball rolling...)

Hope That Helps,
Dex*

appari

how about this Function?

CREATE FUNCTION fn_GetDays (@pID int,@pYear int)
RETURNS @retDates TABLE (sID int,ResidentDays int ,cMonth int, MoveInDate datetime, MoveOutDate datetime, PName varchar(40) )
AS
BEGIN
DECLARE @tempRet TABLE (sID int, firstname varchar(20), lastname varchar(20),active numeric(1), siteid numeric(3), myDate datetime)
DECLARE @Months TABLE (monthNo int)

Declare @lDate datetime,@inDate datetime, @OutDate datetime, @MNo int
select @MNo=1
while @MNo<=12
Begin
insert into @Months values(@MNo)
select @MNo=@MNo+1
End

Select @OutDate=moveoutdate, @inDate=moveindate from T_Site where s_id=@pID
select @lDate=@inDate
while @lDate<=@OutDate
begin
insert @tempRet select @pID, firstname , lastname ,active , siteid, @lDate from T_Site where s_id=@pID
select @lDate=dateadd(dd,1,@lDate)
end

-- copy to the result of the function the required columns
INSERT @retDates
SELECT sID , count(1), monthNo, min(myDate),max(myDate), min(firstname + lastname )
FROM @tempRet, @Months where monthNo = month(myDate) group by sID, monthNo
RETURN
END

call it like this...

select * from [yourDBName]..fn_GetDays(2,2003)

appari

oops i didnt notice you are using SQLserver 7, no user defined functions

change above to SP and Table type to temp table and try.

Lowfatspread

dex
?
(select 1 as MonNum union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9
union select 10 union select 11 union select 12) as Months

hth
;-)