InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 02:19:46 GMT20http://www.sqlservercentral.com/Forums/Topic674892-145-1.aspx[quote]Thank you, much cleaner this way. The data still comes back for each row containing that market code for the given date.[/quote]1. Can you post the resultset for this?2. From you description here, it appears your problem definition may not be complete. Can you post your DDL and sample dataset for each of the Objects?3. Finally, just to confirm - are you looking for a different DATE "format"?Sun, 15 Mar 2009 13:36:02 GMTMando2http://www.sqlservercentral.com/Forums/Topic674892-145-1.aspxThere are ways to take sums and then to take averages of those sums, but they usually involve different groupings. For example I could take the sum of measurements for a day to produce a daily total, which I could then average over a month to give me an average daily total for the month. Is that what you want to do?I'm missing something in your question, because looking at your data sample, I can't seem to find the desired results. [code]declare @sample table (BEGIN_DATE datetime, MARKET_CODE varchar(5), RATE_AMOUNT numeric(5,2))insert into @sample SELECT '12-MAR-2009', 'ADS', 187.5UNION ALL SELECT '12-MAR-2009', 'COMP', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 199UNION ALL SELECT '12-MAR-2009', 'PRO', 330UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'PSBY', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 199UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'CONT', 199UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 330UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'PRO', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 330UNION ALL SELECT '12-MAR-2009', 'ADS', 199UNION ALL SELECT '12-MAR-2009', 'CONT', 199UNION ALL SELECT '12-MAR-2009', 'ADS', 139.3UNION ALL SELECT '12-MAR-2009', 'ADS', 141UNION ALL SELECT '12-MAR-2009', 'ADS', 141UNION ALL SELECT '12-MAR-2009', 'PRO', 199UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'ADS', 167.14UNION ALL SELECT '12-MAR-2009', 'COMP', 0UNION ALL SELECT '12-MAR-2009', 'NLRA', 265UNION ALL SELECT '12-MAR-2009', 'ADS', 172.5UNION ALL SELECT '12-MAR-2009', 'PKGS', 199.99UNION ALL SELECT '12-MAR-2009', 'HSE', 199UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'BAR', 330UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'PRO', 250UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'PRO', 199UNION ALL SELECT '12-MAR-2009', 'HSE', 0UNION ALL SELECT '12-MAR-2009', 'COMP', 0UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'PRO', 230UNION ALL SELECT '12-MAR-2009', 'PRO', 330UNION ALL SELECT '12-MAR-2009', 'PRO', 249select begin_date,market_code,sum(rate_amount) as sumRate,cast(avg(rate_amount) as numeric(5,2)) as avgRatefrom @samplegroup by begin_date,market_code[/code]Fri, 13 Mar 2009 22:12:29 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic674892-145-1.aspx[quote][b]r.hensbergen (3/13/2009)[/b][hr]The way your query is written is possible and works. However, it's not the most versatile. I would suggest to you, you join tables using INNER/OUTER/FULL JOIN. I rewrote your query and changed it that way, that it solved your problems:[code]SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT)FROM RESERVATION_DAILY_ELEMENT_NAME NINNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQINNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_IDRIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODEWHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)GROUP BY RN.BEGIN_DATE, E.MARKET_CODEORDER BY RN.BEGIN_DATE[/code]If there are still bugs, please post the DDL as described in the link below.[/quote]Thank you, much cleaner this way. The data still comes back for each row containing that market code for the given date.I believe I would need to actually SUM and then AVG e.RATE_AMOUNT by Market Code by day. Problem is I have no idea how to write that.Ultimately would like to see it like this:[code]DATE MARKET AVERAGE RATE021209 PRO 269.34021209 BAR 349.98021209 HSE 0.00021209 COMP 0.00[/CODE]Fri, 13 Mar 2009 10:57:45 GMTjonathanmreynoldshttp://www.sqlservercentral.com/Forums/Topic674892-145-1.aspxThe way your query is written is possible and works. However, it's not the most versatile. I would suggest to you, you join tables using INNER/OUTER/FULL JOIN. I rewrote your query and changed it that way, that it solved your problems:[code]SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT)FROM RESERVATION_DAILY_ELEMENT_NAME NINNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQINNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_IDRIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODEWHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)GROUP BY RN.BEGIN_DATE, E.MARKET_CODEORDER BY RN.BEGIN_DATE[/code]If there are still bugs, please post the DDL as described in the link below.Fri, 13 Mar 2009 05:23:59 GMTRonald Hhttp://www.sqlservercentral.com/Forums/Topic674892-145-1.aspxHi, I don't understand your Problem #2 question, regarding Problem #1, I think you should remove RN.BEGIN_DATE from the select.Fri, 13 Mar 2009 04:42:07 GMTBrigadurhttp://www.sqlservercentral.com/Forums/Topic674892-145-1.aspxHello, i'm writing this query:[code]SELECT RN.BEGIN_DATE, E.MARKET_CODE, AVG(E.RATE_AMOUNT)FROM RESERVATION_DAILY_ELEMENT_NAME N, RESERVATION_DAILY_ELEMENTS E, RESERVATION_NAME RN, MARKET_CODES_TEMPLATE MWHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)AND RN.RESV_NAME_ID = N.RESV_NAME_IDAND N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQAND E.MARKET_CODE = M.MARKET_CODEGROUP BY RN.BEGIN_DATE, E.MARKET_CODE, E.RATE_AMOUNTORDER BY RN.BEGIN_DATE[/code]and it returns this result set:[quote] BEGIN_DATE MARKET_CODE AVG(E.RATE_AMOUNT)12-MAR-2009 ADS 187.512-MAR-2009 COMP 012-MAR-2009 PRO 012-MAR-2009 PRO 19912-MAR-2009 PRO 33012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 PSBY 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 PRO 19912-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 CONT 19912-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 PRO 33012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 HSE 012-MAR-2009 PRO 23012-MAR-2009 PRO 012-MAR-2009 PRO 33012-MAR-2009 ADS 19912-MAR-2009 CONT 19912-MAR-2009 ADS 139.312-MAR-2009 ADS 14112-MAR-2009 ADS 14112-MAR-2009 PRO 19912-MAR-2009 PRO 23012-MAR-2009 ADS 167.1412-MAR-2009 COMP 012-MAR-2009 NLRA 26512-MAR-2009 ADS 172.512-MAR-2009 PKGS 199.9912-MAR-2009 HSE 19912-MAR-2009 PRO 23012-MAR-2009 BAR 33012-MAR-2009 PRO 23012-MAR-2009 PRO 25012-MAR-2009 PRO 23012-MAR-2009 PRO 19912-MAR-2009 HSE 012-MAR-2009 COMP 012-MAR-2009 PRO 23012-MAR-2009 PRO 23012-MAR-2009 PRO 23012-MAR-2009 PRO 33012-MAR-2009 PRO 249[/quote][b]Problem #1[/b]I want to average the rate amount per day per market code, and here you see i'm getting a detailed breakout of each individual record associated with the market_code for this date.[b]Problem #2[/b]I'm trying to query this so that I get every "market_code" regardless whether it has been used. The table "market_codes_template" is a list of all configured "market_codes".Can anyone offer a suggestion?thanks.Thu, 12 Mar 2009 20:25:44 GMTjonathanmreynolds