• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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
0
jonesy_33
Asked:
jonesy_33
  • 3
  • 2
1 Solution
 
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:
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
 
jonesy_33Author Commented:
Never got a satisfying solution so I am accepting my to close it!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now