Solved

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

Posted on 2011-03-16
5
234 Views
Last Modified: 2012-10-16
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
Comment
Question by:jonesy_33
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35150892
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
 

Author Comment

by:jonesy_33
ID: 35151307
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 35151472
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
 

Accepted Solution

by:
jonesy_33 earned 0 total points
ID: 35151562
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
 

Author Closing Comment

by:jonesy_33
ID: 38500254
Never got a satisfying solution so I am accepting my to close it!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

749 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