Listing 1 A complex query
Function FormatHourlyQuery(Yr as Long, Wk as Long, Dy as Long, Hr as Long) as string Dim Sql$ Sql$ = "SELECT convert(datetime,'1/1/2000') as OPR_DATE , -1 as ZONE_ID, " & _ "'Unconstrained Market Clearing Price' AS ZONE_NAME, " & _ "convert(varchar(10),'UMCP') AS ZONE_SHORT_NAME, PC.HOUR, "& _ "convert(float,MCP.PRICE)/10000 as PRICE, " & _ "convert(float,MCP.TOTAL_INCR_SUPPLY)/10 as SUP_INC, " & _ "convert(float,MCP.TOTAL_DECR_SUPPLY)/10 as SUP_DEC, " & _ "convert(float,MCP.TOTAL_INCR_DEMAND)/10 as DMD_INC, " & _ "convert(float,MCP.TOTAL_DECR_DEMAND)/10 as DMD_DEC, " & _ "PC.PRICE_CALCULATION_ID as SEQUENCE, PC.INSERT_TIME, PC.IS_VALID_MARKET" & _ "FROM MARKET_CLEARING_PRICE MCP, PRICE_CALCULATION PC " "WHERE PC.YEAR = "& CStr(Yr) & " and PC.WEEK = " & Cstr(Wk) & " " & _ "and PC.WEEKDAY = " & CStr(Dy) & " and PC.HOUR = " & CStr(Hr) & " " & _ "and PC.PRICE_CALCULATION_ID = MCP.PRICE_CALCULATION_ID " & _ "and PC.PRICE_CALCULATION_ID = ( " & _ "SELECT MAX(PC1.PRICE_CALCULATION_ID) " & "FROM PRICE_CALCULATION PC1 " & _ "WHERE PC1.YEAR = "& CStr(Yr) & " and PC1.WEEK = " & Cstr(Wk) & " " & _ "and PC1.WEEKDAY = " & CStr(Dy) & " and PC1.HOUR = " & CStr(Hr) & ")" FormatHourlyQuery = SQL$ End Function