SQL select and count all items that have occured before

Question

I have a table with rows that symbolize order dates:

2009-05-15 13:31:47.713 
2009-05-15 22:09:32.227
2009-05-16 02:38:36.027
2009-05-16 12:06:49.743
2009-05-16 16:20:26.680
2009-05-17 01:36:19.480
2009-05-18 09:44:46.993
2009-05-18 14:06:12.073 
2009-05-18 15:25:47.540
2009-05-19 10:28:24.150

I would like have query that returns the following:

2009-05-15 2
2009-05-16 5
2009-05-17 6
2009-05-18 9
2009-05-19 10

Basically it keeps a running total of all the orders placed by the end of the day of the date indicated. The orders are not the orders on that day but all the orders since the earliest dates in the table.

This is MSSQL 2000 and the datatype in the first table is just datetime, in the second it could be datetime or string, it doesn't really matter for my purposes.

Answer 1

Try this (returns string dates):

SELECT
    LEFT(CONVERT(char(23),YourDate,121),10) AS Date
        ,COUNT(*) AS CountOf
    FROM YourTable
    GROUP BY LEFT(CONVERT(char(23),YourDate,121),10) 
    ORDER BY 1

this will table scan. if it is too slow, consider using a persistant computed column with an index for the date, that will run much faster. However, I'mnot sure if you can do all that in SQL 2000.

EDIT read the question better, try this:

SELECT
    d.YourDate
        ,SUM(dt.CountOf) AS CountOf
    FROM (SELECT
              LEFT(CONVERT(char(23),YourDate,121),10) AS Date
                  ,COUNT(*) AS CountOf
              FROM YourTable
              GROUP BY LEFT(CONVERT(char(23),YourDate,121),10) 
        ) dt
        INNER JOIN (SELECT
                        DISTINCT LEFT(CONVERT(char(23),YourDate,121),10) AS Date
                        FROM YourTable
                   ) d ON dt.Date<=LEFT(CONVERT(char(23),d.YourDate,121),10)
    GROUP BY d.YourDate
    ORDER BY d.YourDate

Answer 2

SELECT Count(*), LEFT(CONVERT(char(23),YourDate,121),10) AS Date FROM
(SELECT
DISTINCT LEFT(CONVERT(char(23),YourDate,121),10) AS Date
FROM YourTable
GROUP BY LEFT(CONVERT(char(23),YourDate,121),10)) x //Gets the distinct dates.
INNER JOIN YourTable y on x.Date >= y.Date
GROUP BY LEFT(CONVERT(char(23),YourDate,121),10)

It's going to be slow. REALLY REALLY slow. I hate to think what run times would be.

Answer 3

I recommend a 2 query solution. This is slow, but I use this method almost daily. The important thing is to NOT join the 2 tables in the first query. You want the duplication of each order for every date in your lookup table.

You will need a Lookup table with 1 row for each date of the time period you're interested in. Let's call it dboDateLookup. Here's what it will look like:

DtIndex
2009-05-15
2009-05-16
2009-05-17
2009-05-18
2009-05-19

Let's also assume the order table, dboOrders has 2 columns, ordernumber and orderdate. ordernumber orderdate
2009-05-15 13:31:47.713 1
2009-05-15 22:09:32.227 2
2009-05-16 02:38:36.027 3
2009-05-16 12:06:49.743 4
2009-05-16 16:20:26.680 5

Query1:
SELECT
Format([ordernumber],"yyyy-mm-dd") AS ByDate,
ordernumber,
(If Format([orderdate],"yyyy-mm-dd")<=[DtIndex],1,0) AS NumOrdersBefore
FROM [dboOrders], [dboDateLookUp];

Query2:
Select
[ByDate],
sum([NumOrdersBefore]) as RunningTotal
from [Query1];

Answer 4

I got this to work on SQL Server 2005. I think it should work with 2000, as well.

SELECT dt, count(q2.YourDate)
    FROM (SELECT DISTINCT CONVERT(varchar,YourDate,101) dt FROM YourTable) t1
    JOIN YourTable q2 ON DATEADD(d,-1,CONVERT(varchar,YourDate,101)) < dt
    GROUP BY dt

This will query the table twice, but at least gives correct output.

Answer 5

I have another one It is not so fancy I ran it on Access so syntax may differ little bit. But it seems to work.

P.S. Im relatively new to SQL

Data:

ID  F1 F2
1   15/05/2009 13:31:47.713
2   15/05/2009 22:09:32.227
3   16/05/2009 02:38:36.027
4   16/05/2009 12:06:49.743
5   16/05/2009 16:20:26.680
6   17/05/2009 01:36:19.480
7   18/05/2009 09:44:46.993
8   18/05/2009 14:06:12.073
9   18/05/2009 15:25:47.540
10  19/05/2009 10:28:24.150

Query:

SELECT Table1.F1 AS Dates, SUM(REPLACE(Len(Table1.F2), Len(Table1.F2), 1)) AS Occurred
FROM Table1
GROUP BY Table1.F1;

Result:

Dates   Occurred
15/05/2009  2
16/05/2009  3
17/05/2009  1
18/05/2009  3
19/05/2009  1