Appointment Query

[JimZ]

Since the mobile version is a little buggy, I've been attempting to write a query to pull my schedule for a week at a time (or a given time period) which can then be imported into Outlook or Google Calendars. From there, it can be synced with my mobile device. I am trying to get only Last name, First name, Procedure description, Start time, and End time however I keep getting stuck in some Do-Loop and the program hangs up (One class in 1982 writing Fortran doesn't help at all!) . I've also used the Appointment reports (which is already in the program) and exported that as a comma-del file, then sorted out which categories I need and then imported this into Outlook. It does work, but is a very long way around this problem. Any help on which category names (LName, FName,AptDateTime etc) that are necessary or another solution to this problem? Thanks.

Jim

[JimZ]

I've made it this far, but have hit 2 walls. I am unable to get the length added to the start time to give me an end time, and I can't figure out how to separate providers. Any suggestions?

SET @StartDate='2009-02-02', @EndDate='2009-02-06';
SELECT pa.PatNum, ProcDescript, pa.PriProv, DATE(AptDateTime), TIME(AptDateTime), length(ap.Pattern)*5 FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE DATE(ap.AptDateTime) >= @StartDate
AND DATE(ap.AptDateTime) <= @EndDate
AND AptStatus
GROUP BY pa.PatNum
ORDER BY AptDateTime;
DROP TABLE IF EXISTS tmp;

[jordansparks]

The mobile version is no longer buggy.

[JimZ]

Thanks, I was jumping the gun to see if I could get the setup to work with a Blackberry.

[jordansparks]

We had to go with XML as the export format. I don't think XML is going to very viewable from any program. So we were not able to make the data very globally useful like we had hoped. But we were able to use a fullblown SQL database on the Windows Mobile device, and I'm really happy about that. SQL makes it blazing fast and very flexible.

[JimZ]

Thanks, I think I understand the limitations. I did finish the Query which will enable the schedule to be exported (with a little work)into Outlook or Google if anyone wants it. You just have to change the dates to reflect the period you need.

SET @StartDate='2009-02-02', @EndDate='2009-02-06';
SELECT pa.PatNum, ProcDescript, pa.PriProv, DATE(AptDateTime), TIME(AptDateTime), length(ap.Pattern)*5/1440 FROM patient pa
INNER JOIN procedurelog pl ON pa.PatNum=pl.PatNum
INNER JOIN procedurecode pc ON pl.CodeNum= pc.CodeNum
INNER JOIN appointment ap ON pl.AptNum=ap.AptNum
WHERE DATE(ap.AptDateTime) >= @StartDate
AND DATE(ap.AptDateTime) <= @EndDate
AND AptStatus
GROUP BY pa.PatNum
ORDER BY AptDateTime;
DROP TABLE IF EXISTS tmp;