I have a SQL table with the following fields:

accounts, orderid's and datetime



Account OrderID Datetime
1 1 2007-03-01 09:30
1 2 2007-03-01 09:35
10 3 2007-03-01 10:30
2 4 2007-03-01 11:30
10 5 2007-03-01 12:30


Using Query Analyzer, I'd like to run a query where the results are a count
of orderId's by account on any given day like what I have below:



Account Orders Date
1 2 2007-03-01
2 1 2007-03-01
10 2 2007-03-01


Eventually getting it to this output:

Date TotalOrder
2007-03-01 5

Thanks for the help!

On Mar 14, 8:04 am, "Spook" <S... (AT) mailinator (DOT) com> wrote:
Try this

declare @tbla table (account int,orderid int, record_date datetime)
insert into @tbla values (1,1,'2007-03-01 09:30')
insert into @tbla values (1,2,'2007-03-01 09:35')
insert into @tbla values (10,3,'2007-03-01 10:30')
insert into @tbla values (2,4,'2007-03-01 11:30')
insert into @tbla values (10,5,'2007-03-01 12:30')

select
T. from (
select account,convert(varchar(10),record_date,101) as record_date,
count(*) as NoofOrders
from @tbla
group by
account,
convert(varchar(10),record_date,101)
with cube ) T
where
(
(T.account is not null and T.record_date is not null )
OR
(T.account is null and T.record_date is null )
)

M A Srinivas

"Spook" <Spook (AT) mailinator (DOT) com> wrote

Here is a query to get you this one:

SELECT Account, COUNT(*) AS Orders, CONVERT(CHAR(10), [Datetime], 126) AS
Date
FROM Orders
GROUP BY Account, CONVERT(CHAR(10), [Datetime], 126)

And here is the next:

SELECT CONVERT(CHAR(10), [Datetime], 126) AS Date, COUNT(*) AS TotalOrders
FROM Orders
GROUP BY CONVERT(CHAR(10), [Datetime], 126)

Regards,

Plamen Ratchev
http://www.SQLStudio.com

the other thing you need to consider is adding a constraint to be sure
that the time is always set to 00:00:00 Hrs so that you do not have
fix it on the fly in your queries. Mop the floor but also fix the
leak.

Thank you all for your help! Plamen's idea was exactly what I needed. Celko,
We need the datetime to be accurate to verify the order times in case of
delays in dispatching.





"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

Spook,

You may want to look at using computed columns,
see SQL Server Books Online for more information.

CREATE TABLE #journal(
account INTEGER NOT NULL,
orderid INTEGER NOT NULL,
record_date DATETIME NOT NULL,
yy AS DATEPART(YY, record_date),
mm AS DATEPART(MM, record_date),
dd AS DATEPART(DD, record_date),
PRIMARY KEY NONCLUSTERED(record_date, account, orderid),
UNIQUE CLUSTERED(yy, mm, dd, account, orderid));

INSERT INTO #journal VALUES ( 1, 1, '20070301 09:30');
INSERT INTO #journal VALUES ( 1, 2, '20070301 09:35');
INSERT INTO #journal VALUES (10, 3, '20070301 10:30');
INSERT INTO #journal VALUES ( 2, 4, '20070301 11:30');
INSERT INTO #journal VALUES (10, 5, '20070301 12:30');

SET STATISTICS IO ON;

SELECT account, COUNT(*), yy, mm, dd
FROM #journal
GROUP BY yy, mm, dd, account;

SET STATISTICS IO OFF;

DROP TABLE #journal;

---
Andrey Odegov
avodeGOV (AT) yandex (DOT) ru
(remove GOV to respond)