Our business has multiple cost/profit centers that I call business units, these are in a table called buinessunits. We also have a table that holds a list of services that are offerred by a business. Each business unit has many services it offers; 1 businees unit => Many Services. We want to be able to query the cost/revenue/profit generated by each business unit and by each service. The problem is that it is possible that the service can be switched to a different business unit, and then possibly back to the original later on. I've looked at multiple configurations, but have not found a design that I feel is good so far. Need to somehow track when a particular service was associated with various businessunits. If you want more info, I can publish the tables for you. This same type of problem exists in at least two other areas of this database we are developing. Please help.
Kind Regards, -Ryan -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Thursday, April 22, 2004 5:57 AM To: [EMAIL PROTECTED] Subject: Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble Hello, I'd love to be able to do that, but I cannot just ORDER BY uu.add_date, because I do not have uu.add_date in the SELECT part of the statement. The reason I don't have it there is because I need distinct YYYY MM DD values back. Is there a trick that I could use to make this more elegant? Thanks, Otis --- Edmund Bacon <[EMAIL PROTECTED]> wrote: > Is there some reason you can't do this: > > SELECT DISTINCT > date_part('year', uu.add_date), date_part('month', uu.add_date), > date_part('day', uu.add_date) > > FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON > ui.id=uu.user_id > WHERE uus.x_id=1 > > ORDER BY > uu.add_date DESC; > > This might be faster, as you only have to sort on one field, and I > think it should give the desired results > > [EMAIL PROTECTED] wrote: > > >Hello, > > > >I am trying to select distinct dates and order them in the reverse > >chronological order. Although the column type is TIMESTAMP, in this > >case I want only YYYY, MM, and DD back. > > > >I am using the following query, but it's not returning dates back in > >the reverse chronological order: > > > >SELECT DISTINCT > > date_part('year', uu.add_date), date_part('month', uu.add_date), > > date_part('day', uu.add_date) > > > >FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON > >ui.id=uu.user_id WHERE uus.x_id=1 > > > >ORDER BY > >date_part('year', uu.add_date), date_part('month', uu.add_date), > >date_part('day', uu.add_date) DESC; > > > > > >This is what the above query returns: > > > > date_part | date_part | date_part > >-----------+-----------+----------- > > 2004 | 2 | 6 > > 2004 | 4 | 20 > >(2 rows) > > > > > >I am trying to get back something like this: > >2004 4 20 > >2004 4 19 > >2004 2 6 > >... > > > >My query is obviously wrong, but I can't see the mistake. I was > >wondering if anyone else can see it. Just changing DESC to ASC, did > >not work. > > > >Thank you! > >Otis > > > > > >---------------------------(end of > broadcast)--------------------------- > >TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > > > > > > -- > Edmund Bacon <[EMAIL PROTECTED]> > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
