Subtract Qty from 2 tables in Sql

Hello all

I am having 2 tables

1. InventoryInwards

2. InventoryIssued

I want an output by subtracting the qty from both the above tables based on NameOfStores, ReferenceNo and Unit.

I wrote the below query, but it is showing wrong output, whereas running the same query on individual tables gives the correct result.

Wrong Output:

declare @storeid bigint
set @storeid=19
select I.NameOfStores, I.ReferenceNo, SUM(Inwards.InwardQty) InwardQty, SUM(ISNULL(Issued.IssuedQty,0)) IssuedQty, SUM((ISNULL(Inwards.InwardQty,0)-ISNULL(Issued.IssuedQty,0)))as Balance
from InventoryInwards as I
LEFT JOIN
(select nameofStores, ISNULL(Sum(ISNULL(Qty,0)),0) as InwardQty
from InventoryInwards where StoreID=@storeid
group By NameOfStores, ReferenceNo, Unit) as Inwards ON I.NameOfStores = Inwards.NameOfStores
left JOIN
(select nameofStores, ISNULL(Sum(ISNULL(Qty,0)),0) as IssuedQty
from InventoryIssued where StoreID=@storeid
group By NameOfStores, ReferenceNo, unit) as Issued on I.NameOfStores= Issued.NameOfStores
where StoreID = @storeid
group by I.NameOfStores, I.ReferenceNo, I.Unit

The above result shows samsung with referenceno 001 having 60 as its inwards qty, where as the actual qty is 10

select nameofStores, ReferenceNo, ISNULL(Sum(ISNULL(Qty,0)),0) as InwardQty
from InventoryInwards where StoreID=19
group By NameOfStores, ReferenceNo, Unit

Please do let me know, where m i making mistakes

Table Structure with sample data

CREATE TABLE [dbo].[InventoryInwards](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[InwardID] [bigint] NULL,
[Date] [datetime] NULL,
[NameOfStores] [nvarchar](max) NULL,
[ReferenceNo] [nvarchar](max) NULL,
[Qty] [decimal](18, 2) NULL,
[Unit] [nvarchar](max) NULL,
[Rate] [decimal](18, 2) NULL,
[Nature] [nvarchar](max) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](max) NULL,
[StoreID] [bigint] NULL,
CONSTRAINT [PK_InventoryInwards] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InventoryIssued](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[IssuedID] [bigint] NULL,
[Date] [datetime] NULL,
[NameOfStores] [nvarchar](max) NULL,
[ReferenceNo] [nvarchar](max) NULL,
[Qty] [decimal](18, 2) NULL,
[Unit] [nvarchar](max) NULL,
[Rate] [decimal](18, 2) NULL,
[Nature] [nvarchar](max) NULL,
[IssuedTo] [nvarchar](max) NULL,
[IssuedBy] [nvarchar](max) NULL,
[Remarks] [nvarchar](max) NULL,
[CreatedDate] [datetime] NULL,
[CreatedBy] [nvarchar](max) NULL,
[StoreID] [bigint] NULL,
CONSTRAINT [PK_InventoryIssued] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[InventoryInwards] ON
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (1, 0, CAST(0x0000A823016B30CD AS DateTime), N'pipe', N'sdf', CAST(12.00 AS Decimal(18, 2)), N'12', CAST(12.00 AS Decimal(18, 2)), N'sf', CAST(0x0000A82300000000 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (2, 0, CAST(0x0000A823016B32BB AS DateTime), N'mobile', N'sdf', CAST(12.00 AS Decimal(18, 2)), N'12', CAST(14.00 AS Decimal(18, 2)), N'sf', CAST(0x0000A823016B32BB AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (3, 0, CAST(0x0000A823016B33C9 AS DateTime), N'mobile', N'sdf', CAST(12.00 AS Decimal(18, 2)), N'12', CAST(16.00 AS Decimal(18, 2)), N'sf', CAST(0x0000A823016B33C9 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (7, 0, CAST(0x0000A823016B38B2 AS DateTime), N'gold', N'sdf', CAST(12.00 AS Decimal(18, 2)), N'12', CAST(22.00 AS Decimal(18, 2)), N'sf', CAST(0x0000A823016B38B2 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (8, 0, CAST(0x0000A823016B3A0C AS DateTime), N'silver', N'sdf', CAST(12.00 AS Decimal(18, 2)), N'12', CAST(26.00 AS Decimal(18, 2)), N'sf', CAST(0x0000A823016B3A0C AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (9, 0, CAST(0x0000A823016B7A25 AS DateTime), N'pen', N'sf', CAST(22.00 AS Decimal(18, 2)), N'232', CAST(112.00 AS Decimal(18, 2)), N'', CAST(0x0000A823016B7A25 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (10, 0, CAST(0x0000A82400000000 AS DateTime), N'asd', N'001', CAST(10.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'allotment', CAST(0x0000A82500EA44E4 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (11, 0, CAST(0x0000A8260000AE20 AS DateTime), N'gold', N'020', CAST(10.00 AS Decimal(18, 2)), N'nos', CAST(120.00 AS Decimal(18, 2)), N'procurement', CAST(0x0000A8260000F098 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (12, 0, CAST(0x0000A8290155BDA2 AS DateTime), N'pipe', N'001', CAST(120.00 AS Decimal(18, 2)), N'12', CAST(120.00 AS Decimal(18, 2)), N'procurement', CAST(0x0000A8290155CF98 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (13, 0, CAST(0x0000A82B00283BF5 AS DateTime), N'asd', N'001', CAST(10.00 AS Decimal(18, 2)), N'12', CAST(120.00 AS Decimal(18, 2)), N'', CAST(0x0000A82B00284854 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (14, 0, CAST(0x0000A82B01836DC1 AS DateTime), N'name1', N'ref1', CAST(100.00 AS Decimal(18, 2)), N'nos', CAST(100.00 AS Decimal(18, 2)), N'purchase', CAST(0x0000A82B01838544 AS DateTime), N'', 19)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (15, 0, CAST(0x0000A82B01836DC1 AS DateTime), N'mobile', N'ref1', CAST(50.00 AS Decimal(18, 2)), N'pcs', CAST(120.00 AS Decimal(18, 2)), N'purchase', CAST(0x0000A82B01839C40 AS DateTime), N'', 19)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (18, 0, CAST(0x0000A82E002DE858 AS DateTime), N'samsung', N'001', CAST(10.00 AS Decimal(18, 2)), N'nos', CAST(100.00 AS Decimal(18, 2)), N'purchase', CAST(0x0000A82E002DF8A8 AS DateTime), N'', 19)
GO
INSERT [dbo].[InventoryInwards] ([ID], [InwardID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [CreatedDate], [CreatedBy], [StoreID]) VALUES (19, 0, CAST(0x0000A82E002E1FF1 AS DateTime), N'samsung', N'002', CAST(50.00 AS Decimal(18, 2)), N'nos', CAST(120.00 AS Decimal(18, 2)), N'purchase', CAST(0x0000A82E002E2FBA AS DateTime), N'', 19)
GO
SET IDENTITY_INSERT [dbo].[InventoryInwards] OFF
GO
SET IDENTITY_INSERT [dbo].[InventoryIssued] ON
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (1, 0, CAST(0x0000A8250101C827 AS DateTime), N'pipe', N'110', CAST(10.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'allotment', N'ashutosh', N'imran', N'asdasdad', CAST(0x0000A82501038436 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (2, 0, CAST(0x0000A8250101C827 AS DateTime), N'mobile', N'5', CAST(5.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'allotment', N'ashutosh', N'imran', N'asdasdad', CAST(0x0000A8250103D29D AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (3, 0, CAST(0x0000A82600168DE7 AS DateTime), N'asd', N'002', CAST(10.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'allotment', N'ranjan', N'amit', N'test remarks', CAST(0x0000A8260016AA2B AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (4, 0, CAST(0x0000A82600168DE7 AS DateTime), N'pipe', N'002', CAST(100.00 AS Decimal(18, 2)), N'12', CAST(12.00 AS Decimal(18, 2)), N'allotment', N'ranjan', N'amit', N'test remarks', CAST(0x0000A8260016B376 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (5, 0, CAST(0x0000A82600168DE7 AS DateTime), N'silver', N'001', CAST(30.00 AS Decimal(18, 2)), N'12', CAST(26.00 AS Decimal(18, 2)), N'allotment', N'ranjan', N'amit', N'test remarks', CAST(0x0000A8260016BD89 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (6, 0, CAST(0x0000A82B002726E8 AS DateTime), N'asd', N'', CAST(10.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'', N'', N'', N'', CAST(0x0000A82B00272D1C AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (7, 0, CAST(0x0000A82B0027A130 AS DateTime), N'asd', N'', CAST(14.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'', N'', N'', N'', CAST(0x0000A82B0027A9A4 AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (8, 0, CAST(0x0000A82B00284F19 AS DateTime), N'asd', N'220', CAST(15.00 AS Decimal(18, 2)), N'meter', CAST(100.00 AS Decimal(18, 2)), N'', N'kumar', N'mahesh', N'', CAST(0x0000A82B00286AAB AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (9, 0, CAST(0x0000A82B00284F19 AS DateTime), N'gold', N'15', CAST(15.00 AS Decimal(18, 2)), N'12', CAST(22.00 AS Decimal(18, 2)), N'', N'kumar', N'mahesh', N'', CAST(0x0000A82B0028715E AS DateTime), N'', 1)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (10, 0, CAST(0x0000A82C00012C18 AS DateTime), N'mobile', N'new ref', CAST(20.00 AS Decimal(18, 2)), N'pcs', CAST(120.00 AS Decimal(18, 2)), N'', N'me', N'me', N'', CAST(0x0000A82C00014645 AS DateTime), N'', 19)
GO
INSERT [dbo].[InventoryIssued] ([ID], [IssuedID], [Date], [NameOfStores], [ReferenceNo], [Qty], [Unit], [Rate], [Nature], [IssuedTo], [IssuedBy], [Remarks], [CreatedDate], [CreatedBy], [StoreID]) VALUES (11, 0, CAST(0x0000A82C0001839C AS DateTime), N'name1', N'', CAST(10.00 AS Decimal(18, 2)), N'nos', CAST(100.00 AS Decimal(18, 2)), N'sold', N'', N'', N'', CAST(0x0000A82C000194C4 AS DateTime), N'', 19)
GO
SET IDENTITY_INSERT [dbo].[InventoryIssued] OFF
GO

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ankit Sharma8y ago
Accepted answer
Hi Anoop,

Please try this SQL and let me know if it works for you
declare @storeid bigint
set @storeid=19
select  I.NameOfStores, I.ReferenceNo, SUM(Inwards.InwardQty) InwardQty,  SUM(ISNULL(Issued.IssuedQty,0)) IssuedQty, SUM((ISNULL(Inwards.InwardQty,0)-ISNULL(Issued.IssuedQty,0)))as Balance
from InventoryInwards as I
LEFT JOIN
        (select ReferenceNo,  nameofStores, ISNULL(Sum(ISNULL(Qty,0)),0) as InwardQty
        from InventoryInwards where StoreID=@storeid
        group By NameOfStores, ReferenceNo, Unit) as Inwards ON I.NameOfStores = Inwards.NameOfStores
        and i.ReferenceNo=Inwards.ReferenceNo

left JOIN
        (select   nameofStores, ISNULL(Sum(ISNULL(Qty,0)),0) as IssuedQty
        from InventoryIssued where StoreID=@storeid
        group By NameOfStores, ReferenceNo, unit) as Issued on I.NameOfStores= Issued.NameOfStores



where i.StoreID = @storeid
group by I.NameOfStores, I.ReferenceNo, I.Unit
Anoop Bansal8y ago
Thanks Ankit... that was I missing out.