It's probably a piece of cake for those in the know but it's proving hard
work for me...

Scenario: I developing a report for a system that managed a filter exchange
business. Basically filters are sent to/from customers to our business. I'm
trying to produce a management report which summarises for each customer
depot, which filter types (PartNo) have we sent them and which have they
sent back.

The desired report is probably best explained by example:

Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13

[Yeah, it looks a bit crap on usenet, but it looks better if you pop it in
Excel]

As you can see not all PartNos may be listed - some may not have been sent
or received within the reporting time period. I dont want to show these
PartNos in such a case.

I have developed a query that lists the totals for each Depot for each
PartNo, eg.

C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10
etc

SQL:
Select Sent.LocationID, Sent.PartNo, Sent.NumSent - Recd.NumRecd as NetTotal
from
  (Select L.LocationID, S.PartNo, Count(*) as NumSent
  from StockMovements S
  inner join Locations L on L.LocationID = S.LocationTo
  Where S.PartNo is not Null
  and PartNo <> ''
  Group By S.PartNo, L.LocationID) as Sent

join
  (Select L.LocationID, S.PartNo, Count(*) as NumRecd
  from StockMovements S
  inner join Locations L on L.LocationID = S.LocationFrom
  Where S.PartNo is not Null
  and PartNo <> ''
  Group By S.PartNo, L.LocationID) as Recd
on Sent.LocationID = Recd.LocationID and Sent.PartNo = Recd.PartNo

[Note: a 'Location' is a Customer Depot]

I'm not sure how to take this query on further to provide the end result.
I'm not sure if some quasi-temporary tables are needed to store some
intermediate data, or whether it can be acheived with other means (ie a
View).

Any suggestions?

Thanks in advance.

CJM

Trimmed DDL:

CREATE TABLE [dbo].[Locations] (
[LocationID] [int] IDENTITY (1, 1) NOT NULL ,
[LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[CustomerID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[SerialNo] [int] NOT NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PartMaster] (
[PartID] [int] IDENTITY (1, 1) NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartType] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[PartDesc] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

[Note: In case it's not obvious, PartMaster contains details on all
available PartNos]

If anything else is missing let me know...

First problem i am having in looking at this that no PartNo in
[StockMovements] so you query should be failing based on what you told
us your table creates are becuase you are pulling PartNo from
[StockMovements]

What is the field that holds the part number in [StockMovements]? Could
you change  [StockMovements] or is it used buy other items?

Sorry - Trimmed a bit too enthusiastically... PartNo *IS* in StockMovements

CREATE TABLE [dbo].[StockMovements] (
[StockMoveID] [int] IDENTITY (1, 1) NOT NULL ,
[Ref] [int] NULL ,
[SerialNo] [int] NOT NULL ,
[PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NULL ,
[LocationTo] [int] NOT NULL ,
[LocationFrom] [int] NOT NULL ,
) ON [PRIMARY]
GO

In the two examples that you gave:

Customer, Depot, PartNo1, PartNo7, PartNo28, Total
Cust1, C1D1, 1, null, -1, 0
Cust1, C1D2, null, -10, null, -10
Cust2, C2D1, 1, -2, -3, -4
Cust3 C3D1, null, null, 1, 1
Total, null, 2, -12, -3, -13


and

C1D1, PartNo1, 1
C1D1, PartNo28, -1
C1D2, PartNo7, -10


are different.

what are the heading in the report?
Is this how you want the data back. IE: Location ID, Part Number, Parts
Sent Out, Part Received Back, Total

Show quote Hide quote The first example is the output I want, the second is the data that results
in that output. The columns for that data are: DepotID, PartNo,
'NetTotalSent' (ie Total sent to that depot - Total received from that
depot)

You can see that for Depot C1D1, they have received 1 x PartNo1 and returned
1 x PartNo28. The net total is therefore 0. [See line 1 of the example
output]

The headings are on the first line: Customer, Depot, PartNo1, PartNo7,
PartNo28, Total. Depending on when you run the report you may have more or
less columns - it depends on which parts have been shipped/returned in that
time period.

No - see above.

I realise that this is quite confusing; and I'm trying to trade-off between
swamping you with too much superfluous detail, and not providing enough to
mek it clear. I hope this somewhat clairifies it.

Thanks

On Thu, 26 Jan 2006 16:32:21 -0000, CJM wrote:

(snip)
Show quoteHide quoteHi CJM,

First, I note that this query will only include parts that are in both
the Sent and the Recd derived table for the same LocationID. If there is
no row for PartNo '123' with LocationTo = 14, then the rows for PartNo
'123' and LocationFrom = 14 will be excluded from the results. If that's
not what you intended, you'll have to replace the JOIN with a FULL OUTER
JOIN and add a bunch of COALESCE functions on the first SELECT line.

Second, I wonder why the PartNo column is called a number but declared
as character, why it is not a key, and why it is used to reference parts
instead of the PartID column that IS a key.

Third, I recommend you to rewrite the query above to the (slightly)
simpler query below:

SELECT     L.LocationID, P.PartNo,
           COUNT(Sent.PartNo) - COUNT(Recd.PartNo) AS NetTotal
FROM       Locations AS L
CROSS JOIN PartMaster AS P
LEFT  JOIN StockMovements AS Sent
      ON   Sent.PartNo = P.PartNo
      AND  Sent.LocationTo = L.LocationID
LEFT  JOIN StockMovements AS Recd
      ON   Sent.PartNo = P.PartNo
      AND  Sent.LocationFrom = L.LocationID
WHERE      Sent.PartNo IS NOT NULL
OR         Recd.PartNo IS NOT NULL
GROUP BY   L.LocationID, P.PartNo

(untested - see www.aspfaq.com/5006 if you prefer a tested solution)

Now you also wanted the part numbers to be columns instead of rows. That
is usually called a crosstab or pivot - and the best way to do it is to
transform the table on the client side.

If you have to do it on the server side, AND you're on SQL Server 2005,
you can also check out the new PIVOT operator. For SQL Server 2000,
google this group for "CROSSTAB" to find some common kludges. Note
however that all these crosstabl techniques require you to know the
columns in advance - and if I understand your question correctly, you
want to be able to dynamically include or exclude columns, based on the
query results.

I know of no supported way to create a dynamic crosstab on the server.
You might want to try your luck with dynamic SQL - but ungh!! You might
also want to investigate if there are third-party products that can help
you out. Rac4SQL is often recommended in this group - though admittedly
always by the same poster, who probably works for the company that sells
Rac4SQL. I have no personal experience with either Rac4SQL or any other
third party product for crosstabbing.

TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
COALESCE functions that are needed? (And why?)

Historical reasons. Many of these PartNos pre-date all our computer systems,
and I suspect some pre-date civilisation itself (as do some of the people in
charge here!)

Some PartNo's are indeed integers but most are alpha numerical. The PartID
was introduced to be a numerical key, but is under-used. In retrospect, I
would have stuck with PartNo as the key.

It *is* simpler, and a lot neater too. I'll digest it (and probably use it
too - thanks)

Update: I've tried it and it works a treat (and the results are more like
what I was expecting)

Show quoteHide quoteYeah, I've done cross-tabs in SQL before, but as you say, the columns need
to be fixed (defined in advance). But the specification for my report *does*
ask for dynamically included and excluded columns... hmmmm....

I've heard of these dynamic SQL techniques, but never actually used them.
I've always found a way around them (ie not using them), but I thought one
of you guys might have a really simple and effective technique (wishful
thinking, it appears).

Again I've heard about Rac4SQL, but have no experience either.

If I could get ALL PartNos listed for all depots, it would be a big
resultset with lots of zeros/nulls, but it would probably do as a starting
point. I've tried adapting my query, linking it in to the PartMaster table,
but I can't get it to returns results for all part regardless of whether
they have been used in the reporting period. Any suggestions?

Thanks Hugo, you've been a great help.

Chris

On Fri, 27 Jan 2006 10:08:37 -0000, CJM wrote:

Show quoteHide quoteHi CJM,

Replace

Select Sent.LocationID, Sent.PartNo, (...)

with

Select COALESCE(Sent.LocationID, Recd.LocationID) AS LocationID,
       COALESCE(Sent.PartNo, Recd.PartNo) AS PartNo,
       (...)

This is needed because the FULL OUTER JOIN can cause either one (but not
both) of Sent.LocationID and Recd.LocationID to be NULL.

(snip)
For a static crosstab, you create a CASE expression inside an aggregate
for each part. You'll have to change the query each time a part is added
to your catalog.

Something like this:

SELECT     L.LocationID,
           COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) -
           COUNT(CASE WHEN P.Partno = 'Part1' THEN 1 END) AS Part1,
           COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) -
           COUNT(CASE WHEN P.Partno = 'Part2' THEN 1 END) AS Part2,
           ...
           COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) -
           COUNT(CASE WHEN P.Partno = 'Part9999' THEN 1 END) AS Part9999
FROM       Locations AS L
CROSS JOIN PartMaster AS P
LEFT  JOIN StockMovements AS Sent
      ON   Sent.PartNo = P.PartNo
      AND  Sent.LocationTo = L.LocationID
LEFT  JOIN StockMovements AS Recd
      ON   Sent.PartNo = P.PartNo
      AND  Sent.LocationFrom = L.LocationID
WHERE      Sent.PartNo IS NOT NULL
OR         Recd.PartNo IS NOT NULL
GROUP BY   L.LocationID

(untested, since you didn't post a repro script)

SQL Server 2005 introduces new techniques for crosstabbing, but I can't
offer any code as I haven't yet had a chance to play with them. If you
have SQL Server 2005, check PIVOT in Books Online.

Problem with DateAdd & GetDate()
Multiple-step OLE DB operation generated errors.
Application - document locking
Ok, time to start a war
Replacement of C code with C# code