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.
| Author |
Topic |
|
fatin
Starting Member
2 Posts |
 Posted - 2011-07-10 : 10:52:03
|
| Hi, I`m new with SQL and I really need help on how to generate a code for below exercise.PART C Table: Transaction CustID TransDt Amount AccountType 55555 11-Jun-03 114.56 Savings 55555 12-Jun-03 119.56 Savings 55555 21-Jun-03 -56.78 Checking 55555 7-Jul-03 359.31 Savings 55555 19-Jul-03 89.56 Checking 55555 3-Aug-03 1000 Savings 77777 3-Dec-03 645.21 Savings 55555 17-Aug-03 -1200 Checking Desired results: CustID C6 C7 C8 S6 S7 S8 S12 55555 -56.78 89.56 -1200 234.12 359.31 1000 77777 645.21 C6 - Checking for Jun, C7 - Checking for Jul, C8 - Checking for Aug S6 - Savings for Jun, S7- Savings for Jul, S8- Savings for Aug, S12- Savings for Dec Roll up the Transaction table to the desired results as shown above. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-10 : 11:38:33
|
| If you're on SQL 2005 or above, look up the PIVOT keyword.--Gail ShawSQL Server MVP |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-07-10 : 15:40:07
|
| People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.Why are you doing Oracle proprietary date formats in a SQL Server Forum? CREATE TABLE Transaction_Journal (cust_id CHAR(5) NOT NULL, trans_date DATE NOT NULL account_type CHAR (3) NOT NULL CHECK (account_type IN ('sav', 'chk') PRIMARY KEY (cust_id, trans_date, account_type) trans_amt DECIMAL (8,2) NOT NULL);>> Desired results: .. I want Entire Stored Procedure to get a "Quarterly Report" for particular month base on the date from database like this: <<Why are you doing display formatting in the database? That is not how we do a tiered architecture; that was 1950's COBOL and monolithic programming. Ad the database guy, you put together the data and throw it over the wall to the front end. That front end can be a report server, iPhone, or anything – we do not care. We are writing SQL and you are still writing 1950's COBOL. Since SQL is a data language, we want a data driven so part of our programming idioms is a report period table. It has a report period name, start date for the period and end date for the report period. SELECT J.cust_id, R.report_period_name, SUM(CASE WHEN J.account_type = 'sav' THEN trans_amt ELSE 0.00 END) AS sav_trans_sav, SUM(CASE WHEN J.account_type = 'chk' THEN trans_amt ELSE 0 END) AS chk_trans_tot FROM Report_Periods AS R, Transaction_Journal AS J WHERE J.trans_date BETWEEN R.period_start_date AND R.period_end_date AND R.report_period_name IN ('2011-06-00', '2011-07-00', '2011-08-00') GROUP BY J.cust_id;--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-10 : 16:07:17
|
| Don't mind Joe. He's always like that. For some reason thinks that insulting people will make them more likely to learn.--Gail ShawSQL Server MVP |
|
|
|
fatin
Starting Member
2 Posts |
Posted - 2011-07-10 : 21:27:06
|
| hi Gail,thanks for the advise, I will try to look at it and do somthing.hi Joe, i know my data was not in a proper format, but like my subject is i`m new in SQL, i dont even know what u said about ISO & COBOL stuff. BTW, thanks for your reply as I may needed later when I have the opportunity to learn more about SQL. Thanks. :D |
|
|
|
|
|
|
|
|
New in SQL..need help