Solved

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

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL - sum months, quarter, YTD 9 91
SQL Select * from 6 36
CREATE DATABASE ENCRYPTION KEY 1 59
upgrade sql 2005 32bit to sql 2008 32 or 64bit on a server 2008 r2 box 6 55
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now