Solved

Calculating Resident days.

Posted on 2003-12-02
45
631 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:tracknfld
  • 22
  • 10
  • 7
  • +3
45 Comments
 
LVL 9

Expert Comment

by:PaulBarbin
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
@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
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
 
LVL 39

Expert Comment

by:appari
Comment Utility
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
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
Dexstar's query works good. Can this be converted into an stored procedure where i pass the id?
0
 
LVL 9

Expert Comment

by:PaulBarbin
Comment Utility
trcknfld, did you try mine?
0
 

Author Comment

by:tracknfld
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
PaulBarbin I will try yours too...
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
Dexstar I see why... 1 appears now... cuz I am a dork :) Maybe its suppose to say 1 :)
0
 

Author Comment

by:tracknfld
Comment Utility
Dexstar, 3 is the month...the cut and paste job was a little off ;)
0
 

Author Comment

by:tracknfld
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
Dex,
There are multiple property locations for residents (siteid) can you incorporate siteid so that you pass id and siteid?
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
I meant, Can the sp accomidate for residents who's move out date is before the current year?
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:tracknfld
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
I am not familar with creating a loop statement to insert the data... Can you do this in QueryAnalyzer?
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
yep, look at the WHILE statement in books online.
0
 
LVL 9

Expert Comment

by:PaulBarbin
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
Thx Paul... Let me give it a whirl...
0
 

Author Comment

by:tracknfld
Comment Utility
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
 
LVL 9

Expert Comment

by:PaulBarbin
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 
LVL 9

Expert Comment

by:PaulBarbin
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 
LVL 9

Accepted Solution

by:
PaulBarbin earned 500 total points
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
YEP :) Thanks for all your help and patience!
0
 

Author Comment

by:tracknfld
Comment Utility
Thx again Paul. I do appreciate it!
0
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
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
 
LVL 19

Expert Comment

by:Dexstar
Comment Utility
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
 

Author Comment

by:tracknfld
Comment Utility
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
 
LVL 9

Expert Comment

by:PaulBarbin
Comment Utility
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

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now