Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

repeatin value in my procedure Expand / Collapse
Author
Message
Posted Wednesday, July 25, 2012 11:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 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

Post #1335567
Posted Thursday, July 26, 2012 1:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 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!



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1335585
Posted Thursday, July 26, 2012 2:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

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.





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
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

Post #1335610
Posted Thursday, July 26, 2012 11:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 table
how to make sum the account......
Post #1336248
Posted Friday, July 27, 2012 12:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 table
how to make sum the account......


OMG!!! I can't believe this

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/
Post #1336266
Posted Friday, July 27, 2012 12:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 -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......
Post #1336274
Posted Friday, July 27, 2012 12:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 -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/
Post #1336279
Posted Friday, July 27, 2012 2:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

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 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/
Post #1336330
Posted Friday, July 27, 2012 3:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 -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???


Post #1336341
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse