Hi all,

I have a table with several rows, each has a datetime field.

I want to query this table, ideally with my stored procedure and return just
a set of month names/numbers if possible, but I keep going around in circles
either getting ALL of my dates back with the names in a new column, or only
the month names, but order incorrectly...

table structure:

PregnancyLog

LogID int
LogDateTime datetime

sample data

LogID, LogDateTime
1,29/01/05
2,30/01/05
3,01/02/05
4,03/02/05
5,04/02/05
6,11/03/05
7,12/03/05
8,23/04/05
9,12/08/05

Expected results

MonthName, MonthNumber
January, 1
February, 2
March, 3
April, 4
August, 8

Any help would be appreciated - my only current resolution would be to
create a view of my data which gets me the month names, and then do a
distinct on that with the stored procedure, but I'd rather just do it once
in the stored procedure if possible.

Regards

Rob

"Rob Meade" wrote ...

I hate it when this happens....looks like I might have sussed it myself...

SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)

Does that look acceptable to anyone?  It gives me the results I wanted but I
just wanted to make sure..

Regards

Rob

On Thu, 24 Nov 2005 23:20:43 GMT, Rob Meade wrote:

Show quoteHide quoteHi Rob,

Looks good.

Here's an (untested) alternative:

SELECT DISTINCT DATENAME(month, LogDateTime) AS MonthName,
                MONTH(LogDateTime)
FROM PregnancyLog
ORDER BY MONTH(LogDateTime)

Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
I'm not sure of that.

Best, Hugo

"Hugo Kornelis" wrote ...

Thank you :o)

Cheers for that Hugo, it worked a treat, I left the MONTH(LogDateTime) in,
and added an alias of MonthNumber as I use this in the application.

But its still less code than I had - many thanks :o)

Regards

Rob

Hi Hugo,

Any ideas how I would add a "count" to the end of the result set of the
number of log items for each month returned by the existin query...

Ie...

MonthName    MonthNumber    Counter
January                    1                     2
February                  2                     6
March                      3                   15

Any help would be really appreciated, I've tried adding COUNT(LogID) to my
query, but then I get message telling me that things need adding to the
aggregate function or the group by clause, which I did try adding again but
then I have to lose the order by or else I get EVERY row
again....nightmare..

Any help appreciated.

Regards

Rob

On Fri, 25 Nov 2005 23:17:45 GMT, Rob Meade wrote:

Show quoteHide quoteHi Rob,

If you need to add a count (or any other aggregate function), then you
can't use my shorter version; you'll have to return to your original
version with GROUP BY.

SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
       COUNT(LogID) AS Counter
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)

should work. If not, you'll need to provide more information, as
described in www.aspfaq.com/5006.

Best, Hugo

"Hugo Kornelis" wrote ...

Hi Hugo,

Worked a treat, many thanks - I thought I tried exactly that, but obviously
not, when I tried it, SQL moaned that I needed to add LogDateTime to the
GROUP BY....

Typical that I'd only just posted to see if I could get a few others to look
in this thread from yesterday as I wasn't sure if you'd return to this
message - and you've already solved it - lol - I'll get flamed now for
posting needlessly...hehe..sorry all :o)

Thanks muchly for the help - the website I'm creating is all about my new
born son, so its kinda important to me - thus appreciate the help even more
than usual :o)

Regards

Rob

On Fri, 25 Nov 2005 23:31:09 GMT, Rob Meade wrote:

(snip)
Hi Rob,

If you insist, I think I can arragne you being flamed. Do you want me to
call Celko over? ;->

Congratulations on your boy. Don't spend all your time building the
website - spend plenty time enjoying him. They grow up so fast......

Best, Hugo

Dynamic Stored Procedure
Dynamic WHERE & Dates
updating column
Sum() And Nulls
selecting from a 6 month date range