Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get data from 3 or more tables Expand / Collapse
Author
Message
Posted Tuesday, April 9, 2013 11:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 9:46 PM
Points: 29, Visits: 79
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
Post #1440651
Posted Tuesday, April 9, 2013 11:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:26 PM
Points: 20,732, Visits: 32,496
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1440658
Posted Tuesday, April 9, 2013 11:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 9:46 PM
Points: 29, Visits: 79
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
Post #1440661
Posted Wednesday, April 10, 2013 4:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 1,917, Visits: 19,585
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
Post #1440727
Posted Wednesday, April 10, 2013 5:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 9:46 PM
Points: 29, Visits: 79
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?
Post #1440744
Posted Wednesday, April 10, 2013 6:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 1,917, Visits: 19,585
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
Post #1440773
Posted Wednesday, April 10, 2013 8:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 9:46 PM
Points: 29, Visits: 79
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
Post #1440833
Posted Wednesday, April 10, 2013 8:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 1,917, Visits: 19,585
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
Post #1440847
Posted Wednesday, April 10, 2013 8:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 1,917, Visits: 19,585
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
Post #1440858
Posted Wednesday, April 10, 2013 9:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, September 15, 2013 9:46 PM
Points: 29, Visits: 79
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


Post #1441095
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse