Nightmare Query

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...
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]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?|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138296034.089998.119680@.g43g2000cwa.googlegroups.com...
> 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|||On Thu, 26 Jan 2006 16:32:21 -0000, CJM wrote:
(snip)
>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 NetTota
l
> 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.
Hi 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.
Hugo Kornelis, SQL Server MVP|||"Amiller" <amiller75@.gmail.com> wrote in message
news:1138297710.003578.172650@.g14g2000cwa.googlegroups.com...
> 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.
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]

> what are the heading in the report?
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.

> Is this how you want the data back. IE: Location ID, Part Number, Parts
> Sent Out, Part Received Back, Total
>
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|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...

> Hi 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.
>
TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
COALESCE functions that are needed? (And why?)

> 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.
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.

> Third, I recommend you to rewrite the query above to the (slightly)
> simpler query below:
>
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)

> 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.
>
Yeah, 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 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.
>
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?

> --
> Hugo Kornelis, SQL Server MVP
Thanks Hugo, you've been a great help.
Chris|||On Fri, 27 Jan 2006 10:08:37 -0000, CJM wrote:

>"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info> wrote in message
> news:g6fit11p66oq34h341b9o02rv8v2a0ah3b@.
4ax.com...
>
>TBH, I thought I had used a FULL JOIN - oops! Well spotted. What are the
>COALESCE functions that are needed? (And why?)
Hi 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)
>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?
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.
Hugo Kornelis, SQL Server MVP