SumLibraryUsage.sql

CREATE VIEW SumLibraryUsage AS
SELECT loc_TRLN_Libraries.TRLN_LibraryName, loc_COUNTER_JR1.TRLN_Year, Sum(COUNTER_Jan+COUNTER_Feb+COUNTER_Mar+COUNTER_Apr+COUNTER_May+COUNTER_Jun) AS Total_COUNTER_JanJun, Sum(COUNTER_Jul+COUNTER_Aug+COUNTER_Sep+COUNTER_Oct+COUNTER_Nov+COUNTER_Dec) AS Total_COUNTER_JulDec, Sum(COUNTER_Jan+COUNTER_Feb+COUNTER_Mar+COUNTER_Apr+COUNTER_May+COUNTER_Jun+COUNTER_Jul+COUNTER_Aug+COUNTER_Sep+COUNTER_Oct+COUNTER_Nov+COUNTER_Dec) AS Total_COUNTER__AllMonths, loc_SERSOL_Providers.SERSOL_ProvName
FROM loc_SERSOL_Providers INNER JOIN (loc_SERSOL_Databases INNER JOIN (loc_TRLN_Costs INNER JOIN (loc_TRLN_Libraries INNER JOIN loc_COUNTER_JR1 ON loc_TRLN_Libraries.TRLN_UniversityID = loc_COUNTER_JR1.TRLN_UniversityID) ON (loc_TRLN_Libraries.TRLN_LibraryID = loc_TRLN_Costs.TRLN_LibraryID) AND (loc_TRLN_Costs.TRLN_Year = loc_COUNTER_JR1.TRLN_Year) AND (loc_TRLN_Costs.PUB_ISSNL = loc_COUNTER_JR1.PUB_ISSNL)) ON loc_SERSOL_Databases.SERSOL_DBCode = loc_TRLN_Costs.SERSOL_DBCode) ON (loc_SERSOL_Providers.SERSOL_ProvID = loc_SERSOL_Databases.SERSOL_ProvID) AND (loc_SERSOL_Providers.SERSOL_ProvID = loc_COUNTER_JR1.SERSOL_ProvID)
GROUP BY loc_TRLN_Libraries.TRLN_LibraryName, loc_COUNTER_JR1.TRLN_Year, loc_SERSOL_Providers.SERSOL_ProvName;