Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


repeatin value in my procedure


repeatin value in my procedure

Author
Message
raghuldrag
raghuldrag
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 473
Hi friends,
I m write procedure for the problem is


account trans date amount
==========================
E2674 Cr 02apr02 3000
R7890 Dr 06jun11 5000
E9700 Cr 7feb09 5000 ===> actual table
R4546 Dr 7aug09 6000
E4545 Cr 9Jan11 8888
R8679 Dr 10jul11 9999


MYQUERY:
=========
ALTER procedure TVRY_POSTINGS
(
@x datetime,
@y datetime
)

as

begin

select

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_no

group by
x.account
y.PR_MONS,
x.Debit,
y.Credit

end

OUTPUT:
=================


account PR_Mons credit debit closinbal
=======================================
E2674 apr 3000 00000 -3000
R7890 jun 0000 50000 5000
E9700 feb 5000 0000 5000
E2674 apr 3000 00000 -3000
R7890 jun 0000 50000 5000
E9700 feb 5000 0000 5000


the 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
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2594 Visits: 8437
raghuldrag (7/25/2012)
Hi friends,
I m write procedure for the problem is

<SNIP>


Hello 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,amount
INTO #yourTable
FROM (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!


Forever trying to learn

For 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 nolock


Craig Wilkinson - Software Engineer
LinkedIn
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


raghuldrag
raghuldrag
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 473
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 table
how to make sum the account......
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5007
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 table
how to make sum the account......


OMG!!! I can't believe thisPinch

The sample data you have provided is good but not error free
I 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 too
Nevertheless, 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 issue
If 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) amount
FROM @report
GROUP BY account, DATENAME(YEAR, trans_date), DATENAME(MONTH, trans_date)




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
raghuldrag
raghuldrag
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 473
account PR_Mons credit debit closinbal
=======================================
E2674 apr 3000 00000 -3000
R7890 jun 0000 50000 5000
E9700 feb 5000 0000 5000
E2674 apr 3000 00000 -3000 ======>expextingh output
R7890 jun 0000 50000 5000
E9700 feb 5000 0000 5000



condition:
**while account "E" means=cr-dr inclosing bal
as the same account "R" means=dr-cr inclosing bal


how to make query friend..... suppose I m giving from one month to another month it display the month in ouput of account......
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5007
raghuldrag (7/27/2012)

account PR_Mons credit debit closinbal
=======================================
E2674 apr 3000 00000 -3000
R7890 jun 0000 50000 5000
E9700 feb 5000 0000 5000
E2674 apr 3000 00000 -3000 ======>expextingh output
R7890 jun 0000 50000 5000
E9700 feb 5000 0000 5000



condition:
**while account "E" means=cr-dr inclosing bal
as the same account "R" means=dr-cr inclosing bal


how 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 match
You have given 5 rows in the sample data and are expecting 6 rows in the output
Even the amounts are not matching either
Can you explain the logic a bit more for your expected result?


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
raghuldrag
raghuldrag
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 473
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.
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5007
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 summarized
Provide 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 Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
raghuldrag
raghuldrag
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 473
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 -3000
E2674 apr 3900 00000 -3000
E2674 apr 4000 00000 -4000

E2674 apr 5000 00000 5000

E2674 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 month


is it possible???
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search