Need help with date comparison

Question

Need help with date comparison

Tuan H. Le

SSC Eights!

Points: 806
More actions
September 1, 2006 at 6:48 pm

#173798

Hi,
I have two tables: TICKETS and AUDITS
Table TICKETS
       ticket_id    owner
       ----------------
       111          X
       222          Y
   Table AUDITS
       ticket_id    action_cd     last_updated_dt
       111          Pending         08/01/2006
       111          Accepted        08/02/2006
       111          Completed       08/03/2006
       222          Pending         08/01/2006
       222          Accepted        08/02/2006
I would like to insert into table AUDITS an Expired record for those tickets which have been accepted but not complete for more than 5 days from the date the ticket was accepted (action = Accepted)
From the above test data it should create a new record in AUDITS table for ticket 222 since the ticket was accepted and its MAX(last_updated_dt) returns 08/02/2006 which has pass the expiration period.
Here's my statement but it doesn't do what I have expected
       INSERT INTO audits (ticket_id,
                           owner,
                           action,
                           last_updated_dt)
       SELECT a1.ticket_id,
              a1.owner,
              'Expired',
              GETDATE()
       FROM audits a1
       INNER JOIN (SELECT ticket_id,
                          MAX(last_updated_dt) AS last_updated_dt
                   FROM audits
                   GROUP BY ticket_id) a2
       ON a1.ticket_id = a2.ticket_id
          AND a1.last_updated_dt = a2.last_updated_dt
          AND a1.action = 'Accepted'
       WHERE a1.last_updated_dt < dateadd(d, -5, getdate())
Thanks in advance for your help!
Tuan

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

First of all, you shouldn't be inserting "Owner" into the Audits table because that's a form of denormalization you don't need here... bit, if you insist, here's a self supporting demo of the code...

--===== If the test tables exist, drop them

IF OBJECT_ID('TempDB..#Audits') IS NOT NULL

DROP TABLE #Audits

IF OBJECT_ID('TempDB..#Tickets') IS NOT NULL

DROP TABLE #Tickets

--===== Create the test tables for demonstration purposes

CREATE TABLE #Tickets (Ticket_ID INT,Owner VARCHAR(15))

-- (dunno what you're using for a PK... but you really need one)

CREATE TABLE #Audits (Ticket_ID INT, Owner VARCHAR(15), Action_CD VARCHAR(15), Last_Updated_Dt DATETIME)

--===== Populate the test tables with test data for demonstration purposes

INSERT INTO #Tickets (Ticket_ID , Owner)

SELECT 111,'X' UNION ALL

SELECT 222,'Y'

INSERT INTO #Audits (Ticket_ID, Action_CD, Last_Updated_Dt)

SELECT 111,'Pending','08/01/2006' UNION ALL

SELECT 111,'Accepted','08/02/2006' UNION ALL

SELECT 111,'Completed','08/03/2006' UNION ALL

SELECT 222,'Pending','08/01/2006' UNION ALL

SELECT 222,'Accepted','08/02/2006'

--===== Show the "before"

SELECT * FROM #Audits

--===== Do the calculated insert (run from here down to see that it won't reinsert previously expired records.

INSERT INTO #Audits (Ticket_ID, Owner,Action_CD, Last_Updated_Dt)

SELECT a.Ticket_ID, t.Owner,'Expired' AS Action_CD, GETDATE() AS Last_Updated_Dt

FROM #Audits a,

#Tickets t

WHERE a.Ticket_ID = t.Ticket_ID

AND a.Action_CD = 'Accepted'

AND a.Last_Updated_Dt < GETDATE() - 5

AND a.Ticket_ID NOT IN (SELECT DISTINCT Ticket_ID

FROM #Audits

WHERE Action_CD IN ('Completed','Expired'))

--===== Show the "after"

SELECT * FROM #Audits

Also, as a matter of style... your table names should describe the content of a single row in the table... in other words, your table names should not be plural.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 2

Here you.

insert into AUDITS(ticket_id,owner,action_cd,last_updated_dt)

select ACCEPT.ticket_id, TICKETS.owner, 'Expired',getdate() --ACCEPT.last_updated_dt as AcceptedDate ,

--COMPLETE.last_updated_dt as CompletedDate,

--Datediff(d,ACCEPT.last_updated_dt , getdate())

from AUDITS ACCEPT INNER JOIN TICKETS ON ACCEPT.ticket_id = TICKETS.ticket_id

LEFT OUTER JOIN

(SElect * from AUDITS where action_cd='Completed') COMPLETE on ACCEPT.ticket_id = COMPLETE.ticket_id

where ACCEPT.action_cd ='Accepted'

and COMPLETE.last_updated_dt is null and Datediff(d,ACCEPT.last_updated_dt , getdate()) > 5

Hope this answers your questions.

Thanks

Sreejith

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

Sreejith,

Good code but if you run it more than once, it inserts additional new "Expired" records for the same ticket(s) that already have "Expired" records... you forgot to check to make sure that an "Expired" record doesn't already exist.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 4

Sreejith Sreedharan

SSCertifiable

Points: 5403

September 3, 2006 at 3:08 pm

#658231

Good Catch..Didn't think about it.

Thanks

Sreejith

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

Hi Jeff and Sreejith -

Thanks for your spending your time helped me with the code. I'm going to test with both versions. Regarding performance, is Sreejith's version run faster since it doesn;t use IN/NOT IN clause?

Much thanks again,

Tuan

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

WHERE IN is not normally an acceptable substitute for a good old fashioned inner join or equi join and tends to be a bit slower. WHERE NOT IN, however, has speeds that are usually as good as an outer join with a null detect when it can be used (compare on a single column). Sometimes, the WHERE NOT IN is a bit faster owing to an execution plan with one less step.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 7

Just a bit of clarification... I've not tested WHERE NOT IN vs OUTER WHERE NULL on sp4... I've seen several differences in execution plans between sp3a and sp4...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Hi Jeff -

That helps alot. Thanks very much for your help again.

Tuan

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

You bet. Tuan... thank you for the feedback...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)