Monthly balance from two tables
-
I have following information with two tables
CREATE TABLE [dbo].[voucherCr](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [nvarchar](50) NULL,
[crparty] [int] NULL,
[cramount] [float] NULL)
CREATE TABLE [dbo].[voucherDr](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [nvarchar](50) NULL,
[drparty] [int] NULL,
[dramount] [float] NULL)
CREATE TABLE [dbo].[voucher](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [nvarchar](50) NULL,
[dt] [date] NULL,
[details] [nvarchar](255) NULL,
[invoicetype] [nvarchar](50) NULL)
Here between all three tables, vouchertype,voucherprefix,voucherno columns are inter related.
Now I have following data for three tables
Voucher
srnovouchertypevoucherprefixvouchernodt detailsinvoicetype
1PURCHASE P 1 2012-12-25 RETAIL INVOICE
2PAYMENT R 1 2012-12-25
3PURCHASE P 2 2012-12-25 RETAIL INVOICE
4PURCHASE P 3 2012-12-25 RETAIL INVOICE
VoucherCr
srnovouchertypevoucherprefixvouchernocrpartycramount
1PURCHASE P 1 2 55000
2PAYMENT R 1 1 55000
3PURCHASE P 2 2 28000
4PURCHASE P 3 2 30550
VoucherDr
srnovouchertypevoucherprefixvouchernodrpartydramount
1PURCHASE P 1 4 55000
2PAYMENT R 1 2 55000
3PURCHASE P 2 4 28000
4PURCHASE P 3 4 29000
5PURCHASE P 3 97 1160
6PURCHASE P 3 98 290
7PURCHASE P 3 46 50
8PURCHASE P 3 66 50
Now I want results as follows
Month Credit Debit
December 168550 168550
-
As per the requirements, here is my query:
SELECT Month(v.dt) [Month],Sum(vc.cramount) Credit,
Sum(vd.dramount) Debit
FROM voucher v
INNER JOIN voucherCr vc
ON v.voucherno = vc.voucherno
AND v.voucherprefix = vc.voucherprefix
AND v.vouchertype = vc.vouchertype
INNER JOIN voucherDr vd
ON v.voucherno = vd.voucherno
AND v.voucherprefix = vd.voucherprefix
AND v.vouchertype = vd.vouchertype
GROUP BY Month(v.dt)
This will yield below result, which is different from yours. Please post the complete requirement..
Month Credit Debit----------- ---------------------- ----------------------
12 290750 168550
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
This would do what you are looking for:
Select p.Month, SUM(p.cramount) As Credit, SUM(q.dramount) As Debit From
(
Select b.voucherno, DATENAME(MM, a.dt) As Month, SUM(b.cramount) As cramount From voucher As a
JOIN voucherCr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(MM, a.dt)
) As p
JOIN
(
Select b.voucherno, DATENAME(MM, a.dt) As Month, SUM(b.dramount) As dramount From voucher As a
RIGHT JOIN voucherDr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(MM, a.dt)
) As q ON p.voucherno = q.voucherno
Group By p.Month
-
Thanks, this worked
-
vinu512 (12/26/2012)
This would do what you are looking for:Select p.Month, SUM(p.cramount) As Credit, SUM(q.dramount) As Debit From
(
Select b.voucherno, DATENAME(MM, a.dt) As Month, SUM(b.cramount) As cramount From voucher As a
JOIN voucherCr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(MM, a.dt)
) As p
JOIN
(
Select b.voucherno, DATENAME(MM, a.dt) As Month, SUM(b.dramount) As dramount From voucher As a
RIGHT JOIN voucherDr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(MM, a.dt)
) As q ON p.voucherno = q.voucherno
Group By p.Month
Thanks Vinu. I got my mistake:-)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
I'm sorry Hemal, there is a slight error in my query. If there is data for more than one year then the data would be grouped only according to month only and not by year. The following is the edited query:
Select p.Month, SUM(p.cramount) As Credit, SUM(q.dramount) As Debit From
(
Select b.voucherno, DATENAME(YY, a.dt) As Year, DATENAME(MM, a.dt) As Month, SUM(b.cramount) As cramount From voucher As a
JOIN voucherCr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(YY, a.dt), DATENAME(MM, a.dt)
) As p
JOIN
(
Select b.voucherno, DATENAME(YY, a.dt) As Year, DATENAME(MM, a.dt) As Month, SUM(b.dramount) As dramount From voucher As a
RIGHT JOIN voucherDr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(YY, a.dt), DATENAME(MM, a.dt)
) As q ON p.voucherno = q.voucherno
Group By p.Month
-
🙂
Thank Vinu Vijayan
-
I am facing problem with one situation
If voucherDr table contains data of two months, i.e. March,April
and voucherCr table contains data of one month, March only
Then I am not getting expected result
-
if on table has two months worth of data and the other has only one month what ARE you expected results? That you see only the complete information for one month? That you see full information for one month and partial information for the other?
Clarity please.
-
Erin Ramsay (3/8/2013)
if on table has two months worth of data and the other has only one month what ARE you expected results? That you see only the complete information for one month? That you see full information for one month and partial information for the other?Clarity please.
I have two tables with following data
Table1
Month dramount------ ---------
April 1709.75
March 5000
Table2
Month cramount------ ---------
March 6295
I want to join two tables and want results as following
Month dramount cramount------ --------- ----------
April 1709.75 NULL
March 5000 6295
How can i achieve this?
As per your query, I need data for all maximum no of months present in either of tables. If data for a month in one table is not present in another table, then it should display NULL, as i presented in my expected results. In my expected results, April month is having entry in table1 so only displaying dramount, while march is having entry for both dramount and cramount so displaying both. This should work for any no of months in any table.
-
I would be tempted to use UNION ALL for such case i.e. something like
Select p.Year, p.Month, SUM(p.cramount) As Credit, SUM(q.dramount) As Debit From(
Select b.voucherno, DATENAME(YY, a.dt) As Year, DATENAME(MM, a.dt) As Month, SUM(b.cramount) As cramount, 0 As dramount From voucher As a
JOIN voucherCr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(YY, a.dt), DATENAME(MM, a.dt)
UNION ALL
Select b.voucherno, DATENAME(YY, a.dt) As Year, DATENAME(MM, a.dt) As Month, As cramount,SUM(b.dramount) As dramount From voucher As a
RIGHT JOIN voucherDr As b ON a.voucherno = b.voucherno AND a.vouchertype = b.vouchertype
Group By b.voucherno, DATENAME(YY, a.dt), DATENAME(MM, a.dt)
) As p
Group By p.Year, p.Month
Also, you need to GROUP BY year as well.
Having said that, I still think there are more joins than I would like to have. So there must be a better way. Since I do not have sql server available, I do not want to post something wrong. But hopefully this UNION ALL trick could give you the core idea.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply