[SQL] Dividing results from two tables with different time frames
I hope I can explain this clearly. I have two queries I’m running in a report. The first one is: select dr.store_id, store.suffix, store.sort_id, year as data_year, (dr.layaway_starting_balance + dr.layaway_net_change) as layaway_balance, (dr.loan_starting_balance + dr.loan_net_change) as loan_balance, dr.inventory_starting_balance + inventory_net_change as inventory, (dr.loan_starting_number + dr.loan_number_change) as number_loan, (dr.loan_starting_balance + dr.loan_net_change) /(dr.loan_starting_number + dr.loan_number_change) as loan_balance_avg from daily_runbalance dr join store on (dr.store_id = store.store_id) where dr.date = '2006-06-30' and dr.store_id = 4 and store.store_id = 4 The second is very long so I’ll just post the relevant pieces. select dr.store_id, store.short_name, store.sort_id, ds.year as data_year, sum(ds.pulled_loan_total) as loan_pulls, sum(ds.renew_loan_amount) as loan_renewals, from daily_runbalance dr join daily_summary ds on (dr.store_id = ds.store_id and dr.date = ds.date) join cash on (dr.store_id = cash.store_id and dr.date = cash.date) join store on (dr.store_id = store.store_id) where dr.date between '2006-04-01' and '2006-06-30' and dr.store_id = 4 group by dr.store_id, store.sort_id, store.short_name, ds.year As you can see the two queries have different time frames. The first one has one date, the second one has a range of dates. What I’m trying to accomplish is to get two percentages. Both have one element from one table divided by an element in the other table. sum(ds.pulled_loan_total)/sum(dr.loan_starting_balance + dr.loan_net_change)*100 as pulls_percent, and (sum(ds.renew_loan_amount)/sum(dr.loan_starting_balance + dr.loan_net_change))*100 as renew_percent, No matter which query I place them in it gives me the wrong data because of the time frames. How can I get the correct data? Thanks Becky Hoff IT Specialist
[SQL] nested select within a DISTINCT block
Hi, here i have a problem with this task... I have a table "cancellation" which stores cancelled bookings and details of charges etc and a table "bookings" which stores details of bookings, for example: cancellation(cancellation_id, booking_id, charge) booking(booking_id, customer_id, product_package_id, details) in the query, i wish to find, how many customers have booked for each product_package_id. if there were 3 bookings for product_package_id=1, and all these are cancelled and therefore exist in cancellation, then the query result shoud display something like package_id, #of bookings 1 0 here are what i tried select distinct b.product_package_id, count (distinct b.customer_id and not exists (select cc from cancellation cc where cc.booking_id=b.booking_id)) from booking as b group by b.product_package_id and it doesnt work. the syntax within the DISTINCT is wrong, unsurprisingly. i also tried select distinct b.product_package_id, count (distinct b.customer_id not in (select cc from cancellation cc where cc.booking_id=b.booking_id)) from booking as b group by b.product_package_id it produced incorrect result. ie, for those canceled bookings are also counted, producing package_id, #of bookings 1 3 which supposed to be package_id, #of bookings 1 0 could anyone give any hints please, many thanks ! -- View this message in context: http://www.nabble.com/nested-select-within-a-DISTINCT-block-tf2271181.html#a6304397 Sent from the PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] hi i am gettin error when i am deleting a function from my pgadmin
On 9/11/06, Penchalaiah P. <[EMAIL PROTECTED]> wrote: Hi good morning to all…. I created some functions in my pgadmin… when I am deleting those functions from that pgadmin its giving error…i.e An ERROR has occurred ERROR:function function_name1(character varying, character varying, character varying, date, character varying) does not exist… May I know the reason y its not deleting….What schema is the function in? Perhaps you need to specify the schema name in your drop statement. It helps if you supply the exact command you are typing in.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
