Running Total using SQL Server with datetime

• • Reply

  18 years ago

  by fiona

  fiona

  New Zealand, New Zealand

  Joined 18 years ago

  I am trying to create a running total on a datetime function.  I don't have a unique id? but at present I can do a count on the number of people who registered for an event in a day by using CONVERT.
  

  
  However, I want it to display a running total, rather than a count of the number of people who register per day, but I can't seem to do this.  
  Please help!!  Also the query relates to more than 1 table.
  This is an example:
  

  
  

  
  select    CONVERT(datetime,
            (CONVERT(varchar,DAY(sea.date))+'/'
            +CONVERT(varchar,MONTH(sea.date))+'/'
            +CONVERT(varchar,YEAR(sea.date))),
                            105) AS RegistrationDate
         
    , COUNT  (CONVERT(datetime,
            (CONVERT(varchar,DAY(sea.date))+'/'
            +CONVERT(varchar,MONTH(sea.date))+'/'
            +CONVERT(varchar,YEAR(sea.date))),
                            105)) As RegNos
  from    table1 sea
    INNER JOIN table2 scc ON sea.number1= scc.number5
    LEFT JOIN table3 s ON s.number2 = scc.number6
    INNER JOIN table4 con ON con.number3 = scc.number7
    LEFT JOIN table5 org ON org.number4= con.number8
  WHERE    blah = 'blah'
  AND    blah = 'blah'
  AND    blah = 'blah'
  AND    blah = 'blah'
  GROUP BY CONVERT(datetime,
            (CONVERT(varchar,DAY(sea.date))+'/'
            +CONVERT(varchar,MONTH(sea.date))+'/'
            +CONVERT(varchar,YEAR(sea.date))),
                            105))

  Report abuse