January 4, 2011 at 6:45 am
I have these tables
inv :invid(auot),invno(int),invdate(DateTime),invamt(currency),acctid(int)
pay:payid(auto,primary),paydate(dateTime),payamt(currency),acctid(int)
acct:acctid(auto,int,primary),acctname(varchar 30),opgbal(currency)
acctbal:acctid(int ref int),balance(currency)
1.I need to get all the records from both the table(inv and pay) which have a particular acctid and order the result by date(irrespective of invdate,paydate)
2.a stored proc/script that will on execution will update a table that has acctid
and balance(currency) as the fields.
balance will be calculated for all the acctid in acct table one by one using sum(invamt) from inv -sum(payamt) from pay +opgbal from acct.
January 4, 2011 at 6:51 am
Please post table definitions, sample data and expected results. See http://qa.sqlservercentral.com/articles/Best+Practices/61537/ for the best way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 4, 2011 at 7:32 am
It looks to me like you have what you need in order to write a query. What's your question?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 7:43 am
After posting i did tried and write the query as follows
SELECT c.invdate,a.acctname,COALESCE(c.invamt, 0) AS Debit,0 AS credit
FROM invoices c
INNER JOIN accounts a ON a.acctid = c.acctid
WHERE a.acctid = 3
UNION ALL
SELECT d.paydate,e.acctname,0 as debit,COALESCE(d.payamt,0) AS credit
FROM payments d
INNER JOIN accounts e ON e.acctid = d.acctid
WHERE e.acctid = 3
ORDER BY a.acctname,c.invdate
however i am still trying to find out how i could possibly get the total of debit and credit
January 4, 2011 at 7:45 am
Take a look at "SUM" in Books Online (or on MSDN). T-SQL can aggregate data with functions like Sum, Max, Min, Avg, and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2011 at 7:46 am
Sorry for the haste.
The above query is working for my purpose.
Next what i am trying to do is to get a list of all acctid , and their balances using the calculation i mentioned in the first post.
January 4, 2011 at 9:08 am
Couldn't test it out but maybe something along these lines?
SELECT d.acctid
, d.acctname
, d.Debit
, c.credit
, d.Debit - c.credit AS InvoiceBalance
FROM (
SELECT a.acctid, a.acctname, SUM(COALESCE(c.invamt, 0)) AS Debit
FROM invoices c
INNER JOIN accounts a ON a.acctid = c.acctid
WHERE a.acctid = 3
GROUP BY a.acctid, a.acctname
) d
LEFT OUTER JOIN (
SELECT e.acctid, SUM(COALESCE(d.payamt, 0)) AS credit
FROM payments d
INNER JOIN accounts e ON e.acctid = d.acctid
WHERE e.acctid = 3
GROUP BY e.acctid
) c
ON d.acctid = c.acctid
ORDER BY d.acctname
_____________________________________________________________________
- Nate
January 4, 2011 at 9:21 pm
superb
This is exactly what i wanted and it serves my purpose quite well.
Thanks RP_DBA.
I really hope you would help me out with this query.
Table trans has the following structure
transid auto not null
transdate datetime
transtype char 1
transamt currency
acctid int
what i am trying to do is get running balance for a particular id.
transtype = 0 will be deducted from transtype=1 to get running balance.
I have looked into some implemention of running balances but somehow i could not
do it properly.
the resultset will be ordered by transdate.
I need some hints on how to do it properly.
Thanks in advance.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply