How can I retrive all the months for a selected date range

I am working on  a report where I need to return all records between a date range selected by the user based only on the month. My problem is my stored proc (see below) is returning correct data when date range selected is for example Jan 1, 2011 to Dec31, 2011, but if date range is Jan 1 2010 to Jan 31 2011 it only returns the records for Jan not all months i.e. Jan, Feb, March, etc...

ALTER PROC [dbo].[USP_RptGetPhysicalDue] '01/01/2011','02/28/2011'
(
@FromDate DATETIME
,@ToDate DATETIME
)
AS
BEGIN
 SELECT
 tblClients.ID
 ,tblClients.ClientId
 ,dbo.ClientDate(tblClients.ClientId) AdmitDate
 ,tblClients.FirstName+' '+tblClients.LastName Patient
 ,'Counselor: '+tblEmployees.FirstName+' '+tblEmployees.LastName Counselor
 ,tblCounselor.AnnualPhysicalDate
 ,dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) ReviewDate
 FROM tblClients
 INNER JOIN tblCounselor ON tblCounselor.ClientID = tblClients.ClientID
 INNER JOIN tblEmployees ON tblCounselor.Counselor = tblEmployees.UserName
 WHERE (MONTH(dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12)) >= MONTH(@FromDate))
    AND (MONTH(dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12)) < MONTH(@ToDate + 1))
 ORDER BY tblClients.ClientId
END
jonesy_33Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jonesy_33Connect With a Mentor Author Commented:
Still not getting any results with you solution?

dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) calculates the review date based on the clients admit date, because physicals need to be done annually.

0
 
BrandonGalderisiCommented:
You don't want to break things down and try using month() because month('2011-01-01') and month('2005-01-01') are the same.  

Try:

ALTER PROC [dbo].[USP_RptGetPhysicalDue] '01/01/2011','02/28/2011'
(
@FromDate DATETIME
,@ToDate DATETIME
)
AS
BEGIN
--Convert the dates to the first day of the month.
set @fromDate = dateadd(m, datediff(m, 0, @fromDate),0)
set @ToDate  = dateadd(m, datediff(m, 0, @ToDate ),0)

 SELECT
 tblClients.ID
 ,tblClients.ClientId
 ,dbo.ClientDate(tblClients.ClientId) AdmitDate
 ,tblClients.FirstName+' '+tblClients.LastName Patient
 ,'Counselor: '+tblEmployees.FirstName+' '+tblEmployees.LastName Counselor
 ,tblCounselor.AnnualPhysicalDate
 ,dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) ReviewDate
 FROM tblClients
 INNER JOIN tblCounselor ON tblCounselor.ClientID = tblClients.ClientID
 INNER JOIN tblEmployees ON tblCounselor.Counselor = tblEmployees.UserName
 WHERE dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) >= @FromDate)
    AND dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) < @ToDate)
 ORDER BY tblClients.ClientId
END
0
 
jonesy_33Author Commented:
I had to make a few changes to it so that I didn't get errors when running, but it doesn't return any results

ALTER PROC [dbo].[USP_RptGetPhysicalDue] --'01/01/2010','02/28/2011'
(
@FromDate DATETIME
,@ToDate DATETIME
)
AS
BEGIN

--Convert the dates to the first day of the month.
set @FromDate = dateadd(m, datediff(m, 0, @FromDate),0)
set @ToDate  = dateadd(m, datediff(m, 0, @ToDate ),0)
print @FromDate
print @ToDate

 SELECT
 tblClients.ID
 ,tblClients.ClientId
 ,dbo.ClientDate(tblClients.ClientId) AdmitDate
 ,tblClients.FirstName+' '+tblClients.LastName Patient
 ,'Counselor: '+tblEmployees.FirstName+' '+tblEmployees.LastName Counselor
 ,tblCounselor.AnnualPhysicalDate
 ,dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) ReviewDate
 FROM tblClients
 INNER JOIN tblCounselor ON tblCounselor.ClientID = tblClients.ClientID
 INNER JOIN tblEmployees ON tblCounselor.Counselor = tblEmployees.UserName
 WHERE dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) >= @FromDate
    AND dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) < @ToDate

 ORDER BY tblClients.ClientId
END

any ideas? could it be made to allow users to enter a a month instead of a date range? would that be easier? I think that would be acceptable from the user perspective, but I am not sure how to implement it
0
 
BrandonGalderisiCommented:
I don't know what this does:

dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12)

But that is the right way to do @FromDate and @ToDate.

Take a step back and not worry about how to make it work in a procedure.  

declare @FromDate DATETIME
,@ToDate DATETIME

set @FromDate = '2011-01-01' -- The first day of the month you want to include
set @ToDate  = '2011-02-01' -- The first day of the month AFTER the one you want to include.

 SELECT
 tblClients.ID
 ,tblClients.ClientId
 ,dbo.ClientDate(tblClients.ClientId) AdmitDate
 ,tblClients.FirstName+' '+tblClients.LastName Patient
 ,'Counselor: '+tblEmployees.FirstName+' '+tblEmployees.LastName Counselor
 ,tblCounselor.AnnualPhysicalDate
 ,dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) ReviewDate
 FROM tblClients
 INNER JOIN tblCounselor ON tblCounselor.ClientID = tblClients.ClientID
 INNER JOIN tblEmployees ON tblCounselor.Counselor = tblEmployees.UserName
 WHERE dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) >= @FromDate
    AND dbo.ReviewDate(dbo.ClientDate(tblClients.ClientId),12) < @ToDate

 ORDER BY tblClients.ClientId
0
 
jonesy_33Author Commented:
Never got a satisfying solution so I am accepting my to close it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.