How to write this query?

[Andy_L_J]

I have the following Table Structure in a SQL 2008 database:

ProductCategory
ProdCat_ID Int PK
Name VarChar(30)

Product
Product_ID Int PK
ProdCat_ID Int FK
...

WorkOrder
WO_ID Int PK
Product_ID Int FK
...

Production
Prodn_ID Int PK
WO_ID Int FK
...

MaterialTransaction
MatTrans_ID Int PK,
TransDate Date,
Prodn_ID FK,
Qty Decimal(18,7)
...

I want to retrieve Qty data for specified Category Names on each date in the query. If I do the following:

SELECT Distinct(mt.TransDate) As [Date],
CASE WHEN pc.Name IN('Hadware','Custom')
THEN SUM(mt.Qty)
ELSE 0 END AS 'Hardware',
CASE WHEN pc.Name LIKE 'DS %'
THEN SUM(mt.Qty)
ELSE 0 End As 'DataStrip'

From MaterialTransaction mt
JOIN Production pr
ON mt.Prodn_ID = pr.Prodn_ID
JOIN WorkOrder wo
ON pr.WO_ID = wo.WO_ID
JOIN Product p
ON wo.Product_ID = p.ID
JOIN ProductCategory pc
ON p.ProdCat_ID = pc.ID
WHERE Date Between '11/24/2009' And '11/25/2009'

GROUP BY mt.TransDate, pc.Name

I end up with results similar to:

OUTPUT:

Date Hardware DataStrip
2009-11-24 00:00:00 0.0000000000 560.4080000
2009-11-24 00:00:00 2786.3100000 0.0000000
2009-11-25 00:00:00 0.0000000 125.5415000

What I require however is:

Date Hardware DataStrip
2009-11-24 00:00:00 2786.310000000 560.4080000
2009-11-25 00:00:00 0.0000000 125.4150000

Any pointers are most appreciated.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Andy_L_J]

I have used the following correlated query:

SELECT mt.TransDate, CASE ISNULL(h.Hardware,0)
WHEN 0 Then 0
ELSE h.Hardware
End As Hardware,
CASE ISNULL(d.Datastrip, 0)
WHEN 0 Then 0
ELSE d.Datastrip
END AS Datastrip
FROM MaterialTransaction mt
LEFT JOIN (SELECT mtt.TransDate,SUM(mtt.Qty) As Hardware
FROM MaterialTransaction mtt
JOIN Production prr
ON mtt.Prodn_ID = prr.Prodn_ID
JOIN WorkOrder woo
ON prr.WO_ID = woo.WO_ID
JOIN Product pp
ON woo.Product_ID = pp.ID
JOIN ProductCategory pcc
ON pp.ProdCat_ID = pcc.ID
WHERE pcc.Name In ('Hardware','Custom', 'Cellular')
GROUP BY mtt.TransDate) h
ON mt.TransDate = h.TransDate
LEFT JOIN (SELECT mtt.TransDate, SUM(mtt.Qty) As Datastrip
FROM MaterialTransaction mtt
JOIN Production prr
ON mtt.Prodn_ID = prr.Prodn_ID
JOIN WorkOrder woo
ON prr.WO_ID = woo.WO_ID
JOIN Product pp
ON woo.Product_ID = pp.ID
JOIN ProductCategory pcc
ON pp.ProdCat_ID = pcc.ID
WHERE pcc.Name In ('DS PVC','DS PETG')
GROUP BY mtt.TransDate) d
ON h.TransDate = d.TransDate
WHERE mt.TransDate BETWEEN '11/24/2009' And '11/25/2009'
GROUP BY mt.TransDate, h.Hardware, d.Datastrip

It works as required, but boy is it ugly. Can this be optimized further? The final query (with 8 sub-queries) was running rather slowly - ~8 secs to retreive 30 rows from around 4000. An nonclustered index on MaterialTransaction.TransDate sped this up to ~1 sec

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

modified on Saturday, February 27, 2010 11:33 PM

[Luc Pattyn]

AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names, so ON prr.WO_ID = woo.WO_ID could be omitted, and others after a field name change too. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

---

[Mycroft Holmes]

Luc Pattyn wrote:

AFAIK joins don't need explicit "ON" clauses if the corresponding fields carry the same names

Erk! what happens if you have fields of the same name and you don't want them joined? Or am I misunderstanding your point.

Never underestimate the power of human stupidity RAH

[Mycroft Holmes]

Andy_L_J wrote:

Can this be optimized further?

Probably but not by the query structure, I have these regularly, as long as it is a transactional type system (as opposed to a batch system)and you are not dealing with 1000s of results there should not be too much pain. Ugly, Pfft, its less than 40 line, has only 2 sub selects, doesn't qualify for ugly.

Never underestimate the power of human stupidity RAH

[Andy_L_J]

Thanks Mycroft. :thumbsup: The final query has 8 subs and does the job. Initially a little slowly until I placed an index on the TransDate field. :-O

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[i j russell]

SELECT mt.TransDate,
SUM(CASE WHEN pc.Name IN('Hadware','Custom')
THEN mt.Qty
ELSE 0 END) AS 'Hardware',
SUM(CASE WHEN pc.Name LIKE 'DS %'
THEN mt.Qty
ELSE 0 End) As 'DataStrip'
From MaterialTransaction mt
JOIN Production pr
ON mt.Prodn_ID = pr.Prodn_ID
JOIN WorkOrder wo
ON pr.WO_ID = wo.WO_ID
JOIN Product p
ON wo.Product_ID = p.ID
JOIN ProductCategory pc
ON p.ProdCat_ID = pc.ID
WHERE Date Between '11/24/2009' And '11/25/2009'
GROUP BY mt.TransDate

[Andy_L_J]

Cheers - Nice:thumbsup: I am testing for speed against the other query.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife