Need help on query to eliminate function
-
We got a view that is starting to take way to much time to run (2mins plus). In this view there is a function that returns a date from another table. By removing this function, the time drops to around 10secs, which is ideal for us.
So I'm trying to create a view that would have the data that the function is currently getting.
We have a table that is a history of the date and time for our clients scheduled appointments. When the appointment is set, the date is entered in this table, and another column is the time stamp of when it was set. So if the appointment time changes, we do not simply update it, we enter a new record with the latest information. What I need to get is a view that has the Appointment ID and its latest date and time set.
Here is the quick look at the table: (took Times(hh:dd:mm) out for this example)
Appt_ID | Appt_Date | Time_Stamp (when entered)
1 12/1/2004 11/1/2004
2 12/5/2004 11/2/2004
3 1/5/2005 11/2/2004
1 12/9/2004 11/3/2004
3 1/18/2004 11/10/2004
So the view should look like:
Appt_ID | Appt_Date | Time_Stamp
1 12/9/2004 11/3/2004
2 12/5/2004 11/2/2004
3 1/18/2004 11/10/2004
Any help is greatly appreciated!
-
Without DDL this is perhaps only a shot in the dark. However, take a look at BOL for GROUP BY and MAX. Should do the trick.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
This is what I come up with, but its not right. Its only removing about 30 rows, and still has multiple instances of the appointments
SELECT dbo.Appointment_Date_Time.Appointment_Log_ID,
dbo.Appointment_Date_Time.Appointment_Date_Time,
MAX(dbo.Appointment_Date_Time.Data_Entry_Time_Stamp)
FROM dbo.Appointment_Date_Time
GROUP BY dbo.Appointment_Date_Time.Appointment_Log_ID,
dbo.Appointment_Date_Time.Appointment_Date_Time
ORDER BY dbo.Appointment_Date_Time.Appointment_Log_ID
-
I'm really close to getting this, just need to get the appointment_date_time in the return values, if I add it in, it says it needs to be in an aggregate function. If I add it into the GROUP BY, i'm back to having the full list.
SELECT dbo.Appointment_Date_Time.Appointment_Log_ID,
MAX(dbo.Appointment_Date_Time.Data_Entry_Time_Stamp)
FROM dbo.Appointment_Date_Time
WHERE dbo.Appointment_Date_Time.Appointment_Log_ID IN
(SELECT dbo.Appointment_Date_Time.Appointment_Log_ID
FROM dbo.Appointment_Date_Time)
GROUP BY dbo.Appointment_Date_Time.Appointment_Log_ID
ORDER BY dbo.Appointment_Date_Time.Appointment_Log_ID
-
Hi,
Try this one:
select
tmp.Appointment_Log_ID, a.Appointment_Date_Time, tmp.Data_Entry_Time_Stamp
from
(select
Appointment_Log_ID, max(Data_Entry_Time_Stamp) as Data_Entry_Time_Stamp
from
Appointment_Date_Time
group by
Appointment_Log_ID
) as tmp
inner join Appointment_Date_Time as a
on (a.Appointment_Log_ID = tmp.Appointment_Log_ID) and (a.Data_Entry_Time_Stamp = tmp.Data_Entry_Time_Stamp)
order by
tmp.Appointment_Log_ID
The point here is to obtain a recordset with appointment ids and their maximum time stamp values (the subquery). After that, a simple join to the Appointment_Log table will give you the corresponding appointment dates. I don't know whether the table has some other columns, but the SQL statement above will make a good use on an index on ID, timestamp and datetime (in THAT order).
Regards,
Goce.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply