Join Query 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

Join Query and Sum

Author

Topic

mohdmartin
Starting Member

22 Posts

Posted - 2007-07-19 : 07:22:10

How do I find the join the query with six tables and calculate
sum quantity. I want to retreive data from the table PPE_ITEM_ISSUE (Personal Protective Euquipment Item) group by employee.
ie. one employee how many item issue and total quantity issue to
each employee,

tables
-------
1.employee
2.department
3.position
4.country
5.PPE_STOCK
6.PPE_ITEM_ISSUE

PPE_ITEM_ISSUE
--------------
PII_ID int (PK)
EMP_ID varchar
RECOMMENDED_BY varchar
APPROVED_BY char
PPE_ID varchar
DATE_APPROVED char
PPE_TYPE varchar
QUANTITY int
....

PPE_STOCK
-------------------
PPE_ID varchar (PK)
PPE_NAME varchar
QUANTITY int

EMPLOYEE
---------------------
EMP_ID varchar (PK)
NAME varchar
DEPARTMENT_CODE varchar
POSITION_CODE int
....

DEPARTMENT
------------
DEPARTMENT_CODE varchar (PK)
DEPARTMENT_NAME varchar

POSITION
--------
POSITION_CODE int (PK)
POSITION_NAME varchar

sample data in PPE_ITEM_ISSUE

EMP_ID RECOMMENDED_BY APPROVED_BY PPE_ID QUANTITY
--------------------------------------------------------
p0012345 p001212 p000643 FE07 15
p0012354 p001552 p001548 EGG05 7
p0011237 p001412 p000643 SG03 5
p0012345 p008788 p001657 EPEM04 10
p0088812 p005453 p001223 EPEM04 8

recommended_by and approved_by means employee ID.

Sample data retrieve from PPE_ITEM_ISSUE Table like this.

Name Current_Deployment Istem issue Recommended By Approved by Quantity sum
-----------------------------------------------------------------
Jones C2016 Safety Helmet Parker Mark 5
Jones C2151 Safety Helmet Khan Haris 10 15
Wagner C1501 Safety Gloves Khan Ahmed 10 10
Ali C2560 Safety Helmet Baseem Ahad 5
Ali C2016 Safety Helmet Waseem Ahad 5 10
......
....

Table script is following

CREATE TABLE [dbo].[EMPLOYEE] (
[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 ,
[DEPARTMENT_CODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POSITION_CODE] [int] NULL ,
[MOBILE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS 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 ,
[CURRENT_DEPLOYMENT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECOMMENDED_BY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE_RECOMMENDED] [datetime] NULL ,
[APPROVED_BY] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PPE_ID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PPE_TYPE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QUANTITY] [int] 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 ,
[LOGIN_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DEPARTMENT] (
[DEPARTMENT_CODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DEPARTMENT_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[POSITION] (
[POSITION_CODE] [int] IDENTITY (1, 1) NOT NULL ,
[POSITION_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[COUNTRY] (
[COUNTRY_CODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COUNTRY_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PROJECT] (
[PROJECT_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PROJECT_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Please help
Thanks

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources