Get data from 3 or more tables

  • 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

  • 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.

  • 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

  • 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

  • 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?

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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 (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

  • 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

  • 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