Selec query using max(date) and group by

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

SQL Server 2000 Forums

Transact-SQL (2000)

Selec query using max(date) and group by

Author

Topic

Kevin2006
Starting Member

7 Posts

Posted - 2006-10-25 : 15:36:36

I have a database with 3 tables:

Sites(SiteID(PK)(int), site description columns)

Readings(ReadingID(PK)(int), SiteID(int), Reading(float), ReadingDT(DateTIme)

Deliveries(DeliveryID(PK)(int), SiteID(int), Delivery_LBS(float), DeliveryDT(DateTime)

I need to collect the reading taken on the most recent sunday(might be this last sunday or the previous sunday depending on the site) for each site and get the reading level for that date. The following query returns the reading for the most recent sunday that each site was read but i am stuck there.


SELECT SiteID, MAX(readingdt) AS LastSundayReading
FROM Reading where (Datepart(WeekDay, readingdt)= 1)
GROUP BY SiteID

I need to know the reading but if i add it to the select statement then i have to add it to the group by statement and i get multiple results for each site. I tried putting it in a subselect to get the reading but i still get multiple results for each site. Here is that attempt:


SELECT d.SiteID, r.Reading, d.lastSundayReading
FROM
(
	SELECT SiteID, MAX(readingdt) AS LastSundayReading
	FROM Reading where (Datepart(WeekDay, readingdt)= 1)
	GROUP BY SiteID
)d 
INNER JOIN Reading r
ON r.SiteID = d.SiteID

Once i get the siteid, reading, readingdt from readings i need to join to both deliveries and sites to get data from them. Is there a way to do a for each loop with sql? It seems like i need to do a for each on the results returned from the 1st query to gather the reading level. Also how can i get the readingid in the 1st query?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-10-25 : 15:39:57

INNER JOIN Reading r
ON r.SiteID = d.SiteID
and r.readingdt = d.LastSundayReading

Jay White

Kevin2006
Starting Member

7 Posts

Posted - 2006-10-25 : 15:54:57

So adding WHERE Clause works for the 1st part


SELECT d.SiteID, r.Reading, d.lastSundayReading
FROM
(
	SELECT SiteID, MAX(readingdt) AS LastSundayReading
	FROM Reading where (Datepart(WeekDay, readingdt)= 1)
	GROUP BY SiteID
)d 
INNER JOIN Reading r
ON r.SiteID = d.SiteID WHERE (r.readingDT = d.LastSundayReading AND r.SiteID = d.SiteID)

The following will return the reading from 2 weeks prior to the last sundays reading. Can anyone help me to merge these two or suggest a better way. I need to end up with a result set that has the FirstSundayReading, LastSundayReading, Sum(Deliveries) Between FirstSundayReading and LastSundayReading, FirstSundayReading + Deliveries - LastSundayReading as Usage.


SELECT SiteID, (MAX(readingdt)-14) AS FirstSundayReading
FROM Reading where (Datepart(WeekDay, readingdt)= 1)
GROUP BY SiteID

Kevin2006
Starting Member

7 Posts

Posted - 2006-10-25 : 15:57:01

quote:
Originally posted by Page47

INNER JOIN Reading r
ON r.SiteID = d.SiteID
and r.readingdt = d.LastSundayReading

Jay White

You beat me to it. I had worked around it although your suggestion is better than my where clause so i will change it. Now if you have suggestions for the rest of my problem that would be awesome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources