July 25, 2012 at 11:33 pm
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
July 26, 2012 at 1:06 am
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[/url] 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!
July 26, 2012 at 2:05 am
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.
July 26, 2012 at 11:05 pm
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......
July 27, 2012 at 12:08 am
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 this:pinch:
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)
SELECTaccount, 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)
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 27, 2012 at 12:27 am
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......
July 27, 2012 at 12:45 am
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?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 27, 2012 at 2:47 am
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.
July 27, 2012 at 3:03 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 27, 2012 at 3:24 am
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???
July 27, 2012 at 4:13 am
raghuldrag (7/27/2012)
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???
The output you have provided is changing with every post and still doesn't match with the sample data you provided
We want the expected output to be based on the sample data for better understanding of the issue
From whatever I have understood, all that I can suggest you is to have a look at "GROUP BY" Clause Books Online.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 30, 2012 at 1:46 am
for above that for E2674 account total value of credit, total value of debit for each month
July 31, 2012 at 8:54 am
Hello raghuldrag,
I think your first need is help analyzing what the problem is.
Your process proceed in three steps:
- Summarize data from @table into #temp1.
- Summarize data from @table into #tempr.
- Join #temp1 and #tempr giving the result.
You detected duplicate values for the E2674 account. So you should check the intermediate tables and answer these questions:
- There are duplicate rows in #temp1 for the E2674 account?
- There are duplicate rows in #tempr for the E2674 account?
- There are duplicate rows for the E2674 account only in the result?
The answer to these questions will hint you if the problem is in the first, the second or the third process. Please do it and tell us your results.
Francesc
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply