Need help with a query statement

Question

Post reply

Need help with a query statement

Tuan H. Le

SSC Eights!

Points: 806
More actions
September 7, 2006 at 10:32 am

#114781

Hi,
I appreciate your help for the following query.
I have two tables PRODUCT and AUDIT
TICKET table data
ticket_id   created_date
100         9/1/2006
101         9/1/2006
102         9/2/2006
103         9/3/2006
AUDIT table data
id  ticket_id   status     last_updated_by  last_updated_date
1   100         PENDING    tom              7/26/2006 3:14:31 PM
2   101         PENDING    tom              7/26/2006 3:14.31 PM
3   103         PENDING    tom              7/26/2006 3:14:31 PM
4   102         EXCLUDED   tom              7/27/2006 4:00:00 PM
5   101         ACCEPTED   tom              7/27/2006 3:15:08 PM
6   101         UNACCEPTED tom              7/27/2006 5:16:11 PM
7   103         ACCEPTED   tom              7/28/2006 6:00:00 PM
The query needs to return those ticket numbers which are last updated as PENDING or UNACCEPTED status (excluding those records that were last updated as EXCLUDED or ACCEPTED) and the ticket created date was between 9/1 - 9/3
With the test data above, it should return two PENDING tickets 100 and 101 (the last updated workflow for ticket 101 was UNACCEPTED so it's the same as in PENDING status)
Here's my current query which doesn't return a correct result
       SELECT t.ticket_id
                  t.created_date
        FROM ticket t
        LEFT JOIN (SELECT a1.ticket_id,
                                   a1.status,
                                   a1.last_updated_date
                       FROM audit a1
                       INNER JOIN (SELECT ticket_id,
                                        MAX(last_updated_date) AS last_updated_date
                                        FROM audit
                                       GROUP BY ticket_id) a2
                       ON a1.ticket_id = a2.ticket_id
                       AND a1.last_updated_date = a2.last_updated_date
                  ) derived_table
            ON t.ticket_id = derived_table.ticket_id
        WHERE derived_table.action IN ('UNACCETED', 'PENDING')
        AND t.created_date BETWEEN DATEADD(dd, 0, '09/01/2006') AND DATEADD(dd, 1, '09/03/2006')
        ORDER BY t.created_date ASC
Thanks in advance for your help.
Tuan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 1

try this

SELECT

t.ticket_id,

t

.created_date

FROM ticket t

LEFT JOIN (SELECT a1.ticket_id,

a1

.status,

a1

.last_updated_date

FROM audit a1

INNER JOIN (SELECT ticket_id,

MAX(last_updated_date) AS last_updated_date

FROM audit

GROUP BY ticket_id) a2

ON a1.ticket_id = a2.ticket_id

AND a1.last_updated_date = a2.last_updated_date

) derived_table

ON t.ticket_id = derived_table.ticket_id

WHERE derived_table.status IN ('UNACCEPTED', 'PENDING')

AND t.created_date BETWEEN cast(convert(char(10),'08/31/2006',108) as smalldatetime) AND cast(convert(char(10),'09/04/2006',108) as smalldatetime)

ORDER BY t.created_date ASC

Tuan H. Le SSC Eights! Points: 806 More actions · Answer 2

Hi Gopi -

Thanks for your response. It seems to me that the created_date in the WHERE clause was not the issue. If I removed the ticket.created_date in the WHERE clause which I wanted to return all the tickets that matched the audit condition, the original issue still existed.

What I need is to return all the unique ticket numbers in the AUDIT table which have the last updated date time and status = PENDING or UNACCEPTED.

Thanks,

Tuan

Ken McKelvey SSCoach Points: 18842 More actions · Answer 3

There is no need for an outer join here:

SELECT T.*

FROM dbo.Ticket T

JOIN dbo.Audit A ON T.ticket_id = A.ticket_id

JOIN ( SELECT A1.ticket_id, MAX(A1.last_updated_date) AS last_updated_date

FROM dbo.Audit A1

GROUP BY A1.ticket_id) D

-- filter audit so only max(last_updated_date) shows

on A.ticket_id = D.ticket_id and A.last_updated_date = D.last_updated_date

WHERE A.status in ('UNACCEPTED', 'PENDING')

and T.created_date between '20060901' and '20060903'

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

You can also try this:

select

t.ticket_id,

t.created_date

from

TICKET t

where

exists ( select

1

from

AUDIT a

where

a.ticket_id = t.ticket_id

and a.status in ('PENDING','UNACCEPTED')

and a.last_updated_date = ( select

max(b.last_updated_date)

from

AUDIT b

where

a.ticket_id = b.ticket_id)

)

and t.created_date between DATEADD(dd, 0, '09/01/2006') AND DATEADD(dd, 1, '09/03/2006')

order by

t.created_date asc

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 5

Hope this helps.

Select audit.* from audit INNER JOIN

(

Select ticket_id,max(last_updated_date) as MaxUpdateDate FROM audit

group by ticket_id ) MaxAudit on audit.ticket_id = MaxAudit.ticket_id

and audit.last_updated_date = MaxAudit.MaxUpdateDate

where audit.Status not in ('EXCLUDED','ACCEPTED')

order by 1

Keep in Mind that if there are 2 status change at the "same time" they will appear twice in the result and if you need 1 record then modify the query to use the Min(ID) or Max(ID) depending upon your needs.

Thanks

Sreejith

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 6

I forgot to add the where clause on created_date

so here it is:

Select audit.* ,ticket.* from audit INNER JOIN

(

Select ticket_id,max(last_updated_date) as MaxUpdateDate FROM audit

group by ticket_id ) MaxAudit on audit.ticket_id = MaxAudit.ticket_id

and audit.last_updated_date = MaxAudit.MaxUpdateDate

INNER JOIN ticket on audit.ticket_id = ticket.ticket_id

where audit.Status not in ('EXCLUDED','ACCEPTED')

and ticket.Created_Date between '9/1/2006' and '9/3/2006'

order by 1

Thanks

Sreejith

Tuan H. Le SSC Eights! Points: 806 More actions · Answer 7

Tuan H. Le

SSC Eights!

Points: 806

September 7, 2006 at 3:03 pm

#659217

Thank YOU all for your quick help.

Tuan