SQL Query help

[Spook]

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!

[M A Srinivas]

On Mar 14, 8:04 am, "Spook" <[e-mail]:

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!

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

[Plamen Ratchev]

"Spook" <Sp***@mailinator.comwrote in message
news:Jz**************@newssvr23.news.prodigy.net.. .

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

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)

>
Eventually getting it to this output:

Date TotalOrder
2007-03-01 5

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

[--CELKO--]

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.

[Spook]

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--" <jc*******@earthlink.netwrote in message
news:11**********************@p15g2000hsd.googlegr oups.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.

[avode]

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
av******@yandex.ru
(remove GOV to respond)