March 24, 2008 at 10:41 am
Greetings folks. I have been asked to show some information to a user, and I want to make sure I tell him correctly what he wants to know. We have contractors who work on jobs, and are supposed to wait until a job is scheduled to start before commencing work on them. However, they sometimes start before they are supposed to, because we have field employees feeding them copies of jobs before they are authorized. So, my user wants to see 1) the jobs that were started before authorized, 2) how many days early the jobs were started, and 3) the production footages on each job before it was authorized. Two tables involved:
IF OBJECT_ID ('TempDB..#Jobs','u') IS NOT NULL
DROP TABLE #Jobs
CREATE TABLE #Jobs
(
ID INT IDENTITY(1,1),
Job VARCHAR(20),--Just a unique job number
RTC SMALLDATETIME-- Released To Contractor, date contractor is authorized to start
PRIMARY KEY(Job)
)
IF OBJECT_ID('TempDB..#Contractor','u') IS NOT NULL
DROP TABLE #Contractor
--Contractors are required to report their daily production in footages every day,
--with the job number, date, and the footage of each type, direct buried(db), or path creation(pc)
--I save these as tab delimited text, and BCP them to a staging table, then insert them into
--my contractor_production table. I can calculate the actual start date by looking at
--MIN(date_of_work).
CREATE TABLE #Contractor
(
ID INT IDENTITY(1,1),
Job VARCHAR(20),
Date_of_work SMALLDATETIME,
db_footage INT,
pc_footage INT
PRIMARY KEY(id)
)
So, now some test data...
INSERT INTO #Contractor (Job,date_of_work,db_footage,pc_footage)
SELECT 'Job1','3/1/2008',100,0 UNION ALL
SELECT 'Job3','3/1/2008',0,300 UNION ALL
SELECT 'Job4','3/1/2008',400,100 UNION ALL
SELECT 'Job1','3/2/2008',100,0 UNION ALL
SELECT 'Job3','3/2/2008',0,300 UNION ALL
SELECT 'Job4','3/2/2008',400,100 UNION ALL
SELECT 'Job1','3/3/2008',100,0 UNION ALL
SELECT 'Job2','3/3/2008',200,50 UNION ALL
SELECT 'Job3','3/3/2008',0,300 UNION ALL
SELECT 'Job4','3/3/2008',400,100 UNION ALL
SELECT 'Job1','3/4/2008',100,0 UNION ALL
SELECT 'Job2','3/4/2008',200,50 UNION ALL
SELECT 'Job3','3/4/2008',0,300 UNION ALL
SELECT 'Job4','3/4/2008',400,100 UNION ALL
SELECT 'Job5','3/4/2008',0,500 UNION ALL
SELECT 'Job1','3/5/2008',100,0 UNION ALL
SELECT 'Job2','3/5/2008',200,50 UNION ALL
SELECT 'Job3','3/5/2008',0,300 UNION ALL
SELECT 'Job4','3/5/2008',400,100 UNION ALL
SELECT 'Job5','3/5/2008',0,500
INSERT INTO #Jobs (Job,RTC)
SELECT 'Job1','2/29/2008' UNION ALL
SELECT 'Job2','2/29/2008' UNION ALL
SELECT 'Job3','2/29/2008' UNION ALL
SELECT 'Job4','3/3/2008' UNION ALL
SELECT 'Job5', NULL
Finally, my query...
SELECT
j.Job,
j.RTC,
c1.Start,
DaysEarly = DATEDIFF(d,c1.start,ISNULL(j.rtc,GETDATE())),
c2.Early_DB_Ftg,
c2.Early_PC_Ftg
FROM #Jobs j,
(
SELECT
Job,
Start = MIN(date_of_work)
FROM #Contractor
GROUP BY job
) c1, --Don't know why this face is here, should be closing paren
(
SELECT
c.Job,
Early_db_ftg = SUM(db_footage),
Early_pc_ftg = SUM(pc_footage)
FROM #Jobs j,
#Contractor c
WHERE j.job = c.job
AND c.date_of_work < = ISNULL(j.rtc,GETDATE())
GROUP BY c.job
) c2 --Don't know why this face is here, should be closing paren
WHERE j.job = c1.job
AND c1.job = c2.job
AND (c1.Start < = j.RTC OR j.RTC IS NULL)
It seems to be telling me correctly that Jobs 4 and 5 were started early, and the footages on each one before they were released. For example, Job4 had 1200 feet of unauthorized db_footage, because it was started on 3/1, but not released until 3/3. I am including the release date in the unauthorized total, because there is a minimum of 1 day admin before a job should hit the field. So, the question is, what is the best way of doing what this query does, and is it telling me what I think it is? I don't know why the little faces are showing up where the closing parens should be in the derived tables.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
March 24, 2008 at 2:53 pm
This may be easier to work with
select
j.job,
j.rtc,
min(date_of_work) start_date,
sum(case when c.date_of_work <= isnull(j.rtc,getdate()) then db_footage end) Early_DB_Ftg,
sum(case when c.date_of_work <= isnull(j.rtc,getdate()) then pc_footage end) Early_PC_Ftg
from #jobs j left join #contractor c on j.job=c.job
group by j.job,j.rtc
having isnull(j.rtc,getdate())>=min(date_of_work)
March 25, 2008 at 3:47 am
Thanks mrpolecat. That does seem much simpler. I can never think of what seems like the obvious, simple solution.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply