August 28, 2008 at 12:22 pm
Looking for some input on the best/fastest way to do this. I'd like to think you can do it using only a query statement, but I always end up using some iterative process that takes way too long.
I want to run a query on the amount of jobs that have been attended in a given month, ultimately to get a count of how many were completed.
In order to figure out which month the job was done in, I join my jobs table with my schedule table, and use the dates that installers where scheduled to do the work.
The problem is that many of the jobs are done over multiple days, so sometimes I have many schedule entries for the one workorder. After the join the workorder gets listed multiple times for jobs that happen over multiple days, and I can't use DISTINCT to filter out the duplicates because the dates are different.
Example:
Workorder Table
Job ID 1, John Smith, 123 Any St, Winnipeg, Manitoba
Schedule Table
Jim Smith, Wed Aug 23, Job ID 1
Jim Smith, Wed Aug 24, Job ID 1
After Join I get this table:
Job ID 1, John Smith, 123 Any St, Winnipeg, Manitoba, Jim Smith, Aug 23
Job ID 1, John Smith, 123 Any St, Winnipeg, Manitoba, Jim Smith, Aug 24
I don't actually care what date it shows Jim Smith attending as long as there is a date, but I only want one line in the result.
Is there something like an INNER JOIN TOP(1)? I don't even know if that makes sense, but any help would be appreciated if you know what I'm getting at.
August 28, 2008 at 12:55 pm
sorry for the quick and dirty scripting..
but i quickly recreated something similar, so that i could give you the query.
CREATE TABLE [dbo].[tbl_job](
[jobid] [int] IDENTITY(1,1) NOT NULL,
[person_name] [nvarchar](50) NOT NULL,
[address] [nvarchar](50) NOT NULL,
) ON [PRIMARY]
CREATE TABLE [dbo].[tbl_schedule](
[schedule_id] [int] IDENTITY(1,1) NOT NULL,
[jobid] [int] NOT NULL,
[sched_date] [datetime] NOT NULL CONSTRAINT [DF_tbl_schedule_sched_date] DEFAULT (getdate()),
) ON [PRIMARY]
SELECT j.jobid, person_name, address, s.sched_date
FROM tbl_job j
inner join
(SELECT jobid, MIN(sched_date) 'sched_date'
FROM tbl_schedule
GROUP BY jobid) s
on j.jobid = s.jobid
so first are 2 similar tables to the ones that you mentioned,
and below that is the query, it will return the first date if the schedule table back for a particular job,
if you change the MIN to MAX, it will get you the lastest date.
You can also add date parameters if jobs will span a certain month... but that is for you to figure out.
good luck...
August 29, 2008 at 7:55 am
Ah MIN/MAX, heh, why didn't I think of that...
Thanks very much for your input and the quick reply.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply