July 28, 2006 at 9:54 am
Hi,
I'm having issue of developing a SQL query to meet the following requirements.
I have two tables PRODUCT_T and AUDIT_T
PRODUCT_T table data
prod_num
100
101
102
103
AUDIT_T table data
id prod_num status last_updated_by last_updated_date
1 100 ACCEPT tom 7/26/2006 3:14:31 PM
2 101 ACCEPT tom 7/26/2006 3:15:08 PM
3 101 UNACCEPT tom 7/26/2006 5:16:11 PM
The query needs to return a total count of products (in PRODUCT_T table) which have not been ACCEPT'ed in the AUDIT_T table. It also needs to add one count for those products whose status is not ACCEPTE'ed with the last timestamped.
In the example above, the correct query should return a total count of 3 (exclude product number 100 but include product number 101 since its last updated record was UNACCEPT'ed.
Here's my current query which doesn't return a correct result because it doesn't not know how to handle product 101 which has two records in AUDIT_T table and its last updated record has a status = UNACCEPT.
SELECT count(1) AS value
FROM product_t
WHERE prod_num NOT IN (SELECT prod_num
FROM audit_t
WHERE status != 'ACCEPT')
Thanks in advance for your help!
Tuan
July 28, 2006 at 10:22 am
SELECT COUNT(*)
FROM Product_T P
LEFT JOIN (SELECT A1.ID,
A1.Prod_Num,
A1.Status
FROM Audit_T A1
INNER JOIN (SELECT Prod_num,
MAX(Last_Updated_Date) as Last_Updated_Date
FROM Audit_T
GROUP BY Prod_Num) A2
ON A1.Prod_Num = A2.Prod_Num and A1.Last_Updated_Date = A2.Last_Updated_Date
) derived_table
ON P.Prod_Num = derived_table.Prod_Num
WHERE COALESCE(derived_table.Status,'UNACCPT') <> 'ACCEPT'
July 28, 2006 at 10:52 am
Hi John -
Thanks for your quick help! I ran your query and it returned a correct result. One more request related to the same query. If in the AUDIT_T table there is a new record whose prod_num = 101 and status = 'COMPLETE', how do I correct your query so that it also handle a different status. In this case it should NOT add 101 record into the total count. Just only add to the count, for example, for the last updated record timestamp for 101 product which has a status = 'UNACCEPT'
In this case, should I add
AND COALESCE(derived_table.wf_action_cd,'UNA') <> 'COM' in the WHERE clause?
SELECT COUNT(*)
FROM Product_T P
LEFT JOIN (SELECT A1.ID,
A1.Prod_Num,
A1.Status
FROM Audit_T A1
INNER JOIN (SELECT Prod_num,
MAX(Last_Updated_Date) as Last_Updated_Date
FROM Audit_T
GROUP BY Prod_Num) A2
ON A1.Prod_Num = A2.Prod_Num and A1.Last_Updated_Date = A2.Last_Updated_Date
) derived_table
ON P.Prod_Num = derived_table.Prod_Num
WHERE COALESCE(derived_table.Status,'UNACCEPT') <> 'ACCEPT'
AND COALESCE(derived_table.Status,'UNACCEPT') <> 'COMPLETE'
I ran the updated query and it returned a correct result. Another question is my AUDIT_T will have a large records since each product record can have up to 5 different workflow status in the AUDIT_T. Do you know whether this could create a performance issue?
Thanks again,
Tuan
July 28, 2006 at 10:59 am
Just change the WHERE clause to:
WHERE COALESCE(derived_table.Status,'UNACCPT') = 'UNACCEPT'
July 28, 2006 at 11:19 am
Hi John -
Again, thanks very much for helping me with this query.
Tuan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply