[thelist] Data Shaping and Count

[Matt Warden]

Michele,

On 5/9/05, Joshua Olson <joshua at waetech.com> wrote:
> > -----Original Message-----
> > From: Michele Foster (WizarDev)
> > Sent: Monday, May 09, 2005 11:17 AM
> >
> > I have this query ....
> >
> > strShapeSQL = "SHAPE... (complex query here...)"
> >
> > Which gives me,
> >
> > JobTitle1 (PositionEn)
> >     Applicant1 (FirstName, LastName)
> >     Applicant2
> >     Applicant3
> >
> > JobTitle2
> >     Applicant1
> >     Applicant2
> > etc.
> 
> Michele,
> 
> Before we delve into a solution for your other problem... is there a real
> need here for data shaping?  Have you tried to tackle this with normal SQL
> and some creative output looping?

As rudy says: "I have seen data shaping and it is the spawn of satan."

It took a while, but working off of that, I would suggest (pardon my
reformulation):

SELECT a.VolApplyID, a.VolApplyContactID, a.DateSubmitted,
j.PositionEn, c.ContactID, c.FirstName, c.LastName,
count(otherapplications.VolApplyID)
FROM VolApply a, Contacts c, Jobs j, VolApply otherapplications
WHERE a.VolApplyContactID = c.ContactID
AND a.Position = j.JobID
AND j.Uniterra = 'Yes'
AND otherapplications.VolApplyContactID = a.VolApplyContactID
GROUP BY a.VolApplyID, a.VolApplyContactID, a.DateSubmitted,
j.PositionEn, c.ContactID, c.FirstName, c.LastName
ORDER BY j.ApplicationDeadline, a.DateSubmitted

(that might not be exactly right. I tried to get in all the conditions
you had, but it has been years since I've used data shaping, and i
could be forgetting something it does.)

The above is based on your subject, where you say you only want a
count. If you want an actual list, you would want something like this
(however, your loop just became thrice nested, I believe):

SELECT a.VolApplyID, a.VolApplyContactID, a.DateSubmitted,
j.PositionEn, c.ContactID, c.FirstName, c.LastName,
otherjobs.PositionEn
FROM VolApply a, Contacts c, Jobs j, VolApply otherapplications, Jobs otherjobs
WHERE a.VolApplyContactID = c.ContactID
AND a.Position = j.JobID
AND j.Uniterra = 'Yes'
AND otherapplications.VolApplyContactID = a.VolApplyContactID
AND otherapplications.Position = otherjobs.PositionEn
ORDER BY j.ApplicationDeadline, a.DateSubmitted

Both of these might need tweaking, as they are untested and there's
only so much the database engine in my head can do. If the results
look close, we can try to work out the kinks.

Thanks,

--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.