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!
tracknfldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PaulBarbinCommented:
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
0
DexstarCommented:
@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*
0
appariCommented:
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)
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

appariCommented:
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.
0
LowfatspreadCommented:
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
;-)
0
tracknfldAuthor Commented:
Dexstar's query works good. Can this be converted into an stored procedure where i pass the id?
0
PaulBarbinCommented:
trcknfld, did you try mine?
0
tracknfldAuthor Commented:
My Sample output from Dexstar:
ResDays   Month        Move in date                              Move out date                            Name
1      3      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
30      4      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
31      5      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
30      6      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
31      7      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
31      8      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
30      9      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
31      10      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
30      11      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
1      12      2003-03-31 00:00:00.000      2003-12-02 22:43:02.950      Sharon Schnitker
30      9      2001-09-17 00:00:00.000      2003-12-02 22:43:02.950      Marian Lausterer
31      10      2001-09-17 00:00:00.000      2003-12-02 22:43:02.950      Marian Lausterer

Why is res days coming up as "1" day?
0
tracknfldAuthor Commented:
PaulBarbin I will try yours too...
0
DexstarCommented:
Here is how to make it a stored procedure:

CREATE PROCEDURE GetResidentHours
(
     @nID INT
)
AS
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 ID, 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, (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
WHERE MonNum BETWEEN Month(MoveInDate) AND Month(COALESCE(MoveOutDate,GETDATE()))) AS ResData
WHERE ID = @nID

@lowfatspread:  That was so obvious, I don't know how I missed it.
0
DexstarCommented:
It is coming up with 1 for the month 12 because today is the 2nd, which is what it should be doing.  I don't know why it is coming up with that for month 3.  What are the dates for that entry?

Dex*
0
tracknfldAuthor Commented:
Dexstar I see why... 1 appears now... cuz I am a dork :) Maybe its suppose to say 1 :)
0
tracknfldAuthor Commented:
Dexstar, 3 is the month...the cut and paste job was a little off ;)
0
tracknfldAuthor Commented:
Dexstar,
Now here is the situation... A resident without a move out date works great. If the move out date is not null or is populated, can you give me the same results for the dates within that range?
0
tracknfldAuthor Commented:
Dex,
There are multiple property locations for residents (siteid) can you incorporate siteid so that you pass id and siteid?
0
DexstarCommented:
It doesn't do that already?  I used the values you gave and got these results:
31      1      2003-01-01 00:00:00.000      2003-03-31 00:00:00.000      John Doe
28      2      2003-01-01 00:00:00.000      2003-03-31 00:00:00.000      John Doe
30      3      2003-01-01 00:00:00.000      2003-03-31 00:00:00.000      John Doe
31      1      2003-01-01 00:00:00.000      2003-03-15 00:00:00.000      Don Brown
28      2      2003-01-01 00:00:00.000      2003-03-15 00:00:00.000      Don Brown
14      3      2003-01-01 00:00:00.000      2003-03-15 00:00:00.000      Don Brown

That includes an end date.

Please provide the sample values to recreate the problem, what values you were expecting, and what values you got instead.  That will help me track it down.

Dex*
0
DexstarCommented:
Try this:
(You said that SiteID was numeric in your original question...)

CREATE PROCEDURE GetResidentHours
(
     @nID INT,
     @nSiteID NUMERIC
)
AS
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 ID, 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, (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
WHERE MonNum BETWEEN Month(MoveInDate) AND Month(COALESCE(MoveOutDate,GETDATE()))) AS ResData
WHERE ID = @nID AND SiteID = @nSiteID
0
DexstarCommented:
Oops!  Try this instead:
CREATE PROCEDURE GetResidentHours
(
     @nID INT,
     @nSiteID NUMERIC
)
AS
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 SiteID, ID, 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, (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
WHERE MonNum BETWEEN Month(MoveInDate) AND Month(COALESCE(MoveOutDate,GETDATE()))) AS ResData
WHERE ID = @nID AND SiteID = @nSiteID
0
tracknfldAuthor Commented:
I changed the name, added an order by field and poof!!! YES!!! Works Great!

Dex, Will a leap year affect it?


***********************************
CREATE PROCEDURE GetResidentDays
(
     @nID INT,
     @nSiteID NUMERIC
)
AS
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 SiteID, ID, 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 Residentinfo, (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
WHERE MonNum BETWEEN Month(MoveInDate) AND Month(COALESCE(MoveOutDate,GETDATE()))) AS ResData
WHERE ID = @nID AND SiteID = @nSiteID ORDER BY MONTH
GO

0
tracknfldAuthor Commented:
Dex, I have a resident with a move in date of 3/24/2000 and a move out date of 7/31/2001. Can the sp accomidate for residents who move in before the current year?
0
tracknfldAuthor Commented:
I meant, Can the sp accomidate for residents who's move out date is before the current year?
0
DexstarCommented:
No, leap year won't affect it.  It finds the end of the month by adding a month to n/1/2003 where N is the month using the DATEADD function.  I checked this to make sure it would handle leap year, and it does (but I'm glad you mentioned it in your requirements).

Glad you got it working.

Dex*
0
tracknfldAuthor Commented:
Almost there..
I have a few residents with where their move out date is null yet the results say differently:

Example output:
7      2002-07-12 00:00:00.000      2003-12-02 23:34:08.717      Gloria Brooks      31
8      2002-07-12 00:00:00.000      2003-12-02 23:34:08.717      Gloria Brooks      30
9      2002-07-12 00:00:00.000      2003-12-02 23:34:08.717      Gloria Brooks      31
10      2002-07-12 00:00:00.000      2003-12-02 23:34:08.717      Gloria Brooks      30
11      2002-07-12 00:00:00.000      2003-12-02 23:34:08.717      Gloria Brooks      1
12      2002-07-12 00:00:00.000      2003-12-02 23:34:08.717      Gloria Brooks      

She hasn't move out yet...
0
DexstarCommented:
Well, it uses the current date to do the calculation when there is no move out date.  Here, this will still do that, but it will select the value separately for display:

SELECT
ResidentDays = DATEDIFF(d, CASE WHEN MoveInDate > MonthStart THEN MoveInDate ELSE MonthStart END,
CASE WHEN MoveOutDateCompute <= MonthEnd THEN MoveOutDateCompute ELSE MonthEnd END),
MonNum As Month, MoveInDate, MoveOutDate, Name
FROM
(SELECT ID, FirstName + ' ' + LastName As Name, MoveInDate, MoveOutDate, COALESCE(MoveOutDate,GETDATE()) AS MoveOutDateCompute, 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, (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
WHERE MonNum BETWEEN Month(MoveInDate) AND Month(COALESCE(MoveOutDate,GETDATE()))) AS ResData
0
tracknfldAuthor Commented:
That works... So is there any way around only calculating one year?
for example: Move In Date: 1/3/2002 and Move Out Date 8/22/2003 yields the following results..

31      1      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
28      2      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
31      3      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
30      4      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
31      5      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
30      6      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
31      7      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson
21      8      2002-01-03 00:00:00.000      2003-08-22 00:00:00.000      Dorothy Aronson

Should there be more months? Watcha think?
0
tracknfldAuthor Commented:
BTW, since this is my first time. I would like to offer you 400 points. How can I give them to you? Or when I hit split points will it let me give them to you then?
0
arbertCommented:
I agree with PaulBarbin--you're going to keep trying to come up with calculations and case statements till the cows come home.  Create a date table, load the keys into your detail records instead of the date.  If you go with an integer as your key and start from a base date, you can just "subtract or add the keys" to come up with calculations that you need above....
0
tracknfldAuthor Commented:
I am not familar with creating a loop statement to insert the data... Can you do this in QueryAnalyzer?
0
arbertCommented:
yep, look at the WHILE statement in books online.
0
PaulBarbinCommented:
I included most of it in my first post, but here is the whole thing:


CREATE TABLE Dates (
      dateid int identity(1,1),       
      sqldate datetime,
      prettydate char(10))

SET NOCOUNT ON
DECLARE @Date datetime
SET @Date = CONVERT(datetime, '01/01/1998')

WHILE @Date < CONVERT(datetime, '01/01/2008')
BEGIN
  INSERT INTO Dates VALUES (@date, Convert(char(10), @date, 101) )
  SET @date =  Dateadd(d, 1, @date)
END

Then a slight modification to my original query:

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, getdate())
GROUP BY MONTH(sqldate), moveindate, moveoutdate, firstname

Give it a shot.
Paul
0
tracknfldAuthor Commented:
Thx Paul... Let me give it a whirl...
0
tracknfldAuthor Commented:
Paul,
Here is a block of sample data from your query:
(Resident Days,Month,Moveindate,Moveoutdate)
30      11      2000-03-10 00:00:00.000      2001-06-17 00:00:00.000      Alex Reinharcz      469      2
31      12      2000-03-10 00:00:00.000      2001-06-17 00:00:00.000      Alex Reinharcz      469      2
62      1      2001-12-27 00:00:00.000      NULL      Alfred Jerome      555      2
56      2      2001-12-27 00:00:00.000      NULL      Alfred Jerome      555      2


The Dates Table created and populated well.
Q1. Resident Days should not exceed 31.
Q2. Can the query be adjusted to accomodate residents who move in before the current year?

Thx!

0
PaulBarbinCommented:
The reason that we were getting 61, is that the query is grouping January 2002 with January 2003 (because we said group on Month).  If I add the year to the query, then we get better results:

SELECT      COUNT(*),
            CAST(YEAR(SqlDate)AS CHAR(4)) + '/' + CAST(MONTH(SqlDate) AS CHAR(2)) 'YearMonth',
            CONVERT(CHAR(10), moveindate, 101) 'Move In Date',
            ISNULL(CONVERT(CHAR(10), moveoutdate, 101) , 'None') 'Move Out Date',
            RTRIM(firstname) + ' ' + RTRIM(lastname)
FROM      ResidentInfo RI
      JOIN Dates D ON
            D.SqlDate BETWEEN RI.MoveinDate AND ISNULL(RI.MoveOutDate, getdate())
GROUP BY  CAST(YEAR(SqlDate)AS CHAR(4)) + '/' + CAST(MONTH(SqlDate) AS CHAR(2)),
            Moveindate,
            Moveoutdate,
            Firstname,
            Lastname
0
tracknfldAuthor Commented:
Beautiful!! Sooo Nice!
Is there a way to split the yearmonth so that they have separte colums?

28      2001/2       09/11/1999      04/15/2001      Art Weyrick
31      2001/3       09/11/1999      04/15/2001      Art Weyrick
15      2001/4       09/11/1999      04/15/2001      Art Weyrick
7      2001/3       03/02/2001      03/08/2001      Aurora Ligad
31      2001/10      03/01/2001      None            Barbara Graves
30      2001/11      03/01/2001      None            Barbara Graves
31      2001/12      03/01/2001      None            Barbara Graves

For example:
28      2001    2          09/11/1999      04/15/2001      Art Weyrick
31      2001    3          09/11/1999      04/15/2001      Art Weyrick

Looking Great!
0
PaulBarbinCommented:
Uhh, yes...

SELECT      COUNT(*),
      YEAR(SqlDate) 'Year',
      MONTH(SqlDate) 'Month',
      CONVERT(CHAR(10), moveindate, 101) 'Move In Date',
      ISNULL(CONVERT(CHAR(10), moveoutdate, 101) , 'None') 'Move Out Date',
      RTRIM(firstname) + ' ' + RTRIM(lastname)
FROM     ResidentInfo RI
     JOIN Dates D ON
          D.SqlDate BETWEEN RI.MoveinDate AND ISNULL(RI.MoveOutDate, getdate())
GROUP BY  YEAR(SqlDate),
       MONTH(SqlDate),
                 Moveindate,
                 Moveoutdate,
                 Firstname,
                 Lastname
0
tracknfldAuthor Commented:
What a beautiful thing! Works awesome!

Final question...I promise :)
Is this possible to turn the query below into a stored procedure where passing nothing gives me everyone, and passing the siteid, id filters it?


***************************
Here is the Final Query:
SELECT  COUNT(*) as ResidentDays,
           YEAR(SqlDate) 'Year',
           MONTH(SqlDate) 'Month',
           CONVERT(CHAR(10), moveindate, 101) 'Move In Date',
           ISNULL(CONVERT(CHAR(10), moveoutdate, 101) , 'None') 'Move Out Date',
           RTRIM(firstname) + ' ' + RTRIM(lastname)as Name,
      Id,
      Siteid
FROM     ResidentInfo RI
     JOIN Dates D ON
          D.SqlDate BETWEEN RI.MoveinDate AND ISNULL(RI.MoveOutDate, getdate())
//** WHERE Siteid =1 and id=227 **//
GROUP BY  YEAR(SqlDate),
            MONTH(SqlDate),
        Moveindate,
        Moveoutdate,
        Firstname,
        Lastname,
      Id,
      Siteid
Order By Name,Year,Month
0
PaulBarbinCommented:
I shouldn't be doing all this, but I've come this far, so why not, right?


CREATE PROCEDURE uspResidentDays

      @ResidentId int = -1,
      @SiteId int = -1

AS
SELECT      COUNT(*) as ResidentDays,
            YEAR(SqlDate) 'Year',
            MONTH(SqlDate) 'Month',
            CONVERT(CHAR(10), moveindate, 101) 'Move In Date',
            ISNULL(CONVERT(CHAR(10), moveoutdate, 101) , 'None') 'Move Out Date',
            RTRIM(firstname) + ' ' + RTRIM(lastname)as Name,
           [Id],
           Siteid
FROM      ResidentInfo RI
      JOIN Dates D ON
            D.SqlDate BETWEEN RI.MoveinDate AND ISNULL(RI.MoveOutDate, getdate())
WHERE      Siteid = CASE WHEN @SiteId = -1 THEN SiteId ELSE @SiteId END AND
            [Id] = CASE WHEN @ResidentId = -1 THEN [Id] ELSE @ResidentId END
GROUP BY YEAR(SqlDate),
            MONTH(SqlDate),
        Moveindate,
        Moveoutdate,
        Firstname,
        Lastname,
            [Id],
            Siteid
ORDER BY 6, 2, 3

Then you can call this stored procedure like this:

uspResidentDays  (gets all data)
uspResidentDays @ResidentId = x (to get one resident)
uspResidentDays @siteid = x (to get all residents for one site)

Thats it, I'm done. :)
Paul
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tracknfldAuthor Commented:
YEP :) Thanks for all your help and patience!
0
tracknfldAuthor Commented:
Thx again Paul. I do appreciate it!
0
DexstarCommented:
Well, I'm glad you got it working, but I can't believe I did all that work and I didn't even get partial credit.
0
tracknfldAuthor Commented:
Dexstar You Are Right! Sorry about that!
I would like to give you partial credit + 200 points for your help. Can I do that? If so, how... as you can tell... I am a newbie!
0
DexstarCommented:
You'll have to pardon my early morning crankiness.  You got it working and that's what's important.

If you really want to do that, however, you basically have 2 options:
1) You can ask the Moderators to re-open this question so you can change how the points were awarded.  Post something the http:/Community_Support/ forum, with a link to this question, and explain what you want to do.

2) You can create a "Points For" question.  This link explains that in more detail:
http:/help.jsp#hi76

D*
0
tracknfldAuthor Commented:
0
arbertCommented:
Welcome to the real world--we all have had questions where we give responses and don't get credit.  It all comes down to the solution that works and works the best...........
0
PaulBarbinCommented:
Funny thing is that would have happened to me had I not pushed for tracknfld to try my response.  I figured I had lost the points to Dexstar, which was ok, because his (her?) responses are usually pretty good.

Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.