Re: Running totals on a select count statement

From: zack (anonymous_at_discussions.microsoft.com)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 06:01:09 -0800

Steve,

The problem is that it the inner sql does not allow an order by clause so I can not break out the y/m/d in the order so that its correct for when its rolling up the total. I need to group these by date w/o time which is a pain in SQL Server because it holds time value with it. I cant seem to find a good function for stripping off the time that still allows it to be seen as a date not a string for sorting purposes. If there is a function thats similiar to MS Access DateValue or a user defined function that I can create to return a "short" date that will be sorted correctly. Here is some sample data and what Im looking for.

P_ID Creation_Date
1 11/10/2003 10:00 am
2 11/11/2003 8:00 am
3 11/11/2003 9:00 am
4 11/11/2003 10:00 am
5 11/11/2003 11:00 am
6 1/12/2004 4:00 am
7 1/12/2004 4:01 am
8 1/12/2004 5:00 am
9 1/12/2004 6:00 am
10 1/12/2004 7:00 am
11 1/12/2004 8:00 am
12 1/12/2004 9:00 am
13 1/12/2004 10:00 am

Looking for -------------------------------------------------
Date Amount SQL Server Return Wanted return
11/10/2003 1 9 1
11/11/2003 4 13 5
1/12/2004 8 8 13

Thanks for all of your help so far I feel like Im so close to getting it correct, but Im not sure if its possible because of the date sorting problems with the inner sql.

zack