Previous months in months order

sushmitha

Registered User.
Local time
Today, 12:24
Joined
Jul 30, 2008
Messages
55
I have been facing problems since started writing queries against previious months, years, days etc.

I wrote the following query to get the previous 12 months which is working fine but the Order of months giving me problem

Suppose if I run today the query I should get months from Oct 2008 till Oct 2007. And the order should be

Oct 2007,Nov 2007, Dec 2007, Jan 2008, Feb 2008, Mar 2008 till Oct 2008

Please help me how to get into there.

Here is the query I am using

SELECT
Format$(tbl.cDate,'mmm yyyy')
FROM
tbl
WHERE
( tbl.cDate Between DateAdd('m',-12,DatePart('m', Date()) & '/1/' & DatePart('yyyy', date())) AND Dateadd('d',-1, DatePart('m', date()) & '/1/' & DatePart('yyyy', date())) )
GROUP BY
Format$(tbl.cDate,'mmm yyyy')
 
ADD and ORDER BY clause that looks like this ....

ORDER BY DateSerial(Year(tbl.cDate), Month(tbl.cDate), 1)
 
ADD and ORDER BY clause that looks like this ....

ORDER BY DateSerial(Year(tbl.cDate), Month(tbl.cDate), 1)


Thank you very much. It is working fine

Also can you tell me how to find the previous 12 working days excluding weekends ??

I already posted this in forums but didnot get it work
 
Thing to remember about dates is that they are actually numbers where integers determain days and decimals depict a part of a day. i.e. .25 is .25 day = .25 * 24 hours a day = 6 hours.

Now the last 12 days is then Yourdate - 12
Offcourse that still works the weekends in...
In 12 working days there are always two weekends, so we add 2 times 2 days to account for the weekends.
Yourdate - 12 - 4

Now one final thing, in 12 workingdays... if today is a monday, and a monday ONLY there are 3 weekends, thus we have to deduct 2 extra days.
Yourdate - 12 - 4 - IIF(weekday(Yourdate,2)=1,2,0)

This is the easy solution....

Offcourse now comes a more complex question, Xmass, easter monday, new years etc.
Are they working days yes or no???
If no and you want to take those kind of days into account in calculating the 12 working days there is a sample database available on this forum here:
http://www.access-programmers.co.uk/forums/showthread.php?t=133329&highlight=Working+days

Be warned... this sample is good, but gets pretty involved.
 
ADD and ORDER BY clause that looks like this ....

ORDER BY DateSerial(Year(tbl.cDate), Month(tbl.cDate), 1)

Why the dateserial???

This will do the same trick??
ORDER BY Year(tbl.cDate), Month(tbl.cDate)
 
Hello Mailman ...

Actually with a GROUP BY neither one of our sort keys will work directly because in a GROUP BY the fields being sorted need to part of the fields/aliases of the SELECT clause ...

But this SQL should do the trick ...

Code:
SELECT Format(MonthBegin, "mmm yyyy") As MonthText
FROM (SELECT DateSerial(Year(tbl.CDate), Month(tbl.CDate), 1) As MonthBegin
      FROM tbl
      WHERE (tbl.cDate Between DateSerial(Year(Date()), Month(Date()) - 12, 1)
                 AND DateSerial(Year(Date()), Month(Date()), 0)
      GROUP BY DateSerial(Year(tbl.CDate), Month(tbl.CDate), 1)) As vTbl
ORDER BY MonthBegin

Or ... if the sub-query is not desired ...

Code:
SELECT DateSerial(Year(tbl.CDate), Month(tbl.CDate), 1) As MonthBegin
FROM tbl
WHERE (tbl.cDate Between DateSerial(Year(Date()), Month(Date()) - 12, 1)
                 AND DateSerial(Year(Date()), Month(Date()), 0)
GROUP BY DateSerial(Year(tbl.CDate), Month(tbl.CDate), 1)
ORDER BY MonthBegin

Then when displaying MonthBegin in a control, set the format property to "mmm yyyy"
 

Users who are viewing this thread

Back
Top Bottom