SEO

#1 (**permalink**) 03-01-2008, 01:41 PM

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!

#2 (**permalink**) 03-01-2008, 01:41 PM

On Mar 14, 8:04 am, "Spook" <S...@mailinator.com> wrote:
> 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

#3 (**permalink**) 03-01-2008, 01:41 PM

"Spook" <Spook@mailinator.com> wrote in message
news:JzJJh.277$rj1.98@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

#4 (**permalink**) 03-01-2008, 01:41 PM

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.

#5 (**permalink**) 03-01-2008, 01:41 PM

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@earthlink.net> wrote in message
news:1173896183.884591.226260@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.
>

#6 (**permalink**) 03-01-2008, 01:42 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

SQL Query help