January 23, 2009 at 9:27 am
Forgive me if I have posted to the wrong forum but I am unclear whether this is a T-SQL or Reporting post.
I have written a query that returns the required data:
WITH ProductComponents AS
(
SELECT
p.Product AS ProductBigInt,
p.ProductId AS ParentProductId,
p.ProductDescription AS ParentDescription,
c.ProductId AS ComponentProductId,
c.ProductDescription AS ComponentDescription,
s.Quantity AS ComponentQuantity
FROM Structures AS s
INNER JOIN Products AS p ON s.Product = p.Product
INNER JOIN Products AS c ON s.Component = c.Product
),
SalesOrderDetails AS
(
SELECT
so.SalesOrderId,
soi.Product AS SOIProductBigInt,
CASE soi.ItemType
WHEN 'P' THEN p.ProductId
WHEN 'S' THEN s.Sundryid
WHEN 'F' THEN f.FreightMethodId
ELSE soi.FreeTextItem
END AS ProductCode,
CASE soi.ItemType
WHEN 'P' THEN p.ProductDescription
WHEN 'S' THEN s.SundryDescription
WHEN 'F' THEN f.FreightMethodDescription
ELSE soi.ItemDescription
END AS ProductDesc,
soi.Quantity AS SalesDetailQuantity,
c.CustomerName,
soi.DueDate
FROM dbo.SalesOrderItems AS soi
INNER JOIN SalesOrders AS so ON soi.SalesOrder = so.SalesOrder
INNER JOIN Customers AS c ON so.Customer = c.Customer
LEFT OUTER JOIN Products p ON soi.Product = p.Product
LEFT OUTER JOIN Sundries s ON soi.Sundry = s.Sundry
LEFT OUTER JOIN FreightMethods f ON soi.FreightMethod = f.FreightMethod
)
SELECT SalesOrderId,CustomerName,ProductCode,ProductDesc,SalesDetailQuantity,ComponentProductId,ComponentDescription,ComponentQuantity
FROM SalesOrderDetails
LEFT OUTER JOIN ProductComponents ON SalesOrderDetails.SOIProductBigInt = ProductComponents.ProductBigInt
WHERE SalesOrderId = @SalesOrderId
I then developed a report to display the data. However I am unable to get the desired report format.
I have tried many variations on grouping but no joy.
I have attached sample data regards the rows the query returns + .jpeg on how the report should look.
My question is can the query be modified to provide the desired report format?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 24, 2009 at 3:36 am
I have resolved (I think) by adding additional grouping on the report.
grpQty (by quantity,ProductCode)
If anyone has an opinion on the code etc please feel free to educate me!
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 24, 2009 at 9:49 am
Phil,
Your providing of sample data is much appreciated, as are the expected results from your requests. However, if you find that you're not getting a lot of responses to your requests, you might want to consider making your sample data insert statements instead of excel files. An insert with the table declaration takes seconds to work with, while excel sheets take significantly longer. Obviously some people do not mind and will work with your data, at least sometimes, just as some people work with sample data copy/pasted directly into the thread. I just think it lowers the number of people willing to work on your question. This is just my opinion, so take it as you will, but I find table insert scripts and sample data easier to work with.
January 24, 2009 at 10:06 am
Thanks Seth. Quite new to SQL. Should I simply script the tables that appear in any query (strip out constraints)? Regards the INSERT I have only ever done INSERT FROM TABLE A SELECT VALUES FROM TABLE B. Based on my excel data what should I be doing?
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 24, 2009 at 10:54 am
The article in my signature describes how to set this up, but let's say you have tableA with one column Aval.
Your table / sample data script would look like this:
DECLARE @TableA TABLE( -- Table Declaration
Aval varchar(25))
INSERT INTO @TABLEA(Aval) -- Sample Data
SELECT 'SomeData' UNION ALL
SELECT 'SomeMore' UNION ALL
SELECT 'Something' UNION ALL
SELECT 'Blah' UNION ALL
SELECT 'BLAHBlah' UNION ALL
SELECT 'Last'
You can copy and paste that directly into query analyzer, click run and you're done. You've got a table set up with data populated in it that can be worked with immediately.
Your expected results are fine, we just need to be able to see what you want, not work with that data, so any format of that works.
January 25, 2009 at 2:42 am
Thanks Seth,I will read your post.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 25, 2009 at 4:20 am
Hi Phil
Looking at your query, I reckon it can be simplified quite a bit - which would usually help with aggregation. Let's have a try.
Firstly, changing it so that the CTE SalesOrderDetails is a standard query with the CTE ProductComponents configured as a derived table gives us this:
SELECT
so.SalesOrderId,
soi.Product AS SOIProductBigInt,
CASE soi.ItemType
WHEN 'P' THEN p.ProductId
WHEN 'S' THEN s.Sundryid
WHEN 'F' THEN f.FreightMethodId
ELSE soi.FreeTextItem
END AS ProductCode,
CASE soi.ItemType
WHEN 'P' THEN p.ProductDescription
WHEN 'S' THEN s.SundryDescription
WHEN 'F' THEN f.FreightMethodDescription
ELSE soi.ItemDescription
END AS ProductDesc,
soi.Quantity AS SalesDetailQuantity,
c.CustomerName,
soi.DueDate,
pc.ComponentProductId,
pc.ComponentDescription,
pc.ComponentQuantity
FROM dbo.SalesOrderItems AS soi
INNER JOIN SalesOrders AS so ON soi.SalesOrder = so.SalesOrder
INNER JOIN Customers AS c ON so.Customer = c.Customer
LEFT OUTER JOIN Products p ON soi.Product = p.Product
LEFT OUTER JOIN Sundries s ON soi.Sundry = s.Sundry
LEFT OUTER JOIN FreightMethods f ON soi.FreightMethod = f.FreightMethod
LEFT OUTER JOIN (
SELECT
p.Product AS ProductBigInt,
p.ProductId AS ParentProductId,
p.ProductDescription AS ParentDescription,
c.ProductId AS ComponentProductId,
c.ProductDescription AS ComponentDescription,
s.Quantity AS ComponentQuantity
FROM Structures AS s
INNER JOIN Products AS p ON s.Product = p.Product
INNER JOIN Products AS c ON s.Component = c.Product) pc ON soi.Product = pc.ProductBigInt
WHERE so.SalesOrderId = @SalesOrderId
Now, if you look at how the derived table is joined to the main part of the query -
it's soi.Product = p.Product (pc.ProductBigInt), and it's a left join.
Well, you already have this exact same join in the main part of the query, so perhaps we can make use of it:
SELECT
so.SalesOrderId,
c.CustomerName,
CASE soi.ItemType
WHEN 'P' THEN p.ProductId
WHEN 'S' THEN s.Sundryid
WHEN 'F' THEN f.FreightMethodId
ELSE soi.FreeTextItem
END AS ProductCode,
CASE soi.ItemType
WHEN 'P' THEN p.ProductDescription
WHEN 'S' THEN s.SundryDescription
WHEN 'F' THEN f.FreightMethodDescription
ELSE soi.ItemDescription
END AS ProductDesc,
soi.Quantity AS SalesDetailQuantity,
c.ProductId AS ComponentProductId,
c.ProductDescription AS ComponentDescription,
s.Quantity AS ComponentQuantity
FROM dbo.SalesOrderItems AS soi
INNER JOIN SalesOrders AS so ON soi.SalesOrder = so.SalesOrder
INNER JOIN Customers AS c ON so.Customer = c.Customer
LEFT OUTER JOIN Sundries s ON soi.Sundry = s.Sundry
LEFT OUTER JOIN FreightMethods f ON soi.FreightMethod = f.FreightMethod
LEFT OUTER JOIN Products p ON soi.Product = p.Product
INNER JOIN Structures AS s ON s.Product = p.Product
INNER JOIN Products AS pc ON s.Component = pc.Product
WHERE so.SalesOrderId = @SalesOrderId
This is a little easier to work with. For testing your aggregation, you could set the whole statement up as a CTE and select from that,
or you could do an "onion select" i.e.
SELECT ...
FROM
(SELECT...(main query here)
) d
GROUP BY...
I hope this works because it's untested, and I hope also that it gives you a few hints and ideas. It's unusual for a query restructured
like this to give a huge performance hike. The purpose is really to make it more readable and easier to maintain,
with a modest performance hike as icing on the cake.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2009 at 7:52 am
Chris many thanks for your efforts, I will wrap my brain around your example and attempt to adopt this approach.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 11, 2009 at 6:12 am
Apologies for previous script it was late and I was tired.
Please find attached new script + sample data which I have tested.
I would like the query to return the Average Build Cost per Product (it would be good to be able to show by Year / Month) i.e.
2009 - Jan - Units Built x - AvgMaterialCost = x AvgLabourCost = x AverageTotalCost = x
2009 - Feb - Units Built x - AvgMaterialCost = x AvgLabourCost = x AverageTotalCost = x
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 11, 2009 at 7:48 am
Hi Phil,
even though we change the thread, the subject is still the same... (for those that might want to look into the "old" story: http://www.sqlservercentral.com/Forums/FindPost732338.aspx
With one BIG difference: your sample data made it easy to work with!
Would the following help you?
SELECT
DATEPART(YEAR,I.EffectiveDate) AS Production_Year,
DATENAME(MONTH,I.EffectiveDate) AS Production_Month,
COUNT (DISTINCT P.ProductId) AS Units_Built,
AVG(I.MaterialValue) AS AvgMaterialCost,
AVG(I.LabourValue) AS AvgLabourCost,
AVG(I.TotalValue) AS AverageTotalCost
FROM inventory I
INNER JOIN ManufacturingOrders M ON I.ManufacturingOrder = M.ManufacturingOrder
AND I.Product = M.Product
INNER JOIN Products P ON M.Product = P.Product
GROUP BY DATEPART(YEAR,I.EffectiveDate),DATEname(MONTH,I.EffectiveDate), CONVERT(CHAR(6),I.EffectiveDate,112)
ORDER BY CONVERT(CHAR(6),I.EffectiveDate,112)
/* Result set
Production_YearProduction_MonthUnits_BuiltAvgMaterialCostAvgLabourCostAverageTotalCost
2009January170.000.0070.00
2009February25856.22250.667851.94
2009March213904.795594.7316984.625
2009May113902.15587.282816055.2271
2009June113916.05588.1416400.27
*/
Edit: Note: I don't know how you'd count the Units_Built so it might be required to change that part...
June 11, 2009 at 9:38 am
Thanks Lutz, I will test. Did I post incorrectly or was it moved?
Boy am I having a bad day! I can update the correct post if so then post my results after testing.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 11, 2009 at 11:18 am
The post hasn't been moved.
You might have picked the wrong post when you replied. Never mind. I'm going to add a reference from the other post to this one.
The only thing we need to make sure that we stay on one thread from now on - either going back to the original one (recommended) or staying here. We shouldn't jump between both...
Regarding the subject:
As you've probably noticed the response was both, faster and more code related than before.
It really helps to provide code in ready to use format...
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply