Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get data from 3 or more tables


Get data from 3 or more tables

Author
Message
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33029
just to clarify....you say "voucherPRETSRET doesnt contain any data but needs to be checked"...but there is a PURCHASE RETURN in the sample data for voucher table.....???

maybe sensible to test with a SALES RETURN at this stage as well......

also I asked that you please provide expected results for the sample data........

regards

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

hemal_301080
hemal_301080
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 109
J Livingston SQL (4/10/2013)
just to clarify....you say "voucherPRETSRET doesnt contain any data but needs to be checked"...but there is a PURCHASE RETURN in the sample data for voucher table.....???

maybe sensible to test with a SALES RETURN at this stage as well......

also I asked that you please provide expected results for the sample data........

regards


The expected result is same as you gave in your previous solution.
We just need to connect these 2 tables more.

SO incoming will be PURCAHSE+SALES RETURN
Outgoing will be PURCHASE RETURN+CASH MEMO + SALES

Let me give you data with purchase return and sales return entry in relative tables.
I am giving you details about voucherPRETSRET,voucher tables as we have data about other tables.
hemal_301080
hemal_301080
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 109
hemal_301080 (4/10/2013)
J Livingston SQL (4/10/2013)
just to clarify....you say "voucherPRETSRET doesnt contain any data but needs to be checked"...but there is a PURCHASE RETURN in the sample data for voucher table.....???

maybe sensible to test with a SALES RETURN at this stage as well......

also I asked that you please provide expected results for the sample data........

regards


The expected result is same as you gave in your previous solution.
We just need to connect these 2 tables more.

SO incoming will be PURCAHSE+SALES RETURN
Outgoing will be PURCHASE RETURN+CASH MEMO + SALES

Let me give you data with purchase return and sales return entry in relative tables.
I am giving you details about voucherPRETSRET,voucher tables as we have data about other tables.





This is sample data for voucher and voucherPRETSRET tables.


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



hemal_301080
hemal_301080
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 109
J Livingston SQL (4/10/2013)
just to clarify....you say "voucherPRETSRET doesnt contain any data but needs to be checked"...but there is a PURCHASE RETURN in the sample data for voucher table.....???

maybe sensible to test with a SALES RETURN at this stage as well......

also I asked that you please provide expected results for the sample data........

regards


this is table definition for voucherPRETSRET


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [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,
CONSTRAINT [PK_voucherPRETSRET] 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



hemal_301080
hemal_301080
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 109
J Livingston SQL (4/10/2013)
just to clarify....you say "voucherPRETSRET doesnt contain any data but needs to be checked"...but there is a PURCHASE RETURN in the sample data for voucher table.....???

maybe sensible to test with a SALES RETURN at this stage as well......

also I asked that you please provide expected results for the sample data........

regards


As you asked about sample output, If needed.

ITEMNAME HSN OPENING INCOMING OUTGOING CLOSING VALUE


HSN will be null
VALUE will be calculated as I showed it in my tested query.
Rest is all same as you gave me in your previous solution
GODOWN can be ommitted at this stage
J Livingston SQL
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33029
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
hemal_301080
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 109
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


GODOWN ITEM HSN OPENING INCOMING OUTGOING CLOSING VALUE
Bhavnagar Rani Cotton Oil 1LTR pouch NULL 0 120 1 119 8046.78
Bhavnagar Laifol cotan tin (15kg) NULL 0 10 0 10 10714.3
Bhavnagar Laifol cotan tin (15kg) NULL 0 20 0 20 21904.8
Bhavnagar Laifol cotan tin (15kg) NULL 0 10 0 10 10476.2
Bhavnagar Laifol new tin(15kg) NULL 0 10 5 5 5595.25
Bhavnagar Gokul cotan oil pauch 1ltr NULL 0 180 0 180 12087
Bhavnagar Gokul cotan oil pauch 1ltr NULL 0 12 0 12 1408.08
Bhavnagar jiru NULL 0 60 0 60 7800
Bhavnagar Tea loos NULL 0 700 0 700 102900
Bhavnagar Gulab singtel (15 ltr tin) NULL 0 2 2 0 0
Bhavnagar Gulab cotan oil tin (15kg) NULL 0 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
J Livingston SQL
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33029
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
hemal_301080
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 109
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search