how to get itemID wise Stock balance different tbl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to get itemID wise Stock balance different tbl

Author

Topic

Qayyum
Starting Member

4 Posts

Posted - 2012-05-08 : 01:43:29

actually i want to get stock balance each itemid wise as openingQty+purchases-issuedQty following is my query which is not give me sufficient and resulted query will show sum of each item three times whereas in the back end i have only one record.

SELECT dbo.IssuedDetail.ItemID, dbo.IssuedDetail.ItemName, dbo.IssuedDetail.Price, dbo.ItemsChart.openingqty AS OpeningQty, dbo.RequisitionDetail.Accept AS QtyIn,
dbo.IssuedDetail.IssuedQty AS QtyOut, ISNULL(SUM(dbo.ItemsChart.openingqty), 0) + ISNULL(SUM(dbo.RequisitionDetail.Accept), 0)
- ISNULL(SUM(dbo.IssuedDetail.IssuedQty), 0) AS QtyBalance
FROM dbo.IssuedDetail LEFT OUTER JOIN
dbo.Issued ON dbo.IssuedDetail.IssuedID = dbo.Issued.IssuedID CROSS JOIN
dbo.RequisitionDetail LEFT OUTER JOIN
dbo.Requistion ON dbo.RequisitionDetail.ReceivingID = dbo.Requistion.ReceivingID CROSS JOIN
dbo.Head LEFT OUTER JOIN
dbo.ItemsChart ON dbo.Head.HeadID = dbo.ItemsChart.HeadID
GROUP BY dbo.IssuedDetail.ItemID, dbo.IssuedDetail.ItemName, dbo.IssuedDetail.Price, dbo.IssuedDetail.IssuedQty, dbo.RequisitionDetail.Accept, dbo.ItemsChart.openingqty

when i change quantity fields Group By to Sum then the result is sum of single value three times

please help me

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-08 : 07:15:06

Please post DDL of the tables and some sample data to go with it...

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Qayyum
Starting Member

4 Posts

Posted - 2012-05-08 : 07:29:03

please give me your email i sent to you my database
actually i want to get the stock balance each itemid wise

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-09 : 01:08:47

quote:
Originally posted by Qayyum

please give me your email i sent to you my database
actually i want to get the stock balance each itemid wise

Sorry, it doesn't work like that. I'd love to help you if you can post the problem you are facing right here in the Forum.

I would advice that you get into what you're working with. When you face a problem, post it on the Forum.....with the DDL of your tables, some sample Data, the Result Set that you are Expecting. And, the most important thing is that you post the "IDEAS/LOGIC" that you are working with. So, that the people who might help you think that you are actually working on something and not just asking someone to do your work.

Try it. There are a lot of people here to help you out. You just need to ask them the right way.
Help us in helping you.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Qayyum
Starting Member

4 Posts

Posted - 2012-05-09 : 06:55:17

Dear Fellows
I have six relational tables like
PrimaryKey Table ForeignKey Table

1.Head ---------------------- ItmesChart
HeadID <------------------ HeadID
HeadName ---------------- ItemID
ItemName
OpeningQty

2.Requistion ----------------- RequisitionDetail
ReceivingID <----------------> ReceivingID
ReceivingDate --------------- ItemID
SupplierName ---------------- ItemName
QtyAccept

3.Issued ------------------- IssuedDetail
IssuedID <---------------------> IssuedID
IssuedDate --------------------- ItemID
DepartmentName ---------------- ItemName

Now I want to calculate each Item Closing Balance as how much a particular Item has opening stock and how much a particular item was In and how much a particular item was out and how much a particular item has closing balance.When I create a view the query will showing result but wrong. When i group by the the ItemID and ItemName the query will showing three result of one item whereas I have only one record in each table of an item. similarly when i Group By ItemID and ItemName and the quantity columns make sum then the query will showing sum of each item as three times.

and the DDL of my tables is as under
1. Primary Key table

USE [StoreDB]
GO
/****** Object: Table [dbo].[Head] Script Date: 05/07/2012 15:59:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Head](
[HeadID] [nvarchar](50) NOT NULL,
[HeadName] [nvarchar](30) NULL,
CONSTRAINT [PK_Head] PRIMARY KEY CLUSTERED
(
[HeadID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Foreign Key table

SE [StoreDB]
GO
/****** Object: Table [dbo].[ItemsChart] Script Date: 05/07/2012 16:01:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ItemsChart](
[HeadID] [nvarchar](50) NULL,
[ItemID] [nvarchar](50) NOT NULL,
[ItemName] [nvarchar](255) NULL,
[openingqty] [int] NULL --(As OpeningQty)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[ItemsChart] WITH CHECK ADD CONSTRAINT [FK_ItemsChart_Head] FOREIGN KEY([HeadID])
REFERENCES [dbo].[Head] ([HeadID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ItemsChart] CHECK CONSTRAINT [FK_ItemsChart_Head]

2. Primary Key table

USE [StoreDB]
GO
/****** Object: Table [dbo].[Requistion] Script Date: 05/07/2012 16:02:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Requistion](
[ReceivingID] [nvarchar](50) NOT NULL,
[Date] [datetime] NULL,
[SupplierName] [nvarchar](50) NOT NULL,
(
[ReceivingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Foreign Key table

USE [StoreDB]
GO
/****** Object: Table [dbo].[RequisitionDetail] Script Date: 05/07/2012 16:03:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RequisitionDetail](
[ReceivingID] [nvarchar](50) NULL,
[ItemName] [nvarchar](50) NOT NULL,
[ItemName] [nvarchar](50) NULL,
[QtyAccept] [int] NULL, --(As QtyIn)

) ON [PRIMARY]

GO
ALTER TABLE [dbo].[RequisitionDetail] WITH CHECK ADD CONSTRAINT [FK_RequisitionDetail_Requistion] FOREIGN KEY([ReceivingID])
REFERENCES [dbo].[Requistion] ([ReceivingID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RequisitionDetail] CHECK CONSTRAINT [FK_RequisitionDetail_Requistion]

3. Primary Key Table

USE [StoreDB]
GO
/****** Object: Table [dbo].[Issued] Script Date: 05/07/2012 16:04:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Issued](
[IssuedID] [nvarchar](50) NOT NULL,
[IssuedDate] [datetime] NULL,
[Department] [nvarchar](50) NULL,
CONSTRAINT [PK_IssueRequisition] PRIMARY KEY CLUSTERED
(
[IssuedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Foreign Key table

USE [StoreDB]
GO
/****** Object: Table [dbo].[IssuedDetail] Script Date: 05/07/2012 16:04:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IssuedDetail](
[IssuedID] [nvarchar](50) NULL,
[ItemID] [nvarchar](50) NOT NULL,
[ItemName] [nvarchar](50) NULL,
[IssuedQty] [float] NULL, --(As Qty Issued)
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[IssuedDetail] WITH CHECK ADD CONSTRAINT [FK_IssuedDetail_Issued] FOREIGN KEY([IssuedID])
REFERENCES [dbo].[Issued] ([IssuedID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[IssuedDetail] CHECK CONSTRAINT [FK_IssuedDetail_Issued]

please help me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources