Aggregate subquery? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Aggregate subquery?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-07-14 : 16:36:31

Folks wanted a way to see what jobs people were reporting labor to, so I came up with a report to show that. After looking at it a bit, the folks said they'd like to know how far along each job is, in comparison to the original estimate they gave for each job.

First query is here:

select 
	ladetail.fjobno as JOBNO,
	ladetail.foperno as OPNO,
	CAST(jodrtg.fopermemo as varchar(25)) as MEMO, 
	ladetail.fpro_id AS WORKC,
	ladetail.fempno as EMPNO,	
	LTRIM(RTRIM(prempl.ffname)) + ' ' + LTRIM(RTRIM(prempl.fname)) AS NAMEFL,	
	CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) as numeric) / 3600 as AHOUR,
	RIGHT(ladetail.fsdatetime, 7) as STIME,
	RIGHT(ladetail.fedatetime, 7) as ETIME
from 
	ladetail 
	left join prempl on ladetail.fempno = prempl.fempno
	left join jodrtg on ladetail.fjobno = jodrtg.fjobno and ladetail.foperno = jodrtg.foperno
where 
	ladetail.fdate in (@WORKDATE)
order by
	ladetail.fempno

Results in a 200ish-row result set of everyone's labor for a given @WORKDATE.

This query:

select
	ladetail.fjobno as JOBNO,
	ladetail.foperno as OPNO,
	((SUM(CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) as numeric) / 3600)) / jodrtg.fuprodtime) as PCTCOMPLETE
from 
	ladetail
	left join jodrtg on ladetail.fjobno = jodrtg.fjobno and ladetail.foperno = jodrtg.foperno
where 
	ladetail.fjobno in (@JOBNO)
	and ladetail.foperno in (@OPNO)
group by
	ladetail.fjobno,
	ladetail.foperno,
	jodrtg.fuprodtime

Returns the percent of the job completed, by dividing actual completed labor by the estimated labor requirement.

How would I pull that single PCTCOMPLETE field into the first query, when it requires specific information (fjobno, foperno) that don't exist until the query actually runs? Any ideas?

I've made a few botched attempts that resulted in a whole slew of 'such and such is not part of the group by or aggregate' warnings, but nothing close to what I want.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-07-15 : 02:48:50

guess I'm missing something but
select
ladetail.fjobno as JOBNO,
ladetail.foperno as OPNO,
CAST(jodrtg.fopermemo as varchar(25)) as MEMO,
ladetail.fpro_id AS WORKC,
ladetail.fempno as EMPNO,
LTRIM(RTRIM(prempl.ffname)) + ' ' + LTRIM(RTRIM(prempl.fname)) AS NAMEFL,
CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) as numeric) / 3600 as AHOUR,
RIGHT(ladetail.fsdatetime, 7) as STIME,
RIGHT(ladetail.fedatetime, 7) as ETIME ,
t.PCTCOMPLETE
from
ladetail
left join prempl on ladetail.fempno = prempl.fempno
left join jodrtg on ladetail.fjobno = jodrtg.fjobno and ladetail.foperno = jodrtg.foperno
left join (select
ladetail.fjobno,
ladetail.foperno,
((SUM(CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) as numeric) / 3600)) / jodrtg.fuprodtime) as PCTCOMPLETE
from
ladetail
left join jodrtg on ladetail.fjobno = jodrtg.fjobno and ladetail.foperno = jodrtg.foperno
where
ladetail.fjobno in (@JOBNO)
and ladetail.foperno in (@OPNO)
group by
ladetail.fjobno,
ladetail.foperno,
jodrtg.fuprodtime
) t
on t.fjobno = ladetail.fjobno
and t.foperno = ladetail.foperno
where
ladetail.fdate in (@WORKDATE)
order by
ladetail.fempno

yhe subquery doesn't take into account the date.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-07-15 : 10:32:56

That's (one) of the problem(s); the subquery to determine 'how complete' a job-operation pairing is has to look at all labor entries across all dates for that particular job and operation.

What they originally wanted was this: they put in a calendar date, and the resulting query shows them every labor entry for that given day shown in hours, sorted by employee number, with the job number and job operation (paired together = unique task). The first query does exactly that.

The new requirement is all the above, plus a 'realtime' look at how complete a job operation is in comparison to their estimate of how long the job should take; then I would add conditional formatting to the datagrid highlighting entries where labor was posted to jobs that had already met or exceeded the estimate.

So basically, what I really need is some way to inject the results - per line - of one query into a completely different query, using two fields in the original query in the 'where' clause of the second. In other words, attaining the desired goal is improbable within the framework of what little I do know.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-15 : 11:24:50

Maybe something like:

SELECT 
    ladetail.fjobno AS JOBNO,
    ladetail.foperno AS OPNO,
    CAST(jodrtg.fopermemo AS varchar(25)) AS MEMO, 
    ladetail.fpro_id AS WORKC,
    ladetail.fempno AS EMPNO,    
    LTRIM(RTRIM(prempl.ffname)) + ' ' + LTRIM(RTRIM(prempl.fname)) AS NAMEFL,
    -- Add the precision and scale for numeric
    CAST(DATEDIFF(s, ladetail.fsdatetime, ladetail.fedatetime) AS numeric) / 3600 AS AHOUR,
    RIGHT(ladetail.fsdatetime, 7) AS STIME,
    RIGHT(ladetail.fedatetime, 7) AS ETIME
    ,D.PCTCOMPLETE
FROM 
    ladetail
    JOIN
    (
        SELECT
            L2.fjobno as JOBNO,
            L2.foperno as OPNO,
            -- Add the precision and scale for numeric
            ((SUM(CAST(DATEDIFF(s, L2.fsdatetime, L2.fedatetime) AS numeric) / 3600)) / J2.fuprodtime) AS PCTCOMPLETE
        FROM 
            ladetail L2
            LEFT JOIN jodrtg J2
                ON L2.fjobno = J2.fjobno
                    AND L2.foperno = J2.foperno
        GROUP BY
            L2.fjobno,
            L2.foperno,
            J2.fuprodtime
    ) D
        ON ladetail.fjobno = D.JOBNO
            AND ladetail.foperno = D.OPNO
    LEFT JOIN prempl ON ladetail.fempno = prempl.fempno
    LEFT JOIN jodrtg ON ladetail.fjobno = jodrtg.fjobno AND ladetail.foperno = jodrtg.foperno
WHERE 
    ladetail.fdate = @WORKDATE
ORDER BY
    ladetail.fempno

If not, post sample data and expected results.

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-07-15 : 17:14:17

That works exactly as I need it to, bravo for pulling that off without even looking :)

Aside from BOL, is there some kind of book or resource or even just a sketch of general rules of thumb that would give me a better sense of when to use an approach like the one Ifor used? I *do* spend a lot of my 'free time' perusing the sqlteam forums and have learned a lot just by doing that, but unfortunately I wear 30,000 hats and thus I don't get as much time to devote as the subject matter deserves. If I had some kind of waypoint to focus on in those few minutees a day I do get, I think I could learn a lot more than I would from going about it randomly, as has been the case to this point.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-16 : 03:47:58

The Microsoft books by Itzik Ben-Gan are good for T-SQL.
Anything by Joe Celko is also worth reading.

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2009-07-16 : 11:22:35

I will look these up; thank you for the suggestion, and thanks again for figuring a way to solve my problem!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources