Count all records of a day and write into Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Count all records of a day and write into Table

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-09-04 : 19:31:48

Hi all,

I need something similar like this: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100581
The main difference: After counting I want to delete the single entries.

Date................................ Value
2008-04-01 14:48:15.327 ... A
2008-04-01 13:53:41.923 ... A
2008-04-02 15:16:25.327 ... B
2008-04-02 16:31:07.383 ... C
2008-04-02 16:25:07.383 ... A
2008-04-02 16:28:07.383 ... A
2008-04-03 16:50:34.217 ... B
2008-04-03 13:23:30.767 ... C
2008-04-04 13:41:41.627 ... D
2008-04-04 13:55:00.497 ... E

I don't care about 'Value', this column could be ignored. I need a script I start once a day. It should count all records for all previous days, day by day, and write the total amount for each day into another SQL table. After that these processed rows should be deleted from the table. So in above sample let's say today is 2008-04-04 and I start the SP. After that the table should look like this:
2008-04-04 13:41:41.627 ... D
2008-04-04 13:55:00.497 ... E

And the Summary table should look like this:
Date........... Count
2008-04-01 ... 2
2008-04-02 ... 4
2008-04-03 ... 2

Or would it be better to do the counting already while writing the single rows? Currently I just add new records to the first table without checking anything. It might be possible to modify the 'AddRecord'-SP so instead of writing lots of single lines for each day I could already compute the total directly. I've just rejected this idea as this might decrease the performance?
Many thanks!

preethi
Starting Member

11 Posts

Posted - 2008-09-04 : 23:06:38

I will go for a solution like this:

DECLARE @Today datetime

SET @Today = DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))

INSERT INTO <DailySummary> ([datecolumn],[CountValue])
SELECT DATEADD(day, 0, DATEDIFF(day, 0, [datecolumn])), COUNT(*)
FROM <Table>
WHERE
	[datecolumn]< @Today
GROUP BY DATEADD(day, 0, DATEDIFF(day, 0, [datecolumn]))

DELETE <Table> WHERE [datecolumn] < @Today

I will add some error handling code to make sure that I do not delete if Insert fails.

For your second question, it depends on the usage. If the server is quite busy most of the time, I would suggest that you should simply dump data (like what you did) and do the rollup during off peak hours. If you do not have time for large processing, but your server has some time for processing all the time, I will go with Instant summary approach.

Hope this Helps!

Cheers,
Preethiviraj Kulasingham
MCITP: DBA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-04 : 23:10:06

[code]DECLARE @table1 TABLE
(
[Date] datetime,
Value CHAR(1)
)

INSERT INTO @table1
SELECT '2008-04-01 14:48:15.327', 'A' UNION ALL
SELECT '2008-04-01 13:53:41.923', 'A' UNION ALL
SELECT '2008-04-02 15:16:25.327', 'B' UNION ALL
SELECT '2008-04-02 16:31:07.383', 'C' UNION ALL
SELECT '2008-04-02 16:25:07.383', 'A' UNION ALL
SELECT '2008-04-02 16:28:07.383', 'A' UNION ALL
SELECT '2008-04-03 16:50:34.217', 'B' UNION ALL
SELECT '2008-04-03 13:23:30.767', 'C' UNION ALL
SELECT '2008-04-04 13:41:41.627', 'D' UNION ALL
SELECT '2008-04-04 13:55:00.497', 'E'

DECLARE @table2 TABLE
(
[Date] datetime,
Cnt int
)

DECLARE @today datetime

SELECT @today = '2008-04-04'

INSERT INTO @table2 ([Date], Cnt)
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0), COUNT(*)
FROM @table1
WHERE [Date] < @today
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, [Date]), 0)

DELETE t1
FROM @table1 t1
WHERE [Date] < @today

SELECT *
FROM @table1

/*
Date Value
----------------------- -----
2008-04-04 13:41:41.627 D
2008-04-04 13:55:00.497 E

(2 row(s) affected)
*/

SELECT *
FROM @table2
/*
Date Cnt
------------------------ -----------
2008-04-01 00:00:00.000 2
2008-04-02 00:00:00.000 4
2008-04-03 00:00:00.000 2

(3 row(s) affected)
*/

[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-04 : 23:11:58

forgot to refresh

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources