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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

ronnyr
Starting Member

21 Posts

Posted - 2006-09-27 : 21:28:25

Hi All,

I need to produce a report where is showing total order per office.
I have 4 different table: order, orderassignment, employee, and office.

This query will show office that have the order assignment ONLY, and
NOT showing anything if that office do not have order assign to them:

select of.OfficeName, SUM(OrderID) FROM Order o
JOIN OrderAssignment oa ON oa.OrderID = o.OrderID
JOIN Office of ON of.Code = e.OfficeCode
JOIN Employee e ON e.EmployeeID = oa.EmployeeID
GROUP BY OfficeName

However, i want to show ALL office on my report and if that office do not have order assignment yet, it will show zero. HOW TO DO THAT?

Ron.

“Know where to find the information and how to use it - That's the secret of success”

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 21:45:28

[code]
select
of.OfficeName,
SUM(case when o.OrderID is null then 0 else 1 end)
FROM
Office of
left join
Employee e
ON of.Code = e.OfficeCode
join
OrderAssignment oa
ON e.EmployeeID = oa.EmployeeID
join
Order o
ON oa.OrderID = o.OrderID
GROUP BY
of.OfficeName

[/code]

CODO ERGO SUM

ronnyr
Starting Member

21 Posts

Posted - 2006-09-27 : 22:35:28

Thanks Mike,

I still cannot showing the office name, i think because i join the with the orderassignment table. is there anyway to do like union or something. thanks.

Ron.

“Know where to find the information and how to use it - That's the secret of success”

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 23:04:16

Since you didn't post you table structure, or any sample data it is hard to help you any further.

Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CODO ERGO SUM

ronnyr
Starting Member

21 Posts

Posted - 2006-09-27 : 23:19:30

Ok, thanks for the blog.
This is my order_assignment table:
CREATE TABLE [oms].[OrderAssignment](
[AssignmentID] [int] NOT NULL,
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[RoleCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FeeSplit] [float] NOT NULL,
[FeeSplitTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[AssignDT] [datetime] NOT NULL,
[LastModifiedDT] [datetime] NOT NULL

this is my employee table:
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] NOT NULL,
[PersonID] [int] NOT NULL,
[EmployeeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DomainUser] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OfficeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DivisionCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedDT] [datetime] NOT NULL,
[LastModifiedDT] [datetime]

this is my office code table:

CREATE TABLE [dbo].[OfficeCode](
[Code] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Office] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Sequence] [int]

and this is my order table:

CREATE TABLE [oms].[Order](
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[AccountID] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CompanyID] [int] NULL,
[PersonID] [int] NOT NULL,
[OrderFee] [int] NULL,
[DueDT] [datetime] NULL,
[CreatedDT] [datetime] NOT NULL,
[StatusCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[StatusCodeDT] [datetime] NOT NULL,
[SpecialInstruction] [varchar](512)

What I want for the result is:

San Francisco Office,
Total Order A: 8
Total Order B: 0
Total Order : 8

Seattle Office,
Total Order A: 0
Total Order B: 0
Total Order: 0

Thanks

Ron.

“Know where to find the information and how to use it - That's the secret of success”

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-27 : 23:29:20

So none of your tables have primary keys defined and none of your tables have foreign key relationships defined?

You didn't post any sample data.

What does the Total in your result represent? Total of what?

CODO ERGO SUM

ronnyr
Starting Member

21 Posts

Posted - 2006-09-27 : 23:52:45

Order table: OrderID as PK
OfficeCode table: Code as PK
Employee table: employeeID as PK
OrderAssignment: assignmentID as PK

Total Order is Total of one of our product. For example productA and productB.

This is product table:

CREATE TABLE [oms].[Product](
[ProductID] [int] NOT NULL,
[ProductTypeCode] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DefaultFee] [int] NOT NULL,
[ProductTerm] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ProductDescription] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductID] ASC

Hope this helps. Thanks

Ron.

“Know where to find the information and how to use it - That's the secret of success”

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources