SP to populate summary table - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

SP to populate summary table

Author

Topic

dish
Starting Member

3 Posts

Posted - 2005-04-10 : 05:47:46

Hello,

I have a transaction table containing a date, username and profit or loss.
eg
2005-04-03 ted 4.5
2005-04-03 tim -3.2
2005-04-03 ted -2.1
2005-04-02 jim 3.2

I would like to create a stored procedure that will populate a summary table periodically which will give me a summary of profit/loss by username over the last day, 7 days, 14 days, 28 days etc.
Then my app can simply retreive the data from this summary table.

I am new to stored procedures so would would be grateful if anyone could point me in the right direction here.At the moment I have to do all the work in my app which results in many calls to the DB. Thanks!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-10 : 12:44:19

Some sample code to create a summary table over last day, 7 days, 14 days, 28 days.

SET NOCOUNT ON
-- Create fictive transaction table
SELECT	DATEADD(hh,-number*2,FLOOR(CAST(GETDATE() AS FLOAT))) AS [date]
	,CASE number%3 WHEN 0 THEN 'ted' WHEN 1 THEN 'jim' ELSE 'tim' END AS username
	,(CAST(CAST(NEWID() AS BINARY(1)) AS INT)-128.0)/10.0 AS profit
INTO	#transactions
FROM	master..spt_values WHERE type = 'P'

-- Create a grouping table
DECLARE @grp TABLE(noDays TINYINT PRIMARY KEY)
INSERT @grp(noDays)
SELECT 1 UNION SELECT 7 UNION SELECT 14 UNION SELECT 28

-- Summarized table
SELECT noDays,username,SUM(profit) AS profit, COUNT(*) AS NoOfTransactions
FROM #transactions JOIN @grp
ON DATEDIFF(dd,[date],FLOOR(CAST(GETDATE() AS FLOAT))) < noDays
GROUP BY noDays,username
ORDER BY noDays,username

-- Display fictive transaction table
SELECT [date],username,profit
FROM #transactions
ORDER BY [date] DESC,username,profit

DROP TABLE #transactions

You could create a procedure that returns the summary table,
then have Your app call the sp at appropriate intervals, and cache the summary table.
That will reduce the trips to the server.
Calculate once, then cache the results (as long they are valid).

rockmoose

dish
Starting Member

3 Posts

Posted - 2005-04-10 : 15:02:45

Thanks very much for the reply. That's really helped. I had kind of imagined having a permanent summary table like

user L7 L14 L28
---- -- --- ---
ted 10 -6 -10
jim -1 10 23

where an sp would be called periodically to update the values. Then my app could just read the rows (2 in this example) as required. What do you think of that approach?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-10 : 15:34:23

Ok,
When working with precalculated values, the users must be aware that the data might not be 100% up to date.

If You retrieve the summary table for each query (no caching), and You want 100% up to date data,
then You might also consider the following:
1. Recalculate the summary table every day
2. Have a trigger on the table that updates the summary table during the day
(basically the trigger would only need to accumulate the sums for the daily transactions)

Pivot example:

SET NOCOUNT ON
-- Create fictive transaction table
SELECT	DATEADD(hh,-number*2,FLOOR(CAST(GETDATE() AS FLOAT))) AS [date]
	,CASE number%3 WHEN 0 THEN 'ted' WHEN 1 THEN 'jim' ELSE 'tim' END AS username
	,(CAST(CAST(NEWID() AS BINARY(1)) AS INT)-128.0)/10.0 AS profit
INTO	#transactions
FROM	master..spt_values WHERE type = 'P'

-- Create a grouping table
DECLARE @grp TABLE(noDays TINYINT PRIMARY KEY)
INSERT @grp(noDays)
SELECT 1 UNION SELECT 7 UNION SELECT 14 UNION SELECT 28

-- Summarized "pivot" table
SELECT	username
	,SUM(CASE WHEN noDays = 1 THEN profit ELSE 0 END) AS L1
	,SUM(CASE WHEN noDays = 7 THEN profit ELSE 0 END) AS L7
	,SUM(CASE WHEN noDays = 14 THEN profit ELSE 0 END) AS L14
	,SUM(CASE WHEN noDays = 28 THEN profit ELSE 0 END) AS L28
FROM #transactions JOIN @grp
ON DATEDIFF(dd,[date],FLOOR(CAST(GETDATE() AS FLOAT))) < noDays
GROUP BY username
ORDER BY username

-- Summarized table
SELECT noDays,username,SUM(profit) AS profit, COUNT(*) AS NoOfTransactions
FROM #transactions JOIN @grp
ON DATEDIFF(dd,[date],FLOOR(CAST(GETDATE() AS FLOAT))) < noDays
GROUP BY noDays,username
ORDER BY noDays,username

-- Display fictive transaction table
SELECT [date],username,profit
FROM #transactions
ORDER BY [date] DESC,username,profit

DROP TABLE #transactions

rockmoose

dish
Starting Member

3 Posts

Posted - 2005-04-10 : 16:13:30

Thanks rockmoose. I understand what you have done, but can't imagine how long it would have taken me to get there. Looks like I've got a lot to learn..... Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources