Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 repeatin value in my procedure Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 25, 2012 11:33 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, March 24, 2014 5:52 AM Points: 160, Visits: 399
 Hi friends, I m write procedure for the problem isaccount trans date amount==========================E2674 Cr 02apr02 3000R7890 Dr 06jun11 5000E9700 Cr 7feb09 5000 ===> actual tableR4546 Dr 7aug09 6000E4545 Cr 9Jan11 8888R8679 Dr 10jul11 9999MYQUERY:=========ALTER procedure TVRY_POSTINGS(@x datetime,@y datetime)asbeginselect account, DATENAME(mm,date) "PR_MONS",Credit= sum( casetrans when 'Cr' then fs_post_amt else 0 end)into #temp1 from @table (nolock)where date between @x and @y and left(account1) in ('E','R')group by account, date select account DATENAME(mm,date) "PR_MONS",Debit= sum( casetrans when 'Dr' then fs_post_amt else 0 end)into #tempr from @table (nolock)where date between @x and @y and left(account1) in ('E','R')group by account, date select x.account y.PR_MONS, x.Debit, y.Credit, Closing_Bal=sum ( case when left(x.account1) in ('E') then x.Debit-y.Credit when left(y.account1) in ('R') then -x.Debit+y.Credit end )from #tempr x(nolock) right outer join #temp1 y(nolock)on x.fs_account_no=y.fs_account_nogroup by x.account y.PR_MONS, x.Debit, y.Creditend OUTPUT:=================account PR_Mons credit debit closinbal=======================================E2674 apr 3000 00000 -3000R7890 jun 0000 50000 5000E9700 feb 5000 0000 5000E2674 apr 3000 00000 -3000R7890 jun 0000 50000 5000E9700 feb 5000 0000 5000the vale of account is repeating in my output..... how to make summarise the account....i wanna one account ve to display for one time for one month,some "account " appreared many times in actual table,that account also display for one time....help me
Post #1335567
 Posted Thursday, July 26, 2012 1:06 AM
 SSCrazy Group: General Forum Members Last Login: 2 days ago @ 7:06 AM Points: 2,404, Visits: 7,311
 raghuldrag (7/25/2012)Hi friends, I m write procedure for the problem isHello and welcome to SSC!I'd like to be able to help you, but it seems you've forgot to post readily consumable sample data and ddl scripts. Simply put, we haven't got enough information to help you. For example, in your query you refer to "fs_post_amt", which doesn't appear in your sample data at all.If you could read this article about the best way to post DDL and sample data then edit your post to include it, it would be extremely useful for the unpaid volunteers of this site.To get you started, here is a better way to show your sample data: -`SELECT account,trans,date,amountINTO #yourTableFROM (VALUES('E2674','Cr','02apr02',3000),('R7890','Dr','06jun11',5000),('E9700','Cr','7feb09',5000),('R4546','Dr','7aug09',6000),('E4545','Cr','9Jan11',8888),('R8679','Dr','10jul11',9999))a(account,trans,date,amount);`Anyone using SQL Server 2008 (this forum section) can execute that and have a table containing the sample data that you have shown. That way, they can provide you with tested, working solutions.If you don't have time to prepare this information, I'd advise you to look up aggregate functions and grouping Thanks! Not a DBA, just trying to learnFor better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockLinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1335585
 Posted Thursday, July 26, 2012 2:05 AM
 SSCertifiable Group: General Forum Members Last Login: Wednesday, August 28, 2013 2:37 AM Points: 5,075, Visits: 4,834
 Good luck getting sample data, DDL and expected results from this user.We have asked a number of times on different topics all related to the same problem the OP is having and we are still waiting.
Post #1335610
 Posted Thursday, July 26, 2012 11:05 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, March 24, 2014 5:52 AM Points: 160, Visits: 399
 create table report(account varchar(30),trans char(30),trans_date date,amount number(50))insert into report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)insert into report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)insert into report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000).......insert into report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)insert into report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)now i need the output of display for account number only one time permonth wheather its appears on many times of the actual tablehow to make sum the account......
Post #1336248
 Posted Friday, July 27, 2012 12:08 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 10, 2014 5:09 AM Points: 2,567, Visits: 4,654
 raghuldrag (7/26/2012)create table report(account varchar(30),trans char(30),trans_date date,amount number(50))insert into report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)insert into report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)insert into report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000).......insert into report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)insert into report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)now i need the output of display for account number only one time permonth wheather its appears on many times of the actual tablehow to make sum the account......OMG!!! I can't believe thisThe sample data you have provided is good but not error freeI got an error for the data type number( you have number in Oracle, not SQL Server )It would have been great if you had included the expected results tooNevertheless, its a good start and I am glad you finally understood what we want to help you I have given the solution as per my understanding of your issueIf the results are not what you desire, please post the desired results`declare @report table(account varchar(30),trans char(30),trans_date date,amount numeric(18,2))insert into @report(account,trans,trans_date,amount) values('E2674','Cr','02apr02',300)insert into @report(account,trans,trans_date,amount) values('R7890','Dr','06jun11',5000)insert into @report(account,trans,trans_date,amount) values('E9700','Cr','7feb09',5000)insert into @report(account,trans,trans_date,amount) values('R7890','Cr','06jun11',5900)insert into @report(account,trans,trans_date,amount) values('E9700','Dr','7feb09',8000)SELECT account, DATENAME(YEAR, trans_date) Year, DATENAME(MONTH, trans_date) Month, SUM(CASE WHEN trans = 'Cr' THEN amount ELSE amount * -1 END) amountFROM @reportGROUP BY account, DATENAME(YEAR, trans_date), DATENAME(MONTH, trans_date)` Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1336266
 Posted Friday, July 27, 2012 12:27 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, March 24, 2014 5:52 AM Points: 160, Visits: 399
 account PR_Mons credit debit closinbal=======================================E2674 apr 3000 00000 -3000R7890 jun 0000 50000 5000E9700 feb 5000 0000 5000E2674 apr 3000 00000 -3000 ======>expextingh outputR7890 jun 0000 50000 5000E9700 feb 5000 0000 5000condition:**while account "E" means=cr-dr inclosing balas the same account "R" means=dr-cr inclosing balhow to make query friend..... suppose I m giving from one month to another month it display the month in ouput of account......
Post #1336274
 Posted Friday, July 27, 2012 12:45 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 10, 2014 5:09 AM Points: 2,567, Visits: 4,654
 raghuldrag (7/27/2012)account PR_Mons credit debit closinbal=======================================E2674 apr 3000 00000 -3000R7890 jun 0000 50000 5000E9700 feb 5000 0000 5000E2674 apr 3000 00000 -3000 ======>expextingh outputR7890 jun 0000 50000 5000E9700 feb 5000 0000 5000condition:**while account "E" means=cr-dr inclosing balas the same account "R" means=dr-cr inclosing balhow to make query friend..... suppose I m giving from one month to another month it display the month in ouput of account......The sample data you provided and the expected output don't matchYou have given 5 rows in the sample data and are expecting 6 rows in the outputEven the amounts are not matching eitherCan you explain the logic a bit more for your expected result? Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1336279
 Posted Friday, July 27, 2012 2:47 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, March 24, 2014 5:52 AM Points: 160, Visits: 399
 hey friend my actual table ve lakhs of data per month,after making the query,I m expexting ve to display account only one time per month and its has been summarize value of that month.
Post #1336322
 Posted Friday, July 27, 2012 3:03 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, April 10, 2014 5:09 AM Points: 2,567, Visits: 4,654
 raghuldrag (7/27/2012)hey friend my actual table ve lakhs of data per month,after making the query,I m expexting ve to display account only one time per month and its has been summarize value of that month.The expected result that you have provided has complete duplicate rows which makes me feel that it is not summarizedProvide us the expected output based on the sample data that you have provided so that we correlate both of them and provide you tested solution Kingston DhasianHow to post data/code on a forum to get the best help - Jeff Modenhttp://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1336330
 Posted Friday, July 27, 2012 3:24 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, March 24, 2014 5:52 AM Points: 160, Visits: 399
 Hi, Above the my procedure query giving output,i m splitting the amount column has credit and debit,closing_bal column is sum of account is E=>debit-credit, account is R=>-debit+credit, OUTPUT:=================account PR_Mons credit debit closinbal=======================================E2674 apr 3000 00000 -3000E2674 apr 3900 00000 -3000E2674 apr 4000 00000 -4000E2674 apr 5000 00000 5000E2674 apr 0000 3900 3900 E2674 apr 6300 00000 6300 if account "E" appears on april month no of times means,ve to display the single time summarise value of one monthis it possible???
Post #1336341

 Permissions