April 9, 2013 at 11:19 pm
I have tables with following definition'
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
)
Items table contains data about items begin purchased and sold
Voucher1 table contains data about which item purchase or sold at which rate and tax and etc
Voucher table contains data about voucher no and data and etc
I want to display each and every item from Items table whether its in voucher1 table or not.
How can I get the data in following table format within a specified two date periods?
I want data of opening qty(before first date),incoming qty(between two dates),outgoing qty(between two dates)
ItemNo VoucherNo OpeningQTY IncomingQTY OutgoingQTY ClosingQTY
April 9, 2013 at 11:48 pm
Why don't you show us what you have tried to solve your problem and where you are having problems.
Sample data (as INSERT INTO statements) for each table and expected results based on the sample data will also help.
April 9, 2013 at 11:57 pm
This is the data inserted in those tables
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 [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 (18, N'PURCHASE RETURN', N'PR1213', 1, N'', CAST(0x37360B00 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 (20, N'CASH MEMO', N'CM1213', 1, NULL, 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
I have tried with following query but not getting opening quantity before the start date(first date from two dates)
select 'BHAVNAGAR' as GODOWN,AA.ITEM,AA.VNO,AA.BATCH,BB.OPQTY,AA.INCOMING,AA.OUTGOING from
(
select A.ITEM as ITEM,A.VNO as VNO,A.BATCH as BATCH,A.QTY as INCOMING,B.QTY as OUTGOING from
(
select I.itemname as ITEM,V1.voucherno as VNO,V1.batchno as BATCH,V1.quantity as QTY from items I
left outer join voucher1 V1
on I.srno=V1.item
left outer join voucher V
on v.vouchertype=v1.vouchertype and v.voucherprefix=v1.voucherprefix and v.voucherno=v1.voucherno and v.invoicetype=v1.invoicetype
where v.dt between @dt1 and @dt2 and v.vouchertype='PURCHASE'
) as A
left outer join
(
select I.itemname as ITEM,V1.voucherno as VNO,V1.batchno as BATCH,V1.quantity as QTY from items I
left outer join voucher1 V1
on I.srno=V1.item
left outer join voucher V
on v.vouchertype=v1.vouchertype and v.voucherprefix=v1.voucherprefix and v.voucherno=v1.voucherno and v.invoicetype=v1.invoicetype
where v.dt between @dt1 and @dt2 and v.vouchertype='SALES'
) as B
on A.item=B.item and A.batch=B.batch
) as AA
left outer join
(
select A.ITEM as ITEM,A.VNO as VNO,A.BATCH as BATCH,A.QTY-B.QTY as OPQTY from
(
select I.itemname as ITEM,V1.voucherno as VNO,V1.batchno as BATCH,V1.quantity as QTY from items I
left outer join voucher1 V1
on I.srno=V1.item
left outer join voucher V
on v.vouchertype=v1.vouchertype and v.voucherprefix=v1.voucherprefix and v.voucherno=v1.voucherno and v.invoicetype=v1.invoicetype
where v.dt < @dt1 and v.vouchertype='PURCHASE'
) as A
left outer join
(
select I.itemname as ITEM,V1.voucherno as VNO,V1.batchno as BATCH,V1.quantity as QTY from items I
left outer join voucher1 V1
on I.srno=V1.item
left outer join voucher V
on v.vouchertype=v1.vouchertype and v.voucherprefix=v1.voucherprefix and v.voucherno=v1.voucherno and v.invoicetype=v1.invoicetype
where v.dt < @dt1 and v.vouchertype='SALES'
) as B
on A.item=B.item and A.batch=B.batch
) as BB
on AA.ITEM=BB.ITEM and AA.batch=BB.batch
I need results in following format
ItemNo Voucherno OpeningQty IncomingQty OutgoingQty ClosingQTy
Each items from Items table should be displayed whether its having data in voucher1 or not
April 10, 2013 at 4:47 am
maybe something along these lines....??
declare @dt1 datetime
declare @dt2 datetime
set @dt1 = '2012-09-01'
set @dt2 = '2012-10-01'
;WITH cte AS
(
SELECT
V1.item,
--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' then V1.quantity 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) AS INCOMING,
SUM(case when v.dt >= @dt1 AND v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else null end) 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' then V1.quantity else 0 end) AS CLOSING
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
GROUP BY V1.item
--,V1.voucherno
)
SELECT items.srno, items.itemname, CTE.*
FROM items LEFT OUTER JOIN
CTE ON items.srno = CTE.item
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 10, 2013 at 5:35 am
J Livingston SQL (4/10/2013)
maybe something along these lines....??
declare @dt1 datetime
declare @dt2 datetime
set @dt1 = '2012-09-01'
set @dt2 = '2012-10-01'
;WITH cte AS
(
SELECT
V1.item,
--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' then V1.quantity 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) AS INCOMING,
SUM(case when v.dt >= @dt1 AND v.dt < @dt2 AND v.vouchertype = 'SALES' then V1.quantity else null end) 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' then V1.quantity else 0 end) AS CLOSING
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
GROUP BY V1.item
--,V1.voucherno
)
SELECT items.srno, items.itemname, CTE.*
FROM items LEFT OUTER JOIN
CTE ON items.srno = CTE.item
This worked great and made my day.
Thank you, I was unaware of this WITH CTE. Can you suggest me from where can i know everything about TSQL and MSSQL?
April 10, 2013 at 6:44 am
Can you suggest me from where can i know everything about TSQL and MSSQL?
Knowing "everything" may take a little time....its a vast subject...try putting "TSQL" into a search engine....see how many results you get 😀
dependent upon what you know already...this series http://www.sqlservercentral.com/stairway/ may be worth reading.
Also...keep reading these forums / posting questions and also try to answer where possible ...its a great place to learn.
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 10, 2013 at 8:18 am
J Livingston SQL (4/10/2013)
Can you suggest me from where can i know everything about TSQL and MSSQL?
Knowing "everything" may take a little time....its a vast subject...try putting "TSQL" into a search engine....see how many results you get 😀
dependent upon what you know already...this series http://www.sqlservercentral.com/stairway/ may be worth reading.
Also...keep reading these forums / posting questions and also try to answer where possible ...its a great place to learn.
kind regards
One problem i am finding is, connecting this with other 2 tables, CASHMEMO,voucherPRETSRET
CASHMEMO contains information about items sold against cash
voucherPRETSRET contains information about items PURCHASE RETURN(OUT) or SALES RETURN(IN)
CASHMEMO table definition
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
)
voucherPRETSRET table definition
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
)
CASHMEMO contains following data
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
voucherPRETSRET doesnt contain any data but needs to be checked
I tried with following query but not getting CASHMEMO(OUT) qty in OUTGOING column
;WITH cte AS
(
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
--,V1.voucherno
)
SELECT (select city from company) as GODOWN, items.itemname as NAME,NULL as HSN, sum(CTE.OPENING) as OPENING,sum(CTE.INCOMING) as INCOMING
,sum(CTE.OUTGOING) as OUTGOING,sum(CTE.CLOSING) as CLOSING,sum(CTE.VALUE) as VALUE
FROM items LEFT OUTER JOIN
CTE ON items.srno = CTE.item
group by items.itemname
Plz help
April 10, 2013 at 8:34 am
hemal_301080 (4/10/2013)
J Livingston SQL (4/10/2013)
Can you suggest me from where can i know everything about TSQL and MSSQL?
Knowing "everything" may take a little time....its a vast subject...try putting "TSQL" into a search engine....see how many results you get 😀
dependent upon what you know already...this series http://www.sqlservercentral.com/stairway/ may be worth reading.
Also...keep reading these forums / posting questions and also try to answer where possible ...its a great place to learn.
kind regards
One problem i am finding is, connecting this with other 2 tables, CASHMEMO,voucherPRETSRET
CASHMEMO contains information about items sold against cash
voucherPRETSRET contains information about items PURCHASE RETURN(OUT) or SALES RETURN(IN)
CASHMEMO table definition
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
)
voucherPRETSRET table definition
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
)
CASHMEMO contains following data
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
voucherPRETSRET doesnt contain any data but needs to be checked
I tried with following query but not getting CASHMEMO(OUT) qty in OUTGOING column
;WITH cte AS
(
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
--,V1.voucherno
)
SELECT (select city from company) as GODOWN, items.itemname as NAME,NULL as HSN, sum(CTE.OPENING) as OPENING,sum(CTE.INCOMING) as INCOMING
,sum(CTE.OUTGOING) as OUTGOING,sum(CTE.CLOSING) as CLOSING,sum(CTE.VALUE) as VALUE
FROM items LEFT OUTER JOIN
CTE ON items.srno = CTE.item
group by items.itemname
Plz help
are these all of your tables / options /variables etc now ?...if so please send a complete set of table create staements / data insert statements and your expected results based on the sample data you provide.
will make it much easier to check your code and problems.
regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 10, 2013 at 8:50 am
will also need details of table/data for item_batch_stock IBS
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 10, 2013 at 9:22 pm
J Livingston SQL (4/10/2013)
will also need details of table/data for item_batch_stock IBS
This is all table i need to check. No further. I did provide only 3 table details so that rest I can check on my side but couldnt.
Any way this is table definition and data for Item_batch_stock. This contains price/unit about particular item per batchno.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO
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
April 10, 2013 at 11:25 pm
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
April 10, 2013 at 11:31 pm
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.
April 10, 2013 at 11:40 pm
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
April 10, 2013 at 11:47 pm
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
April 10, 2013 at 11:52 pm
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply