SEO

#1 (**permalink**) 02-28-2008, 05:27 PM

I'm requesting help for an account report. The desired result is
a report that displays the total(money) for cost center's accounts
based on account access of the user.

Tables:
FY_Orders - stores the orders for items purchasing. Column "fin" is
the owner ID.
CostCenters - Group of Accounts
Accounts - belong to cost centers
AcctAccess - Stores user access to CostCenters' Accounts. Each record
has a center_id, acct_id, fin (many to many)

Basically I want to show values for all cost centers that a particular
'fin'(owner) has access to even center's that have no records in the
FY_Orders table. Any help is greatly appreciated.

The query below only displays results if there are records in the
FY_Orders Table for the fin=10:

SELECT c.center_id, c.center_desc,c.center_num_sh,
SUM(CASE WHEN [Total] IS NOT NUll THEN [Total] ELSE 0 END) AS Total
FROM FY_Orders f

INNER JOIN AcctAccess aa ON
f.cost_center_id = aa.center_id
AND f.fin=10
AND fy='2004'

INNER JOIN CostCenters c ON c.center_id = aa.center_id

GROUP BY f.cost_center_id,total,c.center_desc,c.center_num_ sh,c.center_id

#2 (**permalink**) 02-28-2008, 05:28 PM

[posted and mailed, please reply in news]

DbSkynard (artwookie@yahoo.com) writes:
> Basically I want to show values for all cost centers that a particular
> 'fin'(owner) has access to even center's that have no records in the
> FY_Orders table. Any help is greatly appreciated.

Standard suggestion: your chances to get help with a problem like
this increases immensly if you include:

o CREATE TABLE statements for the tables involved.
o INSERT statements with sample data.
o The desired result from the output data.

This facilitates to understand what you're after, and makes it possible
to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

#3 (**permalink**) 02-28-2008, 05:28 PM

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. You ask people to help you for free, but you don't
give us any specifications we can use, no sample data, and no DDL.

Try using "SUM(COSALESCE (total, 0.00)) AS total", since it is a bit
easier to read and you can probably use an OUTER JOIN for this, but
without DDL, who knows?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Complex SQL JOIN - check this out...