July 14, 2008 at 9:57 am
Hello everyone, I have been struggling with this problem for about a week and I can't seem to figure out the solution. Any help would be VERY appreciated.
I have an already existing database that contains three tables of interest here:
Schools (primary key SchoolID), BuildingChars (linked to Schools via SchoolID, contains a date called EffectiveDate), Meters (linked to Schools via SchoolID, contains a date called StartDate). Every Meters entry is a single month worth of data. Every BuildingChars entry corresponds to a full year of Building Characteristics (SqFt, NumStudents, etc.)
I did not design this database and I have little interest in restructuring because it is already pretty big.
I need to select the SUM(Meters.Usage) for each 12 month period in the Meters table corresponding to a specific school and BuildingChars. So say I have two Schools, one has two BuildingChars entries and 24 Meters entries (12 for each BuildingChars). The other school has one BuildingChar and 12 Meters entries. I want to select from that 3 rows, each one being the sum of the information in Meters corresponding to each School and BuildingChar.
Here is what I have thus far, this selects the sum of the most recent 12 months of usage for each school.
SELECT S.SchoolID, SUM(M.Usage) AS Usage, MAX(M.StartDate) AS EndDate
FROM dbo.BuildingChars AS BC INNER JOIN
dbo.Schools AS S ON BC.SchoolID = S.SchoolID INNER JOIN
dbo.Meters AS M ON S.SchoolID = M.SchoolID
WHERE (M.StartDate IN
(SELECT TOP (12) StartDate
FROM dbo.Meters AS M2
WHERE (SchoolID = S.SchoolID)
ORDER BY StartDate DESC))
GROUP BY S.SchoolID
ORDER BY S.SchoolID
I think that's all the relevant info needed. Please any help is appreciated. Thanks!
July 14, 2008 at 10:04 am
That looks like the query that will do what you need. What am I missing in your request? What help do you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 10:14 am
The query I have written only retrieves the most recent 12 months of utility data. Many schools have years of utility data. I'm trying to create a view that contains the yearly data (the sums of 12 months of data from Meters). This way I can extract data for billing periods ending in 2005, etc. I want my result to look like.
SchoolID Usage EndDate
430 8000 12/1/2004
430 9710 12/1/2005
431 7480 4/1/2004
where the other tables look like:
Schools -
SchoolID Name
430 A Middle School
431 A High School
Meters -
SchoolID Usage StartDate
430 100 1/1/2004
430 95 2/1/2004
430 86 3/1/2004
...
430 76 12/1/2004
430 102 1/1/2005
...
430 100 12/1/2005
431 56 5/1/2003
...
431 102 4/1/2004
BuildingChars -
SchoolID SqFt NumStudents Effective Date
430 10000 600 1/1/2004
430 10000 540 1/1/2005
431 10123 120 5/1/2003
Is this enough information?
July 14, 2008 at 11:12 am
If I read this correctly what you want to do is this?
SELECT S.SchoolID, YEAR(M.StartDate) AS calendarYear, SUM(M.Usage) AS Usage, MAX(M.StartDate) AS EndDate
FROM dbo.BuildingChars AS BC INNER JOIN
dbo.Schools AS S ON BC.SchoolID = S.SchoolID INNER JOIN
dbo.Meters AS M ON S.SchoolID = M.SchoolID
GROUP BY S.SchoolID, YEAR(M.StartDate)
ORDER BY S.SchoolID
I believe that this will work (without actually having your structure to test on). Good luck!
- J
July 14, 2008 at 11:40 am
I don't want to GROUP BY year though, I'd like to group by corresponding buildingchars. Notice how some of the results may be utility bills starting in May and ending in April, for instance.
Basically, I'm trying to establish a relationship between Meters and BuildingChars. They both fall under Schools and I wish the database had been designed so that Meters fell under BuildingChars.
July 14, 2008 at 11:50 am
I'm assuming you're using SQL 2005, because that's the forum this is posted in. Is that correct?
Also, I need to know if the meter dates can be more specific than the month and year they were in. For example, if it's 14 July 2008, and the August 2007 meter reading was on 2 August, would that count as being "in the last 12 months" (August to July), or does it go in the prior 12 months (15 August to 14 July)?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 12:03 pm
Alright... 1 more swing at this.
SELECT S.SchoolID, SUM(M.Usage) AS Usage, MAX(M.StartDate) AS EndDate
FROM dbo.BuildingChars AS BC
INNER JOIN dbo.Schools AS S
ON BC.SchoolID = S.SchoolID
INNER JOIN
(SELECT SchoolID, DATEADD(YEAR,-1,MAX(StartDate)) AS startDt,
MAX(StartDate) AS endDt
FROM dbo.Meters) AS M2
ON S.SchoolID = M.SchoolID
INNER JOIN dbo.Meters AS M
ON M2.SchoolID = M.SchoolID AND M.StartDate BETWEEN M2.startDt AND M2.endDt
GROUP BY S.SchoolID
ORDER BY S.SchoolID
Just noticed that I did not restict the query to a specific end date but that should be easy enough with a WHERE clause on the sub-query.
July 14, 2008 at 12:13 pm
thanks so much for your quick responses. cjeremy, i think your solution is very close. the only problem is i get an error message that says "The multi-part identifier M.SchoolID could not be bound." i'm not sure what this means...
July 14, 2008 at 12:16 pm
Change, "ON S.SchoolID = M.SchoolID" to "ON S.SchoolID = M2.SchoolID".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 14, 2008 at 12:20 pm
egunther (7/14/2008)
thanks so much for your quick responses. cjeremy, i think your solution is very close. the only problem is i get an error message that says "The multi-part identifier M.SchoolID could not be bound." i'm not sure what this means...
No problem... hope this helps. Thanks to GSquared for the syntax checking!
-- J
July 14, 2008 at 12:26 pm
Ah it executed but it isn't giving me the correct results. I'm not 100% on what it's actually calculating now. Curse whoever designed this database. It is still giving me only one result per SchoolID...
July 14, 2008 at 1:07 pm
!!!! Finally figured it out!!
I took the Schools table out of the equation to simplify things. I also added a BuildingID index to the BuildingChars table (didn't require any major changes). Here is the code I ended up using.
SELECT TOP (100) PERCENT SUM(M.Usage) AS Usage, BC.BuildingID, BC.SchoolID
FROM dbo.BuildingChars AS BC INNER JOIN
dbo.Meters AS M ON BC.SchoolID = M.SchoolID AND M.StartDate BETWEEN BC.EffectiveDate AND DATEADD(YEAR, 1, BC.EffectiveDate)
GROUP BY BC.BuildingID, BC.SchoolID
ORDER BY BC.SchoolID
Thank you so much to everyone who replied. I've been pulling my hair out over this problem for the last week or so and your guys insights and code (even if my problem description wasn't the best) really did help.
July 14, 2008 at 1:21 pm
Try this:
;with
FirstDate (FDate) as
(select min(startdate) -- First meter date
from #meters),
MeterYears (SDate, EDate) as -- Meter start and end dates
(select
dateadd(day, -1 * datepart(day, getdate()),
dateadd(day, datediff(day, 0, dateadd(year, -1, getdate())), 0))+1, -- Current year start date
getdate() -- Current Date
union all
select dateadd(year, -1, sdate), dateadd(year, -1, edate) -- Prior Years
from meteryears
inner join firstdate
on sdate >= fdate)
SELECT S.SchoolID, SUM(M.Usage) AS Usage, MAX(M.StartDate) AS EndDate
FROM dbo.BuildingChars AS BC
INNER JOIN dbo.Schools AS S
ON BC.SchoolID = S.SchoolID
INNER JOIN dbo.Meters AS M ON S.SchoolID = M.SchoolID
inner join meteryears
on meters.startdate between meteryears.sdate and meteryears.edate
GROUP BY S.SchoolID, sdate
ORDER BY S.SchoolID
I don't have your tables nor any data, so I can't test this very well. You may need to refine the CTE (MeterYears), or modify it to work off of an input parameter instead of getdate().
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply