Quantity Balance and sum - SQL Server Forums

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

Quantity Balance and sum

Author

Topic

mohdmartin
Starting Member

22 Posts

Posted - 2007-08-14 : 06:52:17

I have following table structure

CREATE TABLE [dbo].[EMPLOYEE] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NAME] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROJECT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PPE_STOCK] (
[REC_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PPE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUANTITY] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PPE_ITEM_ISSUE] (
[PII_ID] [int] IDENTITY (1, 1) NOT NULL ,
[EMP_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATE_REQUEST] [datetime] NULL ,
[QUANTITY_ISSUE] [int] NULL ,
[DATE_ISSUE] [datetime] NULL
) ON [PRIMARY]
GO

I want to calcalate the balance quantity avaiable in the stock.

PPE_STOCK => STOCK OF QUANTITY TABLE
PPE_ITEM_ISSUE => STOCK QUANTITY ISSUE TO EMPLOYEE TABLE

query retrieve like this.

Name....EMP_ID....PPE_NAME,..QUANTITY_ISSUE.Stock Qty.Balance Qty
-----------------------------------------------------------------
Martin..p0012456..Safety Gloves 1 .......100........99
Martin..p0012566..Safety Glass 1 .......100........99
Peter...p00123456.Safety Gloves 1........100........98
Jone....p00987654.Safety Helmet 1........100........99
Khan....p0012122..Safety Helmet 1........100........98
....

stock quanity minus the issue qauantity and retreive balance QTY
how ?

How can calcuate / retrieve the balance quantity by the query ?

Please us query calcuation.

regards
Martin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-14 : 13:06:46

Sample data would be great!

E 12°55'05.25"
N 56°04'39.16"

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 13:31:00

Better post the insert scripts

Ashley Rhodes

mohdmartin
Starting Member

22 Posts

Posted - 2007-08-15 : 01:41:21

Thanks for response.
sample data like this.

insert into EMPLOYEE (EMP_ID,NAME,PROJECT_ID,DEPARTMENT_CODE) values
('P0006360','Martin','C2010',1)
insert into EMPLOYEE (EMP_ID,NAME,PROJECT_ID,DEPARTMENT_CODE) values
('P0006365','Peter','C2016',5)
insert into EMPLOYEE (EMP_ID,NAME,PROJECT_ID,DEPARTMENT_CODE) values
('P0006370','Mark','C2015',10)
insert into EMPLOYEE (EMP_ID,NAME,PROJECT_ID,DEPARTMENT_CODE) values
('P0006375','Khan','C986',15)
insert into EMPLOYEE (EMP_ID,NAME,PROJECT_ID,DEPARTMENT_CODE) values
('P0006380','Mark','C2015',10)
......

insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY) values
('SH01','SAFETY GLOVES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY) values
('SH02','SAFETY MELMET',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY) values
('SH03','SAFETY GLASSES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY) values
('SH04','SAFETY SHOES',100)
insert into ppe_stock (PPE_ID,PPE_NAME,QUANTITY) values
('SH05','SAFETY VET',100)
.....
...

insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006360','SH01','NEW',1)
insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006360','SH02','NEW',1)

insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006365','SH02','NEW',1)
insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006365','SH03','NEW',1)

insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006370','SH03','NEW',1)
insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006370','SH04','NEW',1)

insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006375','SH04','NEW',1)
insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006375','SH05','NEW',1)

insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006380','SH05','NEW',1)
insert into ppe_item_issue (emp_id,ppe_id,ppe_type,quantity_issue) values
('p0006380','SH01','NEW',1)
...
..

how can retrieve/calcuate the balance qty by the query i.e.

Name....EMP_ID....PPE_NAME,..QUANTITY_ISSUE.Stock Qty.Balance Qty
-----------------------------------------------------------------
Martin..p0012456..Safety Gloves 1 .......100........99
Martin..p0012566..Safety Glass 1 .......100........99
Peter...p00123456.Safety Gloves 1........100........98
Jone....p00987654.Safety Helmet 1........100........99
Khan....p0012122..Safety Helmet 1........100........98
....

we have 100 safety Helmet, 50 Helmet issue to employees, balance will be 50.
sometime two Helmet issue to employee incase of Helmet broken etc..

We also want to find out how many Helmet issue to one employee.

thanks and regards
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources