Get data from 3 or more tables

Question

Get data from 3 or more tables

Viewing 4 posts - 16 through 18 (of 18 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

Hi

have been at work ...so sorry for delay

I have had to go back thro your various posts in an attempt to get all the details of your tables...the script below is what I think you have posted.

Please check this and PLEASE provide the expected results.....you have altered my original proposal to group by batchno and also introduced another column "VALUE"...so before going any further can we please agree that this script provides all the details neccessary and that based on this script you will provide expected results....thanks

by doing this....others may well feel able to easily produce your tables and provide other solutions...quickly and tried and tested.

regards

use [tempdb]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[voucherPRETSRET]') AND type in (N'U'))

DROP TABLE [voucherPRETSRET]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[voucher1]') AND type in (N'U'))

DROP TABLE [voucher1]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[voucher]') AND type in (N'U'))

DROP TABLE [voucher]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[items]') AND type in (N'U'))

DROP TABLE [items]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[item_batch_stock]') AND type in (N'U'))

DROP TABLE [item_batch_stock]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cashmemo]') AND type in (N'U'))

DROP TABLE [cashmemo]

GO

CREATE TABLE [dbo].[cashmemo](

[srno] [int] IDENTITY(1,1) NOT NULL,

[voucherprefix] [nvarchar](50) NULL,

[voucherno] [int] NULL,

[partyname] [nvarchar](50) NULL,

[itemname] [nvarchar](50) NULL,

[batch] [nvarchar](50) NULL,

[qty] [float] NULL,

[unit] [nvarchar](50) NULL,

[rate] [float] NULL,

[amt] [float] NULL,

[vatclass] [nvarchar](50) NULL,

[vat] [float] NULL,

[adtaxclass] [nvarchar](50) NULL,

[adtax] [float] NULL,

[total] [float] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[items](

[srno] [int] IDENTITY(1,1) NOT NULL,

[itemname] [nvarchar](255) NULL,

[unitsymbol] [nvarchar](50) NULL,

[itemtype] [nvarchar](50) NULL,

[purchaseledger] [int] NULL,

[salesledger] [int] NULL,

[pvatclass] [int] NULL,

[padtaxclass] [int] NULL,

[svatclass] [int] NULL,

[sadtaxclass] [int] NULL

)

CREATE TABLE [dbo].[voucher1](

[srno] [int] IDENTITY(1,1) NOT NULL,

[vouchertype] [nvarchar](50) NULL,

[voucherprefix] [nvarchar](50) NULL,

[voucherno] [int] NULL,

[item] [int] NULL,

[itemdesc] [nvarchar](50) NULL,

[batchno] [nvarchar](50) NULL,

[quantity] [float] NULL,

[unit] [nvarchar](50) NULL,

[rate] [float] NULL,

[itemamt] [float] NULL,

[disc] [float] NULL,

[finalamt] [float] NULL,

[vatclass] [nvarchar](50) NULL,

[vat] [float] NULL,

[adtaxclass] [nvarchar](50) NULL,

[adtax] [float] NULL,

[total] [float] NULL,

[invoicetype] [nvarchar](50) NULL

)

CREATE TABLE [dbo].[voucher](

[srno] [int] IDENTITY(1,1) NOT NULL,

[vouchertype] [nvarchar](50) NULL,

[voucherprefix] [nvarchar](50) NULL,

[voucherno] [int] NULL,

[voucherrefno] [nvarchar](50) NULL,

[dt] [date] NULL,

[details] [nvarchar](255) NULL,

[invoicetype] [nvarchar](50) NULL

)

CREATE TABLE [dbo].[voucherPRETSRET](

[srno] [int] IDENTITY(1,1) NOT NULL,

[vouchertype] [nvarchar](50) NULL,

[voucherprefix] [nvarchar](50) NULL,

[voucherno] [int] NULL,

[oppvouchertype] [nvarchar](50) NULL,

[oppvoucherprefix] [nvarchar](50) NULL,

[oppvoucherno] [int] NULL,

[oppinvtype] [nvarchar](50) NULL,

[selectedvouchersrno] [int] NULL,

[item] [nvarchar](50) NULL,

[batchno] [nvarchar](50) NULL,

[qty] [float] NULL,

[unit] [nvarchar](50) NULL,

[rate] [float] NULL,

[itemamt] [float] NULL,

[vatclass] [nvarchar](50) NULL,

[vat] [float] NULL,

[adtaxclass] [nvarchar](50) NULL,

[adtax] [float] NULL,

[total] [float] NULL

)

CREATE TABLE [item_batch_stock](

[srno] [int] IDENTITY(1,1) NOT NULL,

[item] [int] NULL,

[batch] [nvarchar](50) NULL,

[stock] [float] NULL,

[priceperunit] [float] NULL,

CONSTRAINT [PK_item_batch_stock] 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]

SET IDENTITY_INSERT [voucher1] ON

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, 1, N'', N'RNP1ltr10', 120, N'LTR ', 67.62, 8114.4, 0, 8114.4, N'Purchase @4', 324.58, N'Purchase adtax @1', 81.14, 8520.12, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 2, 2, N'', N'nn', 20, N'Daba ', 1095.24, 21904.8, 0, 21904.8, N'Purchase @4', 876.19, N'Purchase adtax @1', 219.05, 23000.04, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 2, 3, N'', N'nn', 5, N'Daba ', 1119.05, 5595.25, 0, 5595.25, N'Purchase @4', 223.81, N'Purchase adtax @1', 55.95, 5875.01, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 2, 4, N'', N'nn', 180, N'LTR ', 67.15, 12087, 0, 12087, N'Purchase @4', 483.48, N'Purchase adtax @1', 120.87, 12691.35, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 3, 5, N'', N'nn', 60, N'KGS ', 130, 7800, 0, 7800, N'Purchase @4', 312, N'Purchase adtax @1', 78, 8190, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (6, N'PURCHASE', N'P1213', 4, 6, N'', N'Chungibary PD806 700kg', 700, N'KGS ', 147, 102900, 0, 102900, N'Purchase @4', 4116, N'Purchase adtax @1', 1029, 108045, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (7, N'PURCHASE', N'P1213', 5, 2, N'', N'420', 10, N'Daba ', 1071.43, 10714.3, 0, 10714.3, N'Purchase @4', 428.57, N'Purchase adtax @1', 107.14, 11250.01, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (8, N'PURCHASE', N'P1213', 5, 2, N'', N'nnn', 10, N'Daba ', 1047.62, 10476.2, 0, 10476.2, N'Purchase @4', 419.05, N'Purchase adtax @1', 104.76, 11000.01, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (9, N'PURCHASE', N'P1213', 5, 4, N'', N'nnn', 12, N'LTR ', 117.34, 1408.08, 0, 1408.08, N'Purchase @4', 56.32, N'Purchase adtax @1', 14.08, 1478.48, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (10, N'PURCHASE', N'P1213', 5, 7, N'', N'nnn', 2, N'Daba ', 1857.15, 3714.3, 0, 3714.3, N'Purchase @4', 148.57, N'Purchase adtax @1', 37.14, 3900.01, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (11, N'PURCHASE', N'P1213', 5, 8, N'', N'nn', 5, N'Daba ', 1100, 5500, 0, 5500, N'Purchase @4', 220, N'Purchase adtax @1', 55, 5775, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (12, N'SALES', N'S1213', 1, 7, N'', N'nnn', 2, N'Daba ', 1857.15, 3714.3, 0, 3714.3, N'Sales @4', 148.57, N'Sales adtax @1', 37.14, 3900.01, N'TAX INVOICE')

INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (13, N'SALES', N'S1213', 2, 3, N'', N'nn', 5, N'Daba', 1125, 5625, 0, 5625, N'Sales @4', 225, N'Sales adtax @1', 56.25, 5906.25, N'TAX INVOICE')

SET IDENTITY_INSERT [voucher1] OFF

SET IDENTITY_INSERT [cashmemo] ON

INSERT [cashmemo] ([srno], [voucherprefix], [voucherno], [partyname], [itemname], [batch], [qty], [unit], [rate], [amt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (3, N'CM1213', 1, N'Mahesh', N'Rani Cotton Oil 1LTR pouch', N'RNP1ltr10', 1, N'LTR', 70, 70, N'Sales @4', 2.8, N'Sales adtax @1', 0.7, 73.5)

SET IDENTITY_INSERT [cashmemo] OFF

SET IDENTITY_INSERT [voucherPRETSRET] ON

INSERT [voucherPRETSRET] ([srno], [vouchertype], [voucherprefix], [voucherno], [oppvouchertype], [oppvoucherprefix], [oppvoucherno], [oppinvtype], [selectedvouchersrno], [item], [batchno], [qty], [unit], [rate], [itemamt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (8, N'PURCHASE RETURN', N'PR1213', 1, N'PURCHASE', N'P1213', 1, N'TAX INVOICE', 1, N'Rani Cotton Oil 1LTR pouch', N'RNP1ltr10', 60, N'LTR ', 67.62, 4057.2, N'Purchase @4', 162.29, N'Purchase adtax @1', 40.57, 4260.06)

INSERT [voucherPRETSRET] ([srno], [vouchertype], [voucherprefix], [voucherno], [oppvouchertype], [oppvoucherprefix], [oppvoucherno], [oppinvtype], [selectedvouchersrno], [item], [batchno], [qty], [unit], [rate], [itemamt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (9, N'SALES RETURN', N'SR1213', 1, N'SALES', N'S1213', 2, N'TAX INVOICE', 49, N'Laifol new tin(15kg)', N'nn', 5, N'Daba', 1125, 5625, N'Sales @4', 225, N'Sales adtax @1', 56.25, 5906.25)

SET IDENTITY_INSERT [voucherPRETSRET] OFF

SET IDENTITY_INSERT [voucher] ON

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, N'/1', CAST(0x81350B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 2, N'/2', CAST(0x81350B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 3, N'/3', CAST(0x85350B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 4, N'/4', CAST(0x36360B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 5, N'/5', CAST(0xBE350B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (6, N'JOURNAL', N'J1213', 1, NULL, CAST(0xBE350B00 AS Date), N'', NULL)

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (7, N'PAYMENT', N'PY1213', 1, NULL, CAST(0xBE350B00 AS Date), N'

', NULL)

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (8, N'SALES', N'S1213', 1, N'/1', CAST(0x1A360B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (10, N'RECEIPT', N'R1213', 1, NULL, CAST(0x1E360B00 AS Date), N'

', NULL)

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (19, N'SALES', N'S1213', 2, N'/2', CAST(0x37360B00 AS Date), N'

', N'TAX INVOICE')

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (21, N'CASH MEMO', N'CM1213', 1, NULL, CAST(0x37360B00 AS Date), N'

', NULL)

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (22, N'PURCHASE RETURN', N'PR1213', 1, N'', CAST(0x37360B00 AS Date), N'', NULL)

INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (23, N'SALES RETURN', N'SR1213', 1, N'', CAST(0x37360B00 AS Date), N'', NULL)

SET IDENTITY_INSERT [voucher] OFF

SET IDENTITY_INSERT [items] ON

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (1, N'Rani Cotton Oil 1LTR pouch', N'1', N'RAW', 5, 5, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (2, N'Laifol cotan tin (15kg)', N'3', N'RAW', 5, 5, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (3, N'Laifol new tin(15kg)', N'3', N'RAW', 5, 5, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (4, N'Gokul cotan oil pauch 1ltr', N'1', N'RAW', 5, 5, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (5, N'jiru', N'2', N'RAW', 5, 5, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (6, N'Tea loos', N'2', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (7, N'Gulab singtel (15 ltr tin)', N'3', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (8, N'Gulab cotan oil tin (15kg)', N'3', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (9, N'Emty jr tea 250 gram', N'4', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (10, N'sil', N'4', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (11, N'Stikar (Savaj ti)', N'4', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (12, N'Savaj tea primiam tea pouch (250gm)', N'2', N'RAW', 3, 3, 1, 2, 3, 4)

INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (13, N'Savaj Premium Blend Tea 250 GM', N'2', N'PRODUCTION', 3, 3, 1, 2, 3, 4)

SET IDENTITY_INSERT [items] OFF

SET IDENTITY_INSERT [item_batch_stock] ON

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (1, 1, N'RNP1ltr10', 119, 67.61999999999999)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (2, 2, N'nn', 20, 1095.24)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (3, 3, N'nn', 0, 1119.05)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (4, 4, N'nn', 180, 67.15)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (5, 5, N'nn', 60, 130)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (6, 6, N'Chungibary PD806 700kg', 700, 147)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (7, 2, N'420', 10, 1071.4299999999998)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (8, 2, N'nnn', 10, 1047.6200000000001)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (9, 4, N'nnn', 12, 117.33999999999999)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (10, 7, N'nnn', 0, 1857.15)

INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (11, 8, N'nn', 5, 1100)

SET IDENTITY_INSERT [item_batch_stock] OFF

/*the problem...... tried with following query but not getting CASHMEMO(OUT) qty in OUTGOING column*/

declare @dt1 datetime

declare @dt2 datetime

set @dt1 = '2012-09-01'

set @dt2 = '2012-10-01'

SELECT

V1.item,

V1.batchno,

--V1.voucherno AS VNO,

SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end) AS OPENING,

SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else null end)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else null end),0) AS INCOMING,

SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'SALES' then V1.quantity else null end)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'PURCHASE RETURN' then V1.quantity else null end),0)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else null end),0) AS OUTGOING,

SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end) AS CLOSING,

(SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end))*sum(IBS.priceperunit) AS VALUE

FROM voucher1 AS V1 INNER JOIN

voucher AS V ON V.vouchertype = V1.vouchertype

AND V.voucherprefix = V1.voucherprefix

AND V.voucherno = V1.voucherno

AND V.invoicetype = V1.invoicetype

left outer join voucherPRETSRET VPR

on V.voucherprefix=VPR.voucherprefix

and V.voucherno=VPR.voucherno

and V.vouchertype=VPR.vouchertype

left outer join cashmemo CM

on V.voucherprefix=CM.voucherprefix

and V.voucherno=CM.voucherno

inner join item_batch_stock IBS

on IBS.item=V1.item and IBS.batch=V1.batchno

GROUP BY V1.item,V1.batchno

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

J Livingston SQL (4/11/2013)
Hi
have been at work ...so sorry for delay
I have had to go back thro your various posts in an attempt to get all the details of your tables...the script below is what I think you have posted.
Please check this and PLEASE provide the expected results.....you have altered my original proposal to group by batchno and also introduced another column "VALUE"...so before going any further can we please agree that this script provides all the details neccessary and that based on this script you will provide expected results....thanks
by doing this....others may well feel able to easily produce your tables and provide other solutions...quickly and tried and tested.
regards
use [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[voucherPRETSRET]') AND type in (N'U'))
DROP TABLE [voucherPRETSRET]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[voucher1]') AND type in (N'U'))
DROP TABLE [voucher1]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[voucher]') AND type in (N'U'))
DROP TABLE [voucher]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[items]') AND type in (N'U'))
DROP TABLE [items]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[item_batch_stock]') AND type in (N'U'))
DROP TABLE [item_batch_stock]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cashmemo]') AND type in (N'U'))
DROP TABLE [cashmemo]
GO
CREATE TABLE [dbo].[cashmemo](
[srno] [int] IDENTITY(1,1) NOT NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[partyname] [nvarchar](50) NULL,
[itemname] [nvarchar](50) NULL,
[batch] [nvarchar](50) NULL,
[qty] [float] NULL,
[unit] [nvarchar](50) NULL,
[rate] [float] NULL,
[amt] [float] NULL,
[vatclass] [nvarchar](50) NULL,
[vat] [float] NULL,
[adtaxclass] [nvarchar](50) NULL,
[adtax] [float] NULL,
[total] [float] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[items](
[srno] [int] IDENTITY(1,1) NOT NULL,
[itemname] [nvarchar](255) NULL,
[unitsymbol] [nvarchar](50) NULL,
[itemtype] [nvarchar](50) NULL,
[purchaseledger] [int] NULL,
[salesledger] [int] NULL,
[pvatclass] [int] NULL,
[padtaxclass] [int] NULL,
[svatclass] [int] NULL,
[sadtaxclass] [int] NULL
)
CREATE TABLE [dbo].[voucher1](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[item] [int] NULL,
[itemdesc] [nvarchar](50) NULL,
[batchno] [nvarchar](50) NULL,
[quantity] [float] NULL,
[unit] [nvarchar](50) NULL,
[rate] [float] NULL,
[itemamt] [float] NULL,
[disc] [float] NULL,
[finalamt] [float] NULL,
[vatclass] [nvarchar](50) NULL,
[vat] [float] NULL,
[adtaxclass] [nvarchar](50) NULL,
[adtax] [float] NULL,
[total] [float] NULL,
[invoicetype] [nvarchar](50) NULL
)
CREATE TABLE [dbo].[voucher](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[voucherrefno] [nvarchar](50) NULL,
[dt] [date] NULL,
[details] [nvarchar](255) NULL,
[invoicetype] [nvarchar](50) NULL
)
CREATE TABLE [dbo].[voucherPRETSRET](
[srno] [int] IDENTITY(1,1) NOT NULL,
[vouchertype] [nvarchar](50) NULL,
[voucherprefix] [nvarchar](50) NULL,
[voucherno] [int] NULL,
[oppvouchertype] [nvarchar](50) NULL,
[oppvoucherprefix] [nvarchar](50) NULL,
[oppvoucherno] [int] NULL,
[oppinvtype] [nvarchar](50) NULL,
[selectedvouchersrno] [int] NULL,
[item] [nvarchar](50) NULL,
[batchno] [nvarchar](50) NULL,
[qty] [float] NULL,
[unit] [nvarchar](50) NULL,
[rate] [float] NULL,
[itemamt] [float] NULL,
[vatclass] [nvarchar](50) NULL,
[vat] [float] NULL,
[adtaxclass] [nvarchar](50) NULL,
[adtax] [float] NULL,
[total] [float] NULL
)
CREATE TABLE [item_batch_stock](
[srno] [int] IDENTITY(1,1) NOT NULL,
[item] [int] NULL,
[batch] [nvarchar](50) NULL,
[stock] [float] NULL,
[priceperunit] [float] NULL,
CONSTRAINT [PK_item_batch_stock] 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]
SET IDENTITY_INSERT [voucher1] ON
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, 1, N'', N'RNP1ltr10', 120, N'LTR ', 67.62, 8114.4, 0, 8114.4, N'Purchase @4', 324.58, N'Purchase adtax @1', 81.14, 8520.12, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 2, 2, N'', N'nn', 20, N'Daba ', 1095.24, 21904.8, 0, 21904.8, N'Purchase @4', 876.19, N'Purchase adtax @1', 219.05, 23000.04, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 2, 3, N'', N'nn', 5, N'Daba ', 1119.05, 5595.25, 0, 5595.25, N'Purchase @4', 223.81, N'Purchase adtax @1', 55.95, 5875.01, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 2, 4, N'', N'nn', 180, N'LTR ', 67.15, 12087, 0, 12087, N'Purchase @4', 483.48, N'Purchase adtax @1', 120.87, 12691.35, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 3, 5, N'', N'nn', 60, N'KGS ', 130, 7800, 0, 7800, N'Purchase @4', 312, N'Purchase adtax @1', 78, 8190, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (6, N'PURCHASE', N'P1213', 4, 6, N'', N'Chungibary PD806 700kg', 700, N'KGS ', 147, 102900, 0, 102900, N'Purchase @4', 4116, N'Purchase adtax @1', 1029, 108045, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (7, N'PURCHASE', N'P1213', 5, 2, N'', N'420', 10, N'Daba ', 1071.43, 10714.3, 0, 10714.3, N'Purchase @4', 428.57, N'Purchase adtax @1', 107.14, 11250.01, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (8, N'PURCHASE', N'P1213', 5, 2, N'', N'nnn', 10, N'Daba ', 1047.62, 10476.2, 0, 10476.2, N'Purchase @4', 419.05, N'Purchase adtax @1', 104.76, 11000.01, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (9, N'PURCHASE', N'P1213', 5, 4, N'', N'nnn', 12, N'LTR ', 117.34, 1408.08, 0, 1408.08, N'Purchase @4', 56.32, N'Purchase adtax @1', 14.08, 1478.48, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (10, N'PURCHASE', N'P1213', 5, 7, N'', N'nnn', 2, N'Daba ', 1857.15, 3714.3, 0, 3714.3, N'Purchase @4', 148.57, N'Purchase adtax @1', 37.14, 3900.01, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (11, N'PURCHASE', N'P1213', 5, 8, N'', N'nn', 5, N'Daba ', 1100, 5500, 0, 5500, N'Purchase @4', 220, N'Purchase adtax @1', 55, 5775, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (12, N'SALES', N'S1213', 1, 7, N'', N'nnn', 2, N'Daba ', 1857.15, 3714.3, 0, 3714.3, N'Sales @4', 148.57, N'Sales adtax @1', 37.14, 3900.01, N'TAX INVOICE')
INSERT [voucher1] ([srno], [vouchertype], [voucherprefix], [voucherno], [item], [itemdesc], [batchno], [quantity], [unit], [rate], [itemamt], [disc], [finalamt], [vatclass], [vat], [adtaxclass], [adtax], [total], [invoicetype]) VALUES (13, N'SALES', N'S1213', 2, 3, N'', N'nn', 5, N'Daba', 1125, 5625, 0, 5625, N'Sales @4', 225, N'Sales adtax @1', 56.25, 5906.25, N'TAX INVOICE')
SET IDENTITY_INSERT [voucher1] OFF
SET IDENTITY_INSERT [cashmemo] ON
INSERT [cashmemo] ([srno], [voucherprefix], [voucherno], [partyname], [itemname], [batch], [qty], [unit], [rate], [amt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (3, N'CM1213', 1, N'Mahesh', N'Rani Cotton Oil 1LTR pouch', N'RNP1ltr10', 1, N'LTR', 70, 70, N'Sales @4', 2.8, N'Sales adtax @1', 0.7, 73.5)
SET IDENTITY_INSERT [cashmemo] OFF
SET IDENTITY_INSERT [voucherPRETSRET] ON
INSERT [voucherPRETSRET] ([srno], [vouchertype], [voucherprefix], [voucherno], [oppvouchertype], [oppvoucherprefix], [oppvoucherno], [oppinvtype], [selectedvouchersrno], [item], [batchno], [qty], [unit], [rate], [itemamt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (8, N'PURCHASE RETURN', N'PR1213', 1, N'PURCHASE', N'P1213', 1, N'TAX INVOICE', 1, N'Rani Cotton Oil 1LTR pouch', N'RNP1ltr10', 60, N'LTR ', 67.62, 4057.2, N'Purchase @4', 162.29, N'Purchase adtax @1', 40.57, 4260.06)
INSERT [voucherPRETSRET] ([srno], [vouchertype], [voucherprefix], [voucherno], [oppvouchertype], [oppvoucherprefix], [oppvoucherno], [oppinvtype], [selectedvouchersrno], [item], [batchno], [qty], [unit], [rate], [itemamt], [vatclass], [vat], [adtaxclass], [adtax], [total]) VALUES (9, N'SALES RETURN', N'SR1213', 1, N'SALES', N'S1213', 2, N'TAX INVOICE', 49, N'Laifol new tin(15kg)', N'nn', 5, N'Daba', 1125, 5625, N'Sales @4', 225, N'Sales adtax @1', 56.25, 5906.25)
SET IDENTITY_INSERT [voucherPRETSRET] OFF
SET IDENTITY_INSERT [voucher] ON
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (1, N'PURCHASE', N'P1213', 1, N'/1', CAST(0x81350B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (2, N'PURCHASE', N'P1213', 2, N'/2', CAST(0x81350B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (3, N'PURCHASE', N'P1213', 3, N'/3', CAST(0x85350B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (4, N'PURCHASE', N'P1213', 4, N'/4', CAST(0x36360B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (5, N'PURCHASE', N'P1213', 5, N'/5', CAST(0xBE350B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (6, N'JOURNAL', N'J1213', 1, NULL, CAST(0xBE350B00 AS Date), N'', NULL)
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (7, N'PAYMENT', N'PY1213', 1, NULL, CAST(0xBE350B00 AS Date), N'
', NULL)
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (8, N'SALES', N'S1213', 1, N'/1', CAST(0x1A360B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (10, N'RECEIPT', N'R1213', 1, NULL, CAST(0x1E360B00 AS Date), N'
', NULL)
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (19, N'SALES', N'S1213', 2, N'/2', CAST(0x37360B00 AS Date), N'
', N'TAX INVOICE')
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (21, N'CASH MEMO', N'CM1213', 1, NULL, CAST(0x37360B00 AS Date), N'
', NULL)
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (22, N'PURCHASE RETURN', N'PR1213', 1, N'', CAST(0x37360B00 AS Date), N'', NULL)
INSERT [voucher] ([srno], [vouchertype], [voucherprefix], [voucherno], [voucherrefno], [dt], [details], [invoicetype]) VALUES (23, N'SALES RETURN', N'SR1213', 1, N'', CAST(0x37360B00 AS Date), N'', NULL)
SET IDENTITY_INSERT [voucher] OFF
SET IDENTITY_INSERT [items] ON
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (1, N'Rani Cotton Oil 1LTR pouch', N'1', N'RAW', 5, 5, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (2, N'Laifol cotan tin (15kg)', N'3', N'RAW', 5, 5, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (3, N'Laifol new tin(15kg)', N'3', N'RAW', 5, 5, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (4, N'Gokul cotan oil pauch 1ltr', N'1', N'RAW', 5, 5, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (5, N'jiru', N'2', N'RAW', 5, 5, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (6, N'Tea loos', N'2', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (7, N'Gulab singtel (15 ltr tin)', N'3', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (8, N'Gulab cotan oil tin (15kg)', N'3', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (9, N'Emty jr tea 250 gram', N'4', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (10, N'sil', N'4', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (11, N'Stikar (Savaj ti)', N'4', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (12, N'Savaj tea primiam tea pouch (250gm)', N'2', N'RAW', 3, 3, 1, 2, 3, 4)
INSERT [items] ([srno], [itemname], [unitsymbol], [itemtype], [purchaseledger], [salesledger], [pvatclass], [padtaxclass], [svatclass], [sadtaxclass]) VALUES (13, N'Savaj Premium Blend Tea 250 GM', N'2', N'PRODUCTION', 3, 3, 1, 2, 3, 4)
SET IDENTITY_INSERT [items] OFF
SET IDENTITY_INSERT [item_batch_stock] ON
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (1, 1, N'RNP1ltr10', 119, 67.61999999999999)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (2, 2, N'nn', 20, 1095.24)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (3, 3, N'nn', 0, 1119.05)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (4, 4, N'nn', 180, 67.15)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (5, 5, N'nn', 60, 130)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (6, 6, N'Chungibary PD806 700kg', 700, 147)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (7, 2, N'420', 10, 1071.4299999999998)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (8, 2, N'nnn', 10, 1047.6200000000001)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (9, 4, N'nnn', 12, 117.33999999999999)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (10, 7, N'nnn', 0, 1857.15)
INSERT [item_batch_stock] ([srno], [item], [batch], [stock], [priceperunit]) VALUES (11, 8, N'nn', 5, 1100)
SET IDENTITY_INSERT [item_batch_stock] OFF
/*the problem...... tried with following query but not getting CASHMEMO(OUT) qty in OUTGOING column*/
declare @dt1 datetime
declare @dt2 datetime
set @dt1 = '2012-09-01'
set @dt2 = '2012-10-01'
SELECT
V1.item,
V1.batchno,
--V1.voucherno AS VNO,
SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt1 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt1 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end) AS OPENING,
SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else null end)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else null end),0) AS INCOMING,
SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'SALES' then V1.quantity else null end)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'PURCHASE RETURN' then V1.quantity else null end),0)+isnull(SUM(case when v.dt >= @dt1 AND v.dt <= @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else null end),0) AS OUTGOING,
SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end) AS CLOSING,
(SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES RETURN' then VPR.qty else 0 end) - SUM(case when v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'PURCHASE RETURN' then VPR.qty else 0 end)+SUM(case when v.dt < @dt2 AND v.vouchertype = 'CASH MEMO' then CM.qty else 0 end))*sum(IBS.priceperunit) AS VALUE
FROM voucher1 AS V1 INNER JOIN
voucher AS V ON V.vouchertype = V1.vouchertype
AND V.voucherprefix = V1.voucherprefix
AND V.voucherno = V1.voucherno
AND V.invoicetype = V1.invoicetype
left outer join voucherPRETSRET VPR
on V.voucherprefix=VPR.voucherprefix
and V.voucherno=VPR.voucherno
and V.vouchertype=VPR.vouchertype
left outer join cashmemo CM
on V.voucherprefix=CM.voucherprefix
and V.voucherno=CM.voucherno
inner join item_batch_stock IBS
on IBS.item=V1.item and IBS.batch=V1.batchno
GROUP BY V1.item,V1.batchno

You are correct, this is all i need as you provided in the script.

Plus I am giving expected results as below

GODOWNITEM HSNOPENINGINCOMINGOUTGOINGCLOSINGVALUE

BhavnagarRani Cotton Oil 1LTR pouchNULL0 120 1 119 8046.78

BhavnagarLaifol cotan tin (15kg) NULL0 10 0 10 10714.3

BhavnagarLaifol cotan tin (15kg) NULL0 20 0 20 21904.8

BhavnagarLaifol cotan tin (15kg) NULL0 10 0 10 10476.2

BhavnagarLaifol new tin(15kg) NULL0 10 5 5 5595.25

BhavnagarGokul cotan oil pauch 1ltrNULL0 180 0 180 12087

BhavnagarGokul cotan oil pauch 1ltrNULL0 12 0 12 1408.08

Bhavnagarjiru NULL0 60 0 60 7800

BhavnagarTea loos NULL0 700 0 700 102900

BhavnagarGulab singtel (15 ltr tin) NULL0 2 2 0 0

BhavnagarGulab cotan oil tin (15kg)NULL0 5 0 5 5500

GODOWN and HSN column can be removed as it contains static data not from any table.

Also, this is expected format of results, value might change. but i need answer in this column format.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

there appears to be inconsistency in the way you store "item" across the various tables.

Not sure if this down to your real tables or just the way you have provided example data etc.

short of time at the moment...but one way to try and get everything together would be as follows...

select *

into #temp

from

(

SELECT V1.vouchertype, V1.voucherprefix, V1.voucherno, I.itemname, V1.batchno, V1.quantity

FROM voucher1 AS V1 INNER JOIN items AS I ON V1.item = I.srno

UNION ALL

SELECT vouchertype, voucherprefix, voucherno, item, batchno, qty

FROM voucherPRETSRET

UNION ALL

SELECT 'CASH MEMO' AS vouchertype, voucherprefix, voucherno, itemname, batch, qty

FROM cashmemo

) x

SELECT #temp.*, voucher.dt

FROM #temp LEFT OUTER JOIN voucher ON #temp.vouchertype = voucher.vouchertype

AND #temp.voucherprefix = voucher.voucherprefix

AND #temp.voucherno = voucher.voucherno

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

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

J Livingston SQL (4/12/2013)
there appears to be inconsistency in the way you store "item" across the various tables.
Not sure if this down to your real tables or just the way you have provided example data etc.
short of time at the moment...but one way to try and get everything together would be as follows...
select *
into #temp
from
(
SELECT V1.vouchertype, V1.voucherprefix, V1.voucherno, I.itemname, V1.batchno, V1.quantity
FROM voucher1 AS V1 INNER JOIN items AS I ON V1.item = I.srno
UNION ALL
SELECT vouchertype, voucherprefix, voucherno, item, batchno, qty
FROM voucherPRETSRET
UNION ALL
SELECT 'CASH MEMO' AS vouchertype, voucherprefix, voucherno, itemname, batch, qty
FROM cashmemo
) x
SELECT #temp.*, voucher.dt
FROM #temp LEFT OUTER JOIN voucher ON #temp.vouchertype = voucher.vouchertype
AND #temp.voucherprefix = voucher.voucherprefix
AND #temp.voucherno = voucher.voucherno

I havent tried this but thank you for your effort and reply

C U Soon and have a nice day