By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,362 Members | 1,344 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,362 IT Pros & Developers. It's quick & easy.

Trying to get data by distinct field values

P: 7
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.
Apr 9 '08 #1
Share this Question
Share on Google+
6 Replies


Delerna
Expert 100+
P: 1,134
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
Expand|Select|Wrap|Line Numbers
  1. SELECT a.Account_Num, Ctn_Account,
  2.        a.Work_Order_Num, Cnt_Order,
  3.        Date, Short_Desc, TechID, QCID, QCPassFail
  4. FROM YourTable a
  5. left join
  6. (   SELECT Account_Num, Work_Order_Num,
  7.            count(Account_Num) as Ctn_Account, 
  8.            count(Work_Order_Num) as Cnt_Order
  9.     FROM YourTable
  10. ) b on a.Account_Num=b.Account_Num 
  11.    and a.Work_Order_Num=b.Work_Order_Num
  12.  
Apr 9 '08 #2

P: 7
Expand|Select|Wrap|Line Numbers
  1. Account_Num  Work_Order_Num   Schedule_Date    Short_Desc   TechID  QCID QCPassFail
  2. 20661406     25853100           2007-10-12       VIDEO DISC  73002   73011  1
  3. 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.
Apr 9 '08 #3

ck9663
Expert 2.5K+
P: 2,878
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
Apr 9 '08 #4

P: 7
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).
Apr 9 '08 #5

Delerna
Expert 100+
P: 1,134
Not sure if this suits all of your data but it works for the data you provide

Expand|Select|Wrap|Line Numbers
  1. SELECT Account_Num,
  2.       Work_Order_Num,
  3.       count(Account_Num) as Account_Num,
  4.       count(Work_Order_Num as Work_Order_Num,
  5.       Schedule_Date,
  6.       first(Short_Desc)  as Short_Desc,
  7.       TechID,
  8.       QCID,
  9.       QCPassFail
  10. FROM YourTable
  11. GROUP BY  Account_Num,Work_Order_Num,Schedule_Date,TechID,QCID,QCPassFail
  12.  
Or this may suit better
Expand|Select|Wrap|Line Numbers
  1. SELECT Account_Num,
  2.       Work_Order_Num,
  3.       count(Account_Num) as Account_Num,
  4.       count(Work_Order_Num as Work_Order_Num,
  5.       first(Schedule_Date) as Schedule_Date,
  6.       first(Short_Desc)  as Short_Desc,
  7.       first(TechID) as TechID,
  8.       first(QCID) as QCID,
  9.       first(QCPassFail) as QCPassFail
  10. FROM YourTable
  11. GROUP BY  Account_Num,Work_Order_Num
  12.  
Apr 9 '08 #6

P: 7
Working solution:

Expand|Select|Wrap|Line Numbers
  1.  SELECT DISTINCT Account_Num,
  2.  
  3.             Work_Order_Num,
  4.  
  5.             Schedule_Date,
  6.  
  7.             Assigned_Installer,
  8.  
  9.             MIN(Short_Description) as Short_Desc,
  10.  
  11.             QCID,
  12.  
  13.             QCPassFail
  14.  
  15.       FROM QCData 
  16.  
  17.       WHERE QCPassFail IS NOT NULL AND Schedule_Date >= '3/1/2008' AND Schedule_Date <= '3/31/2008'
  18.  
  19.       GROUP BY Schedule_Date, Account_Num, Work_Order_Num, Assigned_Installer, QCID, QCPassFail
Apr 10 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.