By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,591 Members | 650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,591 IT Pros & Developers. It's quick & easy.

Comparing the same attribute with two different date ranges

P: 1
I am trying to develop a sales report coming off our postgresql server and am rumming into some problems. Basically I just want to group by the customer name and view a customers orders and sales from the previous month compared to the current month. I know in Access I could use an IIF command, but it isn't supported in Postgres. I think I need to use the CASE WITH function but am not sure how to use it properly. The SQL code I am working with is:

Expand|Select|Wrap|Line Numbers
  1. SELECT customer.name AS "Company", customer.businessnumber AS "RRID", customer.dcp_elec_price AS "DCP Pricing", customer.dcl_elec_price AS "DCL Pricing", 
  2. Count(IIf(orderitems.order_date BETWEEN '2009-06-01' AND '2009-06-17', orderitems.customer_id)) AS "Prev Month Total Orders",
  3. Count(IIf(orderitems.order_date BETWEEN '2009-07-01' AND '2009-07-17', orderitems.customer_id)) AS "Curr Month Total Orders",
  4. Avg(IIf(orderitems.order_date BETWEEN '2009-06-01' AND '2009-06-17', orderitems.sellprice)) AS "Prev Month Avg Sale Price",
  5. Avg(IIf(orderitems.order_date BETWEEN '2009-07-01' AND '2009-07-17', orderitems.sellprice)) AS "Curr Month Avg Sale Price",
  6. Sum(IIf(orderitems.order_date BETWEEN '2009-06-01' AND '2009-06-17', orderitems.sellprice)) AS "Prev Total Sales ($)",
  7. Sum(IIf(orderitems.order_date BETWEEN '2009-07-01' AND '2009-07-17', orderitems.sellprice)) AS "Curr Month Total Sales ($)"
  8. FROM customer, orderitems
  9. WHERE customer.id = orderitems.customer_id
  10. GROUP BY customer.name, customer.businessnumber, customer.dcp_elec_price, customer. dcl_elec_price
  11. ORDER BY customer.name
  12.  
Also I thought maybe I need to join the order items table twice with an alias and use two different date ranges, however, doing this caused my aggregates to group incorrectly and produced inaccurate results.

I'm new the the group, but thanks in advance for the help.
Jul 21 '09 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 700
What postgres version do you use?
Feb 21 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.