Proper logic for a stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Proper logic for a stored procedure

Proper logic for a stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

Shadow.2802

Posted Monday, June 14, 2010 2:20 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 1:28 PM
Points: 10, Visits: 38

Experts:

I have a report that I need to generate through multiple queries.
Basically, I am using three tables pulling the same fields:
Table #1- cb_nd_daily, Table #2 cc-sv_daily, Table#3-CD_daily.
From these tables, I need to pull the
br---->branch
type----->product type
bibal----->acct balance
acct count----->number of accts

What I am doing is I am getting the total dollar amount of checking, savings, and cds for this institution. Once I get the total, I need to run another query that would break down the product type(checking,savings,CD) by different branches. So I wrote 22 queries and here is just an example of them:
1) Here I am getting the total amount of checking deposits this bank has and total number of checking accounts
Select sum(bibal),count(acct) from cb_nd_daily
---- I would then do the same for savings accounts pulling from SV(cb_sv_daily) and CD from (cb_cd_daily)

The next set of queries I have to filter out by either br # or type.

2) select sum(bibal),count(acct) from cb_nd_daily
where br = 291
----here I am getting the toal of checking accounts and amount for branch 291. I would then apply the same filter for savings and cd

3) Select sum(bibal),count(acct) from cb_nd_daily
where br= 291
and type in (52,77)
-----Here I want to pull all accts that are type 52 & 77)

How do I begin to create a stored procedure where the information would populate for the entire institution and then run subquent queries to populate information based on those filters.

I was thinking of creating a table first, do an insert, and summary but kind of lost on how this logic would work. Any assistance will be appreciated. Thanks

Post #937125

Douglas Hudson

Posted Thursday, July 08, 2010 8:50 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, January 17, 2012 10:48 AM
Points: 20, Visits: 12

I'm not clear on why you have three tables with what appears to be the same data, but it sounds to me as though it would make sense to create a table of summarized data, then use that for various reporting purposes. Periodically you would delete (or truncate) the table and re-populate it. You would want to populate it with something like this:

select type, br, sum(bibal), count(*)
from <table>
group by type, br

That would give you balance totals and counts by branch and type. You could either use the summary table directly for reporting, or summarize it further for totals by branch (across all types) or type (across all branches). This type of approach is commonly used in financial reporting applications -- the data might be refreshed monthly. For a financial institution, you might want to refresh the data daily (overnight).

This approach makes sense if the volume of data is very large (so you don't want to be re-querying the raw data many times) and it's OK if the data is as of a particular point in time (like last night, or last month). If the volume of data isn't too large, or you need to have current, real-time results, then the summary-table approach would not make sense. In that case, you probably want to create a couple of stored procedures with parameters for the branch or type, so they are not hard-coded. Two or three stored procs would probably cover most of it -- no way you should need twenty of them.

Doug

Post #949356

CELKO

Posted Monday, July 12, 2010 3:40 PM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Please post DDL instead of a narrative description, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you wantto learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

If you don't know anything about RDBMS, then get a copy of the simplest intro book I know -- http://www.amazon.com/Manga-Guide-Databases-Mana-Takahashi/dp/1593271905

This sounds like attribute splitting, a common design error, where a set of entities are put in many tables when they need to be in one.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #951080

« Prev Topic | Next Topic »

Permissions