DISTINCT MonthName for a lot of dates....

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 ...

> Any help would be appreciated
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:

>"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
>
Hi 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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> Looks good.
Thank you :o)

> Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
> I'm not sure of that.
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:

>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
>
Hi 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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> 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.
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)
> I'll get flamed now for
>posting needlessly...hehe..sorry all :o)
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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Displaying the dates in a column.

Can someone help me with this. I've been trying to produce it this way but still unsuccessful.
I'd like my table to look like this:

Month: January

Day wkDay
-
1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
. .
. .
31 Wed

I want to display the whole month..
Please help..
Thanks..Hi,

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

select datepart(d, getdate()) 'Day',
case datepart(dw, getdate())
when 1 then 'Sun'
when 2 then 'Mon'
when 3 then 'Tue'
when 4 then 'Wed'
when 5 then 'Thu'
when 6 then 'Fri'
when 7 then 'Sat'
end 'wkDay'

BuNnY_MoOn wrote:

Can someone help me with this. I've been trying to produce it this way but still unsuccessful.
I'd like my table to look like this:

Month: January

Day wkDay
-
1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
. .
. .
31 Wed

I want to display the whole month..
Please help..
Thanks..

|||

you don't need a CASE statement left,3 and datename(dw) is enough

select datepart(d, getdate()) 'Day', left(datename(dw, getdate()) ,3)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks..

But i was sort of thinking of listing all the dates as per indicated in my example:

Day wkDay

1 Mon
2 Tues
3 Wed
4 Thurs
5 Fri
6 Sat
7 Sun
8 Mon
9 Tues
10 Wed
11 Thurs
12 Fri
13 Sat
14 Sun
. .
. .
31 Wed

not just one day..

|||

here you go

--first create a number table--do this only ONCE!!!!
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0

WHILE @.intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)

SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO

--now run this
SELECT datepart(dd,DATEADD(dd,numberID,GETDATE())) as Day,left(datename(dw,DATEADD(dd,numberID,GETDATE())),3) as DayName
FROM dbo.NumberPivot
WHERE NumberID < 100

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks a bunch Sql Menace! |||Create a calendar table and use it rather than writing code. It is much more flexible, robust and can handle more scenarios easily (different types of calendars - fiscal, yearly; holidays; language settings etc). Search the WWW for pointers on how to build a Calendar table.|||Thanks

Umachandar Jayachandran - MS.
I'll take that into consideration..

displaying null dates

how to display NA in a date field if the date is NULL? Please Help.

select case when <Col Name> is null then 'NA'

else cast(<Col Name> as varchar(12))end <Col Name>

from <Table Name>

Mat

|||

You can use the following query..

Select Isnull(Convert(Varchar,DateColumnName,101),'NA') From YourTable

Here the date value will be converted as US format..

If you need both time & date use the following query..

Select Isnull(Convert(Varchar,DateColumnName,101) + ' ' + Convert(Varchar,DateColumnName,114),'NA') From YourTable

You can find all the date format convertion on Books Online under the title CAST and CONVERT

Displaying difference between two dates in the HH:mm:ss format

I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.

For example:

Start Date - 30/01/2007 09:00:01
End Date - 01/02/2007 20:40:04

When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!

I would like to be able to get it to be 59:40:03.

Any help would be much appreciated.I've worked it out like this:

First use DateDiff on the two fields using a hidden text box, then use the following code.

=IIF(Len(CStr(Fix(ReportItems!GrossTimeMins.Value/60)))= 1,
"0" + CStr(Fix(ReportItems!GrossTimeMins.Value/60)),
CStr(Fix(ReportItems!GrossTimeMins.Value/60)))
+ ":"
+ IIF(Len(CStr(Fix(ReportItems!GrossTimeMins.Value mod 60)))= 1,
"0" + CStr(Fix(ReportItems!GrossTimeMins.Value mod 60)),
CStr(Fix(ReportItems!GrossTimeMins.Value mod 60)))

If anyone thinks of a better way of doing it quite happy for you to comment.

Display Report Title in Report

Hello,
I am trying to display the report title with from and to dates. I currently have start and end dates as parameters and when I insert into my report in the 'Design' view the dates do not appear when refreshed. Please provide how to steps. Help!!!!
JSjust drag and drop ur date parameters in page header and preview report, it will prompt you to enter parameter values...

Display N/A Something When Data Does Not Exist

Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
of Values
On my report I put a filter on a list filtering out the dates with
expressions. Such as = (colDate.value = myDate)
But some time myDate does not exist in the dataset, so the after the filter
the list has no results.
What i want to achieve in this case is to display an N/A. i tried something
like this in the textbox contorl inside the list
=IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
Any help is appreciated. ThanksHi,
Why not try something within your stored procedure
Select Col1(ISNULL, 'N/A') FROM TABLE
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||Is this in an actual "list" control? There is a property in the properties
panel, called "NoRows" .. you simply type the text you want to display when
there are no rows in the list. Hope that fixes your problem.
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||thanks that worked.
"isamu" wrote:
> Is this in an actual "list" control? There is a property in the properties
> panel, called "NoRows" .. you simply type the text you want to display when
> there are no rows in the list. Hope that fixes your problem.
> "gMaster" wrote:
> > Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> > of Values
> > On my report I put a filter on a list filtering out the dates with
> > expressions. Such as = (colDate.value = myDate)
> >
> > But some time myDate does not exist in the dataset, so the after the filter
> > the list has no results.
> >
> > What i want to achieve in this case is to display an N/A. i tried something
> > like this in the textbox contorl inside the list
> > =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> >
> > Any help is appreciated. Thanks

DISPLAY DAtes

Hello

I have this as a calculated field for displaying dates like 1/1/2007

=DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value))

This format increments the day vaues fine but how can I get it to increment month vaues too.

i.e presently it gives me day numbers perfect 1/2/2007 ..2nd january

but for 5th february it gives date as 1/5/2007. month value remains 1.

any help appreciated.

thanks

I haven't tried this, but it looks like you are adding the minutes instead of months. Change the 'n' to an 'm'.

=DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("m", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value))

Hope this helps.

Jarret

|||

Thanks Jarret,

I do need to add the mkinute offset too..its the conversion from UTC to local time.

for the month I have done this now

=DATEADD("m",Parameters!MONTH.value,DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value,Parameters!BDateTime.Value)))

but this gives me an incremented month..

ie for Janury 5th..

it gives the date as 2/5/2007 i.e feb 5th

Thanks

|||

Oh, I'm sorry. Can you send some example records for TimeIncInteger, GMTOffSet, BDateTime, and how you want them to display as well?

Jarret

|||

All right,

The inc integer values go from 0-27 for february,

GMT offset is fixed at -360

and BDate Time is in the format Feb 1 2007 6:00AM

I think if I do DATEADD("m",Parameters!GMTOffSet.Value - 1, DATEADD("d", Fields!TimeIncInteger.Value , DATEADD("n", Parameters!GMTOffSet.Value, Parameters!BDateTime.Value)))

it would work but would it be best practice to put -1 there.

thanks

kiran

|||

Your original formula subtracts 6 hours from your BDateTime value, then adds TimeIncInteger days to that.

For example, the BDateTime value is 2/15/07 6am and the TimeIncInteger value is 21. The first thing in your expression is to remove 6 hours, making it 2/15/07 12am. Then, you add 21 days to this, making it 3/8/07 12am.

Depending on your starting day and increment value, the month will change. Is this not what you wanted?

Jarret

|||

Yes Jarret,

The day should change like that.

Waht I have in the report is dates by month where month is a parameter.

SO on selection of a month the day values should go from 1...end of month which happens by the time increment value.

The problem I was facing is since the parameter is a dropdown of month how to get the month value to change.

After adding =DATEADD("m",Parameters!MONTH.value-1,DATEADD("d"......................)

this works but I wanted to know of a better way to do so rather than using the -1.

Thanks for the help.

Kiran.