pseudo GROUP BY on a field w/ different values 1

[link9]

Hello all --

I have a report which shows data aggregated at an account level, where any one account might have one or many units that belong to it.

This report shows a "Date Received" for surveys that we receive from the field. I group by this field in order to get one nice dataset in order to display.

The problem is that some accounts (nay, most accounts) are sending in different units' surveys on different dates, and so now because of my GROUP BY clause, my account level report is quickly turning into a unit level report -- i.e. the units are falling out onto different rows of data due to the difference in the "dateReceived" column.

My question is: Is there some way that I can make the SELECT statement simply group by the latest dateReceived?

If I were going to program this, I would do a lookup on every row of account data and extract the latest dateReceived, but this would be horribly innefficient, and so I'd really like to have a single SQL Statement that would take care of it for me.

If anyone could confirm or deny the existence of such a statement for me, I would (as always) greatly appreciate it.

Thanks!
Paul Prewett

 

[tlbroadbent]

Paul,

I have an idea how to do what you have described but would like a little more detail. Could you possibly post the current query so I might make a recommendation?

Thanks, Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[link9]

Yes, as long as you don't take me to task for not using aliases for my tables, I'd be happy to. ;-) Actually, I heeded your last bit of advice and have started doing that, but haven't modified this particular view to reflect it.

Additionally, I cut out a mass of stuff to sort of trim it down, so if I've missed a GROUP BY column, don't be surprised.

[return].dateIn in the column in question. Thanks for your help. paul

------------------------------------------

SELECT TOP 5000 demo.college AS college,
masterDemo.buf7 AS buf7, demo.gmName AS gmName,
demo.address1 AS address1, demo.address2 AS address2,
demo.city AS city, demo.state AS state,
demo.phone AS phone, demo.ext AS ext,
demo.email AS email,
typeRRS = CASE WHEN demo.typeRRS = 3 THEN 3 ELSE 1 END,
SUM(typeBD) AS typeBD,
surveyNum.dateOrdered AS ordered,
masterDemo.seg AS seg, masterDemo.subSeg AS subseg,
[return].dateIn AS received
FROM masterDemo LEFT OUTER JOIN
customerCount ON
masterDemo.unit = customerCount.unit LEFT OUTER JOIN
[return] ON masterDemo.unit = [return].unit LEFT OUTER JOIN
surveyNum ON
masterDemo.unit = surveyNum.unit LEFT OUTER JOIN
demo ON masterDemo.unit = demo.unit LEFT OUTER JOIN
surveyType ON
masterDemo.unit = surveyType.unit LEFT OUTER JOIN
vpo ON masterDemo.vpo = vpo.vpoNum LEFT OUTER JOIN
dm ON masterDemo.dm = dm.dmNum LEFT OUTER JOIN
svp ON masterDemo.svp = svp.svpNum
GROUP BY demo.college, masterDemo.buf7, demo.gmName,
demo.address1, demo.address2, demo.city, demo.state, demo.phone, demo.ext, demo.email,
CASE WHEN demo.typeRRS = 3 THEN 3 ELSE 1 END, surveyNum.dateOrdered, [return].dateIn
masterDemo.seg, masterDemo.subseg
ORDER BY masterDemo.svp, demo.college

 

[tlbroadbent]

I won't take anyone to task. I prefer aliases but probably overdue it. You'll note that I did remove some of the aliases and add others - personal preference. ;-)

I'm not sure that I understand the requirement, yet. But I've created a query that I'd like you to test. Then get back to me with any questions, comments or problems encountered. I have considered a different approach but would like you to tell me if I'm on the right track or not.

I've designed the solution to include a derived table (query) which also has a correlated sub-query. The correlated subquery is the key to getting the max date. I hope this works as I have no way to test it.

Select * From
(SELECT TOP 5000
d.college,
m.buf7,
d.gmName,
d.address1,
d.address2,
d.city,
d.state,
d.phone,
d.ext,
d.email,
typeRRS =
CASE
WHEN d.typeRRS = 3
THEN 3
ELSE 1
END,
SUM(typeBD) AS typeBD,
s.dateOrdered AS ordered,
m.seg,
m.subSeg,
(Select max(dateIn) From [Return]
Where unit=m.unit) AS received
FROM masterDemo As m
LEFT OUTER JOIN customerCount
ON m.unit = customerCount.unit
LEFT OUTER JOIN SurveyNum As s
ON m.unit = s.unit
LEFT OUTER JOIN demo As d
ON m.unit = d.unit
LEFT OUTER JOIN [return] As r
ON m.unit = r.unit
LEFT OUTER JOIN surveyType As t
ON m.unit = t.unit

--are the next three tables necessary in this query?
LEFT OUTER JOIN vpo As v
ON m.vpo = v.vpoNum
LEFT OUTER JOIN dm
ON m.dm = dm.dmNum
LEFT OUTER JOIN svp
ON m.svp = svp.svpNum
) As q
GROUP BY
college,
buf7,
gmName,
address1,
address2,
city,
state,
phone,
ext,
email,
typeRRS,
typeBD,
Ordered,
Received,
seg,
subseg
ORDER BY
svp,
college Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[link9]

Excellent. Makes perfect sense. Do you know how many boxes of chocolate my company is going to owe you this christmas? ;-)

And yes, those other tables are needed. I just chopped them out of the SELECT part to get the size of the post down. They allow the view to be easily filtered by user choice in the ASP page where it's used.

I'll let you know when it works.

paul

 

[link9]

Due to all the other things going on here, I just got around to implementing this solution... I had to modify it to account for some stuff I didn't tell you about, the the basic logic is intact, and works like a dream.

Thanks a million.

paul