Trying to get data by distinct field values

[gadall]

I have a table that has job data, Account_Num, Work_Order_Num, Date, Short_Desc, TechID, QCID, QCPassFail.

In this table there may be multiple records with the same Account_Num and Work_Order_Num, however when it comes time to check the QCPassFail, I need to only count 1 for each unique Account_Num/Work_Order_Num pair.

My problem comes in that I need more than just those 2 fields for reporting purposes so a SELECT DISTINCT on those two fields only won't work.

I've tried using a subquery with WHERE x IN, and I've had no luck trying to use an inner join using the same table.

[Delerna]

So for the accounts that have more than 1 record, what are the rules that you want to apply in order to work out which one of them you want the query to return.
Or are you after something like this

SELECT a.Account_Num, Ctn_Account,
       a.Work_Order_Num, Cnt_Order,
       Date, Short_Desc, TechID, QCID, QCPassFail
FROM YourTable a
left join
(   SELECT Account_Num, Work_Order_Num,
           count(Account_Num) as Ctn_Account, 
           count(Work_Order_Num) as Cnt_Order
    FROM YourTable
) b on a.Account_Num=b.Account_Num 
   and a.Work_Order_Num=b.Work_Order_Num
 

[gadall]

Account_Num  Work_Order_Num   Schedule_Date    Short_Desc   TechID  QCID QCPassFail
20661406     25853100           2007-10-12       VIDEO DISC  73002   73011  1
20661406     25853100           2007-10-12       CHSI DISC     73002   73011  1

For the purposes of this report, I only need 1 of these records, which one, is irrelevant.

[ck9663]

What kind of report are you generating? If just a count or summary, you don't need the entire record, which could be easier to be implemented in a query.

-- CK

[gadall]

What kind of report are you generating? If just a count or summary, you don't need the entire record, which could be easier to be implemented in a query.

-- CK

I need all the information shown in the records (the actual records have a lot more information) This information is pulled into an excel spreadsheet for a detailed report (the count is done by formula in excel).

[Delerna]

Not sure if this suits all of your data but it works for the data you provide

SELECT Account_Num,
      Work_Order_Num,
      count(Account_Num) as Account_Num,
      count(Work_Order_Num as Work_Order_Num,
      Schedule_Date,
      first(Short_Desc)  as Short_Desc,
      TechID,
      QCID,
      QCPassFail
FROM YourTable
GROUP BY  Account_Num,Work_Order_Num,Schedule_Date,TechID,QCID,QCPassFail
 

Or this may suit better

SELECT Account_Num,
      Work_Order_Num,
      count(Account_Num) as Account_Num,
      count(Work_Order_Num as Work_Order_Num,
      first(Schedule_Date) as Schedule_Date,
      first(Short_Desc)  as Short_Desc,
      first(TechID) as TechID,
      first(QCID) as QCID,
      first(QCPassFail) as QCPassFail
FROM YourTable
GROUP BY  Account_Num,Work_Order_Num
 

[gadall]

Working solution:

 SELECT DISTINCT Account_Num,
 
            Work_Order_Num,
 
            Schedule_Date,
 
            Assigned_Installer,
 
            MIN(Short_Description) as Short_Desc,
 
            QCID,
 
            QCPassFail
 
      FROM QCData 
 
      WHERE QCPassFail IS NOT NULL AND Schedule_Date >= '3/1/2008' AND Schedule_Date <= '3/31/2008'
 
      GROUP BY Schedule_Date, Account_Num, Work_Order_Num, Assigned_Installer, QCID, QCPassFail