Solved

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

Posted on 2011-03-16
5
233 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
  • 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

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.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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