Home Page link  

Group by Date

  
Groups | Search | About
User Password
Register Now! Lost Password?

Point-Of-Sale Software - - MS Point Of Sale software discussed here

 Post an article  get this group's latest topics as an RSS feed add this group's latest topics to your My MSN content add this group's latest topics to your My Yahoo content  add this group's latest topics to your Google content  YahooMyWeb Yahoo!  Google Google  Windows Live Favorites Windows Live  del.icio.us del.icio.us  digg digg  Add to Netscape Netscape
Subject Author Date
Group by Date Holly 01-10-2006
---> Re: Group by Date Glenn Adams [MV...01-10-2006
Posted by Holly on January 10, 2006, 12:34 pm
Please log in for more thread options
 I am working on updating the Shipping Log report. I've added the Date
Created field, and I would like to group the report by that field, so that I
can get a total of shipping charges collected by day each month. However,
when I arrange the report to do that, the date field expands to include the
timestamp, so each shipping record is on its own line. Am I overlooking an
easy way to tell the report to exclude the timestamps and group by day only?
Any advice would be appreciated.



Posted by Glenn Adams [MVP - Retail Mgmt on January 10, 2006, 4:47 pm
Please log in for more thread options
 All date fields in SQL Server include the time. When you use Group By, the
values must match exactly to be grouped, so a date field doesn't work very
well fro grouping as you have seen as you end up with a group for each
second or millisecond...

To fix this you need to CONVERT the date into a string that only includes
the date...

Enter this in the "Formula" line of the Column definition for your new
column:

Formula = "CONVERT(datetime, CONVERT(varchar(10), DateCreated, 101), 101)"

This is going to convert the DateCreated to a text (varchar) value striping
off the time, then convert it back to a datetime with a default time of
00:00:00 so that all of the date filters will still work as they do
currently ...

Hope that helps...



--
--
Glenn Adams
Tiber Creek Consulting
http://www.tibercreek.com
glenn@tibercreek.com
----------------------------------------------
Please DO NOT respond to me directly but post all responses here in the
newsgroup so that all can share the information

>I am working on updating the Shipping Log report. I've added the Date
>Created field, and I would like to group the report by that field, so that
>I can get a total of shipping charges collected by day each month.
>However, when I arrange the report to do that, the date field expands to
>include the timestamp, so each shipping record is on its own line. Am I
>overlooking an easy way to tell the report to exclude the timestamps and
>group by day only? Any advice would be appreciated.
>
>



Posted by Holly on January 11, 2006, 10:08 am
Please log in for more thread options
Thanks guys, this worked out perfectly!


> All date fields in SQL Server include the time. When you use Group By,
> the values must match exactly to be grouped, so a date field doesn't work
> very well fro grouping as you have seen as you end up with a group for
> each second or millisecond...
>
> To fix this you need to CONVERT the date into a string that only includes
> the date...
>
> Enter this in the "Formula" line of the Column definition for your new
> column:
>
> Formula = "CONVERT(datetime, CONVERT(varchar(10), DateCreated, 101), 101)"
>
> This is going to convert the DateCreated to a text (varchar) value
> striping off the time, then convert it back to a datetime with a default
> time of 00:00:00 so that all of the date filters will still work as they
> do currently ...
>
> Hope that helps...
>
>
>
> --
> --
> Glenn Adams
> Tiber Creek Consulting
> http://www.tibercreek.com
> glenn@tibercreek.com
> ----------------------------------------------
> Please DO NOT respond to me directly but post all responses here in the
> newsgroup so that all can share the information
>
>>I am working on updating the Shipping Log report. I've added the Date
>>Created field, and I would like to group the report by that field, so that
>>I can get a total of shipping charges collected by day each month.
>>However, when I arrange the report to do that, the date field expands to
>>include the timestamp, so each shipping record is on its own line. Am I
>>overlooking an easy way to tell the report to exclude the timestamps and
>>group by day only? Any advice would be appreciated.
>>
>>
>
>



Posted by Rick Brown on January 11, 2006, 12:07 pm
Please log in for more thread options
That's a keeper! (g) Very handy.

> All date fields in SQL Server include the time. When you use Group By,
> the values must match exactly to be grouped, so a date field doesn't work
> very well fro grouping as you have seen as you end up with a group for
> each second or millisecond...
>
> To fix this you need to CONVERT the date into a string that only includes
> the date...
>
> Enter this in the "Formula" line of the Column definition for your new
> column:
>
> Formula = "CONVERT(datetime, CONVERT(varchar(10), DateCreated, 101), 101)"
>
> This is going to convert the DateCreated to a text (varchar) value
> striping off the time, then convert it back to a datetime with a default
> time of 00:00:00 so that all of the date filters will still work as they
> do currently ...
>
> Hope that helps...
>
>
>
> --
> --
> Glenn Adams
> Tiber Creek Consulting
> http://www.tibercreek.com
> glenn@tibercreek.com
> ----------------------------------------------
> Please DO NOT respond to me directly but post all responses here in the
> newsgroup so that all can share the information
>
>>I am working on updating the Shipping Log report. I've added the Date
>>Created field, and I would like to group the report by that field, so that
>>I can get a total of shipping charges collected by day each month.
>>However, when I arrange the report to do that, the date field expands to
>>include the timestamp, so each shipping record is on its own line. Am I
>>overlooking an easy way to tell the report to exclude the timestamps and
>>group by day only? Any advice would be appreciated.
>>
>>
>
>



Posted by dan levin on January 10, 2006, 11:55 pm
Please log in for more thread options
Hi Holly
here is an example SQL I wrote that illustrates grouping by date. It looks
mean, but it really isnt. Essentially it extracts the day, month and year
from the stored datetime field and then splices the extracted values into a
string with "/" (forward slash) seperators to form something like d/mm/yyyy.
It then calls the spliced date "TransactionDate". Note the comments
symbolised by --

select sum(Total) as 'TotalSales' , -- unimportant for your purposes

-- This is the line that extracts the date as a string
-- not the year extraction is 4 numbers long so leave 4 spaces in the
varchar for it else it will fail:: varchar(4)

cast(
cast(day(Time) as varchar(2))+'/'+
cast(month(Time) as varchar(2))+'/'+
cast(year(Time) as varchar(4))
as datetime) as TransactionDate

into #Tdate -- unimportant for your purposes
from [Transaction] -- unimportant for your purposes
where 1=1 -- unimportant for your purposes

-- group the date string exactly as extracted
-- same as the above cast excpet on 1 line
group by cast( cast(day(Time) as varchar(2)) +'/'+ cast(month(Time) as
varchar(2)) +'/'+ cast(year(Time) as varchar(4)) as datetime)

Hope this helps you.
Dan


>I am working on updating the Shipping Log report. I've added the Date
>Created field, and I would like to group the report by that field, so that
>I can get a total of shipping charges collected by day each month.
>However, when I arrange the report to do that, the date field expands to
>include the timestamp, so each shipping record is on its own line. Am I
>overlooking an easy way to tell the report to exclude the timestamps and
>group by day only? Any advice would be appreciated.
>
>



Similar ThreadsPosted
RE: Reports - group by date doesnt group by the date correctly March 7, 2007, 12:59 pm
Reports - group by date doesnt group by the date correctly March 7, 2007, 1:00 pm
Items with last received date greater than last sold date March 21, 2006, 9:23 pm
THANKS A LOT FOR THIS GROUP July 28, 2007, 5:14 pm
Group methods February 21, 2006, 4:41 pm
A PCCharge Pro Group Started November 11, 2005, 5:46 pm
Report Group Methods February 2, 2006, 3:50 pm
VPN, group policy, and best practices? August 14, 2006, 7:52 pm
"File Group Is Full" help!! August 30, 2006, 9:54 am
Separate Group of store July 18, 2007, 3:17 pm

Contact Us | Privacy Policy
This site is not affiliated with Intuit - makers of Quickbooks and Quicken software
This site is not affiliated with Sage Software - makers of Peachtree accounting software
XML SitemapXML Sitemap