December 18, 2014 at 6:28 am
Dear SQL gurus
I am looking for SQL query which uses 2 tables CASH and BALANCE.
eg: Need Tablename, ColumsList and data in the results set.
eg: 10 rows shown below and ordered based on Acct_number
--------------------------------------
Row1,CASH,ACCT_NUMBER,AMOUNT,DEBIT_CREDIT_FLAG,ENTITY,BUSINESS_DATE,CURRENCY,REFERENCE,TRADE_TYPE,SUB ACC CODE
Row2,BALANCE,ACCT_NUMBER,OPENING_BALANCE,CLOSING_BALANCE,CLOSING_BAL_DEBIT_CREDIT_FLAG,BUSINESS_DATE,CURRENCY
Row3,CASH,10,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row4,CASH,10,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row5,CASH,10,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row6,BALANCE,10,500,750,DR,12/12/2014,USD
Row7,CASH,20,500,CR,ABC,12/12/2014,USD,INTL,,US05
Row8,CASH,20,1000,DR,DEF,12/12/2014,USD,DOM,,US07
Row9,CASH,20,75,DR,XYZ,12/12/2014,USD,DOM,,US05
Row10,BALANCE,20,500,750,DR,12/12/2014,USD
Your help on this will be most appreciated.
December 18, 2014 at 7:59 am
You want one resultset, but with different columns from different tables? Can't be done.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 18, 2014 at 8:10 pm
This looks quite a bit like a CSV export I recently did that was to be imported into AccPac.
Is what you want really a CSV file format?
That can be done. But I would need your two sample tables as DDL with consumable data to show you how.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 19, 2014 at 4:02 am
Hi
As you know, I am at client side and I am not allowed to share co info. Instead I can give you the output I need using EMP and DEPT tables
But I am expecting the output as below:
Only difference will be here Deptno is a numeric field. But my acc_number will be varchar with (char, numeric and some hypens in it)
-------
DEPT,DEPTNO,DNAME,LOC
EMP, DEPTNO,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM
DEPT,10,ACCOUNTING,NEW YORK
EMP,10,7782 ,CLARK ,MANAGER ,7839 ,1981-06-09 ,2450 ,
EMP,10,7839 ,KING ,PRESIDENT , ,1981-11-17 ,5000 ,
EMP,10,7934 ,MILLER ,CLERK ,7782 ,1982-01-23 ,1300 ,
DEPT,20,RESEARCH,DALLAS
EMP,20,7369 ,SMITH ,CLERK ,7902 ,1980-12-17 ,800 ,
EMP,20,7566 ,JONES ,MANAGER ,7839 ,1981-04-02 ,2975 ,
EMP,20,7788 ,SCOTT ,ANALYST ,7566 ,1982-12-09 ,3000 ,
EMP,20,7876 ,ADAMS ,CLERK ,7788 ,1983-01-12 ,1100 ,
EMP,20,7902 ,FORD ,ANALYST ,7566 ,1981-12-03 ,3000 ,
DEPT,30,SALES,CHICAGO
EMP,30,7499 ,ALLEN ,SALESMAN ,7698 ,1981-02-20 ,1600 ,300
EMP,30,7521 ,WARD ,SALESMAN ,7698 ,1981-02-22 ,1250 ,500
EMP,30,7654 ,MARTIN ,SALESMAN ,7698 ,1981-09-28 ,1250 ,1400
EMP,30,7698 ,BLAKE ,MANAGER ,7839 ,1981-05-01 ,2850 ,
EMP,30,7844 ,TURNER ,SALESMAN ,7698 ,1981-09-08 ,1500 ,
EMP,30,7900 ,JAMES ,CLERK ,7698 ,1981-12-03 ,950 ,
DEPT,40,OPERATIONS,BOSTON
Thanks
December 19, 2014 at 4:35 am
It would be helpful to know how the output is going to be used.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 19, 2014 at 4:42 am
The output is used as a report to check the cash amount from cash table
and opening balance, closing balance from balance table based on account number.
December 19, 2014 at 4:58 am
This script uses data available at hand to show the idea.
select res = case det.rn
when 0 then '[titles],'
+ t1.title_id +','
+ t1.title +','
+ cast(t1.[pubdate] as varchar(50))
else det.itm
end
from [titles] t1
cross apply (
select rn = 0, itm = null
union
select rn = 1
,itm = '[titleauthor],'
+ t1.title_id +','
+ cast([au_ord] as varchar(50))+','
+ cast([royaltyper] as varchar(50))
from [titleauthor] t2
where t2.title_id = t1.title_id ) det
order by t1.title_id, det.rn
Output
[titles],BU1032,The Busy Executive's Database Guide,Jun 12 1991 12:00AM
[titleauthor],BU1032,1,60
[titleauthor],BU1032,2,40
[titles],BU1111,Cooking with Computers: Surreptitious Balance Sheets,Jun 9 1991 12:00AM
[titleauthor],BU1111,1,60
[titleauthor],BU1111,2,40
[titles],BU2075,You Can Combat Computer Stress!,Jun 30 1991 12:00AM
[titleauthor],BU2075,1,100
[titles],BU7832,Straight Talk About Computers,Jun 22 1991 12:00AM
[titleauthor],BU7832,1,100
[titles],MC2222,Silicon Valley Gastronomic Treats,Jun 9 1991 12:00AM
[titleauthor],MC2222,1,100
[titles],MC3021,The Gourmet Microwave,Jun 18 1991 12:00AM
[titleauthor],MC3021,1,75
[titleauthor],MC3021,2,25
Adapt it for your tables.
December 19, 2014 at 6:57 am
Sure will try and confirm.
Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply