August 27, 2009 at 10:00 am
Good afternoon all and Thanks in advance! I would like to run the below code but I need the defects to show once and not repeat.
1. Should I write another query?
2. Is there a line I could add to my current script?
3. Should I write a subquery?
SELECT tlkp_Disposition.Disposition_Description, count(*) AS Disposition_Count, tlkp_Defects.Defect_Title, Count(*) as Defect_Count,
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
August 27, 2009 at 10:31 am
Well, to really help we will also need the DDL (create table statements) for the tables in the query, sample data for the tables that reflect the problem you are experiencing, and the expected results your are looking for.
Please read the first article in my signature block about asking for assistance to see how you should post this additional information.
August 27, 2009 at 12:28 pm
Lynn,
Here is the form.
FROM:
TO:
I would like to drill down my search but, I need to add more tables. The tables exist in SQL Server 2005. I am querying them. I am also linking to AS400 but that's another post.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply