May 19, 2011 at 1:04 pm
prashantcomp90 (5/19/2011)
which base tables. do mean database tables. but from that how can i get this output. I use parameterized query for that but it wont work properly
You need to SELECT from the Source Table that has the data that you need to load into your destination Tble or Object. Forget the term Base Table, I did not mean to confuse you.
What are you trying to do in your package?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 1:12 pm
I want to filter the data which has same group name.and display them in report in two different columns and their sperate amounts.
May 19, 2011 at 1:23 pm
prashantcomp90 (5/9/2011)
Hello,I am working on sql2005 and I want to create view for one table.
Table details :
table name : groups
fields:
groupnm
debit
credit
and etc.
I want to create view with these three columns. I want to search on Assets and Liability. It should create six columns. three for assets,debit and credit and other three Liability,debit and credit.
I use the queary but it display only three columns and the data is combine I want to display it in seperate columns.
How can I perform this task.
prashantcomp90 (5/19/2011)
I want to filter the data which has same group name.and display them in report in two different columns and their sperate amounts.
I do not see an Assets or Liability Column. Are they in the same table? If not you need to perform a Join.
Sounds Like you want to Group By GroupNm. Why are you storing this information in one Table?
quote]prashantcomp90 (5/19/2011)
What column(s) do you to filter by and what two columns do you want to display. What aggregates do you need if any in Addition to the Group?
You should store the GroupID in this table and it does not sound like the Assets, Liabilities, Debit, Credit Column should be in the same table.
Are you in the process of creating this database or is it being used?
What schema and data do you currently have?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 1:35 pm
see forget about my last posts
I tell u from beginning.
I have one table name as ledger
in that column names are :
id,accnm, grpnm,supergrp,debitamt,creditamt.
now when i save data in ledger it save like this:
id accnm grpnm supergrp debitamt creditamt
1 abc cash Assets 10 10
2. pqr bank Assets 20 20
3. xyz ss Liability 30 30
4. qq loan Liability 40 40
assets and liability are not columns in the table the data save in the table.
May 19, 2011 at 1:49 pm
prashantcomp90 (5/19/2011)
see forget about my last postsI tell u from beginning.
I have one table name as ledger
in that column names are :
id,accnm, grpnm,supergrp,debitamt,creditamt.
now when i save data in ledger it save like this:
id accnm grpnm supergrp debitamt creditamt
1 abc cash Assets 10 10
2. pqr bank Assets 20 20
3. xyz ss Liability 30 30
4. qq loan Liability 40 40
assets and liability are not columns in the table the data save in the table.
Please read this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537
After you have provided the information request, then tell me wich colums you want displayed, Grouped By and how you want to filter the data so that I can better assist you.
If you do not know how to script out the tables, let me know.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 2:26 pm
Thanks for information.
table
create table group
(
id numeric(25),
accnm varchar(100),
grpnm varchar(100),
supergrp varchar(100),
debitamt numeric(20),
creditamt numeric(20)
)
insert into group(id,accnm,grpnm,supergrp,debitamt,creditamt)
select 1,'abc','cash','Assets',10,10
select 2,'pqr','bank','Assets',20,20
select 3,'xyz','ss','Liability',30,30
select 4,'qq','loan','Liability', 40,40
now, i want to group by supergrp column to sort or filter the data from his table.
I hope I posted correctly
May 19, 2011 at 2:35 pm
prashantcomp90 (5/19/2011)
Thanks for information.table
create table group
(
id numeric(25),
accnm varchar(100),
grpnm varchar(100),
supergrp varchar(100),
debitamt numeric(20),
creditamt numeric(20)
)
insert into group(id,accnm,grpnm,supergrp,debitamt,creditamt)
select 1,'abc','cash','Assets',10,10
select 2,'pqr','bank','Assets',20,20
select 3,'xyz','ss','Liability',30,30
select 4,'qq','loan','Liability', 40,40
now, i want to group by supergrp column to sort or filter the data from his table.
I hope I posted correctly
ok, we got the GROUP BY Column.
What Column do you want to ORDER BY (sort)
What column(s) do you want to include in the WHERE Clause (Filter)
What Columns and/or aggregates do you want to display in the Select Statement (Display).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 2:45 pm
select accnm,debitamt,creditamt from group where supergrp = 'Assets'
select accnm,debitamt,creditamt from group where supergrp='Liability'
now it should display seperate two outputs for assets and Liability.
May 19, 2011 at 2:53 pm
prashantcomp90 (5/19/2011)
select accnm,debitamt,creditamt from group where supergrp = 'Assets'select accnm,debitamt,creditamt from group where supergrp='Liability'
now it should display seperate two outputs for assets and Liability.
No I don't think that is what you want.
Did yo ever read the following article?
http://www.sqlservercentral.com/articles/Best+Practices/61537
Did you ever answer the other questions regarding SELECT, WHERE,ORDER BY, GROUP BY?
Welsh Corgi (5/19/2011)
prashantcomp90 (5/19/2011)
see forget about my last postsI tell u from beginning.
I have one table name as ledger
in that column names are :
id,accnm, grpnm,supergrp,debitamt,creditamt.
now when i save data in ledger it save like this:
id accnm grpnm supergrp debitamt creditamt
1 abc cash Assets 10 10
2. pqr bank Assets 20 20
3. xyz ss Liability 30 30
4. qq loan Liability 40 40
assets and liability are not columns in the table the data save in the table.
Please read this article:
http://www.sqlservercentral.com/articles/Best+Practices/61537
After you have provided the information request, then tell me witch columns you want displayed, Grouped By and how you want to filter the data so that I can better assist you.
If you do not know how to script out the tables, let me know.
Please provide the information in the format as request in the following linl:
http://www.sqlservercentral.com/articles/Best+Practices/61537
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 3:02 pm
This is what i want. the queary pass in given example like that i want how to pass gruupby.
how to make groupby to supergrp
May 19, 2011 at 3:16 pm
prashantcomp90 (5/19/2011)
This is what i want. the queary pass in given example like that i want how to pass gruupby.how to make groupby to supergrp
Yes but I need a little more information and we can go forward.
You have still not answered my questions and unfortunately I can't find my crystal ball.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 3:29 pm
SELECT accnm,debitamt,creditamt FROM group
GROUP BY supergrp
ORDER BY MAX(ID) DESC
WHERE supergrp = 'Assets')fs WHERE supergrp = 'Liability'
I think this is what you expect.
I think this is not correct but i try to do.
May 19, 2011 at 3:34 pm
prashantcomp90 (5/19/2011)
SELECT accnm,debitamt,creditamt FROM groupGROUP BY supergrp
ORDER BY MAX(ID) DESC
WHERE supergrp = 'Assets')fs WHERE supergrp = 'Liability'
How did you come up with that?
I guess you have it all figured out?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 3:50 pm
Welsh Corgi (5/19/2011)
prashantcomp90 (5/19/2011)
SELECT accnm,debitamt,creditamt FROM groupGROUP BY supergrp
ORDER BY MAX(ID) DESC
WHERE supergrp = 'Assets')fs WHERE supergrp = 'Liability'
How did you come up with that?
I guess you have it all figured out?
You have the GROUP BY and ORDER BY before the WHERE CLAUSE.
You cant use the STATEMENT WHERE supergrp = 'Assets')fs WHERE supergrp = 'Liability'
WHERE supergrp IN ('Assets'),'Liability')
What does fs mean "WHERE supergrp = 'Assets')fs"
You can't list the following columns in the SELECT Statement since you performed a GROUP BY on supergrp.
They must be aggregates. To get those values as non-aggregates you will need a subquery.
Perhaps I missed it but I did not see a script to load test data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 3:58 pm
prashantcomp90 (5/10/2011)
Then what changes I have to perform on table to get this output.
You tell me, you know your business.
prashantcomp90 (5/10/2011)
please give me solution for this problem. How I can solve this.
I'm not sure anyone can provide you with a solution to a problem when you have not defined what you want.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply