[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

record count of most recent month and the previous months records

Posted on 2012-09-15
14
Medium Priority
?
522 Views
Last Modified: 2012-09-16
I would like a query that will give me the total record count for the most recent year month and another query that returns the previous months record count of the most recent month.

my table has:
[abicode]

[BuildYearMonth]

I tried this to get the previous months but it fails:
SELECT AbiBuilds.AbiCode, AbiBuilds.BuildYearMonth
FROM AbiBuilds
GROUP BY AbiBuilds.AbiCode, AbiBuilds.BuildYearMonth
HAVING (((AbiBuilds.BuildYearMonth)=DateAdd('m',-1,Max([buildyearmonth]))));

Open in new window


This gives me all the rows of the most recent months records, I would like the count:

SELECT AbiBuilds.AbiCode, Max(AbiBuilds.BuildYearMonth) AS MaxOfBuildYearMonth
FROM AbiBuilds
GROUP BY AbiBuilds.AbiCode;

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 7
  • 5
  • 2
14 Comments
 

Author Comment

by:PeterBaileyUk
ID: 38402717
I got the total of current myself but the previous count eludes me:

SELECT Count(*) AS Nb
FROM (SELECT AbiBuilds.AbiCode, Max(AbiBuilds.BuildYearMonth) AS MaxOfBuildYearMonth
FROM AbiBuilds
GROUP BY AbiBuilds.AbiCode);
0
 

Author Comment

by:PeterBaileyUk
ID: 38402721
I took that idea SELECT Count(*) AS Nb from an experts advice previously
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38402752
What is [BuildYearMonth]?  What sort of data does it contain ... is it date?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PeterBaileyUk
ID: 38402770
yes its a string in the form "2012-08" etc
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38402782
SELECT TOP 2 AbiBuilds.AbiCode, Max(AbiBuilds.BuildYearMonth) AS MaxOfBuildYearMonth
FROM AbiBuilds
GROUP BY AbiBuilds.AbiCode
ORDER BY AbiBuilds.BuildYearMonth DESC 

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38402786
that fails saying i am trying to execute a query that does not include the specified expression buildyearmonth as part of an aggregate function

does this give me the record count?
0
 

Author Comment

by:PeterBaileyUk
ID: 38402793
I am looking for the record count for current month (max(buildyearmonth) - 1 (ie previous month). I never know what the current month year month is unles i use max on build year month I was hoping dateadd('m',-1,max([buildyearmonth])) would get me to those records but it didnt.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38402796
>> yes its a string in the form "2012-08" etc
err ... no, that means it is a string.  A date is specific datatype that contains complete date values.  Doing dateadd on a string should not work.  
Turn the value into a date first, e.g.
    dateadd('m',-1,max(datevalue([buildyearmonth]&'-01')))
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38402798
SELECT TOP 2 AbiBuilds.AbiCode, Max(AbiBuilds.BuildYearMonth) AS MaxOfBuildYearMonth
FROM AbiBuilds
GROUP BY AbiBuilds.AbiCode
ORDER BY Max(AbiBuilds.BuildYearMonth) DESC 

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38402809
This might help too
SELECT ab.AbiCode, datediff('m',datevalue([ab.buildyearmonth]&'-01'),latest_date) months_diff, count(*) as row_count
FROM AbiBuilds ab
INNER JOIN (SELECT AbiCode, max(datevalue([buildyearmonth]&'-01')) latest_date
            FROM AbiBuilds 
            GROUP BY AbiCode) mv
        ON ab.AbiCode = mv.AbiCode
GROUP BY ab.AbiCode, datediff('m',datevalue([ab.buildyearmonth]&'-01'),latest_date)
ORDER BY ab.AbiCode, datediff('m',datevalue([ab.buildyearmonth]&'-01'),latest_date) DESC

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38402835
ok id 38402798 didnt work it just returns current months codes. I tried the     dateadd('m',-1,max(datevalue([buildyearmonth]&'-01'))) and added it into the criteria line but it doesnt work ie returns no records.

ok this works:
so how do i create a criteria line that will take "2012-08" (as in latest buildyearmonth) and return records for "2012-07"? I chose the year hyphen month string as ee recommended it and that access would understand its a date without it being date format.

SELECT AbiBuilds.AbiCode, AbiBuilds.[BuildYearMonth]
FROM AbiBuilds
GROUP BY AbiBuilds.AbiCode, AbiBuilds.[BuildYearMonth]
HAVING (((AbiBuilds.[BuildYearMonth])="2012-07"));

Open in new window

0
 
LVL 25

Accepted Solution

by:
lwadwell earned 2000 total points
ID: 38402844
Yes "2012-08" will be interpreted as a date in a DATEADD() ... but it returns a date.  That date is not comparable to a string (text) value.  Run
    SELECT abicode, buildyearmonth, dateadd('m', -1, buildyearmonth) as less_1m from abibuilds
and see what I mean.  The date 1/07/2012 is not equal to the string '2012-07'.  So you needed to convert the types on both sides, e.g.
    HAVING (((datevalue(AbiBuilds.BuildYearMonth)&'-01')=DateAdd('m',-1,Max([buildyearmonth]))));



btw.  I fixed the other query I posted above (needed the 'as' before the alias name)
SELECT ab.AbiCode, datediff('m',datevalue([ab.buildyearmonth]&'-01'),latest_date) as months_diff, count(*) as row_count
FROM AbiBuilds ab
INNER JOIN (SELECT AbiCode, max(datevalue([buildyearmonth]&'-01')) as latest_date
            FROM AbiBuilds 
            GROUP BY AbiCode) mv
        ON ab.AbiCode = mv.AbiCode
GROUP BY ab.AbiCode, datediff('m',datevalue([ab.buildyearmonth]&'-01'),latest_date)
ORDER BY ab.AbiCode, datediff('m',datevalue([ab.buildyearmonth]&'-01'),latest_date)

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38402849
Alternatively ... take the date and format it back to a string
    HAVING AbiBuilds.BuildYearMonth=FORMAT(DateAdd('m',-1,Max([buildyearmonth])),"yyyy-mm");
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38402994
very elegant thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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