SQL Server 2005 Query View

Question

Post reply

SQL Server 2005 Query View

shiningworld_4u

SSC Enthusiast

Points: 119
More actions
June 17, 2010 at 1:36 am

#220193

HI!
am quite new in sql and realy dont have any advance expertize in sql. I am working on a leger report according to the accounts. The query have to show the debit and credit amount and balance by formula (creditamount-debitamount) and at the end of each row it have to show total balance till at that time.
following is the current query
select b.id ledgerid,b.vouchercode,a.accountid,a.description,
(select accountid from account where id = a.accountid)accountname,
a.date, isnull(a.creditamount,0)creditamount,isnull(a.debitamount,0)debitamount,
sum(isnull(a.creditamount,0)-isnull(a.debitamount,0))balance,
sum(isnull(a.creditamount,0)-isnull(a.debitamount,0)) + isnull(( select sum(isnull(creditamount,
0)-isnull(debitamount,0)) from ledgerdetails where date < DATEADD(dd, DATEDIFF
(dd,0, a.date), 0) and accountid = '40'),0)total
from ledgerdetails a inner join
ledger b on a.ledgerid = b.id Where convert(datetime,Convert(varchar,a.date,107))
between convert(datetime,Convert(varchar,Convert(DateTime,'4/1/2010 0:00:00'),107))
and convert(datetime,Convert(varchar,Convert(DateTime,'6/18/2010 0:00:00'),107)) and
a.accountid = '40' and b.status = 'Enable' group by a.date,a.creditamount,b.id,
a.debitamount,a.accountid,a.description,b.vouchercode
This query return the following results.
but last column should have to show the following results.
-3406210
-3409210
-3329504
-3229504
-3244504
-3259504
Need Suggestions from this kind of result.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Hello

There are at least three methods for deriving a running total:

1. Triangular join, as you are attempting here

2. Recursive CTE

3. Quirky update

Method 1 is not recommended as it performs poorly on all but the most modest data sets and hammers the server to the wall on larger data sets.

Method 2 performs rather better then method 1 and scales ok, and can be recommended depending upon requirements.

Method 3 is by far the fastest, updating a million rows of test data in a few seconds. An excellent article written by Jeff Moden covering Method 3 can be found here[/url].

A question for you - there's room for improvement in this query - lots of room. Are you interested in this, or simply solving the immediate problem of the running total?

Here's a reformatted version of your query which is a little easier on the eye:

SELECT

ledgerid = b.id,

b.vouchercode,

a.accountid,

a.description,

accountname = (SELECT accountid FROM account WHERE id = a.accountid),

a.date,

creditamount = isnull(a.creditamount,0),

debitamount = isnull(a.debitamount,0),

balance = sum(isnull(a.creditamount,0)-isnull(a.debitamount,0)),

total = sum(isnull(a.creditamount,0)-isnull(a.debitamount,0))

+ isnull((

SELECT sum(isnull(creditamount, 0)-isnull(debitamount,0))

FROM ledgerdetails

WHERE DATE < DATEADD(dd, DATEDIFF(dd,0, a.date), 0) AND accountid = '40'

),0)

FROM ledgerdetails a

INNER JOIN ledger b ON a.ledgerid = b.id

WHERE

convert(datetime,Convert(varchar,a.date,107))

between convert(datetime,Convert(varchar,Convert(DateTime,'4/1/2010 0:00:00'),107))

and convert(datetime,Convert(varchar,Convert(DateTime,'6/18/2010 0:00:00'),107))

AND a.accountid = '40'

AND b.status = 'Enable'

GROUP BY b.id, b.vouchercode, a.accountid, a.description, a.date, a.creditamount, a.debitamount

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

shiningworld_4u SSC Enthusiast Points: 119 More actions · Answer 2

A question for you - there's room for improvement in this query - lots of room. Are you interested in this, or simply solving the immediate problem of the running total?
Cheers
ChrisM

Am also interesting in improvement of this query. I am looking forward for your suggestions to improve this query this will definitely improve my query writing skills. and its all because i have not done a lot of practice to write sql.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

The date filter in the WHERE clause will work much more efficiently if the date can be compared directly to a lower and upper bound without any transformation. The expression convert(datetime,Convert(varchar,a.date,107)) prohibits the use of an index to find matching dates. Leave the time portion on the date, but change the operators:

DECLARE @Startdate DATETIME, @Enddate DATETIME

SET @Startdate = '4/1/2010 0:00:00'

SET @Enddate = '6/18/2010 0:00:00'

--SELECT @Startdate, @Enddate, DATEADD(dd, 1, @Enddate) -- testing