Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Structure Grouping/Averaging Expand / Collapse
Author
Message
Posted Thursday, March 12, 2009 8:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 9, 2011 9:02 PM
Points: 25, Visits: 90
Hello, i'm writing this query:

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 M
WHERE 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_ID
AND N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ
AND E.MARKET_CODE = M.MARKET_CODE
GROUP BY RN.BEGIN_DATE, E.MARKET_CODE, E.RATE_AMOUNT
ORDER BY RN.BEGIN_DATE

and it returns this result set:


BEGIN_DATE MARKET_CODE AVG(E.RATE_AMOUNT)
12-MAR-2009 ADS 187.5
12-MAR-2009 COMP 0
12-MAR-2009 PRO 0
12-MAR-2009 PRO 199
12-MAR-2009 PRO 330
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PSBY 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PRO 199
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 CONT 199
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PRO 330
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 HSE 0
12-MAR-2009 PRO 230
12-MAR-2009 PRO 0
12-MAR-2009 PRO 330
12-MAR-2009 ADS 199
12-MAR-2009 CONT 199
12-MAR-2009 ADS 139.3
12-MAR-2009 ADS 141
12-MAR-2009 ADS 141
12-MAR-2009 PRO 199
12-MAR-2009 PRO 230
12-MAR-2009 ADS 167.14
12-MAR-2009 COMP 0
12-MAR-2009 NLRA 265
12-MAR-2009 ADS 172.5
12-MAR-2009 PKGS 199.99
12-MAR-2009 HSE 199
12-MAR-2009 PRO 230
12-MAR-2009 BAR 330
12-MAR-2009 PRO 230
12-MAR-2009 PRO 250
12-MAR-2009 PRO 230
12-MAR-2009 PRO 199
12-MAR-2009 HSE 0
12-MAR-2009 COMP 0
12-MAR-2009 PRO 230
12-MAR-2009 PRO 230
12-MAR-2009 PRO 230
12-MAR-2009 PRO 330
12-MAR-2009 PRO 249


Problem #1
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.

Problem #2
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.
Post #674892
Posted Friday, March 13, 2009 4:42 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:39 AM
Points: 579, Visits: 27,690
Hi, I don't understand your Problem #2 question, regarding Problem #1, I think you should remove RN.BEGIN_DATE from the select.
Post #675029
Posted Friday, March 13, 2009 5:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 9, 2013 5:08 AM
Points: 1,127, Visits: 603
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:
SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT)
FROM RESERVATION_DAILY_ELEMENT_NAME N
INNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ
INNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_ID
RIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODE
WHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)
GROUP BY RN.BEGIN_DATE, E.MARKET_CODE
ORDER BY RN.BEGIN_DATE

If there are still bugs, please post the DDL as described in the link below.


Ronald Hensbergen

Help us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/
-------------------------------------------------------------------------
2+2=5 for significant large values of 2
Post #675049
Posted Friday, March 13, 2009 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 9, 2011 9:02 PM
Points: 25, Visits: 90
r.hensbergen (3/13/2009)
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:
SELECT RN.BEGIN_DATE, M.MARKET_CODE, AVG(E.RATE_AMOUNT)
FROM RESERVATION_DAILY_ELEMENT_NAME N
INNER JOIN RESERVATION_DAILY_ELEMENTS E ON N.RESV_DAILY_EL_SEQ = E.RESV_DAILY_EL_SEQ
INNER JOIN RESERVATION_NAME RN ON RN.RESV_NAME_ID = N.RESV_NAME_ID
RIGHT OUTER JOIN MARKET_CODES_TEMPLATE M ON E.MARKET_CODE = M.MARKET_CODE
WHERE TRUNC(RN.BEGIN_DATE) BETWEEN TRUNC(PMS_P.BUSINESS_DATE) AND TRUNC(PMS_P.BUSINESS_DATE +1)
GROUP BY RN.BEGIN_DATE, E.MARKET_CODE
ORDER BY RN.BEGIN_DATE

If there are still bugs, please post the DDL as described in the link below.


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:
DATE       MARKET      AVERAGE RATE
021209 PRO 269.34
021209 BAR 349.98
021209 HSE 0.00
021209 COMP 0.00

Post #675425
Posted Friday, March 13, 2009 10:12 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 12:32 PM
Points: 4,004, Visits: 6,064
There 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.


declare @sample table (BEGIN_DATE  datetime,  MARKET_CODE  varchar(5), RATE_AMOUNT numeric(5,2))
insert into @sample

SELECT '12-MAR-2009', 'ADS', 187.5
UNION ALL SELECT '12-MAR-2009', 'COMP', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PSBY', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'CONT', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'ADS', 199
UNION ALL SELECT '12-MAR-2009', 'CONT', 199
UNION ALL SELECT '12-MAR-2009', 'ADS', 139.3
UNION ALL SELECT '12-MAR-2009', 'ADS', 141
UNION ALL SELECT '12-MAR-2009', 'ADS', 141
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'ADS', 167.14
UNION ALL SELECT '12-MAR-2009', 'COMP', 0
UNION ALL SELECT '12-MAR-2009', 'NLRA', 265
UNION ALL SELECT '12-MAR-2009', 'ADS', 172.5
UNION ALL SELECT '12-MAR-2009', 'PKGS', 199.99
UNION ALL SELECT '12-MAR-2009', 'HSE', 199
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'BAR', 330
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 250
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 199
UNION ALL SELECT '12-MAR-2009', 'HSE', 0
UNION ALL SELECT '12-MAR-2009', 'COMP', 0
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 230
UNION ALL SELECT '12-MAR-2009', 'PRO', 330
UNION ALL SELECT '12-MAR-2009', 'PRO', 249


select begin_date,market_code,sum(rate_amount) as sumRate,cast(avg(rate_amount) as numeric(5,2)) as avgRate
from @sample
group by begin_date,market_code



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #675770
Posted Sunday, March 15, 2009 1:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 13, 2010 2:54 PM
Points: 25, Visits: 107
Thank you, much cleaner this way. The data still comes back for each row containing that market code for the given date.


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"?




Post #676167
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse