By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

How to specify starting date and ending date of a month in a query?

P: 1
I have a cross-tab query which displays values in a monthly basis (name of the customer as rows and months as columns and points as values) . The problem is my report should show monthly reports from the 6th of the month to the 5th of the succeeding month. Ex: values should return are from dates 11-6-2012 to 12-5-2012.

This is the SQL of my query:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(([Points]*[Quantity])) AS Expr1
  2. SELECT [Orders Query].CustomerName, [Orders Query].Company, Sum([Points]*[Quantity]) AS [Total Of Points]
  3. FROM [Orders Query]
  4. WHERE ((([Orders Query].CustomerName) Like "*Catalino*" Or ([Orders Query].CustomerName) Like "*Bartolo*" Or ([Orders Query].CustomerName) Like "*Dones*" Or ([Orders Query].CustomerName) Like "*Aliwana*" Or ([Orders Query].CustomerName) Like "*Abcas*" Or ([Orders Query].CustomerName) Like "*Gattud*" Or ([Orders Query].CustomerName) Like "*Gallo*"))
  5. GROUP BY [Orders Query].CustomerName, [Orders Query].Company
  6. PIVOT Format([PaymentDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Nov 17 '12 #1

✓ answered by NeoPa

What you need to do is to subtract 5 days from the date value. That way the sixth (and later) of any month still reflects the current month but the fifth (and earlier) reflect the prior month.

IE: Your PIVOT line might look like :
Expand|Select|Wrap|Line Numbers
  1. PIVOT Format(DateAdd('d',-5,[PaymentDate]),'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Share this Question
Share on Google+
2 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
Do you want this query to pull any month or just the previous month to current month? In other words, if you ran the query today, would you be wanting 10-6-12 through 11-5-12 or do you want to be able to pull 7-6-12 through 8-5-12?
Nov 17 '12 #2

NeoPa
Expert Mod 15k+
P: 31,186
What you need to do is to subtract 5 days from the date value. That way the sixth (and later) of any month still reflects the current month but the fifth (and earlier) reflect the prior month.

IE: Your PIVOT line might look like :
Expand|Select|Wrap|Line Numbers
  1. PIVOT Format(DateAdd('d',-5,[PaymentDate]),'mmm') In ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')
Nov 17 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.