July 20, 2012 at 10:15 am
July 20, 2012 at 10:33 am
Four separate threads for the same problem and none of them have an answer. Why? Because you won't provide the information requested in the format needed to help you.
Read this article and follow the instructions on what and how to post the information we need to help you:
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
July 23, 2012 at 5:29 am
finally got answer of dis question:
THE QUERY:
************
ALTER procedure TEST_POSTINGS
(
@x datetime,
@y datetime
)
as
begin
--set @x='01-apr-2011'
--set @y='30-apr-2011'
select
locn,
account_no,
Opening_bal=
sum(
case
when left(account_no,1)=('A') and transct='Cr' thenamount * -1
when left(account_no,1)=('A') and transct='Dr' thenamount
when left(account_no,1)=('L') and transct='Dr' thenamount * -1
when left(account_no,1)=('L') and transct='Cr' thenamount
end
)
into #temp
from
FMS..fs_postings (nolock)
where
fs_tran_date
and left(account_no,1) in ('A','L')
group by
locn,
account_no
select
locn,
account_no,
Debit=
sum(
case transct
when 'Dr' thenamount else 0 end),
Credit=
sum(
case transct
when 'Cr' thenamount else 0 end)
into #temp1
from
FMS..fs_postings (nolock)
where
fs_tran_date between @x and @y
--and left(account_no,1) in ('A','L')
group by
locn,
account_no
select
x.locn,
x.account_no,
y.Opening_bal,
x.Debit,
x.Credit,
Closing_Bal=
(
case
when left(x.account_no,1) in ('A','E') then y.Opening_bal+x.Debit-x.Credit
when left(x.account_no,1) in ('L','R') then y.Opening_bal-x.Debit+x.Credit
end
)
from
#temp1 x (nolock)
left outer join
#temp y (nolock)
on
x.locn=y.locn
and x.account_no=y.account_no
end
July 23, 2012 at 5:33 am
Why nolock? Do you know the consequences of using that hint?
Also if you need to use nolock make sure its WITH (NOLOCK) and not just (NOLOCK)
July 23, 2012 at 8:44 am
anthony.green (7/23/2012)
Why nolock? Do you know the consequences of using that hint?Also if you need to use nolock make sure its WITH (NOLOCK) and not just (NOLOCK)
Maybe they like duplicate and/or missing data in addition to potential entire database corruption.
As Anthony hinted, that hint is very dangerous when used improperly (and performance is NOT the proper use of that hint).
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2012 at 1:19 am
in that same procedure i want to the column of
prev_colsing_bal =(
case when left(y.fs_account_no,1) in ('E') then y.Debit-y.Credit
when left(y.fs_account_no,1) in ('R') then -y.Debit+y.Credit
end
)
pls give any idea.....
July 24, 2012 at 7:29 am
raghuldrag (7/24/2012)
in that same procedure i want to the column ofprev_colsing_bal =(
case when left(y.fs_account_no,1) in ('E') then y.Debit-y.Credit
when left(y.fs_account_no,1) in ('R') then -y.Debit+y.Credit
end
)
pls give any idea.....
Still nothing to work with for ddl and sample data. You seem determined to get an answer with providing as little information as possible.
What is wrong with the code you posted? It could be made a little cleaner but the syntax looks fine.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 24, 2012 at 7:36 am
I think you meant:
"Stll nutin 2 wk wit 4 ddl n smpl dta"
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy