July 25, 2011 at 10:41 am
I'm working with SQL Server 2008 R2 Reporting Services and trying to extract records from my database for accounts opened between the first day and the last day of the previous month. The data field that I'm using to indicate the day that the account was opened is SH_ACCT.open_dt. I'm using the following code:
open_dt between
CONVERT_VARCHAR(30),DATEADD(dd,1,(dateadd(mm,datediff(mm,0,dateadd(mm,-2,getdate())),))),101) AND CONVERT(VARCHAR(30),dateadd(mm,datediff(mm,0,dateadd(mm,-1,getdate())),0),101)
I've tried testing each component separately and when I test I am able to retrieve the records which opened on the first day of the previous month.
I'm also able to retrieve only those records which opened on the last day of the previous month.
However, when I try to extract all the records which opened between the first day and the last day of the previous month, I am getting returns for all records which opened during June of any year, NOT just the current year....
The full code for this query is:
SELECT SH_ACCT.acn, MEMBER_ACCT.mbr_nam, MEMBER_ACCT.mbr_ssn, CONVERT(VARCHAR(30),SH_ACCT.open_dt,101) AS open_dt
FROM SQLUser.MEMBER_ACCT MEMBER_ACCT, SQLUser.SH_ACCT SH_ACCT
WHERE MEMBER_ACCT.acn = SH_ACCT.acn AND ((MEMBER_ACCT.mbr_cd<>'21') AND (SH_ACCT.sh_sfx='0') AND (MEMBER_ACCT.close_dt Is Null) AND (CONVERT(VARCHAR(30),SH_ACCT.open_dt,101) Between (CONVERT(VARCHAR(30),dateadd(mm,datediff(mm,0,dateadd(mm,-2,getdate())),0),101)) And (CONVERT(VARCHAR(30),dateadd(mm,datediff(mm,0,dateadd(mm,-1,getdate())),0),101))))
AND the BEGINNER's frustration grows!!!
Thanks for your assistance in advance...
July 25, 2011 at 11:14 am
Why are you converting your dates to strings?
I have always had problems with BETWEEN dropping records on me, so I never use it. I always go this route:
SH_ACCT.open_dt >= dateadd(mm,datediff(mm,0,dateadd(mm,-2,getdate())),0)
And SH_ACCT.open_dt <= dateadd(mm,datediff(mm,0,dateadd(mm,-1,getdate())),0)
It's a little extra typing, but it serves me well.
FYI, your WHERE clause pulls May records, not June.
July 25, 2011 at 12:01 pm
Thanks for your response. I'll give this a try......actually, I inadvertently pasted a modified set of code into the initial post: This is the actual code to retrieve prior month data (JUNE):
SELECT SH_ACCT.acn, MEMBER_ACCT.mbr_nam, MEMBER_ACCT.mbr_ssn, CONVERT(VARCHAR(30),SH_ACCT.open_dt,101) AS open_dt
FROM SQLUser.MEMBER_ACCT MEMBER_ACCT, SQLUser.SH_ACCT SH_ACCT
WHERE MEMBER_ACCT.acn = SH_ACCT.acn AND ((MEMBER_ACCT.mbr_cd<>'21') AND (SH_ACCT.sh_sfx='0') AND (MEMBER_ACCT.close_dt Is Null) AND (CONVERT(VARCHAR(30),SH_ACCT.open_dt,101) Between CONVERT(VARCHAR(30),dateadd(dd,1,(dateadd(mm,datediff(mm,0,dateadd(mm,-2,getdate())),0))),101) And CONVERT(VARCHAR(30),dateadd(mm,datediff(mm,0,dateadd(mm,-1,getdate())),0),101)))
This code actually retrieves all accounts opened in the month of June regardless of the year (Not just the current year).
We are converting the date to string because the end users tend to "freak" when they see the system datetime with hh:mm:ss rather than just the date only format......
July 25, 2011 at 12:04 pm
rbond 51820 (7/25/2011)
We are converting the date to string because the end users tend to "freak" when they see the system datetime with hh:mm:ss rather than just the date only format......
Then make the format pretty in the client or the report. Don't do it in the Select statement. I think half your problem is that you're converting to string instead of matching on date and the string is properly deciding that everything with a June date counts, regardless of year.
July 25, 2011 at 12:45 pm
Thank you very much for your assistance......as a "non-programmer" and a REAL newbie.....this has been most helpful.....
Your suggestions have been used and I now find my query working to perfection....
Thanks again...
July 26, 2011 at 7:34 am
Glad I could help.
Remember, keep "pretty" on the user side. Don't worry about it for the backend stuff.
July 28, 2011 at 8:34 am
Brandie:
Thanks again for your assistance.....I've got another "quick" question.
Other than the obvious "@Timestamp" method of appending a datetime to the end of a file name, do you know of another way to append another "variable" value to the end of a file name....I have a number of reports that cover specific time ranges and it would be nice to be able to append those ranges to the file name dynamically...
Thanks...
July 28, 2011 at 8:45 am
Post that question in a new thread. There are a lot of ways of doing that in SSIS, so I'm sure it can be done in SSRS, but off the top of my head, I'm drawing a blank.
If you put the question in a new thread with the appropriate title, you'll get new people looking at it that will help you out.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply