Combine data from 2 tables and insert in another table

Question

Post reply

Combine data from 2 tables and insert in another table

hemal_301080

Say Hey Kid

Points: 677
More actions
April 14, 2013 at 8:52 am

#292327

I have following 3 table definition with data
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [voucherDr](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[drparty] [int] NULL,
[dramount] [float] NULL,
[invoicetype] [nvarchar](50) NULL,
CONSTRAINT [PK_voucherDr] PRIMARY KEY CLUSTERED
(
[srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [voucherDr] ON
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, 5, 8114.4, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 1, 3, 324.58, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 1, 4, 81.14, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 2, 5, 21904.8, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 2, 3, 876.19, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (6, N'PURCHASE', N'P1213', 2, 4, 219.05, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (7, N'PURCHASE', N'P1213', 2, 5, 5595.25, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (8, N'PURCHASE', N'P1213', 2, 3, 223.81, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (9, N'PURCHASE', N'P1213', 2, 4, 55.95, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (10, N'PURCHASE', N'P1213', 2, 5, 12087, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (11, N'PURCHASE', N'P1213', 2, 3, 483.48, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (12, N'PURCHASE', N'P1213', 2, 4, 120.87, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (13, N'PURCHASE', N'P1213', 3, 5, 7800, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (14, N'PURCHASE', N'P1213', 3, 3, 312, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (15, N'PURCHASE', N'P1213', 3, 4, 78, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (16, N'PURCHASE', N'P1213', 4, 5, 102900, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (17, N'PURCHASE', N'P1213', 4, 3, 4116, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (18, N'PURCHASE', N'P1213', 4, 4, 1029, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (19, N'PURCHASE', N'P1213', 5, 5, 10714.3, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (20, N'PURCHASE', N'P1213', 5, 3, 428.57, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (21, N'PURCHASE', N'P1213', 5, 4, 107.14, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (22, N'PURCHASE', N'P1213', 5, 5, 10476.2, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (23, N'PURCHASE', N'P1213', 5, 3, 419.05, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (24, N'PURCHASE', N'P1213', 5, 4, 104.76, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (25, N'PURCHASE', N'P1213', 5, 5, 1408.08, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (26, N'PURCHASE', N'P1213', 5, 3, 56.32, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (27, N'PURCHASE', N'P1213', 5, 4, 14.08, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (28, N'PURCHASE', N'P1213', 5, 5, 3714.3, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (29, N'PURCHASE', N'P1213', 5, 3, 148.57, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (30, N'PURCHASE', N'P1213', 5, 4, 37.14, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (31, N'PURCHASE', N'P1213', 5, 5, 5500, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (32, N'PURCHASE', N'P1213', 5, 3, 220, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (33, N'PURCHASE', N'P1213', 5, 4, 55, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (34, N'JOURNAL', N'J1213', 1, 1, 10000, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (35, N'PAYMENT', N'PY1213', 1, 2, 8520.12, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (36, N'SALES', N'S1213', 1, 25, 3900.01, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (39, N'RECEIPT', N'R1213', 1, 1, 3900, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (40, N'RECEIPT', N'R1213', 1, 9, 0.01, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (49, N'SALES', N'S1213', 2, 25, 5906.25, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (62, N'SALES RETURN', N'SR1213', 1, 31, 5625, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (63, N'SALES RETURN', N'SR1213', 1, 6, 225, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (64, N'SALES RETURN', N'SR1213', 1, 7, 56.25, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (65, N'CASH MEMO', N'CM1213', 1, 1, 71, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (66, N'SALES', N'S1213', 3, 25, 15750, N'TAX INVOICE')
SET IDENTITY_INSERT [voucherDr] OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [voucherCr](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[crparty] [int] NULL,
[cramount] [float] NULL,
[invoicetype] [nvarchar](50) NULL,
CONSTRAINT [PK_voucherCr] PRIMARY KEY CLUSTERED
(
[srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [voucherCr] ON
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, 2, 8520.12, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 2, 11, 41566.4, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 3, 12, 8190, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 4, 13, 108045, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 5, 14, 33403.51, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (6, N'JOURNAL', N'J1213', 1, 26, 10000, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (7, N'PAYMENT', N'PY1213', 1, 1, 8520.12, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (8, N'SALES', N'S1213', 1, 8, 3714.3, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (9, N'SALES', N'S1213', 1, 6, 148.57, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (10, N'SALES', N'S1213', 1, 7, 37.14, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (12, N'RECEIPT', N'R1213', 1, 25, 3900.01, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (36, N'SALES', N'S1213', 2, 8, 5625, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (37, N'SALES', N'S1213', 2, 6, 225, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (38, N'SALES', N'S1213', 2, 7, 56.25, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (51, N'SALES RETURN', N'SR1213', 1, 25, 5906.25, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (52, N'CASH MEMO', N'CM1213', 1, 8, 67.62, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (53, N'CASH MEMO', N'CM1213', 1, 6, 2.7, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (54, N'CASH MEMO', N'CM1213', 1, 7, 0.68, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (55, N'SALES', N'S1213', 3, 8, 15000, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (56, N'SALES', N'S1213', 3, 6, 600, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (57, N'SALES', N'S1213', 3, 7, 150, N'TAX INVOICE')
SET IDENTITY_INSERT [voucherCr] OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ledgerBalance](
[srno] [int] IDENTITY(1,1) NOT NULL,
[party] [int] NULL,
[openingbalance] [float] NULL,
[closingbalance] [float] NULL,
CONSTRAINT [PK_ledgerBalance] PRIMARY KEY CLUSTERED
(
[srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I have data about party no being credited and debited in two respective tables voucherCr,voucherDr
I want to combine these two tables, party wise. and insert that data in ledgerBalance tables in party and closingBalance column.
So
combined data example will be
Partyno Amount(Debit-Credit)
1 40000
11 3500
2 3450
and so on. the amount data is just for example.
party no in both tables can be joined and then get data of dramount and cramount and then subtract it, and then store that two column data in ledgerbalance(party,closingbalance). Openingbalance column will be blank
from voucherCr, crparty and cramount column is important
voucherDr, drparty and dramount column needs to be considered

Viewing 6 posts - 1 through 5 (of 5 total)

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

hemal_301080 Say Hey Kid Points: 677 More actions · Answer 1

Each unique party no from both the tables must be in the result.

For example

VoucherCr

PartyNo Cramount

1 10000

5 20000

7 50000

VoucherDr

partyNo dramount

2 4500

3 4000

1 50000

5 10000

Result should be

partyNo Amount(dramount-cramount)

1 40000

2 4500

3 4000

5 -10000

7 -50000

This needs to be inserted in ledgerbalance(party,closingbalance)

Pankaj067 Old Hand Points: 313 More actions · Answer 2

Hello,

It can be done using

INSERT INTO select from yourtable

I have created the T-sql query for you, please have a look

[font="Times New Roman"]

INSERT INTO dbo.ledgerBalance (party,closingbalance)

select

distinct

temp.ID,

(case when temp2.DrAmount IS NULL then 0 else temp2.DrAmount end)-

(case when temp1.CrAmount IS NULL then 0 else temp1.CrAmount end) as Amount

from

(

select distinct crparty as ID from dbo.voucherCr

union

select distinct drparty as ID from dbo.voucherDr

)temp

LEFT JOIN

(select crparty,sum(cramount) as CrAmount from dbo.voucherCr vc

group by crparty

) temp1

ON temp.ID = temp1.crparty

LEFT JOIN

(select drparty,SUM(dramount) as DrAmount from dbo.voucherDr

group by drparty

)temp2

ON temp.ID = temp2.drparty[/font]

Using this you can get the desired output as you described in the problem statement

Thanks:-)

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

hemal_301080 (4/14/2013)
I have following 3 table definition with data
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [voucherDr](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[drparty] [int] NULL,
[dramount] [float] NULL,
[invoicetype] [nvarchar](50) NULL,
CONSTRAINT [PK_voucherDr] PRIMARY KEY CLUSTERED
(
[srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [voucherDr] ON
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, 5, 8114.4, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 1, 3, 324.58, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 1, 4, 81.14, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 2, 5, 21904.8, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 2, 3, 876.19, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (6, N'PURCHASE', N'P1213', 2, 4, 219.05, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (7, N'PURCHASE', N'P1213', 2, 5, 5595.25, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (8, N'PURCHASE', N'P1213', 2, 3, 223.81, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (9, N'PURCHASE', N'P1213', 2, 4, 55.95, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (10, N'PURCHASE', N'P1213', 2, 5, 12087, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (11, N'PURCHASE', N'P1213', 2, 3, 483.48, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (12, N'PURCHASE', N'P1213', 2, 4, 120.87, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (13, N'PURCHASE', N'P1213', 3, 5, 7800, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (14, N'PURCHASE', N'P1213', 3, 3, 312, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (15, N'PURCHASE', N'P1213', 3, 4, 78, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (16, N'PURCHASE', N'P1213', 4, 5, 102900, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (17, N'PURCHASE', N'P1213', 4, 3, 4116, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (18, N'PURCHASE', N'P1213', 4, 4, 1029, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (19, N'PURCHASE', N'P1213', 5, 5, 10714.3, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (20, N'PURCHASE', N'P1213', 5, 3, 428.57, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (21, N'PURCHASE', N'P1213', 5, 4, 107.14, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (22, N'PURCHASE', N'P1213', 5, 5, 10476.2, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (23, N'PURCHASE', N'P1213', 5, 3, 419.05, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (24, N'PURCHASE', N'P1213', 5, 4, 104.76, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (25, N'PURCHASE', N'P1213', 5, 5, 1408.08, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (26, N'PURCHASE', N'P1213', 5, 3, 56.32, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (27, N'PURCHASE', N'P1213', 5, 4, 14.08, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (28, N'PURCHASE', N'P1213', 5, 5, 3714.3, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (29, N'PURCHASE', N'P1213', 5, 3, 148.57, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (30, N'PURCHASE', N'P1213', 5, 4, 37.14, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (31, N'PURCHASE', N'P1213', 5, 5, 5500, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (32, N'PURCHASE', N'P1213', 5, 3, 220, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (33, N'PURCHASE', N'P1213', 5, 4, 55, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (34, N'JOURNAL', N'J1213', 1, 1, 10000, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (35, N'PAYMENT', N'PY1213', 1, 2, 8520.12, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (36, N'SALES', N'S1213', 1, 25, 3900.01, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (39, N'RECEIPT', N'R1213', 1, 1, 3900, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (40, N'RECEIPT', N'R1213', 1, 9, 0.01, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (49, N'SALES', N'S1213', 2, 25, 5906.25, N'TAX INVOICE')
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (62, N'SALES RETURN', N'SR1213', 1, 31, 5625, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (63, N'SALES RETURN', N'SR1213', 1, 6, 225, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (64, N'SALES RETURN', N'SR1213', 1, 7, 56.25, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (65, N'CASH MEMO', N'CM1213', 1, 1, 71, NULL)
INSERT [voucherDr] ([srno], [vouchertype], [voucherprefix], [voucherno], [drparty], [dramount], [invoicetype]) VALUES (66, N'SALES', N'S1213', 3, 25, 15750, N'TAX INVOICE')
SET IDENTITY_INSERT [voucherDr] OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [voucherCr](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[crparty] [int] NULL,
[cramount] [float] NULL,
[invoicetype] [nvarchar](50) NULL,
CONSTRAINT [PK_voucherCr] PRIMARY KEY CLUSTERED
(
[srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [voucherCr] ON
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, 2, 8520.12, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 2, 11, 41566.4, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 3, 12, 8190, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 4, 13, 108045, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 5, 14, 33403.51, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (6, N'JOURNAL', N'J1213', 1, 26, 10000, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (7, N'PAYMENT', N'PY1213', 1, 1, 8520.12, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (8, N'SALES', N'S1213', 1, 8, 3714.3, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (9, N'SALES', N'S1213', 1, 6, 148.57, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (10, N'SALES', N'S1213', 1, 7, 37.14, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (12, N'RECEIPT', N'R1213', 1, 25, 3900.01, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (36, N'SALES', N'S1213', 2, 8, 5625, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (37, N'SALES', N'S1213', 2, 6, 225, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (38, N'SALES', N'S1213', 2, 7, 56.25, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (51, N'SALES RETURN', N'SR1213', 1, 25, 5906.25, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (52, N'CASH MEMO', N'CM1213', 1, 8, 67.62, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (53, N'CASH MEMO', N'CM1213', 1, 6, 2.7, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (54, N'CASH MEMO', N'CM1213', 1, 7, 0.68, NULL)
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (55, N'SALES', N'S1213', 3, 8, 15000, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (56, N'SALES', N'S1213', 3, 6, 600, N'TAX INVOICE')
INSERT [voucherCr] ([srno], [vouchertype], [voucherprefix], [voucherno], [crparty], [cramount], [invoicetype]) VALUES (57, N'SALES', N'S1213', 3, 7, 150, N'TAX INVOICE')
SET IDENTITY_INSERT [voucherCr] OFF
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ledgerBalance](
[srno] [int] IDENTITY(1,1) NOT NULL,
[party] [int] NULL,
[openingbalance] [float] NULL,
[closingbalance] [float] NULL,
CONSTRAINT [PK_ledgerBalance] PRIMARY KEY CLUSTERED
(
[srno] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I have data about party no being credited and debited in two respective tables voucherCr,voucherDr
I want to combine these two tables, party wise. and insert that data in ledgerBalance tables in party and closingBalance column.
So
combined data example will be
Partyno Amount(Debit-Credit)
1 40000
11 3500
2 3450
and so on. the amount data is just for example.
party no in both tables can be joined and then get data of dramount and cramount and then subtract it, and then store that two column data in ledgerbalance(party,closingbalance). Openingbalance column will be blank
from voucherCr, crparty and cramount column is important
voucherDr, drparty and dramount column needs to be considered

You can accomplish this task using MERGE

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

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

;WITH Credits AS (

SELECT crparty, cramount = SUM(crAmount)

FROM voucherCr

GROUP BY crparty

), Debits AS (

SELECT drparty, drAmount = SUM(drAmount)

FROM voucherDr

GROUP BY drparty

)

INSERT INTO ledgerBalance (party, closingBalance)

SELECT

Party = COALESCE(c.crparty, d.drparty),

Amount = ISNULL(drAmount,0) - ISNULL(cramount,0)

FROM Credits c

FULL OUTER JOIN Debits d ON d.drparty = c.crparty

^{“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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

kapil_kk (4/15/2013)
You can accomplish this task using MERGE

Let me ask you a thoughtful question... do you know how to get to the Moon? It's "easy". "You can accomplish this task using" a rocket ship. 😉

The Op busted a hump to provide a viable test harness. Consider creating some working code to demonstrate how to use MERGE for this problem. ~~I'd give it a shot but I'm stuck on a 2K5 box for another couple of hours.~~

Actually, looking back at the original problem, I believe I'd have come up with an answer similar to what Chris came up with instead of using MERGE. Pre-aggregation, like Chris did, is a powerful and highly performant tool.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)