Calculations with Queries
-
I have written a Stored Procedure to Run on a daily basis to record the number of records in each of the tables within my database. The ouput of this script is to a table and is as follows...
Date TableName NoOfRecords
23-04-2003 TableA 10
23-04-2003 TableB 50
24-04-2003 TableA 15
24-04-2003 TableB 60
I want to be able to work out the daily increase in records of each of the tables. Does anyone have any ideas,
Thanking you all in advance
-
Hi,
do you want to store this in your db? Or calculate it at runtime?
Is there a primary key in your table?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
[RunTime would be fine, I have a Primary Key Called RecNo, Which is just incrememted. Thanks ]
Hi,
do you want to store this in your db? Or calculate it at runtime?
Is there a primary key in your table?
Cheers,
Frank
[/quote]
-
You might try something like this:
SET DATEFORMAT DMY
CREATE TABLE ABC ([DATE] DATETIME, TABLENAME CHAR(10), NOOFRECORDS INT)
INSERT INTO ABC VALUES ('23-04-2003','TABLEA',10)
INSERT INTO ABC VALUES ('23-04-2003','TABLEB',50)
INSERT INTO ABC VALUES ('24-04-2003','TABLEA',15)
INSERT INTO ABC VALUES ('24-04-2003','TABLEB',60)
INSERT INTO ABC VALUES ('25-04-2003','TABLEA',20)
INSERT INTO ABC VALUES ('25-04-2003','TABLEB',80)
SELECT T1.[DATE],T1.TABLENAME, T1.NOOFRECORDS
, (SELECT TOP 1 T1.NOOFRECORDS - NOOFRECORDS FROM ABC WHERE [DATE] < T1.[DATE] AND TABLENAME = T1.TABLENAME
order by [date] desc )
AS DIFF
FROM ABC T1
DROP TABLE ABC
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Hi Greg,
I have adapted your example. It works well on that 6 records. In my table there are about 21,000 records and 177 tablenames. Running your query results in a timeout. Any ideas?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry Correction,
No timeout, nut after 5 Minutes I cancelled the query...
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Second correction,
finally after some 15 minutes I have an result. Next time I will use a WHERE clause to SELECT only some tablenames of interest.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
In reality do you really want to return all rows for every day, or do you only want to return rows for the latest day. If this is the case, then this should speed things up a bit. This only selects the records for the latest date.
declare @d datetime
select top 1 @d=[date] from abc order by [date] desc
SELECT T1.[DATE],T1.TABLENAME, T1.NOOFRECORDS
, (SELECT TOP 1 T1.NOOFRECORDS - NOOFRECORDS FROM ABC WHERE [DATE] < T1.[DATE] AND TABLENAME = T1.TABLENAME
order by [date] desc )
AS DIFF
FROM ABC T1
where [date] = @d
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Since you state is recorded on a daily basis then you should not have gaps in data. If that is the case this may run better with the same results.
SELECT
T1.[DATE],
T1.TABLENAME,
T1.NOOFRECORDS,
(T1.NOOFRECORDS - ISNULL(T2.NOOFRECORDS,T1.NOOFRECORDS)) AS DIFF
FROM
ABC T1
LEFT JOIN
ABC T2
ON
T1.TABLENAME = T2.TABLENAME AND
T1.[DATE] = DATEADD(d,1,T2.[DATE]) -
Hi Antares686,
but what, if you were to apply this on a case where there are gaps.
Meaning calculating performance of a stock, mutual fund or so...If you have the complete history you still have to cope with weekends, bank holidays.
As you are working for a bank are there any requirements for this by your specialist departments or is this one thing the they must do on their own?
Just asking that, because I'm working as an asset manager and doing this thing using spreadsheets. But would be glad to implement that into SQL Server.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry,
your last answer and my question overlapped.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
That was what I was affraid of. However weekends can be handled with the following.
SELECT
T1.[DATE],
T1.TABLENAME,
T1.NOOFRECORDS,
(T1.NOOFRECORDS - ISNULL(T2.NOOFRECORDS,T1.NOOFRECORDS)) AS DIFF
FROM
ABC T1
LEFT JOIN
ABC T2
ON
T1.TABLENAME = T2.TABLENAME AND
T1.[DATE] = DATEADD(d,(
CASE DATEPART(dw,T2.[DATE])
WHEN 6 THEN 3 -- When Friday need Monday hich is 3 days away.
WHEN 7 THEN 2 -- When Saturday, Monday is two days away.
ELSE 1 -- All others need next day.
END),T2.[DATE])I am sure a lookup table for holidays with the number of days to add will work for holidays as well. As for other gaps Let me think about awhile and if I can think of anything to deal with a potential non-exception gap I will post and email to let you know.
As for requirements group I am unaware of one. I am on my own.
If anyone else wants to add please do. Other concepts without the extra select I would be interested in other options or additional comment on mine.
-
Hi,
thanks for posting in advance
Maybe I haven't expressed myself precise. What I wanted to know, is if you have to deal with that kind of request (performance calculations or other financial calculations) from other departments and implement them in SQL Server or is this one thing the other department must do on their own in island solutions.
I hope things have become more clear?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sorry, I didn't understand. I generally don't deal with other groups with that specific type of need. I am an island to myself altough I do import and export data from/to other areas. For the most part I believe that even related systems tend to handle this type of logic themselves individually just for integirity purposes and enforcement of their specific business rules.
-
quote:
I generally don't deal with other groups with that specific type of need. I am an island to myself altough I do import and export data from/to other areas.
You're the lucky one
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply