Posted by avode on 03/15/07 08:29

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)

[Back to original message]