pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDesign Problem...
2+ messages / 2 participants
[nested] [flat]
* Design Problem...
@ 2004-04-22 15:09 Ryan Riehle <rkr@buildways.com>
0 siblings, 1 reply; 2+ messages in thread
From: Ryan Riehle @ 2004-04-22 15:09 UTC (permalink / raw)
To: pgsql-sql
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: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of ogjunk-pgjedan@yahoo.com
Sent: Thursday, April 22, 2004 5:57 AM
To: pgsql-sql@postgresql.org
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 <ebacon@onesystem.com> 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
>
> ogjunk-pgjedan@yahoo.com 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
> majordomo@postgresql.org)
> >
> >
>
> --
> Edmund Bacon <ebacon@onesystem.com>
>
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Design Problem...
@ 2004-04-24 16:54 Greg Sabino Mullane <greg@turnstep.com>
parent: Ryan Riehle <rkr@buildways.com>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2004-04-24 16:54 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> 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.
First, you will get more responses if you do not create a new topic in
the middle of an existing thread. Here is a simplified answer to your problem.
Basically, you need to create a link between a business and a service,
and note when that link was created. You can grab the highest creation
time for a service to see which business currently owns it. Depending on
how often things change around, you may want to simply have a trigger on
the bs_map table that updates a "business" column in the services table,
rather than having to compute the max creation time constantly.
CREATE TABLE business (
id SERIAL UNIQUE,
bname VARCHAR
);
CREATE TABLE service (
id SERIAL UNIQUE,
sname VARCHAR
);
CREATE TABLE bs_map (
business INTEGER NOT NULL,
service INTEGER NOT NULL,
assigned TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_business_fk"
FOREIGN KEY (business) REFERENCES business(id)
ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE bs_map ADD CONSTRAINT "bs_map_service_fk"
FOREIGN KEY (service) REFERENCES service(id)
ON DELETE RESTRICT ON UPDATE CASCADE;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404241255
-----BEGIN PGP SIGNATURE-----
iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ
MTilGUtbg0y4DOAENUzXc80=
=Jw5D
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-04-24 16:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-22 15:09 Design Problem... Ryan Riehle <rkr@buildways.com>
2004-04-24 16:54 ` Greg Sabino Mullane <greg@turnstep.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox