Solved

Calculating Resident days.

Posted on 2003-12-02
45
637 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
ID: 9863963
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
ID: 9863996
@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
ID: 9864034
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
ID: 9864040
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
ID: 9864057
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
ID: 9864131
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
ID: 9864153
trcknfld, did you try mine?
0
 

Author Comment

by:tracknfld
ID: 9864159
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
ID: 9864170
PaulBarbin I will try yours too...
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9864172
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
ID: 9864186
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
ID: 9864200
Dexstar I see why... 1 appears now... cuz I am a dork :) Maybe its suppose to say 1 :)
0
 

Author Comment

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

Author Comment

by:tracknfld
ID: 9864237
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
ID: 9864284
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
ID: 9864288
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
ID: 9864303
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
ID: 9864309
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
ID: 9864339
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
ID: 9864357
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
ID: 9864364
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
ID: 9864366
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:tracknfld
ID: 9864389
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
ID: 9864411
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
ID: 9864442
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
ID: 9864451
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
ID: 9864469
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
ID: 9864501
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
ID: 9864612
yep, look at the WHILE statement in books online.
0
 
LVL 9

Expert Comment

by:PaulBarbin
ID: 9866567
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
ID: 9866807
Thx Paul... Let me give it a whirl...
0
 

Author Comment

by:tracknfld
ID: 9866909
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
ID: 9867063
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
ID: 9867166
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
ID: 9867216
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
ID: 9867385
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
ID: 9867474
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
ID: 9867510
YEP :) Thanks for all your help and patience!
0
 

Author Comment

by:tracknfld
ID: 9867584
Thx again Paul. I do appreciate it!
0
 
LVL 19

Expert Comment

by:Dexstar
ID: 9867839
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
ID: 9868065
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
ID: 9868127
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
ID: 9868206
0
 
LVL 34

Expert Comment

by:arbert
ID: 9870185
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
ID: 9870245
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

863 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

22 Experts available now in Live!

Get 1:1 Help Now