Re: Running totals on a select count statement

From: Steve Kass (skass_at_drew.edu)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 00:45:58 -0500

Can you let me know what's wrong with this result? I assume part of the
problem is that comparing dd/mm/yyyy strings doesn't put things in date
order - you should just compare the actual datetime values, or if
Creation_Date is not a datetime column, cast it to datetime for the
comparison. Aside from that, you'll have to give me the source data and
result you want, or I won't be able to tell you how to get it.

SK

zack wrote:

>Steve,
>
>Im almost there but Im running into a problem with things being out of order. Here is what I have so far:
>SELECT convert(Char(10), AR.Creation_Date, 101) AS DateWritten, COUNT(AR.Creation_Date) AS Written,
> (
> Select count(AR2.Creation_Date) from dbo.vw_Nothing AR2
> where convert(Char(10), AR2.Creation_Date, 101) <= convert(Char(10), AR.Creation_Date, 101)
> )
> as RunningTotal
>FROM dbo.vw_Nothing AR
>GROUP BY DATEPART(yy, AR.Creation_Date), DATEPART(mm, AR.Creation_Date), DATEPART(dd, AR.Creation_Date), CONVERT(Char(10), AR.Creation_Date, 101)
>
>returns:
>11/10/2003 1 32
>11/11/2003 4 36
>11/13/2003 1 37
>12/18/2003 1 38
>12/22/2003 1 39
>12/23/2003 6 45
>12/24/2003 2 47
>12/30/2003 3 50
>01/12/2004 9 9
>01/29/2004 3 12
>01/30/2004 1 13
>02/06/2004 1 14
>02/10/2004 3 17
>02/24/2004 2 19
>03/04/2004 11 30
>03/05/2004 1 31
>
>Whats happening is that the ordering is lost in the subquery and I can not specify an order by clause in the sub query. When I order the outside sql correctly the count is then incorrect. Any thoughts????? Possible a different date function.
>
>

Relevant Pages

  • still trying to compare dates
    ... causes the SQLserver not access an index defined on a datetime column. ... Documentation on DATEDIFF is flawed: ... Is the parameter that specifies on which part of the date to calculate ... Therefore comparing dates using ...
    (microsoft.public.sqlserver)
  • Re: Set selection to previous month
    ... because you're comparing a DateTime field to a number. ... Because these record selection formulas are wrong. ...
    (microsoft.public.vb.crystal)
  • Re: Comparing dates in a query statement
    ... I was comparing the text box ... > I don't know why this doesn't work with your unbound textbox. ... >> Does the following statement strip the time from a datetime field: ... >>> function to convert back to a DateTime or SmallDateTime field if you ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Comparing date and time
    ... You either have 2 DateTime values, or you have 2 strings in the format ... They are pure data. ... Note that I spoke of comparing DateTime values. ... Comparing 2 strings will only tell you which string is alphabetically before ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: date comparioson in a Case statement
    ... > I have a case statement where I am comparing two dates ... > Syntax error during implicit conversion of VARCHAR value 'NA' to a DATETIME ... I'm sure there is some ugly mess of cast statements you could make that ...
    (microsoft.public.sqlserver.programming)