August 31, 2009 at 8:03 am
Good morning All,
I need to combine the two queries but there are certain records I need to display only once (aggregate). For example, defects that repeat over a date range, I need to show the once but give a total. Now, I need to add lower query to the top and aggregate.
SELECT tlkp_Disposition.Disposition_Description, count(*) AS Disposition_Count, tlkp_Defects.Defect_Title,
SUM(case tlkp_Disposition.Disposition_ID when 1 then tlkp_Defects.Defect_ID else 0 end) FixedOnLine,
SUM(case tlkp_Disposition.Disposition_ID when 2 then tlkp_Defects.Defect_ID else 0 end) Repair,
SUM(case tlkp_Disposition.Disposition_ID when 3 then tlkp_Defects.Defect_ID else 0 end) Scrap
FROM
tbl_Assembly_Holds
join tlkp_Disposition on tbl_Assembly_Holds.Disposition_ID = tlkp_Disposition.Disposition_ID
join tbl_Assembly_Hold_Defects on tbl_Assembly_Hold_Defects.TagNumber = tbl_Assembly_Holds.TagNumber
join tlkp_Defects on tbl_Assembly_Hold_Defects.Defect_ID = tlkp_Defects.Defect_ID
WHERE
tbl_Assembly_Holds.Record_Date BETWEEN '#FORM.dateFrom#' AND '#FORM.dateTo#'
GROUP BY
tlkp_Defects.Defect_Title, tlkp_Disposition.Disposition_Description
SELECT tlkp_Item.Model, Count(*) Model_Count
FROM tbl_Assembly_Production
JOIN tlkp_Item ON tbl_Assembly_Production.Item = tlkp_Item.Item
JOIN tbl_Assembly_Holds ON tbl_Assembly_Production.WorkOrder = tbl_Assembly_Holds.WorkOrder
GROUp by tbl_Assembly_Production.Item, tlkp_Item.Model
Thanks in Advance
August 31, 2009 at 9:52 am
I have to re-think this.
1. In my SUM (CASE) the values are being multiplied i.e.,
disposition_ID(sum)*defect_ID
so, if disposition_ID (1) appears 72 times for defect_ID (19) then my results are 1368 and not 72 for 19.
The whole thing is incorrect.
August 31, 2009 at 9:57 am
How about providing us with your table structure, sample data (in a readily consumable format) and an example of the output your would want to see based off of your sample data. Here is a thread that shows how to correctly post a question with those components.
http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 31, 2009 at 11:34 am
Thanks. That's definitely easier said than done. I will try to post sometime today. S2K5.
August 31, 2009 at 12:09 pm
kabaari (8/31/2009)
Thanks. That's definitely easier said than done. I will try to post sometime today. S2K5.
Maybe, but with what you are asking for, someone will have to mock up an example to give you an answer so that someone should be you if you want help. Many of the frequent posters out here on SSC go over and above to help people out which many times involves creating our own sample data and schema to 'fix' someones problems. The best way to get help is to provide all of the information that someone would need to help you. You know, help us to help you.
You should be able to mock up something and give us an example of the result set you want in 10-15 minutes. I'm not asking for a full schema script for your DB and bcp imports of your data. Just an example that illustrates your problem.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply