Report across multiple tables - 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

SQL Server Development (2000)

Report across multiple tables

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-02-22 : 04:21:36

I have the following 3 tables:
Expenditure
--------------
ReqNo _PK
ReqAmount

one
to
one

Order
---------------
OrderNo _PK
OrderAmount
ReqNo _FK

one
to
many

Payment
---------------
PayNo _PK
PayAmount
OrderNo _FK

I want to be able to pull the following report: eg:
ReqNo | ReqAmount | OrderNo | OrderAmount | PayNo | PayAmount
--------------------------------------------------------------
1234a | $12345.00 | 123456b | $12345.00 | 1234c | $1000.00
----- | --------- | ------- | --------- | 1235c | $5000.00
----- | --------- | ------- | --------- | Total | $6000.00
1236a | $42342.78 | 342344c | $41231.87 | 2344d | $5431.00
etc....

Sorry about all the ---, trying to format nicely.
Can this come from one statement??..

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-22 : 06:23:24

It can. Whether it should is a different matter.

Haven't tested this, so it might have some typos.


SELECT ReqNo, ReqAmount, OrderNo, OrderAmount, PayNo, PayAmount
FROM (
  SELECT
    o.OrderNo AS OrderNoSort, 0 AS TotFlag,
    CASE WHEN PayNo = MinPayNo THEN e.ReqNo ELSE '-----' END AS ReqNo,
    CASE WHEN PayNo = MinPayNo THEN e.ReqAmount ELSE '---------' END AS ReqAmount,
    CASE WHEN PayNo = MinPayNo THEN o.OrderNo ELSE '-------' END AS OrderNo,
    CASE WHEN PayNo = MinPayNo THEN o.OrderAmount ELSE '---------' END AS OrderAmount,
    p.PayNo,
    p.PayAmount
  SELECT ReqNo, ReqAmount, OrderNo, OrderAmount, PayNo, PayAmount
  FROM Order o
  INNER JOIN Expenditure e ON o.ReqNo = e.ReqNo
  INNER JOIN Payment p ON o.OrderNo = p.OrderNo
  INNER JOIN (
      SELECT OrderNo, MIN(PayNo) AS MinPayNo
      FROM Payment
      GROUP BY OrderNo) pmin ON o.OrderNo = pmin.OrderNo
UNION ALL
  SELECT OrderNo, 1,
    '-----', '---------', '-------', '---------', 'Total', SUM(PayAmount)
  FROM Payment
  GROUP BY OrderNo
) a
ORDER BY OrderNoSort, TotFlag, PayNo

Edited by - Arnold Fribble on 02/22/2002 06:25:35

Scott
Posting Yak Master

145 Posts

Posted - 2002-02-22 : 07:03:05

Changed as follows, but gives error:
Server: Msg 1033, Level 15, State 1, Line 26
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
original error:
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Order'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'pmin'.
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near ')'.


SELECT ReqNo, ReqAmount, OrderNo, OrderAmount, PayNo, PayAmount
FROM (
  SELECT
    o.OrderNo AS OrderNoSort, 0 AS TotFlag,
    CASE WHEN PayNo = MinPayNo THEN e.ReqNo ELSE '-----' END AS ReqNo,
    CASE WHEN PayNo = MinPayNo THEN e.ReqAmount ELSE '---------' END AS ReqAmount,
    CASE WHEN PayNo = MinPayNo THEN o.OrderNo ELSE '-------' END AS OrderNo,
    CASE WHEN PayNo = MinPayNo THEN o.OrderAmount ELSE '---------' END AS OrderAmount,
    p.PayNo,
    p.PayAmount
from (
  SELECT ReqNo, ReqAmount, OrderNo, OrderAmount, PayNo, PayAmount
  FROM [Order] o
  INNER JOIN Expenditure e ON o.ReqNo = e.ReqNo
  INNER JOIN Payment p ON o.OrderNo = p.OrderNo
  INNER JOIN (
      SELECT OrderNo, MIN(PayNo) AS MinPayNo
      FROM Payment
      GROUP BY OrderNo) pmin ON o.OrderNo = pmin.OrderNo
UNION ALL
  SELECT OrderNo, 1,
    '-----', '---------', '-------', '---------', 'Total', SUM(PayAmount)
  FROM Payment
  GROUP BY OrderNo
) a
ORDER BY OrderNoSort, TotFlag, PayNo

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-22 : 07:12:44

Oops! Yes, an extra line got pasted in there somehow.


SELECT ReqNo, ReqAmount, OrderNo, OrderAmount, PayNo, PayAmount
FROM (
  SELECT
    o.OrderNo AS OrderNoSort, 0 AS TotFlag,
    CASE WHEN PayNo = MinPayNo THEN e.ReqNo ELSE '-----' END AS ReqNo,
    CASE WHEN PayNo = MinPayNo THEN e.ReqAmount ELSE '---------' END AS ReqAmount,
    CASE WHEN PayNo = MinPayNo THEN o.OrderNo ELSE '-------' END AS OrderNo,
    CASE WHEN PayNo = MinPayNo THEN o.OrderAmount ELSE '---------' END AS OrderAmount,
    p.PayNo,
    p.PayAmount
  FROM [Order] o
  INNER JOIN Expenditure e ON o.ReqNo = e.ReqNo
  INNER JOIN Payment p ON o.OrderNo = p.OrderNo
  INNER JOIN (
      SELECT OrderNo, MIN(PayNo) AS MinPayNo
      FROM Payment
      GROUP BY OrderNo) pmin ON o.OrderNo = pmin.OrderNo
UNION ALL
  SELECT OrderNo, 1,
    '-----', '---------', '-------', '---------', 'Total', SUM(PayAmount)
  FROM Payment
  GROUP BY OrderNo
) a
ORDER BY OrderNoSort, TotFlag, PayNo

Scott
Posting Yak Master

145 Posts

Posted - 2002-02-22 : 07:53:16

Works great thanks!

The other matter on a high level what would be other ways to do it. Performance wasn't too bad less than a second (1500 rows) on my laptop with MSDE 600MHz 128MB.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources