Hi, ttg-ga:
I have naturally made a few design decisions in the course of writing
the stored procedure below. I changed the defaults to arguments to be
Null's as this is a more familiar practice for me. Thus my version of
GetResidentDays takes three parameters, @ResidentId, @SiteId, and
@Year, any of which limit the record set if specified as non-Null, but
otherwise do not limit the results.
First the test data I used:
CREATE table ResidentInfo
(
id int not Null,
firstname varchar(20) not Null,
lastname varchar(35) not Null,
moveindate datetime not Null,
moveoutdate datetime Null,
active numeric not Null default 1, -- active
siteid numeric not Null
)
go
SELECT count(*) from ResidentInfo
go
insert into ResidentInfo values
(1,'John','Doe','1/1/2003','3/31/2003',1,1)
go
insert into ResidentInfo values
(2,'Don','Brown','1/1/2003','3/15/2003',1,2)
go
It seems a little unusual to me to use the numeric datatype for the
last two fields, esp. if active can only be 0 or 1. But that's how
you described the existing table, so I went with it.
Notice that in describing your counts for the two test cases above,
you cited 16 days for Don Brown in March 2003. This should be 15
days.
From your examples, however, I concluded that you mean to include both
the move-in and move-out dates in the overall counts. The code below
reflects this assumption.
I didn't need to code anything special for leap years because I'm
relying only on the datediff function in Transact-SQL to provide
counts. However one of the awkward things in SQL is to piece together
a date from its "parts". Although datepart will extract the month,
day, year, etc. given a valid datetime, there is no system provided
inverse function. Here the one "error handling" snippet that I wrote
tries to validate that the glueing together of a month and year to
make a first of a month has gone well, or to exit before launching
into the main loop which depends critically on the first of a month
value for termination.
The trickiest logic concerns counting the last day (moveoutdate) in
the right month, but I'll let you look at how I've written the code.
You can ask if you have questions about it. I tested this by updating
the ResidentInfo table to add one more day to moveoutdate.
Finally I tested the handling of Null in moveoutdate by setting both
records to have this. The results returned roll forward through the
current month (Jan. 2004 at this time) unless you specify a year. If
you specify a year it assumes you want all twelve months of that year
(which projects quite a bit into the future for 2004 if you use that
as an input argument).
I think that about covers the assumptions I made that are worth
reviewing by you in the context of your business needs. Some of what
you want to do in formatting a report is best managed with a reporting
tool, like Crystal Reports. That is, while one could construct a lot
of PRINT or SELECT statements to output a report with subtotals
directly from SQL, the language is not designed to make this easy.
But reporting tools should handle that function nicely. Let me know
if you are unsure what I'm referring to as a reporting tool.
Okay, here's the code to DROP and CREATE the stored procedure,
followed by a few of the artifacts of my testing.
regards, mathtalk-ga
/*
[MS SQL Server - SET QUOTED_IDENTIFIER (Transact-SQL)]
http://msdn.microsoft.com/library/en-us/tsqlref/ts_set-set_9jxu.asp
*/
DROP proc GetResidentDays
go
CREATE proc GetResidentDays
@ResidentId int = Null,
@SiteId int = Null,
@Year int = Null
as
CREATE table #ResidentDays
(
ResidentId int not Null,
"Year" int not Null,
"Month" int not Null,
ResidentDays int not Null,
constraint PK_ResDays primary key
(
ResidentID,
"Year",
"Month"
)
)
set nocount on
declare @thisYear int,
@thisMonth int,
@this1stOf datetime,
@next1stOf datetime,
@stopYear int,
@stopMonth int
-- Find the earliest month to start on
-- and the latest month (to stop with)
-- As written the counts will run through
-- the full end of the current month for
-- residents whose moveoutdate is Null
if ( @Year is Null )
begin
select @thisYear = datePart(year, min(moveindate)),
@thisMonth = datePart(month, min(moveindate))
from ResidentInfo
where ( @ResidentId is Null
OR id = @ResidentId
)
and ( @SiteId is Null
OR siteid = @SiteId
)
and active = 1
select @stopYear = datePart(year, getdate( )),
@stopMonth = datePart(month, getdate( ))
end
else
select @thisYear = @Year,
@thisMonth = 1, -- set to beginning of year
@stopYear = @Year,
@stopMonth = 12 -- set to end of year
select @this1stOf = convert( datetime,
convert(varchar,@thisMonth)
+ '/1/'
+ convert(varchar,@thisYear)
)
-- The idea is to loop through the months
-- and add rows to #ResidentDays as we go
-- A key point, deduced from the examples in
-- the Question & Clarification, is that both
-- a resident's movein and moveout days count
if 0 = ISDATE( convert(varchar,@thisMonth)
+ '/1/'
+ convert(varchar,@thisYear)
)
begin
select @thisMonth, @thisYear, @this1stOf
return 0
end
while ( @thisYear < @stopYear
OR ( @thisYear = @stopYear
and @thisMonth <= @stopMonth
)
)
begin
select @next1stOf = dateadd(month, 1, @this1stOf)
insert into #ResidentDays
select id,
@thisYear,
@thisMonth,
datediff(day,
case
when ( @this1stOf <= moveindate )
then moveindate
else @this1stOf
end,
case
when ( @next1stOf > moveoutdate )
then dateadd(day, 1, moveoutdate)
-- counts a resident's last day
else @next1stOf -- catch if moveoutdate is Null
end
)
from ResidentInfo
where ( @ResidentId is Null
OR @ResidentId = id
)
and ( @SiteId is Null
OR @SiteId = siteid
)
and moveindate < @next1stOf -- limits records to residents
and ( moveoutdate is Null
OR moveoutdate >= @this1stOf -- during thisMonth & thisYear
)
and active = 1 -- who are still marked active
select @thisYear = datepart(year, @next1stOf),
@thisMonth = datepart(month, @next1stOf),
@this1stOf = @next1stOf
end
set nocount off
SELECT * from #ResidentDays
go
GetResidentDays Null, Null, 2004
go
update ResidentInfo
set moveoutdate = dateadd(day, 1, moveoutdate)
go
update ResidentInfo
set moveoutdate = Null
go |
