SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 MAX date subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/19/2013 :  11:22:05  Show Profile  Reply with Quote
Hi,

I am trying to write a query pulling the last te_event from a table by the latest te_date for a set of records. My query is below

SELECT tickets.t_number, tickets.t_reference,ticket_events.te_event, ticket_events.te_date, FROM ticket_events INNER JOIN
tickets ON ticket_events.te_system_ref = tickets.t_number INNER JOIN
warrants_due ON tickets.t_number = warrants_due.wd_system_ref
WHERE tickets.t_number in (1091421,
1091426,
1091429,
1091430,
1091432,
1091450)

There are 25,000 tickets in the subquery. I can't work out where to put the MAX (ticket_events.te_date) statement.

Any help appreciated
thanks
John

James K
Flowing Fount of Yak Knowledge

3327 Posts

Posted - 11/19/2013 :  14:26:00  Show Profile  Reply with Quote
I didn't quite follow your explanation, but perhaps this?
SELECT * FROM (
SELECT  tickets.t_number ,
        tickets.t_reference ,
        ticket_events.te_event ,
        ticket_events.te_date,
        ROW_NUMBER() OVER (PARTITION BY ticket_events.te_event ORDER BY ticket_events.te_date DESC) RN
FROM    ticket_events
        INNER JOIN tickets ON ticket_events.te_system_ref = tickets.t_number
        INNER JOIN warrants_due ON tickets.t_number = warrants_due.wd_system_ref
WHERE   tickets.t_number IN ( 1091421, 1091426, 1091429, 1091430, 1091432,
                              1091450 )
) s WHERE RN = 1;
Go to Top of Page

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/20/2013 :  03:29:54  Show Profile  Reply with Quote
Hi James,

Thanks for the reply but I didn't explain it very well.
I have a table with Tickets and each of these tickets have a series of events that can happen to them stored in the events table. I need to pull out just the latest event for each ticket by the event date. I have 25,000 tickets to do this to. I know that MAX will bring up the latest date but I don't know where to put the MAX statement so I get all 25,000 results. I hope this is a little clearer.
Thanks again for the help.

John
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/20/2013 :  04:10:19  Show Profile  Reply with Quote
quote:
Originally posted by AfcDons

Hi James,

Thanks for the reply but I didn't explain it very well.
I have a table with Tickets and each of these tickets have a series of events that can happen to them stored in the events table. I need to pull out just the latest event for each ticket by the event date. I have 25,000 tickets to do this to. I know that MAX will bring up the latest date but I don't know where to put the MAX statement so I get all 25,000 results. I hope this is a little clearer.
Thanks again for the help.

John


I think previous suggestion would give you what you want.
Also this


SELECT tickets.t_number, tickets.t_reference,ticket_events.te_event, ticket_events.te_date, FROM ticket_events INNER JOIN
tickets ON ticket_events.te_system_ref = tickets.t_number INNER JOIN
warrants_due ON tickets.t_number = warrants_due.wd_system_ref
WHERE tickets.t_number in (1091421,
1091426,
1091429,
1091430,
1091432,
1091450)
AND EXISTS (SELECT 1
FROM FROM ticket_events te 
INNER JOIN tickets t
ON te.te_system_ref = t.t_number
WHERE t_number = ticket.t_number
GROUP BY t.t_number
HAVING MAX(te.te_date) = ticket_events.te_event
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/22/2013 :  03:51:36  Show Profile  Reply with Quote
Hi Visakh,

I have run the code but it fails with the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I am confused by the line
HAVING MAX(ticket_events.te_date) = ticket_events.te_event
as te_date is a date field and te_event is varchar

The previous code from James didn't work as it pulled back all the records from the event table when I only want the latest.
Thanks again for your help with this as it has been frustrating me for a while.

John
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 11/22/2013 :  04:02:52  Show Profile  Reply with Quote
in which format do you have date values in ticket_events.te_date ?
try to convert ticket_events.te_event column to te_date format... (mm/dd/yyyy ot dd/mm/yyyy )
reference :
http://stackoverflow.com/questions/10878740/conversion-of-a-varchar-datatype-to-a-datetime-datatype-resulted-in-an-out-of-ra
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

--
Chandu
Go to Top of Page

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/22/2013 :  04:25:05  Show Profile  Reply with Quote
Hi,

The Te_event column isn't a date column. It is a VarChar column.
It has text in it.

thanks
John
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 11/22/2013 :  04:32:44  Show Profile  Reply with Quote
May be its a typo.. try this

HAVING MAX(ticket_events.te_date) = ticket_events.te_date

--
Chandu
Go to Top of Page

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/22/2013 :  04:52:14  Show Profile  Reply with Quote
Hi Chandu,

Thanks for this but I am getting too many returns in the resultset.
There are only six tickets so I was hoping for six results ( last event for each ticket) but I get 161 results ( all the events for each ticket)

Thanks again for your help.

John
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/22/2013 :  05:41:38  Show Profile  Reply with Quote
quote:
Originally posted by AfcDons

Hi Chandu,

Thanks for this but I am getting too many returns in the resultset.
There are only six tickets so I was hoping for six results ( last event for each ticket) but I get 161 results ( all the events for each ticket)

Thanks again for your help.

John


show your used code please.
does your te_date filed has timepart?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/22/2013 :  06:00:08  Show Profile  Reply with Quote
Hi,

some of the results look like below

t_number t_reference te_event te_date
1091450 HZ9636390A Debt registration requested 2009-06-30 04:48:35.000
1091450 HZ9636390A Debt Registration Re-Scheduled 2009-06-30 16:42:13.000
1091450 HZ9636390A Debt registration requested 2009-07-01 04:55:57.000
1091450 HZ9636390A Debt registration accepted 2009-07-01 15:55:37.000

The te_date does have a timpart as above.

Thanks for your help
John
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/22/2013 :  06:59:24  Show Profile  Reply with Quote
i think the reason is not using proper alias
try this


SELECT t.t_number, t.t_reference,te.te_event, te.te_date
FROM ticket_events te 
INNER JOIN tickets t
ON te.te_system_ref = t.t_number 
INNER JOIN warrants_due w
ON t.t_number = w.wd_system_ref
WHERE t.t_number in (1091421,
1091426,
1091429,
1091430,
1091432,
1091450)
AND EXISTS (SELECT 1
FROM ticket_events te1 
INNER JOIN tickets t1
ON te1.te_system_ref = t1.t_number
WHERE t1.t_number = t.t_number
GROUP BY t1.t_number
HAVING MAX(te1.te_date) = te.te_event
)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

AfcDons
Starting Member

United Kingdom
10 Posts

Posted - 11/22/2013 :  07:29:36  Show Profile  Reply with Quote
Hi visakh

I had to change the last bit
HAVING MAX(te1.te_date) = te.te_event to
HAVING MAX(te1.te_date) = te.te_date

And it is now working. Thanks very much for this. It will help me in the future.

Thanks to everyone else who replied

John
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/22/2013 :  07:30:29  Show Profile  Reply with Quote
quote:
Originally posted by AfcDons

Hi visakh

I had to change the last bit
HAVING MAX(te1.te_date) = te.te_event to
HAVING MAX(te1.te_date) = te.te_date

And it is now working. Thanks very much for this. It will help me in the future.

Thanks to everyone else who replied

John


Ah copy pasted it from earlier suggestion and forgot to change it
anyways glad that you picked it up

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000